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:
Show quoted text
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 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@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
Import Notes
Resolved by subject fallback
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/datacomparerI 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.