pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Stef <svb@ucs.co.za>
To: Stef <svb@ucs.co.za>
Cc: pgsql-ADMIN@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] [SOLVED] Postgres schema comparison.
Date: Wed, 9 Mar 2005 19:19:14 +0200
Message-ID: <20050309191914.30dfbbd6@svb.ucs.co.za> (raw)
In-Reply-To: <20050307180837.44cd681f@svb.ucs.co.za>
References: <20050307113312.2200b65c@svb.ucs.co.za>
	<F393FF18-8F05-11D9-A12B-000A95B03262@pgedit.com>
	<20050307170914.51376f05@svb.ucs.co.za>
	<1098CCF1-8F1E-11D9-A12B-000A95B03262@pgedit.com>
	<20050307180837.44cd681f@svb.ucs.co.za>

Hi all,

If anyone is interested, here's the final solution
that I'm using to build a list of tables and their md5sums
based on what the psql interface queries when you do '\d [TABLE NAME]'

I attached the function I created, and this is the SQL I run :
select relname||':'||get_table_checksum(relname) from pg_class where relkind = 'r' and relname not like ('pg_%') and relname not like ('sql_%') order by relname;

This gives the same result for a specific table across  all versions of postgres  >= 7.3,
and runs for a minute or so for +- 450 tables on my machine.
It may break if you have some exotic definitions that I didn't test for, 
but I think it's pretty solid as it is here.

Kind Regards
Stefan

Stef mentioned :
=> Here's my final solution that runs in less than a minute for +- 543 tables :
=> for x in $(psql -tc "select relname from pg_class where relkind = 'r' and relname not like 'pg_%'")
=> do 
=>    echo "$(psql -tc "select  encode(digest('$(psql -c  '\d '${x}'' mer9188_test | tr -d \"\'\")', 'md5'), 'hex')" mer9188_test | grep -v "^$"|tr -d " "):${x}"
=> done > compare_list.lst

Attachments:

  get_table_checksum.sql (application/octet-stream, 5.8K) download

view thread (19+ messages)

Message-ID: <20050309191914.30dfbbd6@svb.ucs.co.za>
Permalink:  ../20050309191914.30dfbbd6@svb.ucs.co.za/
Also on:    postgresql.org/message-id/20050309191914.30dfbbd6@svb.ucs.co.za

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: svb@ucs.co.za, pgsql-ADMIN@postgresql.org
  Subject: Re: [SQL] [SOLVED] Postgres schema comparison.
  In-Reply-To: <20050309191914.30dfbbd6@svb.ucs.co.za>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox