Postgres schema comparison.

Stef

unread,

Mar 7, 2005, 4:33:12 AM3/7/05

to

Hi all,

I've got a master database with many other databases that
all have (or is supposed to have) the same exact same schema
as the master database (the master database is basically an empty
template database containing the schema definition).

The problem is that none of the schemas actually match the master schema.
e.g. missing columns, columns not in the correct order (attnum), missing indexes
and primary keys, and in severe cases, missing sequences and tables.

I have the wonderful job of re-synch'ing all the schemas out there not
conforming to the master. I've looked everywhere for something that
will help doing this. I'm specifically looking for a way to do a sumcheck
or something similar on tables and/or schema as a whole to be able to
do a table comparison with the master database.

It will be a bonus to pick up exactly what is missing, but for now, just identifying
differences is what I want to achieve. I'm using postgres 7.3 mostly, but
I may want to use this for 7.4 and 8.0 databases as well.

Has anybody got some suggestions of what I can do or use to do this.

TIA
Kind Regards
Stefan

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Markus Schaber

unread,

Mar 7, 2005, 7:01:59 AM3/7/05

to

Hi, Stef,

Stef schrieb:

> It will be a bonus to pick up exactly what is missing, but for now, just identifying
> differences is what I want to achieve. I'm using postgres 7.3 mostly, but
> I may want to use this for 7.4 and 8.0 databases as well.
>
> Has anybody got some suggestions of what I can do or use to do this.

There are (at least) two independently developed pgdiff applications,
they can be found at:

http://pgdiff.sourceforge.net/

http://gborg.postgresql.org/project/pgdiff/projdisplay.php

I did not try the first one, but the latter one worked on some of my
datas, but fails on others. I filed a bug report some time ago, but got
no answer, so I'm afraid this tool currently is unmaintained:
http://gborg.postgresql.org/project/pgdiff/bugs/bugupdate.php?895

But maybe a pg_dump --schema-only on all the databases, and then
manually diffing the files may already fulfil your needs.

Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

John DeSoi

unread,

Mar 7, 2005, 7:39:30 AM3/7/05

to

On Mar 7, 2005, at 4:33 AM, Stef wrote:

> I have the wonderful job of re-synch'ing all the schemas out there not
> conforming to the master. I've looked everywhere for something that
> will help doing this. I'm specifically looking for a way to do a
> sumcheck
> or something similar on tables and/or schema as a whole to be able to
> do a table comparison with the master database.
>

Develop a function that builds a string describing the tables/schemas
you want to compare. Then have your function return the md5 sum of the
string as the result. This will give you a 32 character value you can
use to determine if there is a mismatch.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Stef

unread,

Mar 7, 2005, 7:54:44 AM3/7/05

to

Markus Schaber mentioned :
=> There are (at least) two independently developed pgdiff applications,
=> they can be found at:
=>
=> http://pgdiff.sourceforge.net/
=>
=> http://gborg.postgresql.org/project/pgdiff/projdisplay.php

Thanks a lot!

=> I did not try the first one, but the latter one worked on some of my
=> datas, but fails on others. I filed a bug report some time ago, but got
=> no answer, so I'm afraid this tool currently is unmaintained:
=> http://gborg.postgresql.org/project/pgdiff/bugs/bugupdate.php?895
=>
=> But maybe a pg_dump --schema-only on all the databases, and then
=> manually diffing the files may already fulfil your needs.

I've tested something similar, that seems to work ok for me for now :
pg_dump -s -t [TABLE] [DBNAME] | grep -v "^--" | md5sum

The problem I have with this, is that I have to run the command per table,
and seeing that I have over 500 tables in each database, this takes quite a
long time.

I'll test some of the above pgdiffs, and see if either can do it better.

Kind Regards
Stefan

Richard_...@raytheon.com

unread,

Mar 7, 2005, 8:03:43 AM3/7/05

to

Are you just synching the schemas, or do you also need to synch the data?

Rick

John DeSoi
<de...@pgedit.com> To: Stef <s...@ucs.co.za>
Sent by: cc: pgsql...@postgresql.org, pgsq...@postgresql.org
pgsql-sql-owner@pos Subject: Re: [SQL] [ADMIN] Postgres schema comparison.
tgresql.org

03/07/2005 07:39 AM

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)

Stef

unread,

Mar 7, 2005, 8:40:51 AM3/7/05

to

Richard_...@raytheon.com mentioned :
=> Are you just synching the schemas, or do you also need to synch the data?

Schemas now, data later.

To do the data part, I'm thinking of using slony, because it seems to be able to
do pretty much everything I need from that side. But, unfortunately I can't
even start fixing the data before the schemas aren't fixed.

Kind Regards
Stefan

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane

unread,

Mar 7, 2005, 9:30:53 AM3/7/05

to

Stef <s...@ucs.co.za> writes:
> Markus Schaber mentioned :

> => But maybe a pg_dump --schema-only on all the databases, and then
> => manually diffing the files may already fulfil your needs.

> I've tested something similar, that seems to work ok for me for now :
> pg_dump -s -t [TABLE] [DBNAME] | grep -v "^--" | md5sum

> The problem I have with this, is that I have to run the command per table,

Why?

If the problem is varying order of table declarations, try 8.0's
pg_dump.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Stef

unread,

Mar 7, 2005, 10:09:14 AM3/7/05

to

John DeSoi mentioned :
=> Develop a function that builds a string describing the tables/schemas
=> you want to compare. Then have your function return the md5 sum of the
=> string as the result. This will give you a 32 character value you can
=> use to determine if there is a mismatch.

Is it possible to somehow pass the output of : "\d [TABLE NAME]"
to this function? If not, what would return me consistent text
that will describe the columns, indexes and primary keys of a table?

Kind Regards
Stefan

Stef

unread,

Mar 7, 2005, 10:22:06 AM3/7/05

to

Markus Schaber mentioned :
=> Some weeks ago, I posted here a script that uses psql to create split
=> dumps. Maybe you can reuse some of its logics to create per-table
=> md5sums for all tables in a database automatically.

Thanks, but I've got something very similar to this already. I almost
thought you managed to split the output of the single schema dump of
"pg_dump --schema-only" onto portions belonging to the various tables.
That would be very impressive :)

Kind Regards
Stefan

---------------------------(end of broadcast)---------------------------

John DeSoi

unread,

Mar 7, 2005, 10:32:07 AM3/7/05

to

On Mar 7, 2005, at 10:09 AM, Stef wrote:

> Is it possible to somehow pass the output of : "\d [TABLE NAME]"
> to this function? If not, what would return me consistent text
> that will describe the columns, indexes and primary keys of a table?
>

I'm not sure you can use \d directly, but if you startup psql with the
-E option it will show you all the SQL it is using to run the \d
command. It should be fairly easy to get the strings you need from the
results of running a similar query. The psql source is a good place to
look also.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

---------------------------(end of broadcast)---------------------------

Goulet, Dick

unread,

Mar 7, 2005, 10:33:08 AM3/7/05

to

My favorite for this task is WinSql available from
http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp. It can compare
the structure and content of the two tables.

-----Original Message-----
From: John DeSoi [mailto:de...@pgedit.com]
Sent: Monday, March 07, 2005 7:40 AM
To: Stef
Cc: pgsql...@postgresql.org; pgsq...@postgresql.org
Subject: Re: [ADMIN] Postgres schema comparison.

On Mar 7, 2005, at 4:33 AM, Stef wrote:

> I have the wonderful job of re-synch'ing all the schemas out there
not
> conforming to the master. I've looked everywhere for something that
> will help doing this. I'm specifically looking for a way to do a
> sumcheck
> or something similar on tables and/or schema as a whole to be able to
> do a table comparison with the master database.
>

Develop a function that builds a string describing the tables/schemas

you want to compare. Then have your function return the md5 sum of the

string as the result. This will give you a 32 character value you can

use to determine if there is a mismatch.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

---------------------------(end of broadcast)---------------------------

TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Stef

unread,

Mar 8, 2005, 3:00:00 AM3/8/05

to

Jim Buttafuoco mentioned :
=> I use dblink to attach to both databases and query pg_namespace, pg_class, pg_attribute ... to get the diffs. See
=> attached as an example. look for the dblink_connect lines to specify your database. You will need to install
=> contrib/dblink. I used this with 7.4.X series and have NOT tested yet with 8.0.X.

Thanks!

This is something I haven't even thought of.
Only some of the machines have dblink installed at the moment,
but that's the same work as having to install pgcrypto everywhere.

This is actually more thorough. It seems to be working with some minor changes on 7.3
(The dblink functions don't allow multiple connections, and take only one argument, so
I created temp tables in stead). This is actually very fast.

Thanks again.

Kind Regards
Stefan

---------------------------(end of broadcast)---------------------------

Matteo Beccati

unread,

Mar 8, 2005, 3:59:20 AM3/8/05

to

Hi,

> I have the wonderful job of re-synch'ing all the schemas out there not
> conforming to the master. I've looked everywhere for something that
> will help doing this. I'm specifically looking for a way to do a sumcheck
> or something similar on tables and/or schema as a whole to be able to
> do a table comparison with the master database.
>
> It will be a bonus to pick up exactly what is missing, but for now, just identifying
> differences is what I want to achieve. I'm using postgres 7.3 mostly, but
> I may want to use this for 7.4 and 8.0 databases as well.
>
> Has anybody got some suggestions of what I can do or use to do this.

I've made a simple PHP script which compares the schemas of two
databases. It was made in a hurry and is far from being complete, but it
works for my purposes :)

If you want give it a try, let me know

Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

KÖPFERL Robert

unread,

Mar 9, 2005, 5:39:01 AM3/9/05

to

|Subject: Re: [SQL] [ADMIN] Postgres schema comparison.
|
|

| My favorite for this task is WinSql available from
|http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp. It
|can compare
|the structure and content of the two tables.

And will it also generate DIFF-SQL-Scripts to make a target-DB look like a
MasterDB?
Do you know? How about Structural Changes as adding a column?