X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F277856524; Wed, 9 Mar 2005 17:19:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83734-05; Wed, 9 Mar 2005 17:19:36 +0000 (GMT) Received: from ucsns.ucs.co.za (ucs.co.za [196.23.43.2]) by svr1.postgresql.org (Postfix) with ESMTP id F14015650E; Wed, 9 Mar 2005 17:19:23 +0000 (GMT) Received: from ucspost.ucs.co.za (mailgw1.ucs.co.za [196.23.43.253]) by ucsns.ucs.co.za (Postfix) with ESMTP id DF7FD2BD44; Wed, 9 Mar 2005 19:19:12 +0200 (SAST) Received: from jhb.ucs.co.za (jhb.ucs.co.za [172.31.1.3]) by ucspost.ucs.co.za (Postfix) with ESMTP id 5805AD9FA2; Wed, 9 Mar 2005 19:19:15 +0200 (SAST) Received: from svb.ucs.co.za (svb.ucs.co.za [172.31.1.148]) by jhb.ucs.co.za (Postfix) with ESMTP id E4F1B973EC; Wed, 9 Mar 2005 19:19:14 +0200 (SAST) Date: Wed, 9 Mar 2005 19:19:14 +0200 From: Stef To: Stef Cc: pgsql-ADMIN@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] [SOLVED] Postgres schema comparison. Message-ID: <20050309191914.30dfbbd6@svb.ucs.co.za> In-Reply-To: <20050307180837.44cd681f@svb.ucs.co.za> References: <20050307113312.2200b65c@svb.ucs.co.za> <20050307170914.51376f05@svb.ucs.co.za> <1098CCF1-8F1E-11D9-A12B-000A95B03262@pgedit.com> <20050307180837.44cd681f@svb.ucs.co.za> X-Mailer: Sylpheed-Claws 1.0.1 (GTK+ 1.2.10; i386-pc-linux-gnu) User-Agent: sillypheed-claws (anti-aliased) X-Face: +lU/o'$=Zv9%Xjo]Kq[_{n&Kb`f{YEWH4&Y#M]'6BkD?VoWe#*@S\MWyD'I>s:0v4{#\[J]l*?]t2uaO(j$d*77Ca-UHo.tkl2)0$B3Jxww\o:xor]<:*Yfx9Vceh)9I/'2g\RKhGTxIIsR^ke^`LVeZ9ksb*:$kQeR@ X-Operating-System: sid X-X-X: _-^-_ Mime-Version: 1.0 Content-Type: multipart/mixed; boundary=Multipart_Wed__9_Mar_2005_19_19_14_+0200_fImIqy4MDS0oTL5o X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.325 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_WHOIS X-Spam-Level: X-Archive-Number: 200503/128 X-Sequence-Number: 16626 --Multipart_Wed__9_Mar_2005_19_19_14_+0200_fImIqy4MDS0oTL5o Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 --Multipart_Wed__9_Mar_2005_19_19_14_+0200_fImIqy4MDS0oTL5o Content-Type: application/octet-stream; name=get_table_checksum.sql Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename=get_table_checksum.sql LS0gQXV0aG9yICA6IFN0ZWZhbiB2YW4gZGVyIEJlcmcgKHN2YkB1Y3MuY28uemEpIAotLSBQdXJw b3NlIDogVGFrZXMgdGFibGUgbmFtZSBhcyBwYXJhbWV0ZXIgYW5kIHJldHVybnMgYW4gbWQ1c3Vt Ci0tICAgICAgICAgICBnZW5lcmF0ZWQgZnJvbSBhbGwgb2JqZWN0cyBhdHRhY2hlZCB0byBhIHRh YmxlLgpjcmVhdGUgb3IgcmVwbGFjZSBmdW5jdGlvbiBnZXRfdGFibGVfY2hlY2tzdW0odGV4dCkg cmV0dXJucyB0ZXh0CmFzICcKZGVjbGFyZQoJdGFibGUgICAgICAgYWxpYXMgZm9yICQxOwogICAg ICAgIHNlYXJjaCAgICAgIHRleHQ7CiAgICAgICAgdGFib2lkICAgICAgdGV4dDsKCXEgICAgICAg ICAgIHRleHQ7CglxYSAgICAgICAgICB0ZXh0OwogICAgICAgIHRhYl9yZWMgICAgIHJlY29yZDsK ICAgICAgICBkZWZfcmVjICAgICByZWNvcmQ7CiAgICAgICAgaGFzaW5kZXggICAgYm9vbGVhbjsK ICAgICAgICBoYXNydWxlcyAgICBib29sZWFuOwogICAgICAgIGhhc2luZGV4dCAgIHRleHQ7CiAg ICAgICAgaGFzcnVsZXN0ICAgdGV4dDsKICAgICAgICBub3RudWxsICAgICAgYm9vbGVhbjsKICAg ICAgICBoYXNkZWYgICAgICAgYm9vbGVhbjsKICAgICAgICBub3RudWxsdCAgICAgdGV4dDsKICAg ICAgICBoYXNkZWZ0ICAgICAgdGV4dDsKICAgICAgICBpc3ByaW1hcnkgICAgYm9vbGVhbjsKICAg ICAgICBpc3VuaXF1ZSAgICAgYm9vbGVhbjsKICAgICAgICBpc3ByaW1hcnl0ICAgdGV4dDsKICAg ICAgICBpc3VuaXF1ZXQgICAgdGV4dDsKICAgICAgICBtZDVzdW0gICAgICAgdGV4dDsKYmVnaW4K ICAgICAgICBtZDVzdW0gOj0gXCdOT19TVUNIX1RBQkxFXCc7CiAgICAgICAgdGFib2lkIDo9IG51 bGw7CiAgICAgICAgc2VhcmNoIDo9IFwnXlwnIHx8IHRhYmxlIHx8IFwnJFwnOwoJZm9yIHRhYl9y ZWMgaW4gU0VMRUNUIGMub2lkIGFzIG9pZCxuLm5zcG5hbWUgYXMgbnNwbmFtZSAsYy5yZWxuYW1l IGFzIHJlbG5hbWUKICAgICAgICAgICAgICAgICAgICAgICBGUk9NIHBnX2NhdGFsb2cucGdfY2xh c3MgYwogICAgICAgICAgICAgICAgICAgICAgICAgICBMRUZUIEpPSU4gcGdfY2F0YWxvZy5wZ19u YW1lc3BhY2UgbiBPTiBuLm9pZCA9IGMucmVsbmFtZXNwYWNlCiAgICAgICAgICAgICAgICAgICAg ICAgV0hFUkUgcGdfY2F0YWxvZy5wZ190YWJsZV9pc192aXNpYmxlKGMub2lkKQogICAgICAgICAg ICAgICAgICAgICAgIEFORCBjLnJlbG5hbWUgfiBzZWFyY2gKICAgICAgICAgICAgICAgICAgICAg ICBPUkRFUiBCWSAyLCAzCiAgICAgICAgbG9vcAogICAgICAgICAgIHRhYm9pZCA6PSB0YWJfcmVj Lm9pZDsKICAgICAgICAgICBxYSAgICAgOj0gdGFiX3JlYy5uc3BuYW1lIHx8IFwnOlwnIHx8IHRh Yl9yZWMucmVsbmFtZTsKICAgICAgICBlbmQgbG9vcDsKCiAgICAgICAgaWYgdGFib2lkIGlzIG51 bGwKICAgICAgICB0aGVuCiAgICAgICAgICAgcmV0dXJuIG1kNXN1bTsKICAgICAgICBlbmQgaWY7 CgoJZm9yIHRhYl9yZWMgaW4gU0VMRUNUIHJlbGhhc2luZGV4LCByZWxraW5kLCByZWxjaGVja3Ms IHJlbHRyaWdnZXJzLCByZWxoYXNydWxlcwogICAgICAgICAgICAgICAgICAgICAgIEZST00gcGdf Y2F0YWxvZy5wZ19jbGFzcyBXSEVSRSBvaWQgPSB0YWJvaWQKICAgICAgICBsb29wCiAgICAgICAg ICAgaGFzaW5kZXggOj0gdGFiX3JlYy5yZWxoYXNpbmRleDsKICAgICAgICAgICBoYXNydWxlcyA6 PSB0YWJfcmVjLnJlbGhhc3J1bGVzOwogICAgICAgICAgIGhhc2luZGV4dCA6PSBcJ2ZcJzsKICAg ICAgICAgICBpZiBoYXNpbmRleAogICAgICAgICAgIHRoZW4gCiAgICAgICAgICAgICAgaGFzaW5k ZXh0IDo9IFwndFwnOwogICAgICAgICAgIGVuZCBpZjsKICAgICAgICAgICBoYXNydWxlc3QgOj0g XCdmXCc7CiAgICAgICAgICAgaWYgaGFzcnVsZXMKICAgICAgICAgICB0aGVuIAogICAgICAgICAg ICAgIGhhc3J1bGVzdCA6PSBcJ3RcJzsKICAgICAgICAgICBlbmQgaWY7CiAgICAgICAgICAgcWEg Oj0gcWEgfHwgXCc6XCcgfHwgaGFzaW5kZXh0IHx8IFwnOlwnIHx8IHRhYl9yZWMucmVsa2luZCB8 fCAgXCc6XCcgfHwgdGFiX3JlYy5yZWxjaGVja3M7CiAgICAgICAgICAgcWEgOj0gcWEgfHwgXCc6 XCcgfHwgdGFiX3JlYy5yZWx0cmlnZ2VycyB8fCBcJzpcJyB8fCBoYXNydWxlc3Q7CiAgICAgICAg ZW5kIGxvb3A7CgoJZm9yIHRhYl9yZWMgaW4gU0VMRUNUIGEuYXR0bmFtZSBhcyBhdHRuYW1lLCBw Z19jYXRhbG9nLmZvcm1hdF90eXBlKGEuYXR0dHlwaWQsIGEuYXR0dHlwbW9kKSBhcyBhdHR0eXBp ZCwKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgYS5hdHRub3RudWxsIGFzIGF0dG5vdG51 bGwsIGEuYXR0aGFzZGVmIGFzIGF0dGhhc2RlZiwgYS5hdHRudW0gYXMgYXR0bnVtCiAgICAgICAg ICAgICAgICAgICAgICAgRlJPTSBwZ19jYXRhbG9nLnBnX2F0dHJpYnV0ZSBhCiAgICAgICAgICAg ICAgICAgICAgICAgV0hFUkUgYS5hdHRyZWxpZCA9IHRhYm9pZCBBTkQgYS5hdHRudW0gPiAwIEFO RCBOT1QgYS5hdHRpc2Ryb3BwZWQKICAgICAgICAgICAgICAgICAgICAgICBPUkRFUiBCWSBhLmF0 dG51bQogICAgICAgIGxvb3AKICAgICAgICAgICBub3RudWxsICA6PSB0YWJfcmVjLmF0dG5vdG51 bGw7CiAgICAgICAgICAgaGFzZGVmICAgOj0gdGFiX3JlYy5hdHRoYXNkZWY7CiAgICAgICAgICAg bm90bnVsbHQgOj0gXCdmXCc7CiAgICAgICAgICAgaWYgbm90bnVsbAogICAgICAgICAgIHRoZW4g CiAgICAgICAgICAgICAgbm90bnVsbHQgOj0gXCd0XCc7CiAgICAgICAgICAgZW5kIGlmOwogICAg ICAgICAgIGhhc2RlZnQgOj0gXCdmXCc7CiAgICAgICAgICAgaWYgaGFzZGVmCiAgICAgICAgICAg dGhlbiAKICAgICAgICAgICAgICBoYXNkZWZ0IDo9IFwndFwnOwogICAgICAgICAgIGVuZCBpZjsK ICAgICAgICAgICBxYSA6PSBxYSB8fCBcJzpcJyB8fCB0YWJfcmVjLmF0dG5hbWUgfHwgXCc6XCcg fHwgdGFiX3JlYy5hdHR0eXBpZCB8fCAgXCc6XCcgfHwgbm90bnVsbHQ7CiAgICAgICAgICAgcWEg Oj0gcWEgfHwgXCc6XCcgfHwgaGFzZGVmdCB8fCBcJzpcJyB8fCB0YWJfcmVjLmF0dG51bTsKICAg ICAgICBlbmQgbG9vcDsKCglmb3IgdGFiX3JlYyBpbiBTRUxFQ1QgYS5hdHRudW0gYXMgYXR0bnVt CiAgICAgICAgICAgICAgICAgICAgICAgRlJPTSBwZ19jYXRhbG9nLnBnX2F0dHJpYnV0ZSBhCiAg ICAgICAgICAgICAgICAgICAgICAgV0hFUkUgYS5hdHRyZWxpZCA9IHRhYm9pZCBBTkQgYS5hdHRu dW0gPiAwIEFORCBOT1QgYS5hdHRpc2Ryb3BwZWQgYW5kIGEuYXR0aGFzZGVmID0gdHJ1ZQogICAg ICAgICAgICAgICAgICAgICAgIE9SREVSIEJZIGEuYXR0bnVtCiAgICAgICAgCiAgICAgICAgbG9v cAogICAgICAgICAgIHFhIDo9IHFhIHx8IFwnOlwnIHx8IHRhYl9yZWMuYXR0bnVtOwogICAgICAg ICAgIGZvciBkZWZfcmVjIGluIFNFTEVDVCBjb2FsZXNjZShudWxsaWYoY29hbGVzY2UobnVsbGlm KHJlcGxhY2UocmVwbGFjZShzdWJzdHJpbmcoZC5hZHNyYyBmb3IgMTI4KSxcJ1xcXCdcJyxcJ1wn KSxcJzo6dGV4dFwnLFwnXCcpLFwndFwnKSxcJ3RydWVcJyksXCdmXCcpLFwnZmFsc2VcJykgYXMg ZGVmIEZST00gcGdfY2F0YWxvZy5wZ19hdHRyZGVmIGQKICAgICAgICAgICAgICAgICAgICAgICAg ICBXSEVSRSBkLmFkcmVsaWQgPSB0YWJvaWQgQU5EIGQuYWRudW0gPSB0YWJfcmVjLmF0dG51bQog ICAgICAgICAgIGxvb3AKICAgICAgICAgICAgICAgcWEgOj0gcWEgfHwgXCc6XCcgfHwgZGVmX3Jl Yy5kZWY7CiAgICAgICAgICAgZW5kIGxvb3A7CiAgICAgICAgZW5kIGxvb3A7CgoJZm9yIHRhYl9y ZWMgaW4gU0VMRUNUIGMyLnJlbG5hbWUsIGkuaW5kaXNwcmltYXJ5LCBpLmluZGlzdW5pcXVlLCBw Z19jYXRhbG9nLnBnX2dldF9pbmRleGRlZihpLmluZGV4cmVsaWQpIGFzIGluZGV4cmVsaWQKICAg ICAgICAgICAgICAgICAgICAgICBGUk9NIHBnX2NhdGFsb2cucGdfY2xhc3MgYywgcGdfY2F0YWxv Zy5wZ19jbGFzcyBjMiwgcGdfY2F0YWxvZy5wZ19pbmRleCBpCiAgICAgICAgICAgICAgICAgICAg ICAgV0hFUkUgYy5vaWQgPSB0YWJvaWQgQU5EIGMub2lkID0gaS5pbmRyZWxpZCBBTkQgaS5pbmRl eHJlbGlkID0gYzIub2lkIAogICAgICAgICAgICAgICAgICAgICAgIE9SREVSIEJZIGkuaW5kaXNw cmltYXJ5IERFU0MsIGkuaW5kaXN1bmlxdWUgREVTQywgYzIucmVsbmFtZQogICAgICAgIGxvb3AK ICAgICAgICAgICBpc3ByaW1hcnkgIDo9IHRhYl9yZWMuaW5kaXNwcmltYXJ5OwogICAgICAgICAg IGlzdW5pcXVlICAgOj0gdGFiX3JlYy5pbmRpc3VuaXF1ZTsKICAgICAgICAgICBpc3ByaW1hcnl0 IDo9IFwnZlwnOwogICAgICAgICAgIGlmIGlzcHJpbWFyeQogICAgICAgICAgIHRoZW4gCiAgICAg ICAgICAgICAgaXNwcmltYXJ5dCA6PSBcJ3RcJzsKICAgICAgICAgICBlbmQgaWY7CiAgICAgICAg ICAgaXN1bmlxdWV0IDo9IFwnZlwnOwogICAgICAgICAgIGlmIGlzdW5pcXVlCiAgICAgICAgICAg dGhlbiAKICAgICAgICAgICAgICBpc3VuaXF1ZXQgOj0gXCd0XCc7CiAgICAgICAgICAgZW5kIGlm OwogICAgICAgICAgIHFhIDo9IHFhIHx8IFwnOlwnIHx8IHRhYl9yZWMucmVsbmFtZSB8fCBcJzpc JyB8fCBpc3ByaW1hcnl0IHx8ICBcJzpcJyB8fCBpc3VuaXF1ZXQ7CiAgICAgICAgICAgcWEgOj0g cWEgfHwgXCc6XCcgfHwgdGFiX3JlYy5pbmRleHJlbGlkOwogICAgICAgIGVuZCBsb29wOwoKCWZv ciB0YWJfcmVjIGluIFNFTEVDVCB0LnRnbmFtZQogICAgICAgICAgICAgICAgICAgICAgIEZST00g cGdfY2F0YWxvZy5wZ190cmlnZ2VyIHQKICAgICAgICAgICAgICAgICAgICAgICBXSEVSRSB0LnRn cmVsaWQgPSB0YWJvaWQKICAgICAgICAgICAgICAgICAgICAgICBBTkQgKG5vdCB0Z2lzY29uc3Ry YWludCAgT1IgTk9UIEVYSVNUUyAgKFNFTEVDVCAxIEZST00KICAgICAgICAgICAgICAgICAgICAg ICAgICAgIHBnX2NhdGFsb2cucGdfZGVwZW5kIGQgSk9JTiBwZ19jYXRhbG9nLnBnX2NvbnN0cmFp bnQgYyBPTgogICAgICAgICAgICAgICAgICAgICAgICAgICAgKGQucmVmY2xhc3NpZCA9IGMudGFi bGVvaWQgQU5EIGQucmVmb2JqaWQgPSBjLm9pZCkgCiAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgV0hFUkUgZC5jbGFzc2lkID0gdC50YWJsZW9pZCBBTkQgZC5vYmppZCA9IHQub2lkIEFORCBk LmRlcHR5cGUgPSBcJ2lcJyBBTkQKICAgICAgICAgICAgICAgICAgICAgICAgICAgICBjLmNvbnR5 cGUgPSBcJ2ZcJykpCiAgICAgICAgICAgICAgICAgICAgICAgT1JERVIgQlkgdC50Z25hbWUKICAg ICAgICBsb29wCiAgICAgICAgICAgcWEgOj0gcWEgfHwgXCc6XCcgfHwgdGFiX3JlYy50Z25hbWU7 CiAgICAgICAgZW5kIGxvb3A7CgoJZm9yIHRhYl9yZWMgaW4gU0VMRUNUIGNvbm5hbWUsIHN1YnN0 cmluZyhwZ19jYXRhbG9nLnBnX2dldF9jb25zdHJhaW50ZGVmKG9pZCkgZnJvbSBcJ14oLipcXFxc XFw1MSlbIF0/W09dP1tOXT9cJykgYXMgY29uZGVmCiAgICAgICAgICAgICAgICAgICAgICAgRlJP TSBwZ19jYXRhbG9nLnBnX2NvbnN0cmFpbnQgcgogICAgICAgICAgICAgICAgICAgICAgIFdIRVJF IHIuY29ucmVsaWQgPSB0YWJvaWQgQU5EIHIuY29udHlwZSA9IFwnZlwnCiAgICAgICAgICAgICAg ICAgICAgICAgT1JERVIgQlkgY29ubmFtZQogICAgICAgIGxvb3AKICAgICAgICAgICBxYSA6PSBx YSB8fCBcJzpcJyB8fCB0YWJfcmVjLmNvbm5hbWUgfHwgXCc6XCcgfHwgdGFiX3JlYy5jb25kZWY7 CiAgICAgICAgZW5kIGxvb3A7CgoJZm9yIHRhYl9yZWMgaW4gU0VMRUNUIGVuY29kZShkaWdlc3Qo cWEsIFwnbWQ1XCcpLFwnaGV4XCcpIGFzIG1kNQogICAgICAgIGxvb3AKICAgICAgICAgICBtZDVz dW0gOj0gdGFiX3JlYy5tZDU7CiAgICAgICAgZW5kIGxvb3A7CgogICAgICAgIHJldHVybiBtZDVz dW07CmVuZDsgJyBMQU5HVUFHRSAncGxwZ3NxbCcgOwo= --Multipart_Wed__9_Mar_2005_19_19_14_+0200_fImIqy4MDS0oTL5o--