How to get the equivalent of pg_dump -s <dbname> in Python (psycopg2)?

Question

My task is to compare the schemas of several databases in a cluster of our app in Postgres against the same databases of a different version of our app.

The comparison should only check the schema and not data.

The most basic way would be for me to use subproccess to execute

    pg_dump -U <user> -s <database> > schema.txt

for each database, then run a diff.

Two questions

1) Is this the right approach to see if the schema has changed? 2) Is this possible through psycopg2, without using subproccess or pg_dump or psql?

Thanks!

Answer 1

It depends on what is meant by if the schema has changed. If that literally means if there's any difference at all between the schemas, no matter how significant, then yes, you would want to dump the schemas and then compare them.

For this task, you would definitely want to use pg_dump with the --schema-only option. There isn't an SQL statement that does this, so doing this directly via psycopg2 isn't possible. (There are lower-level Postgres library functions that are available that allow programs like PgAdmin3 to show all the DDL for tables and such, but I believe that would need to be called directly via libpq).

However, if what you want to find out is some other type of difference, it may be possible (if not somewhat more involved) to do it via psycopg2 by querying the various system catalog tables.

For example, you could query pg_tables to determine what tables are present, and compare those to see if any are missing. You could then query those tables directly to get more information, say, to check to see if the counts are the same, etc.