Here is a comparison between two large tables in two different instances. One instance is postgres 9.0.3, the other 9.1beta (from git://git.postgresql.org/git/postgresql.git).

The table compared (named "public.x") has 230M rows.

This approach may not particularly fast, but it is simple. (Make sure the order by phrase is such that the order is determined.) And it just compares the table data itself -- there may still be differences, in indexes, for example.

(Obviously, -h and -p parameters are specific to my setup.) 


--
--  (psql): showing rowcount:
--

$ select to_char(count(*), repeat('G999',4)) as rowcount from public.x
+;
    rowcount         
-------------------
       232,765,672
(1 row)

#
#  (bash): dump tab-delimited file from the 9.1 instance table
#
$ time echo "copy (select * from public.x order by 1,2,3)
    to stdout delimiter E'\t'" \
  | psql -qtA -h /tmp -p 6542 -d testdb > public.x_on_testdb.txt

real    42m47.107s

#
#  (bash): dump tab-delimited file from the 9.0.3 instance table
#
$ time echo "copy (select * from public.x order by 1,2,3)
    to stdout delimiter E'\t'" \
  | psql -qtA -h localhost -p 55432 -d ms > public.x_on_ms.txt
    
real    57m49.028s

#
#  (bash): compare the two output files
#

$ time md5sum public.x_on_testdb.txt public.x_on_ms.txt
3313cba4d2a1cb14b5ba3dcc5378c33e  public.x_on_testdb.txt
3313cba4d2a1cb14b5ba3dcc5378c33e  public.x_on_ms.txt

[download]

Another approach would be to use postgres' md5 function (documentation), and dump the md5 of a text representation of every row, and compare those.

HTH... and I am interested how you eventually do the comparison; perhaps you could let us now how it goes?

In reply to Re: Verifying 2 databases are in sync with brute force by erix
in thread Verifying 2 databases are in sync with brute force by dwhite20899

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.