A script to backup a PostgreSQL.

pg_backup.py

#!/usr/bin/env python


import sys
import subprocess
import optparse
import os
from datetime import datetime


DATE_FORMAT = '%Y-%m-%d-%H-%M'


def capture(cmd, compress=None):
    """
    Run a PostgreSQL command using Popen and capture the output. Optionally
    will run the command in `compress`, using `stdout` from `cmd`.

    :param cmd: Command to run
    :type cmd: sequence
    :param compress: Optional second command to run with the `stdout` of `cmd`
    :type compress: sequence
    :returns: `stdout` and `stderr` of `cmd` or `compress`, where applicable
    """
    try:
        print 'running `%s`' % ' '.join(cmd)
        proc = subprocess.Popen(cmd, stdout=subprocess.PIPE,
                                stderr=subprocess.PIPE)
        if compress is not None:
            print 'running `%s`' % ' '.join(compress)
            proc = subprocess.Popen(compress, stdin=proc.stdout,
                                     stdout=subprocess.PIPE)
        stdout, stderr = proc.communicate()
        return stdout, stderr
    except OSError, e:
        print 'command `%s` died with:\n\t%s' % (' '.join(cmd), e)
        sys.exit(2)

def limit(num, path):
    """
    Limit backups by purging up to ``total - num``.

    :param num: The max number of backups we want.
    :type num: int>0
    :param path: The backup directory
    :type path: str
    :returns: Number of backups purged
    """
    if num <= 0:
        return 0
    files = os.listdir(path)
    if len(files) <= num:
        return 0
    datetimes = []
    for f in files:
        if not f.index('.'):
            continue
        newf = f[0:f.index('.')]
        dt = '-'.join(newf.split('-')[0:DATE_FORMAT.count('-')+1])
        try:
            datetimes.append((datetime.strptime(dt,
                DATE_FORMAT), os.path.join(path, f)))
        except ValueError:
            print 'Skipped file:', f
    datetimes.reverse()
    datetimes = sorted(datetimes, key=lambda l:l[0])
    for i in range(0, len(datetimes)-num):
        print 'Deleting %s' % datetimes[i][1]
        os.remove(datetimes[i][1])


if __name__ == '__main__':
    parser = optparse.OptionParser()
    parser.add_option('-b', '--bin-dir', type='string',
        dest='bindir', default='',
        help='If psql scripts aren\'t on PATH, set their directory.')
    parser.add_option('-g', '--with-gzip', action='store_true', dest='gzip',
        help='Use gzip compression')
    parser.add_option('-z', '--with-bzip2', action='store_true', dest='bz2',
        help='Use bzip2 compression')
    parser.add_option('-d', '--backup-dir', action='store', dest='backdir',
        help='Backup directory', default='./')
    parser.add_option('-l', '--limit', type='int', dest='limit')
    (opts, args) = parser.parse_args()

    filename = datetime.now().strftime(DATE_FORMAT)
    filepath = os.path.join(opts.backdir, filename)
    cmd = [os.path.join(opts.bindir, 'pg_dumpall')]
    pipe_cmd = None
    if opts.bz2:
        filepath += '.bz2'
        pipe_cmd = ['bzip2', '-c']
    elif opts.gzip:
        filepath += '.gz'
        pipe_cmd = ['gzip', '-c']
    else:
        filepath += '.sql'
        cmd += ['-f', filepath]
    out, err = capture(cmd, pipe_cmd)
    if err:
        print 'command `%s` died with:\n\t%s' % (err, e)
        sys.exit(2)
    print 'Writing backup to `%s`' % filepath
    f = open(filepath, 'w')
    f.write(out)
    limit(opts.limit, os.path.split(filepath)[0])
    print 'Backup to %s completed %s' % (opts.backdir, datetime.now())