Postgresql Database Backup Using Python

Question

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 1

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

Answer 2

If your OS is Linux, you can use the code below. First, you should run apt-get install postgresql.

def create_essentials():
    yaml_file = open("settings.yaml", 'r')
    settings = yaml.load(yaml_file)
    db_name = settings["db_name"]
    db_user = settings["db_user"]
    db_password = settings["db_password"]
    db_host = settings["db_host"]
    db_port = settings["db_port"]
    backup_path = settings["backup_path"]
    filename = settings["filename"]
    filename = filename + "-" + time.strftime("%Y%m%d") + ".backup"
    command_str = str(db_host)+" -p "+str(db_port)+" -d "+db_name+" -U "+db_user
    return command_str, backup_path, filename


def backup_database(table_names=None):
    command_str,backup_path,filename = create_essentials()
    command_str = "pg_dump -h "+command_str

    if table_names is not None:
        for x in table_names:
            command_str = command_str +" -t "+x

    command_str = command_str + " -F c -b -v -f '"+backup_path+"/"+filename+"'"
    try:
        os.system(command_str)
        print "Backup completed"
    except Exception as e:
        print "!!Problem occured!!"
        print e

def restore_database(table_names=None):
    command_str,backup_path,filename = create_essentials()
    command_str = "pg_restore -h "+command_str

    if table_names is not None:
        for x in table_names:
            command_str = command_str +" -t "+x

    command_str = command_str + " -v '"+backup_path+"/"+filename+"'"
    
    try:
        os.system(command_str)
        print "Restore completed"
    except Exception as e:
        print "!!Problem occured!!"
        print e

Answer 3

The first idea that comes to my mind is to dump your tables calling pg_dump command, similar to the approach presented here (but google is plenty of alternatives).

However, since your backup strategy requires you to select precise dates and not only tables, you will probably have to rely on a sequence of queries, and then my advise is to use a library like Psycopg.

EDIT:

I cannot provide a complete example since I don't know:

• which tables do you want to dump
• what is the precise backup strategy for each table (i.e. the SELECT statement)
• how you want to restore them. By deleting the table and then re-creating it, by overwriting db rows basing on an ID attribute, ...

the following example generates a file that stores the result of a single query.

import psycopg

conn = psycopg2.connect("dbname=test user=postgres")  # change this according to your RDBMS configuration
cursor = conn.cursor()

table_name='YOUR_TABLE_HERE'  # place your table name here
with open("table_dump.sql") as f:
    cursor.execute("SELECT * FROM %s" % (table_name))  # change the query according to your needs
    column_names = []
    columns_descr = cursor.description
    for c in columns_descr:
        column_names.append(c[0])
    insert_prefix = 'INSERT INTO %s (%s) VALUES ' % (table_name, ', '.join(column_names))
    rows = cursor.fetchall()
    for row in rows:
    row_data = []
        for rd in row:
            if rd is None:
                row_data.append('NULL')
            elif isinstance(rd, datetime.datetime):
                row_data.append("'%s'" % (rd.strftime('%Y-%m-%d %H:%M:%S') ))
            else:
                row_data.append(repr(rd))
    f.write('%s (%s);\n' % (insert_prefix, ', '.join(row_data)))  # this is the text that will be put in the SQL file. You can change it if you wish.

Answer 4

1. first export the password of postgres using below command os.environ["PGPASSWORD"]='{}'.format(database_password) Note: database_password is variable to which i have assigned a password value
2. change the working dir path to \bin folder os.chdir(r'C:/Program Files/PostgreSQL/15/bin')
3. create dump command command = 'pgdump -h {0} -U {1} -p 5432 -d{2}'.format(host,user,dbname)
4. call subprocess.Popen p=subprocess.Popen(command,shell=True)
5. issue wait command till process complete p.wait()