使用Python模块Subprocess来设置pg_dump和pg_restore的密码

Question

问题：在Python脚本中使用PSQL的pg_dump和pg_restore命令，并使用subprocess模块。

背景：我正在使用以下来自本地主机（即Ubuntu 14.04.5 LTS）的python 2.7脚本，创建一个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()函数返回以下错误信息：

('', 'Password: \npg_restore: [archiver (db)]连接到数据库“database_name”失败：FATAL：用户“username”的密码身份验证失败\nFATAL：用户“username”的密码身份验证失败\n')*

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

有人有类似的经历吗？我真的卡住了！

问候， D.

- Dimitris

Answer 1

以下操作成功，并进行了注释。

虽然我不确定为什么在使用完整命令（即未在列表中拆分）并在Popen中使用shell=True时，pg_restore会产生密码认证错误，但另一方面，pg_dump在使用shell=True和完整命令时却可以正常工作。这是否与 < 有关呢？

#!/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()

- Dimitris

Answer 2

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

    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()

- alexfv

---

在使用（Python 3.7.4）的subprocess.run(...)时，我遇到了困难，无法使用input=关键字。没有使用shell=True关键字，但是使用了env=就解决了问题。这可能与某些奇怪的_tty_相关问题有关。 - Dimitri Hautot

Answer 3

这是一个用于获取Postgres数据库转储并恢复到新数据库的Python脚本。

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) )

- Sarath Chandran K

---

请注意，在pg_restore命令中必须提供“--format=custom”才能正常工作。 - tree em

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。

- tsveti_iko