Thread: compare two databases
there is any utility or command for compare two databases, tables structure (schema).
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
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
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
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
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 |