Mixing client and server PSQL versions

I need to compare the table structures between two databases on different servers. The databases should be identical, but one is running on Windows PG 9.0 and the other is on Linux PG 8.3. How hard could it be?

I need to run the check from another Linux PG 8.3 server, so I did a manual check using \d.

$ psql -h 123.123.123.123 mydb
Welcome to psql 8.3.13 (server 9.0.3), the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

WARNING:  You are connected to a server with major version 9.0,
but your psql client is major version 8.3.  Some backslash commands,
such as \d, might not work properly.

mydb=# \dt allstats.*
                 List of relations
  Schema  |         Name         | Type  |  Owner
----------+----------------------+-------+----------
 allstats | age_sex              | table | postgres
 allstats | birth_country        | table | postgres
 allstats | birth_region         | table | postgres
...
(41 rows)

mydb=# \d allstats.sale_stat
ERROR:  column "reltriggers" does not exist
LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr...
                                                ^

So \d failed miserably because I was connecting an 8.3 client to a 9.0 server and the catalog definitions have obviously changed between versions. Since I am stuck with using these versions, I will have to come up with my own query instead of relying on \d.

To do this, I first reverse engineer the \d command by run the following in my session:

(NOTE that you could also use the -E psql switch as shown by Andrea in the comments)

set log_statement = 'all'

Which immediately enables logging every SQL statement – but ONLY for my session, which is a good thing. Next, I run the psql command on a local 8.3 database:

performance=# \d stats.database_sizes;
            Table "stats.database_sizes"
  Column   |            Type             | Modifiers
-----------+-----------------------------+-----------
 timestamp | timestamp without time zone |
 host      | character varying(30)       |
 database  | character varying(64)       |
 size      | bigint                      |
Indexes:
    "database_sizes_1" btree (host, "timestamp")
    "database_sizes_2" btree (host, database, "timestamp")

The log file shows the real query behind the command, in fact there are five of them:

SELECT c.oid,
	  n.nspname,
	  c.relname
	FROM pg_catalog.pg_class c
	     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
	WHERE c.relname ~ '^(database_sizes)$'
	  AND n.nspname ~ '^(stats)$'
	ORDER BY 2, 3;

SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
	relhasoids , reltablespace
	FROM pg_catalog.pg_class WHERE oid = '16414'

SELECT a.attname,
	  pg_catalog.format_type(a.atttypid, a.atttypmod),
	  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
	   FROM pg_catalog.pg_attrdef d
	   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
	  a.attnotnull, a.attnum
	FROM pg_catalog.pg_attribute a
	WHERE a.attrelid = '16414' AND a.attnum > 0 AND NOT a.attisdropped
	ORDER BY a.attnum

SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace
	FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
	WHERE c.oid = '16414' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
	ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname

SELECT c.oid::regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '16414' ORDER BY inhseqno ASC

The one that was erroring when connecting to my 9.0 server was the second one. The reltriggers column has been renamed to relhastriggers. Using these logged queries, I was able to construct a single query that gives me all the information I am interested in and will run on both 8.3 and 9.0 databases:

select x.nspname || '.' || x.relname as "Table", x.attnum as "#", x.attname as "Column", x."Type", case x.attnotnull when true then 'NOT NULL' else '' end as "NULL?"
	, r.conname as "Constraint", r.contype as "C", r.consrc, fn.nspname || '.' || f.relname as "F Key", d.adsrc as "Default"
from (
	SELECT c.oid, a.attrelid, a.attnum, n.nspname, c.relname, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type", a.attnotnull
	FROM   pg_catalog.pg_attribute a, pg_namespace n, pg_class c
	WHERE  a.attnum > 0
	AND NOT a.attisdropped
	AND a.attrelid = c.oid
	and c.relnamespace = n.oid
	and n.nspname not in ('pg_catalog','pg_toast','information_schema')
) x
left join pg_attrdef d on d.adrelid = x.attrelid and d.adnum = x.attnum
left join pg_constraint r on r.conrelid = x.oid and r.conkey[1] = x.attnum
left join pg_class f on r.confrelid = f.oid
left join pg_namespace fn on f.relnamespace = fn.oid
where x.relname = 'sale_stat'
order by 1,2

       Table        | # |   Column    |       Type        |  NULL?   | Constraint | C | consrc | F Key |                   Default
--------------------+---+-------------+-------------------+----------+------------+---+--------+-------+---------------------------------------------
 allstats.sale_stat | 1 | stat_id     | integer           | NOT NULL |            |   |        |       | nextval('allstats.sale_stat_seq'::regclass)
 allstats.sale_stat | 2 | system_id   | character varying |          |            |   |        |       |
 allstats.sale_stat | 3 | region_id   | integer           |          |            |   |        |       |
 allstats.sale_stat | 4 | cat_mjr_id  | character varying |          |            |   |        |       |
 allstats.sale_stat | 5 | interval    | integer           |          |            |   |        |       |
 allstats.sale_stat | 6 | stat_year   | integer           |          |            |   |        |       |
 allstats.sale_stat | 7 | interval_no | integer           |          |            |   |        |       |
 allstats.sale_stat | 8 | sales_cnt   | integer           |          |            |   |        |       |
 allstats.sale_stat | 9 | median_sale | integer           |          |            |   |        |       |
(9 rows)

And now to compare every detail of two table structures I can just diff the output of the above query after it has run on the two databases.