This could be useful when you need something like this:
- to compare database objects between two databases
- to compare database objects between two different time snapshots in one database – for example after fresh installation and after migration from previous version
There are some commercial programs available but if you like to have “everything in hand” and you want/ need to stick to free solution then you can try this.
I use very simple python program which needs psycopg library. Advantage is that this program is incredibly quick. It is written very simply and stupidly because in hurry there is no time to write really nice code. Quickest way always prevails…
When you use this program you get export file. To compare export files from different times or databases I highly recommend to use WinMerge – intelligently shows all differences.
This approach is of course very manual but you have everything in hand and can easily see new issues – which is not that simple with automated tools.
Just remember – all your queries have to have some “ORDER BY” to be sure that you always get the same order.
One limitation of this approach is that it does not reformat source code of procedures or views so you depend on output from PostgreSQL. There are therefore differences between pg versions. Pg 8.4 puts view source code almost all in one line and adds many brackets “(” “)” into code. Which is not at all nice! Pg 9.3 does nice formating. Therefore you will see different source code for the same view from different pg versions.
Of course the format I write data into output file suits to me. Because I can then easily see differences. I guess you definitely will change it…
#!/usr/bin/python
import psycopg2
import sys
import pprint
import os
import datetime
def main():
###### here put parameters #######
filename_comment = 'new_inst_v202' ##here you put some short description of action in database
hostname = "xxx.xxx.xxx.xxx" ## here IP address of machine with postgresql
date = str(datetime.date.today())
#in file name you will see all necessary infos which is useful when choosing datafile for comparison in WinMerge
filename = "d:/export/all_objects_" + hostname + "-" + date + "-" + filename_comment + ".sql"
print filename
f = open(filename,'w')
userpass = " user='xxxxxxx' password='xxxxxxxx'" ## here put user and password for connect into postgresql
pg_conn_string = "host='" + hostname + "' dbname='postgres'" + userpass
pg_conn = psycopg2.connect(pg_conn_string)
dbs_cursor = pg_conn.cursor()
dbs_cursor.execute("select datname from pg_database where datistemplate is false and datname not in ('postgres', 'test') order by datname") ## here you eliminate which databases will not be exported
dbs_records = dbs_cursor.fetchall()
pg_conn.close()
for database in dbs_records :
dbname = database[0]
title = "************************** database " + dbname + " *************************************"
f.write(title+"\n")
f.write(" "+"\n")
conn_string = "host='" + hostname + "' dbname='" + dbname + "'" + userpass
print dbname + " -> %s" % (conn_string)
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
##export all functions
cursor.execute("select s.nspname, p.proname, pg_get_functiondef(p.oid) from pg_proc p join pg_namespace s on p.pronamespace=s.oid where nspname not in ('pg_catalog', 'information_schema') order by s.nspname, p.proname, p.proargnames, p.proargtypes")
records = cursor.fetchall()
### export of all functions code
f.write("-------------- functions -------------\n")
for line in records :
f.write("*** "+dbname+".")
for x in line :
towrite = str(x)
towrite = towrite.replace('\r\n', '\n')
f.write(towrite+"\n")
##export all views
cursor.execute("select schemaname, viewname, definition from pg_views where schemaname not in ('information_schema', 'pg_catalog') order by schemaname, viewname")
records = cursor.fetchall()
### export of all views
f.write("-------------- views -------------\n")
for line in records :
f.write("*** "+dbname+".")
for x in line :
towrite = str(x)
towrite = towrite.replace('\r\n', '\n')
f.write(towrite+"\n")
##if you need to check for example some queries stored is dedicated table - do it
cursor.execute("select count(*) from information_schema.tables where table_schema||'.'||table_name='data.my_special_queries'");
rownum = cursor.fetchone()[0]
if rownum > 0 :
cursor.execute("select * from data.my_special_queries order by id")
records = cursor.fetchall()
f.write("-------------- my_special_queries -------------\n")
for line in records :
f.write("*** "+dbname+".")
for x in line :
towrite = str(x)
towrite = towrite.replace('\r\n', '\n')
f.write(">>>" + towrite+"<<<\n")
cursor.execute("select count(*) from information_schema.tables where table_schema||'.'||table_name='data.report_queries'");
rownum = cursor.fetchone()[0]
if rownum > 0 :
cursor.execute("select * from data.report_queries order by id")
records = cursor.fetchall()
f.write("-------------- report queries -------------\n")
for line in records :
f.write("*** "+dbname+".")
for x in line :
towrite = str(x)
towrite = towrite.replace('\r\n', '\n')
f.write(">>>" + towrite+"<<<\n")
##check table columns
cursor.execute("SELECT table_schema, table_name, column_name, case when character_maximum_length is not null then data_type||'('||character_maximum_length||')' else data_type end as data_type, dtd_identifier FROM information_schema.columns c WHERE table_schema NOT IN ('information_schema','pg_catalog') order by table_catalog, table_schema, table_name, cast(dtd_identifier as int)")
records = cursor.fetchall()
f.write("-------------- table columns -------------\n")
for line in records :
f.write("*** "+dbname+".")
for x in line :
f.write(str(x)+"\n")
##check view columns
cursor.execute("select sequence_schema, sequence_name, data_type from information_schema.sequences order by sequence_schema, sequence_name")
records = cursor.fetchall()
f.write("-------------- sequences -------------\n")
for line in records :
f.write("*** "+dbname+".")
for x in line :
f.write(str(x)+"\n")
conn.close()
f.close()
##run only when this program is launched directly on as part of "import" function in python
if __name__ == "__main__":
main()