I have a script I've been using that does a db comparison, and it works very well.
In order to ensure things are in the right order, I have to ...
- query for table and view names (FROM pg_tables WHERE schemaname = 'public' ...), with an ORDER BY clause, natch.
- create a batch command file with one line for each table and view. This command is a pg_dump of the schema, which is
appendedto an output file
I also output function definitions, as well as the actual data (not just the schema) of some tables that basically have
staticor lookup data.
There's a little more to it to suit my particular needs, but the general approach works well. Oh, one other thing I
sometimesneed to do is to delete lines with db owner if the two db's have different owners.
-Roger
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Rick Gigger
Sent: Thursday, February 09, 2006 11:09 PM
To: Philippe Ferreira
Cc: Nicholas Walker; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Database Comparison tool?
Is the ordering guaranteed to be the same on both boxes if you do this?
Rick
On Feb 9, 2006, at 1:03 PM, Philippe Ferreira wrote:
>
>> Are there any tools that can compare a database schema, and
>> produce sql of the changes from one version to the next.
>>
>> We have a development server, and it would be great to be able to
>> just run a tool, where we could produce the changes, review it,
>> and then commit to production.
>
> Hi,
>
> Do a "pgdump" of both databases, and use the "diff" tool to compare
> the two generated files !
>
> (But I hope your databases are not too big...)
>
> Philippe Ferreira.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
