Dumps a Trac database to a compressed sql file by excluding tables containing sensitive data, such as session cookies. Only works on Trac instances using a postgres database.

dump-trac.py

#!/usr/bin/env python

"""
Dumps a Trac database to a compressed sql file by excluding tables
containing sensitive data, such as session cookies.
Only works on Trac instances using a postgres database.

Usage: trac-env.py <projenv> <outfile>

  projenv: path to Trac's environment directory
  outfile: path where the gzip-compressed sql file will be stored

"""

from __future__ import print_function

import subprocess
import tempfile
import os
import sys
import urlparse
import ConfigParser


PSQL_BINARY = 'psql'
PGDUMP_BINARY = 'pg_dump'

EXCLUDED_TABLES = set([
    'auth_cookie',
    'session',
    'session_attribute',
    'spamfilter_bayes',
    'spamfilter_log',
])

DEFAULT_PORT = 5432


class PostgresPassword(object):
    def __init__(self, password):
        self.password = password

    def __enter__(self):
        fh, self.path = tempfile.mkstemp(text=True)
        os.write(fh, '*:*:*:*:{}'.format(self.password))
        os.close(fh)
        os.environ['PGPASSFILE'] = self.path

    def __exit__(self, type, value, traceback):
        os.remove(self.path)


def get_db_conf(trac_env):
    conf = os.path.join(trac_env, 'conf', 'trac.ini')
    parser = ConfigParser.ConfigParser()
    parser.read(conf)
    db_url = parser.get('trac', 'database')
    db_conf = urlparse.urlparse(db_url)
    assert db_conf.scheme == 'postgres'
    return {
        'user': db_conf.username,
        'password': db_conf.password,
        'host': db_conf.hostname,
        'port': str(db_conf.port if db_conf.port else DEFAULT_PORT),
        'name': db_conf.path.strip('/')
    }


def make_list_command(db):
    cmd = [
        PSQL_BINARY,
        '-c', r'\dt',
        '-h', db['host'],
        '-p', db['port'],
        db['name'], db['user'],
    ]
    return cmd


def make_dump_command(db, tables, outfile):
    cmd = [
        PGDUMP_BINARY,
        '--no-owner',
        '--no-privileges',
        '--column-inserts',
        '--host', db['host'],
        '--port', db['port'],
        '--username', db['user'],
        '--file', outfile,
        '--compress', '9',
    ]

    for table in tables:
        # We could as well use --exclude-table here
        # and not go the extra step of listing tables,
        # but this makes it more explicit.
        cmd.append('--table')
        cmd.append(table)

    cmd.append(db['name'])

    return cmd


def get_tables_to_dump(db):
    cmd = make_list_command(db)
    out = subprocess.check_output(cmd, env=os.environ)

    tables = out.splitlines()[3:-2]
    tables = (line.strip().split()[2] for line in tables)
    tables = (t for t in tables if t not in EXCLUDED_TABLES)

    return tuple(tables)


def dump_data(db, tables, outfile):
    cmd = make_dump_command(db, tables, outfile)
    subprocess.check_call(cmd, env=os.environ)


def print_conf(db_conf):
    print('')
    print('Trac database settings:')
    print('')
    for k, v in db_conf.iteritems():
        print(' * {:10s}: {}'.format(k, v))


def print_tables(tables):
    print('')
    print('The following tables will be dumped:')
    print('')
    for t in tables:
        print(' * {}'.format(t))
    print('')
    print('Is this OK? (Ctrl-C to abort)')


def print_summary(outfile):
    print('The dump is located at:')
    print('')
    print('    ' + os.path.realpath(outfile))
    print('')


def confirm():
    try:
        sys.stdin.read(1)
    except KeyboardInterrupt:
        print('')
        print('Ctrl-C received, dump aborted')
        sys.exit(1)


def main(trac_env, outfile):
    db_conf = get_db_conf(trac_env)
    print_conf(db_conf)

    with PostgresPassword(db_conf['password']):
        tables = get_tables_to_dump(db_conf)
        print_tables(tables)
        confirm()

        print('Launching pg_dump...')
        dump_data(db_conf, tables, outfile)
        print('')

        print_summary(outfile)


if __name__ == '__main__':
    if len(sys.argv) != 3:
        print('Usage: {} <projenv> <outfile>'.format(sys.argv[0]))
        sys.exit(1)
    else:
        main(*sys.argv[1:])

If memory serves me print() is python3 specific. The following snippet should work for python2 and python3.

def print_(msg):
sys.stdout.write(str(msg) + "\n")
sys.stdout.flush()

As long as no additional parameters are passed, print(...) works in python 2 as well

Edit: otherwise, a better solution would be from __future__ import print_function (http://docs.python.org/2/library/__future__.html)