Skip to content

Instantly share code, notes, and snippets.

@reinbach
Created Mar 7, 2012
Embed
What would you like to do?
Python script to backup PostgreSQL databases
#!/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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment