pg_dump& pg_restore使用python模块子进程的密码 [英] pg_dump & pg_restore password using python module subprocess

问题描述

问题:在Python脚本中使用PSQL pg_dump和pg_restore并使用subprocess模块.

Problem: Use the PSQL pg_dump and pg_restore in a Python script and using the subprocess module.

背景:我正在使用来自本地主机(即Ubuntu 14.04.5 LTS)的以下python 2.7脚本在PSQL服务器(即PostgreSQL 9.4.11)中创建表的备份并进行还原进入新版本的PSQL服务器(例如PostgreSQL 9.6.2)中的远程主机(例如Ubuntu 16.04.2 LTS).

Background: I am using the following python 2.7 script from the localhost (i.e. Ubuntu 14.04.5 LTS) to create a backup of a table in a PSQL server (i.e. PostgreSQL 9.4.11) and restore it into the remote host (i.e. Ubuntu 16.04.2 LTS) in a newer version of PSQL server (i.e. 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()函数返回以下错误:

When I use the functions sequentially as above the dump_table() function finishes successfully and creates the /tmp/table.sql file but the restore_table() function returns the following error:

(''，'密码:\ npg_restore:与数据库的[存档(db)]连接 "database_name"失败:致命:用户的密码身份验证失败 用户名" \ nFATAL:用户的密码身份验证失败 用户名" \ n')*

('', 'Password: \npg_restore: [archiver (db)] connection to database "database_name" failed: FATAL: password authentication failed for user "username"\nFATAL: password authentication failed for user "username"\n')*

我已经检查了凭据&通过在外壳程序中执行pg_restore的命令来输出输出，并且我还包括了.pgpass的凭据(尽管不相关，因为我在p.communicate()中传递了密码)

I have checked the credentials & outputs by executing the commands for pg_restore in the shell and I have also included the credentials to .pgpass (although not relevant since I am passing the password in p.communicate())

有人有类似经历吗?我几乎被困住了！

Anyone had similar experience? I am pretty much stuck!

关于， D.

推荐答案

对以下作品和所做的更改进行了评论.

The following works and the changes made are commented.

我不确定在使用完整命令(即未在列表中拆分)并在Popen中使用shell=True时，为什么pg_restore会产生该密码身份验证错误，但是另一方面，pg_dump可以工作可以使用shell=True&完整的命令. <是否需要做任何事情?

I am not sure though why the pg_restore produces that password authentication error when using the full command (i.e. not split in the list) and using shell=True in Popen, but pg_dump on the other hand works fine using shell=True & the full command. Does < have to do anything with it?

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

这篇关于pg_dump& pg_restore使用python模块子进程的密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！