postgresql tips

psql命令行免密码

在 Shell 命令行中，使用 postgresql-client 连接 PostgreSQL 数据库时，每次都要输入密码。如果要写 Shell Script，做一些类似于备份的自动化管理工作，每次都还要手动输入密码是一件让人很不爽的事情，也没有实现真正意义上的自动化。

何如在连接 PostgreSQL 数据库时，不用手动输入密码，官方利用密码文件(Password File)提供了一种解决方案：

Unix/Linux 在用户家目录创建一个 .pgpass 隐藏文件或者是创建一个环境变量 PGPASSFILE 引用一个含有密码信息的文件(文件内容同 .pgpass 文件，此处文件名可随意，隐不隐藏都行)，Windows 对应的是 %APPDATA%\postgresql\pgpass.conf 文件( %APPDATA% 是用户配置里面的 Application Data 子目录)。

该文件记录着连接数据库需要的所有信息，按下面数据格式，每行一条记录信息：

hostname:port:database:username:password
该文件中可以有注释内容，注释符号是 # 。

前四个字段可以是确定的字面值，也可以使用通配符 * 匹配所有。

连接数据库的时候，系统自动从前到后遍历该文件，使用最先匹配到的记录，因此，当你在文件中使用了通配符 * 的时候，应该优先把最具体的信息放在文件的最前面。

如果记录信息中包含 : 或 \ 这两个符号，需要对其进行转义，就是在字符前面加转义符号 \ 。

在 Unix/Linux 系统中 .pgpass 文件权限只能对用户自己开放，不能对用户组和其他人开放，就是说 .pgpass 文件对用户组和其他人是"不可读，不可写，不可执行"的，通常会赋予 .pgpass 文件权限 600 ，命令如下：

chmod 0600 ~/.pgpass
如果任何一个权限对用户组或其他人开放了，该文件就会被忽略。

利用环境变量 PGPASSFILE 引用的文件权限也要满足这个要求，否则同样会被忽略。

在 Windows 中，会假设该文件存放在一个安全的目录中，所有没有对其权限进行特别的检查。

至此，数据库对应的 .pgpass 文件设置完成，以后再连接数据库就不需要手动输入密码了，Shell Script 也能够实现真正意义上的自动化管理。

psql -h 10.150.10.150 -p 5432 -U postgres -d dbname – 到.pgpass文件中寻找匹配信息，如果没有，依然需要手动输入密码

数据备份

默认情况下postgresql不支持命令行中有密码，有时为了使用命令行定时备份，就需要workaround

• 配置环境变量
  具体方法见 PostgreSQL 自动输入密码

• 使用db url
  pg_dump.exe -Fc -v --dbname=postgresql://username:pass@127.0.0.1:5432/dbname -f a.bak

• 使用脚本

@ECHO OFF
@setlocal enableextensions
@cd /d "%~dp0"
 
set PGPASSWORD=12334
SET PGPATH="E:\Program Files\PostgreSQL\12\bin\"
SET SVPATH=e:\db-backup\
SET PRJDB=postgres
set d=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
 
SET DBDUMP=%PRJDB%_%d%.bak
@ECHO OFF
%PGPATH%pg_dump -Fc -v -n reporting -n scada -h 127.0.0.1 -U user -d  %PRJDB%  -f %SVPATH%%DBDUMP%

echo Backup Taken Complete %SVPATH%%DBDUMP%

forfiles /p %SVPATH% /d /m *.bak -5 /c "cmd /c echo deleting @file ... && del /f @path"

pgadmin去除master密码

在 #PGADMIN4#/v5/web/目录新建文件config_local.py
输入以下内容即可

MASTER_PASSWORD_REQUIRED=False

postgresql命令行(psql,pg_dump)密码输入的方法

默认情况下postgresql不支持命令行中有密码，有时为了使用命令行定时备份，就需要自动读取密码

psql

• 使用.pgpass(linux)和pgpass.conf(win) 来存储密码, 参考PostgreSQL 自动输入密码
• 使用环境变量PGPASSWORD
• psql -c “\dt” “host=127.0.0.1 user=hello password=123 dbname=test”

pg_dump

• 使用db url pg_dump.exe -Fc -v --dbname=postgresql://username:pass@127.0.0.1:5432/dbname -f a.bak
• 使用脚本 postgresql备份

@ECHO OFF
@setlocal enableextensions
@cd /d "%~dp0"
 
set PGPASSWORD=12334
SET PGPATH="E:\Program Files\PostgreSQL\12\bin\"
SET SVPATH=e:\db-backup\
SET PRJDB=postgres
set d=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
 
SET DBDUMP=%PRJDB%_%d%.bak
@ECHO OFF
%PGPATH%pg_dump -Fc -v -n reporting -n scada -h 127.0.0.1 -U user -d  %PRJDB%  -f %SVPATH%%DBDUMP%

echo Backup Taken Complete %SVPATH%%DBDUMP%

forfiles /p %SVPATH% /d /m *.bak -5 /c "cmd /c echo deleting @file ... && del /f @path"