postgres_backup.py

#!/usr/bin/env python
#mostly based off https://gist.github.com/sconts/7394525
# tested on ubuntu 16.04 with python 3.6, postgresql server and client ver 9.6
import argparse
import subprocess
import os
import sys
import time 

def parse_args():
    parser = argparse.ArgumentParser(
        formatter_class=argparse.ArgumentDefaultsHelpFormatter,
        description='backup all databases from postgres')

    parser.add_argument('--username', action='store',
                        dest='db_username', default='postgres', help='postgres username')
    parser.add_argument('--password', action='store',
                        dest='db_password', default='password', help='postgres password')
    parser.add_argument('--host', action='store',
                        dest='db_host', default='localhost', help='postgres host')
    parser.add_argument('--port', action='store',
                        dest='db_port', default='5432', help='postgres port')
    parser.add_argument('--backupdir', action='store',
                        dest='db_backupdir', default='/nfs/backups/pgdb', help='postgres backup folder')
    parser.add_argument('--defaultdb', action='store',
                        dest='default_db', default='postgres', help='default postgres database')

    return parser.parse_args()


if __name__ == '__main__':

    cmd_args = parse_args()

    db_username = cmd_args.db_username
    db_password = cmd_args.db_password
    default_db = cmd_args.default_db
    db_host = cmd_args.db_host
    db_port = cmd_args.db_port
    db_backupdir = cmd_args.db_backupdir
    date = time.strftime('%Y-%m-%d')

    os.putenv('PGPASSWORD', db_password)

    if not os.path.exists(db_backupdir):
            os.mkdir(db_backupdir)

    #GET DB NAMES
    get_db_names = "psql -U%s -d%s -h%s -p%s --tuples-only -c '\l' | awk -F\| '{ print $1 }' | grep -E -v '(template0|template1|^$)'" % (
        db_username, default_db, db_host, db_port)

    #MAKE BACKUP OF SYSTEMGRANTS
    os.popen("pg_dumpall -U%s -h%s -p%s -g|gzip -9 -c > %s/system.%s.gz" %
            (db_username, db_host, db_port, db_backupdir, date))

    #MAKING DB BACKUP
    for db_name in os.popen(get_db_names).readlines():
        db_name = db_name.strip()
        if db_name == "":
            print("empty db_name")
            continue
        else:
            print("backing up %s" % db_name)
        db_dump_filename = "%s/%s-%s.sql" % (db_backupdir, db_name, date)
        ps_exec_cmd = "nice -n 19 pg_dump -C -F c -U%s -h%s -p%s -d%s > %s" % (
            db_username, db_host, db_port, db_name, db_dump_filename)
        os.popen(ps_exec_cmd)

    os.unsetenv('PGPASSWORD')
    print ("backup complete.")