Generating a dependency graph for Postgres DB

This post was mostly inspired by this one, which shows how to generate a dependency graph for a MySQL database. Here we do something similar for PostgreSQL.

This script will generate the required digraph data to pipe into graphviz dot which will generate a visual representation of dependencies in a database schema, based on foreign key constraints.

The script:

from optparse import OptionParser, OptionGroup

import psycopg2
import sys


def writedeps(cursor, tbl):
    sql = """SELECT
        tc.constraint_name, tc.table_name, kcu.column_name,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name
    FROM
        information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON
        tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu ON
        ccu.constraint_name = tc.constraint_name
    WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = '%s'"""
    cursor.execute(sql % tbl)
    for row in cursor.fetchall():
        constraint, table, column, foreign_table, foreign_column = row
        print '"%s" -> "%s" [label="%s"];' % (tbl, foreign_table, constraint)


def get_tables(cursor):
    cursor.execute("SELECT tablename FROM pg_tables WHERE schemaname='public'")
    for row in cursor.fetchall():
        yield row[0]


def main():
    parser = OptionParser()

    group = OptionGroup(parser, "Database Options")
    group.add_option("--dbname", action="store", dest="dbname",
            help="The database name.")
    group.add_option("--dbhost", action="store", dest="dbhost",
            default="localhost",  help="The database host.")
    group.add_option("--dbuser", action="store", dest="dbuser",
            help="The database username.")
    group.add_option("--dbpass", action="store", dest="dbpass",
            help="The database password.")
    parser.add_option_group(group)

    (options, args) = parser.parse_args()

    if not options.dbname:
        print "Please supply a database name, see --help for more info."
        sys.exit(1)

    try:
        conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'"
            % (options.dbname, options.dbuser, options.dbhost, options.dbpass))
    except psycopg2.OperationalError, e:
        print "Failed to connect to database,",
        print "perhaps you need to supply auth details:\n %s" % str(e)
        print "Use --help for more info."
        sys.exit(1)

    cursor = conn.cursor()

    print "Digraph F {\n"
    print 'ranksep=1.0; size="18.5, 15.5"; rankdir=LR;'
    for i in get_tables(cursor):
        writedeps(cursor, i)
    print "}"

    sys.exit(0)


if __name__ == "__main__":
    main()

You could run it as follows:

python postgres-deps.py --dbname some_database | dot -Tpng > deps.png

Note: for other options use:

python postgres-deps.py --help

That should spit out one of these:

Share Comments
comments powered by Disqus