Python script to backup PostgreSQL databases

psql_backup.py

#!/usr/bin/env python
#
# script backs up a list of databases to a specified dir
#
# for the pg_dump call if a password is required, make use
# of a .pgpass file located in the user's home dir that
# is running this script
# see http://www.postgresql.org/docs/8.4/static/libpq-pgpass.html
#
import datetime
import subprocess
import psycopg2
import sys

EXCLUDE_DATABASES = ('template1', 'template0', 'postgres')
DATABASE_HOST = "localhost"
DATABASE_USER = ""
DATABASE_PASS = ""
BACKUP_DIR = "/tmp/"

# get list of databases to backup
# run pg_dump to backup databases to backup dir

def get_db_conn():
    conn_string = "host='{host}' user='{user}' password='{password}'".format(
        host=DATABASE_HOST,
        user=DATABASE_USER,
        password=DATABASE_PASS
    )
    try:
        conn = psycopg2.connect(conn_string)
        cursor = conn.cursor()
        return cursor
    except:
        exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
        sys.exit("Database connection failed!\n ->{error}".format(
            error=exceptionValue
        ))

def main():
    cursor = get_db_conn()
    cursor.execute("SELECT datname FROM pg_database")
    results = cursor.fetchall()
    for database in results:
        if database[0] not in EXCLUDE_DATABASES:
            try:
                backup_file = "{backup_dir}{database}{version}.sql".format(
                    database=database[0],
                    backup_dir=BACKUP_DIR,
                    version=datetime.date.today().weekday()
                )
                subprocess.call("pg_dump -U {user} {database} > {backup_file}".format(
                    user=DATABASE_USER,
                    database=database[0],
                    backup_file=backup_file
                ), shell=True)
            except:
                exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
                sys.exit("Database backup failed for {database}!\n ->{error}".format(
                    database=database[0],
                    error=exceptionValue
                ))
            print("{database} -> {backup_file}".format(
                database=database[0],
                backup_file=backup_file
            ))
    

    
if __name__ == "__main__":
    main()