Skip to content

Instantly share code, notes, and snippets.

@hzbd
Created November 10, 2013 06:16
Show Gist options
  • Save hzbd/7394525 to your computer and use it in GitHub Desktop.
Save hzbd/7394525 to your computer and use it in GitHub Desktop.
PostgreSQL database backup script (Python recipe)
#!/usr/bin/env python
import os
import time
username = 'root'
defaultdb = 'postgres'
port = '5433'
backupdir='/www/backup/'
date = time.strftime('%Y-%m-%d')
#GET DB NAMES
get_db_names="psql -U%s -d%s -p%s --tuples-only -c '\l' | awk -F\| '{ print $1 }' | grep -E -v '(template0|template1|^$)'" % (username, defaultdb, port)
#MAKE BACKUP OF SYSTEMGRANTS
os.popen("pg_dumpall -p%s -g|gzip -9 -c > %s/system.%s.gz" % (port, backupdir, date))
#MAKING DB BACKUP
for base in os.popen(get_db_names).readlines():
base = base.strip()
fulldir = backupdir + base
if not os.path.exists(fulldir):
os.mkdir(fulldir)
filename = "%s/%s-%s.sql" % (fulldir, base, date)
os.popen("nice -n 19 pg_dump -C -F c -U%s -p%s %s > %s" % (username, port, base, filename))
@Sidhartha03
Copy link

I am getting "psql: FATAL: Peer authentication failed for user "username". How to add the password field to the script?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment