pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do i compare 2 postgresql instanses ?
5+ messages / 4 participants
[nested] [flat]
* How do i compare 2 postgresql instanses ?
@ 2008-07-08 12:42 Potluri Srikanth <spotluri@ismartpanache.com>
2008-07-08 13:02 ` Re: How do i compare 2 postgresql instanses ? Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Potluri Srikanth @ 2008-07-08 12:42 UTC (permalink / raw)
To: pgsql-admin
How do i compare 2 postgresql instanses ?
1) compare all objects including data in tables a) production box b)
DR site ?
can you help me with the query, through which i can get all the
object of each schema/database ?
Regards,
Srikanth k Potluri
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do i compare 2 postgresql instanses ?
2008-07-08 12:42 How do i compare 2 postgresql instanses ? Potluri Srikanth <spotluri@ismartpanache.com>
@ 2008-07-08 13:02 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2008-07-08 13:20 ` Re: How do i compare 2 postgresql instanses ? Michael Nacos <m.nacos@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Achilleas Mantzios @ 2008-07-08 13:02 UTC (permalink / raw)
To: pgsql-admin; spotluri@ismartpanache.com
Στις Tuesday 08 July 2008 15:42:28 ο/η Potluri Srikanth έγραψε:
>
> How do i compare 2 postgresql instanses ?
> 1) compare all objects including data in tables a) production box b)
> DR site ?
> can you help me with the query, through which i can get all the
> object of each schema/database ?
> Regards,
> Srikanth k Potluri
>
you could write a little program (awk) to produce an sql file of the form:
select count(*) from airlines;
select count(*) from airports;
select count(*) from airticket;
select count(*) from airticketflights;
....
select count(*) from zzzobjects;
and then run it against the two databases, and diff the outputs.
If the outputs are not identical then you have 2 different databases,
otherwise you have to search more to know the answer.
So this technique can prove that yours DBs are *not* identical, but does not say anything
about the opposite.
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do i compare 2 postgresql instanses ?
2008-07-08 12:42 How do i compare 2 postgresql instanses ? Potluri Srikanth <spotluri@ismartpanache.com>
2008-07-08 13:02 ` Re: How do i compare 2 postgresql instanses ? Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2008-07-08 13:20 ` Michael Nacos <m.nacos@gmail.com>
2008-07-08 14:28 ` Re: How do i compare 2 postgresql instanses ? Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Nacos @ 2008-07-08 13:20 UTC (permalink / raw)
To: pgsql-admin
Hello there. Simple counts can certainly prove two databases are not the
same version. But what if you actually want to look at the data? I have been
thinking about row checksums, would you say this would be a reasonable way
of verifying two databases are equivalent? I have many other things on my
mind, so I haven't tried implementing this yet.
Michael
> you could write a little program (awk) to produce an sql file of the form:
> select count(*) from airlines;
> select count(*) from airports;
> select count(*) from airticket;
> select count(*) from airticketflights;
> ....
> select count(*) from zzzobjects;
>
> and then run it against the two databases, and diff the outputs.
> If the outputs are not identical then you have 2 different databases,
> otherwise you have to search more to know the answer.
> So this technique can prove that yours DBs are *not* identical, but does
> not say anything
> about the opposite.
>
> --
> Achilleas Mantzios
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do i compare 2 postgresql instanses ?
2008-07-08 12:42 How do i compare 2 postgresql instanses ? Potluri Srikanth <spotluri@ismartpanache.com>
2008-07-08 13:02 ` Re: How do i compare 2 postgresql instanses ? Achilleas Mantzios <achill@matrix.gatewaynet.com>
2008-07-08 13:20 ` Re: How do i compare 2 postgresql instanses ? Michael Nacos <m.nacos@gmail.com>
@ 2008-07-08 14:28 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-08 15:21 ` Re: How do i compare 2 postgresql instanses ? Michael Nacos <m.nacos@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2008-07-08 14:28 UTC (permalink / raw)
To: Michael Nacos <m.nacos@gmail.com>; pgsql-admin
>>> "Michael Nacos" <m.nacos@gmail.com> wrote:
> what if you actually want to look at the data? I have been
> thinking about row checksums, would you say this would be a
reasonable way
> of verifying two databases are equivalent?
Our home-grown replication technique does synchronization checks
during idle time. It works pretty well for us, and we do use md5sum
techniques to minimize bandwidth.
In summary, a process requests a middle tier on a "source" machine to
send "sync" information; that middle tier reads some set of rows and
sends the md5 sum of all data in the rows, along with information on
what rows from which table were used to develop the number. The
requesting process then asks similar middle tiers on all replication
targets to return their md5sum for the same set of rows. If there are
any differences, the source and all differing replication targets are
queried for the actual values, which are compared using match-merge
type logic on the primary key sequence.
Something similar might work for you.
-Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do i compare 2 postgresql instanses ?
2008-07-08 12:42 How do i compare 2 postgresql instanses ? Potluri Srikanth <spotluri@ismartpanache.com>
2008-07-08 13:02 ` Re: How do i compare 2 postgresql instanses ? Achilleas Mantzios <achill@matrix.gatewaynet.com>
2008-07-08 13:20 ` Re: How do i compare 2 postgresql instanses ? Michael Nacos <m.nacos@gmail.com>
2008-07-08 14:28 ` Re: How do i compare 2 postgresql instanses ? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2008-07-08 15:21 ` Michael Nacos <m.nacos@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Nacos @ 2008-07-08 15:21 UTC (permalink / raw)
To: pgsql-admin
Thanks Kevin... I am working on something different right now but I'll soon
have to give it a go, one way or the other...
Michael
On Tue, Jul 8, 2008 at 3:28 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov>
wrote:
>
> Our home-grown replication technique does synchronization checks
> during idle time. It works pretty well for us, and we do use md5sum
> techniques to minimize bandwidth.
>
> In summary, a process requests a middle tier on a "source" machine to
> send "sync" information; that middle tier reads some set of rows and
> sends the md5 sum of all data in the rows, along with information on
> what rows from which table were used to develop the number. The
> requesting process then asks similar middle tiers on all replication
> targets to return their md5sum for the same set of rows. If there are
> any differences, the source and all differing replication targets are
> queried for the actual values, which are compared using match-merge
> type logic on the primary key sequence.
>
> Something similar might work for you.
>
> -Kevin
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-07-08 15:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-08 12:42 How do i compare 2 postgresql instanses ? Potluri Srikanth <spotluri@ismartpanache.com>
2008-07-08 13:02 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2008-07-08 13:20 ` Michael Nacos <m.nacos@gmail.com>
2008-07-08 14:28 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-08 15:21 ` Michael Nacos <m.nacos@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox