Thread: Database Comparison tool?
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. Or is there a system table that will give me the last modification date of a table/view/procedure, etc, etc...
> 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.
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 >
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
Hi, On Wed, 2006-02-08 at 18:22 +0000, Nicholas Walker wrote: > Are there any tools that can compare a database schema, and produce sql > of the changes from one version to the next. http://www.sqlmanager.net/en/products/postgresql/dbcomparer http://www.sqlmanager.net/en/products/postgresql/datacomparer I did not try them (I don't have Windows) but ISTM these will help you. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/
[ please refrain from top-quoting, and try not to repeat the entire
previous post; we do have archives you know ]
Rick Gigger <rick@alpinenetworking.com> writes:
> On Feb 9, 2006, at 1:03 PM, Philippe Ferreira wrote:
>> Do a "pgdump" of both databases, and use the "diff" tool to compare
>> the two generated files !
> Is the ordering guaranteed to be the same on both boxes if you do this?
For recent pg_dumps (since 8.0 I think) it should be the same as long
as both DBs are actually identical. If there are different interobject
dependencies, that might force different dump orders.
regards, tom lane
On February 10, 2006 00:57, Devrim GUNDUZ wrote: > Hi, > > On Wed, 2006-02-08 at 18:22 +0000, Nicholas Walker wrote: > > Are there any tools that can compare a database schema, and produce sql > > of the changes from one version to the next. > > http://www.sqlmanager.net/en/products/postgresql/dbcomparer > http://www.sqlmanager.net/en/products/postgresql/datacomparer > > I did not try them (I don't have Windows) but ISTM these will help you. > > Regards, Someone on the list recently mentioned these tools: pgdiff: http://pgdiff.sourceforge.net/ zongle: http://zongle.sourceforge.net I haven't tried either of them, and they look rather young. Nevertheless, they might be worth a look. Luca
You can use SchemaCrawler, a free open-source tool that can compare schemas as well as data. SchemaCrawler is a command-line tool to output your database schema and data in a readable form. The output is designed to be diff-ed with previous versions of your database schema. http://schemacrawler.sourceforge.net/ Sualeh Fatehi.