Skip to content
Advertisement

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.

Advertisement

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
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement