X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP
	id 8FCF8D1B89B; Wed, 29 Oct 2003 17:46:22 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 67714-03; Wed, 29 Oct 2003 13:45:52 -0400 (AST)
Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194])
	by svr1.postgresql.org (Postfix) with ESMTP
	id 4067ED1B588; Wed, 29 Oct 2003 13:45:51 -0400 (AST)
Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162])
	by smtp.istop.com (Postfix) with ESMTP
	id B5EC836958; Wed, 29 Oct 2003 12:45:35 -0500 (EST)
Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar)
	by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian))
	id 1AEuNz-0004Me-00; Wed, 29 Oct 2003 12:45:35 -0500
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stef <svb@ucs.co.za>, Rod Taylor <rbt@rbt.ca>,
	pgsql-admin@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [SQL] Table versions
References: <20031029125752.35069e3b.svb@ucs.co.za>
	<1067435458.99917.34.camel@jester> <3731.1067437850@sss.pgh.pa.us>
In-Reply-To: <3731.1067437850@sss.pgh.pa.us>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 29 Oct 2003 12:45:35 -0500
Message-ID: <87wuaoey4g.fsf@stark.dyndns.tv>
Lines: 24
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/404
X-Sequence-Number: 10965

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Returning to the original problem, it seems to me that comparing "pg_dump
> -s" output is a reasonable way to proceed.  

I've actually started checking in a pg_dump -s output file into my CVS tree. 

However I prune a few key lines from it. I prune the TOC OID numbers from it,
and anything not owned by the user I'm interested in.

The makefile rule I use looks like:

schema.sql:
	pg_dump -U postgres -s user | sed '/^-- TOC entry/d;/^\\connect - postgres/,/^\\connect - user/d;/^SET search_path/d;/^$$/d;/^--$$/d' > $@


This still suffers from one major deficiency. The order that objects are
outputed isn't necessarily consistent between databases. If I add tables to
the development server but then add them to the production server in a
different order the schema still shows differences even though the objects in
the two databases are identical.

-- 
greg