For posterity, it appears my issues were https://wiki.postgresql.org/wiki/Nov2013ReplicationIssue.
On Nov 4, 2013, at 3:48 PM, Ben Chobot wrote: > Anybody? I've tried this again on another streaming replication server, and > again had pg_toast errors until I re-basebackup'd it. Does it make sense to > try disabling streaming replication for the backup and just use wal replay > from the archive? My slave will be a bit behind, but I'll have drained the > clients from it anyway, so that's not a big deal for a temporary thing. > > On Nov 1, 2013, at 1:44 PM, Ben Chobot wrote: > >> I've got a bunch of independent database clusters, each with a couple of >> streaming replication slaves. I'm starting to upgrade them to 9.1.10, but on >> 3 of the 3 I've tried so far, this has somehow resulted in data corruption. >> I'm hoping it was the upgrade itself that caused the corruption, instead of >> 9.1.10 just exposing some latent corruption, for two reasons: >> 1. after doing a basebackup from the master, the same queries that started >> causing problems on the new 9.1.10 node no longer cause problems >> 2. the second 9.1.9 slave of each cluster doesn't have issues that the >> newly-upgraded 9.1.10 node did have with the same query. >> >> Interestingly, this seems to only be affecting my replication slaves. We >> snapshot all our clusters at a filesystem level and restore those snapshots >> into standalone, non-replicating test environments, and those test >> environments were all upgraded to 9.1.10 without issue. >> >> All my servers are running a mostly-stock config of 9.1.9, which has been >> modified like so: >> >> max_connections = 1000 # we don't really go this high, fwiw >> shared_buffers = 6000MB # 60GB machines >> wal_level = hot_standby >> hot_standby = on >> effective_cache_size = 34278MB >> log_min_duration_statement = 50 >> log_duration = on >> >> They store their data on XFS, with the options noatime,allocsize=1m. >> >> As for the errors, on two nodes, a primary key lookup resulted in multiple >> rows. Interestingly, this was on the same index for both nodes, which I >> would find unlikely if it was just random corruption or hardware-related, >> given that the systems have the same schema but independent data and usage. >> Using pg_reorg on that table on the master node fixed the issue on the node >> I tried it on, but exposed another problem: >> >> ERROR: unexpected chunk number 3 (expected 1) for toast value 67728890 in >> pg_toast_2619 >> >> The pg_toast error occurred on another node too. Sadly these were are >> production systems and I didn't know if the pg_toast table was the same for >> all three nodes, and it's quite possible that all three nodes would have >> exhibited the same set of problems if I'd let them run production queries >> long enough. >> >> Anyway, naturally this is making me nervous to do more upgrades, so I'm >> hoping I can figure out if it's the upgrade that's killing me, or if 9.1.10 >> is exposing underlying problems, or what. Unfortunately I have no idea what >> to look for. >> >> These system are ubuntu precise, and I did recall this from my memory: >> >> http://www.postgresql.org/message-id/21697.1343141...@sss.pgh.pa.us >> >> ... but it appears that the fix for that has already been applied to my >> postgresql-common package. Looking over the postgres logs before the >> upgrade, I don't see anything abnormal. >> >> Does this behavior sound familiar to anybody else out there, or does anybody >> have suggestions of what to look for or try? >
