title: verifying data consistency between two postgresql databases
body: <p>This is specifically about maintaining confidence in using various replication solutions that you'd be able to failover to the other server without data loss. Or in a master-master situation that you could know within a reasonable amount of time if one of the databases has fallen out of sync.</p>
  
  <p>Are there any tools out there for this, or do people generally depend on the replication system itself to warn over inconsistencies? I'm currently most familiar with postgresql WAL shipping in a master-standby setup, but am considering a master-master setup with something like PgPool. However, as that solution is a little less directly tied with PostgreSQL itself (my basic understanding is that it provides the connection an app would use, thus intercepting the various SQL statements, and would then send them on to whatever servers were in its pool), it got me thinking more about actually verifying data consistency.</p>
  
  <p>Specific requirements:</p>
  
  <ol>
  <li><p>I'm not talking about just table structure. I'd want to know that actual record data is the same, so that I'd know if records were corrupted or missed (in which case, I would re-initialize the bad database with a recent backup + WAL files before bringing it back into the pool)</p></li>
  <li><p>Databases are in the order of 30-50 GB. I'm doubting that raw SELECT queries would work very well.</p></li>
  <li><p>I don't see the need for real-time checking (though it would, of course, be nice). Hourly or even daily would be better than nothing.</p></li>
  <li><p>Block-level checking wouldn't work. It would be two databases with independent storage.</p></li>
  </ol>
  
  <p>Or is this type of verification simply not realistic?</p>