On Mon, Oct 22, 2012 at 12:31 PM, Chris Angelico <rosuav@xxxxxxxxx> wrote: > On Tue, Oct 23, 2012 at 6:26 AM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote: >> What did you do to look for corruption? That PosgreSQL succeeds at >> going through crash-recovery and then starting up is not a good >> indicator that there is no corruption. > > I fired up Postgres and looked at the logs for any signs of failure. > >> Did you do something like compute the aggregates on pgbench_history >> and compare those aggregates to the balances in the other 3 tables? > > No, didn't do that. My next check will be done over the network > (similar to diskchecker), with a script that fires off requests, waits > for them to be confirmed committed, and then records a local copy, and > will check that local copy once the server's back up again. That'll > tell me if transactions are being lost. If you like Perl, the count.pl from this message might be a useful starting point: http://archives.postgresql.org/pgsql-hackers/2012-02/msg01227.php It was designed to check consistency after postmaster crashes, not OS crashes, so the checker runs on the same host as postgres does. Obviously for pull-the-plug test, you need run it on a different host; so all the DBI->connect(....) calls need to be changed to do that. > I'm kinda feeling my way in the dark here. Will check out the > aggregates on pgbench_history when I get to work today; thanks for the > tip! Here's an example with pgbench_accounts, the other 2 should look analogous. select aid, abalance, count(*) from (select aid,abalance from pgbench_accounts union all select aid, sum(delta) from pgbench_history group by aid) as foo group by aid, abalance having abalance!=0 and count(*)!=2; This should return zero rows. Any other result indicates corruption. pgbench truncates pgbench_history, but does not reset the balances to zero on the other tables. So if you want to run the test repeatedly, you have to do pgbench -i between runs, or manually reset the balance columns. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
