pg_dump & pg_restore password using python module

Question

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

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

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:

('', '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')*

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!

Regards, D.

Answer 1

The following works and the changes made are commented.

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

Answer 2

You can use environment variables https://www.postgresql.org/docs/11/libpq-envars.html and "--no-password" option for 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()