On Wed, Dec 29, 2010 at 1:53 PM, bricklen <bricklen@xxxxxxxxx> wrote: > On Wed, Dec 29, 2010 at 12:11 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> >> The difference in ctid, and the values of xmin and relfrozenxid, >> seems to confirm my suspicion that this wasn't just random cosmic rays. >> You did something on the source DB that rewrote the table with a new >> relfilenode (possibly CLUSTER or some form of ALTER TABLE; plain VACUUM >> or ANALYZE wouldn't do it). ÂAnd for some reason the standby hasn't >> picked up that change in the pg_class row. ÂI suspect the explanation >> is that your technique for setting up the standby is flawed. ÂYou can't >> just rsync and have a valid snapshot of the DB --- you need to be sure >> that enough WAL gets replayed to fix any inconsistencies arising from >> the time-extended nature of the rsync operation. ÂBut you didn't say >> exactly how you did that. >> > > Definitely no CLUSTER commands were issued, and there should have been > no ALTER commands issued (yesterday was a holiday, no one was here). > Would a TRUNCATE have the same effect though? I grep'd through our > application, and it appears that at least 3 tables get truncated, one > of them several times per hour. The often-truncated table wasn't one > of the bad ones, but the others are the ones I've already identified > as non-existent. Update: Set up the warm standby again and encountered the same issue, with two of the three previously-identified tables -- the ones that can get truncated throughout the day. We're going to try again overnight when those tables are not truncated and see if that gives us a correctly-working standby. >From what I could find from posts to these lists, TRUNCATE commands do reset the relfilenode, and that could account for the issue we are experiencing. What I find odd is that we have one other table that is truncated every 15 minutes (aggregate table) but that one was fine in both attempts at the warm standby. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
