使用 python 模块子进程pg_dump和pg_restore密码

Question

问题：在 Python 脚本中使用 PSQLpg_dump和pg_restore并使用subprocess模块。

背景：我正在使用来自本地主机的以下python 2.7脚本(即Ubuntu 14.04.5 LTS) 在 PSQL 服务器中创建表的备份(即PostgreSQL 9.4.11)并将其恢复到远程主机(即Ubuntu 16.04.2 LTS) 在较新版本的 PSQL 服务器(即PostgreSQL 9.6.2)。

#!/usr/bin/python
from subprocess import PIPE,Popen
def dump_table(host_name,database_name,user_name,database_password,table_name):
command = 'pg_dump -h {0} -d {1} -U {2} -p 5432 -t public.{3} -Fc -f /tmp/table.dmp'
.format(host_name,database_name,user_name,table_name)
p = Popen(command,shell=True,stdin=PIPE)
return p.communicate('{}n'.format(database_password))
def restore_table(host_name,database_name,user_name,database_password):
command = 'pg_restore -h {0} -d {1} -U {2} < /tmp/table.dmp'
.format(host_name,database_name,user_name)
p = Popen(command,shell=True,stdin=PIPE)
return p.communicate('{}n'.format(database_password))
def main():
dump_table('localhost','testdb','user_name','passwd','test_tbl')
restore_table('remotehost','new_db','user_name','passwd')
if __name__ == "__main__":
main()

当我按上述顺序使用这些函数时，dump_table()函数成功完成并创建/tmp/table.sql文件，但restore_table()函数返回以下错误：

(''， '密码： pg_restore： [存档器 (db)] 连接到数据库 "database_name"失败：致命：用户的密码身份验证失败 "用户名"致命： 用户的密码身份验证失败 "用户名"')*

我已经通过在shell中执行pg_restore命令来检查凭据和输出，并且我还将凭据包含在.pgpass中(尽管不相关，因为我正在传递密码p.communicate()

有人有类似的经历吗？我几乎被卡住了！

问候 D.

Answer 1

以下作品和所做的更改进行了注释。

我不确定为什么pg_restore在使用完整命令(即不在列表中拆分)并在Popen中使用shell=True时会产生密码身份验证错误，但另一方面，pg_dump使用shell=True和 full 命令工作正常。<必须用它做什么吗？

#!/usr/bin/python
from subprocess import PIPE,Popen
import shlex
def dump_table(host_name,database_name,user_name,database_password,table_name):
command = 'pg_dump -h {0} -d {1} -U {2} -p 5432 -t public.{3} -Fc -f /tmp/table.dmp'
.format(host_name,database_name,user_name,table_name)
p = Popen(command,shell=True,stdin=PIPE,stdout=PIPE,stderr=PIPE)
return p.communicate('{}n'.format(database_password))
def restore_table(host_name,database_name,user_name,database_password):
#Remove the '<' from the pg_restore command.
command = 'pg_restore -h {0} -d {1} -U {2} /tmp/table.dmp'
.format(host_name,database_name,user_name)
#Use shlex to use a list of parameters in Popen instead of using the
#command as is.
command = shlex.split(command)
#Let the shell out of this (i.e. shell=False)
p = Popen(command,shell=False,stdin=PIPE,stdout=PIPE,stderr=PIPE)
return p.communicate('{}n'.format(database_password))
def main():
dump_table('localhost','testdb','user_name','passwd','test_tbl')
restore_table('localhost','testdb','user_name','passwd')
if __name__ == "__main__":
main()

Answer 2

您可以使用环境变量 https://www.postgresql.org/docs/11/libpq-envars.html 和"--no-password"选项进行pg_dump。

def dump_schema(host, dbname, user, password, **kwargs):
command = f'pg_dump --host={host} ' 
f'--dbname={dbname} ' 
f'--username={user} ' 
f'--no-password ' 
f'--format=c ' 
f'--file=/tmp/schema.dmp '
proc = Popen(command, shell=True, env={
'PGPASSWORD': password
})
proc.wait()

Answer 3

这是一个 python 脚本，用于获取 postgres 转储并将其还原到新数据库。

import subprocess
DB_NAME = 'PrimaryDB'  # your db name
DB_USER = 'postgres' # you db user
DB_HOST = "localhost"
DB_PASSWORD = 'sarath1996'# your db password
dump_success = 1
print ('Backing up %s database ' % (DB_NAME))
command_for_dumping = f'pg_dump --host={DB_HOST} ' 
f'--dbname={DB_NAME} ' 
f'--username={DB_USER} ' 
f'--no-password ' 
f'--file=backup.dmp '
try:
proc = subprocess.Popen(command, shell=True, env={
'PGPASSWORD': DB_PASSWORD
})
proc.wait()
except Exception as e:
dump_success = 0
print('Exception happened during dump %s' %(e))

if dump_success:
print('db dump successfull')
print(' restoring to a new database database')
"""database to restore dump must be created with 
the same user as of previous db (in my case user is 'postgres'). 
i have #created a db called ReplicaDB. no need of tables inside. 
restore process will #create tables with data.
"""
backup_file = '/home/Downloads/BlogTemplate/BlogTemplate/backup.dmp' 
"""give absolute path of your dump file. This script will create the backup.dmp in the same directory from which u are running the script """

if not dump_success:
print('dump unsucessfull. retsore not possible')
else:
try:
process = subprocess.Popen(
['pg_restore',
'--no-owner',
'--dbname=postgresql://{}:{}@{}:{}/{}'.format('postgres',#db user
 'sarath1996', #db password
 'localhost',  #db host
 '5432', 'ReplicaDB'), #db port ,#db name
'-v',
backup_file],
stdout=subprocess.PIPE
)
output = process.communicate()[0]
except Exception as e:
print('Exception during restore %e' %(e) )

Answer 4

您可以尝试使用dbname选项，如下所示：

def backup():
command = 'pg_dump --dbname=postgresql://{}:{}@{}:{}/{} > {}/schema.dmp'.format(DB_USER, DB_PASS, DB_HOST, DB_PORT, DB_NAME, BACKUP_DIR)
try:
proc = subprocess.Popen(command, shell=True, env={
'PGPASSWORD': DB_PASS
})
proc.wait()
print('Backup successful')
except Exception as e:
print('Exception happened during backup: %s' % (e))

注意：这也适用于备份 Docker 容器中的 postgres Airflow