Skip to content

Automated backup and restore postgres database – different server – using python

August 21, 2012

we are using dump and restore commands  for DB backup and restoring

below code is used to backup db from one server to restore another server

import os
import pxssh
import pexpect

#DB backup local in local folder
#below line excute dump command syntax(pg_dump -U {user-name} {source_db} -f {dumpfilename.sql})
#change ur appropiate names

os.system('pg_dump -U openerp test_001 -f dump_test.backup')
print "backup_successfull"

######################################################################

#backup file send to server
#scp(secured copy) syntax "scp 'source-path' 'destination-path'"---change source-path and destination-path

child = pexpect.spawn('scp /home/usr_name/Desktop/dump_test.backup  usr_name@123.123.123.123:///home/usr_name')
child.expect('.*password:*')

#change password in below line
child.sendline('secret_passwd')
child.expect(pexpect.EOF)
#####################################################################

#Create a server login and restore DB
s = pxssh.pxssh()
#change ur hostID, username, password
if not s.login ('123.123.123.111', 'username', 'secret_passwd'):
    print "SSH session failed on login."
    print str(s)
else:
    print "SSH session login successful in 123.123.123.111"
   #below line excute restore command syntax(psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}})
   s.sendline('psql -U db_user -d dump_ssh -f dump_test.backup')
   s.prompt()
   print s.before     # print everything before the prompt.
   s.logout()

#end of code
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: