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.
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()
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()
Here is a python script for taking postgres dump and restore it to a new database.
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) )
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With