Postgresql Database Backup Using Python

I would like to backup database using Python code. I want to backup some tables of related data. How to backup and how to choose desired tables using “SELECT” statement?

e.g.

I want to get data from 2014-05-01 to 2014-05-10 of some tables and output this result as .sql extension file

How can I get this format using python code? If you don’t mind, please explain. Thanks.

Answer

Use psycopg2 to establish the data connection. There are quite a few examples in the documentation:

http://initd.org/psycopg/

Once you have your data source configured, iterate through the results of your “SELECT” statement building a INSERT INTO statement by printing the result set to a file. Basically some reverse logic.

That way, if the time comes and you need to use your backup file, you simply run the SQL file which inserts the data back in…

Example:

import psycopg2
import sys


con = None

try:

    con = psycopg2.connect(database='local', user='local', password='local',port='1970')
    cur = con.cursor()
    cur.execute('SELECT x FROM t')
    f = open('test.sql', 'w')
    for row in cur:
        f.write("insert into t values (" + str(row) + ");")
except psycopg2.DatabaseError, e:
    print 'Error %s' % e
    sys.exit(1)
finally:
    if con:
        con.close()

Then to restore:

psql <dbname> <username> < test.sql