Re: Database Comparison tool?

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 appended to an output file

I also output function definitions, as well as the actual data (not just the schema) of some tables that basically have static or 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 sometimes need to do is to delete lines with db owner if the two db's have different owners.

-Roger

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Rick Gigger
Sent: Thursday, February 09, 2006 11:09 PM
To: Philippe Ferreira
Cc: Nicholas Walker; pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly