Thread: compare two databases

compare two databases

From
Ashok Chauhan
Date:
there is any utility or command for compare two databases,
tables structure (schema).



Re: compare two databases

From
Bruno LEVEQUE
Date:
Maybe you can use :

pg_dump -s -t Thetable firstdatabasename > first
pg_dump -s -t Thetable seconddatabasename > second

diff first second



Bruno


Ashok Chauhan wrote:

>there is any utility or command for compare two databases,
>tables structure (schema).
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>

--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com



Re: compare two databases

From
Yuji Shinozaki
Date:
Here's a question about that:  Do dumps always occur
in a predictable row order?

If it is not the case, then you might need to sort each
table dump before the diff.

yuji
----

On Sun, 16 Nov 2003, Bruno LEVEQUE wrote:

> Maybe you can use :
>
> pg_dump -s -t Thetable firstdatabasename > first
> pg_dump -s -t Thetable seconddatabasename > second
>
> diff first second
>
>
>
> Bruno
>
>
> Ashok Chauhan wrote:
>
> >there is any utility or command for compare two databases,
> >tables structure (schema).
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> >    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
> >
>
> --
> Bruno LEVEQUE
> System Engineer
> SARL NET6D
> bruno.leveque@net6d.com
> http://www.net6d.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>
>

Yuji Shinozaki                            Computer Systems Senior Engineer
ys2n@virginia.edu            Advanced Technologies Group
(434)924-7171                Information Technology & Communication
http://www.people.virginia.edu/~ys2n    University of Virginia


Re: compare two databases

From
Andrew Sullivan
Date:
On Sun, Nov 16, 2003 at 09:46:08AM -0500, Yuji Shinozaki wrote:
>
> Here's a question about that:  Do dumps always occur
> in a predictable row order?

No, at least not between systems.  I'm pretty sure that the same
system, if left unchanged between them, wil dump the tables in the
same order.  They key to all this is understanding the implications
of lazy vacuum: tuples do not always stay in the same place on the
disk.

> If it is not the case, then you might need to sort each
> table dump before the diff.

Yes.

--
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: compare two databases

From
Reece Hart
Date:
You might consider comparing the schemas with pg_dump -s and diff (as was already suggested), then comparing the data with a (e.g.) perl DBI script connected to two databases simultaneously.

If the two databases are truly descended from the same design (e.g., restored from the same dump), then I'd expect the dump order to be the same and the diff probably won't be corrupted with false differences. I'd strip lines matching /^--/ to facilitate the diff.

A simple-minded implementation (I excel here) of the perl-DBI part doesn't require table-specific code. I'd do it like this: 1) connect to both databases; 2) for each (common) table, create a statement which selects rows in some canonical order (e.g., sorted by primary key or some non-degenerate multi-column tuple), and then fetch into a hash one-by-one (cursors would be better, but IIRC the DBI code doesn't support them). Because you're fetching into a hash, you can write a single routine to compare all elements of the common hash keys (you'll already know of differences between keys/columns themselves from the schema diff). This is not the speed-optimal solution, but it shouldn't be too bad in total time and you'd probably spend even more time implementing the optimal solution.

Obviously, this will miss certain diabolical changes like column or table renames, but it should suffice for most mutations of two instances from a common schema and content.

-Reece

-- 
Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0