Re: How can I avoid Frozenxid wraparound on failover to a standby(PITR) database?

OK, I am glad to hear that what I thought might be the problem is fixed in PG 8.2. I feel much better about that :-)

That must mean there was something unusual about this particular DB. I have several other "cloned" DB's configured the same way(They haven't failed over yet), and checking the age of frozenXIDs on them show that they are fine. The primary DB box in my problem setup is completely inaccessible, so I have no way to check what the FrozenXID values were there. One thing I could try is forcing a failover on one of the clones and see if it exhibits the same behaviour. From what you say I'm guessing the answer is that they will be OK.

My standard settings in the config file are:
autovacuum_freeze_max_age = 2,000,000,000
vacuum_freeze_min_age = 100,000,000
This particular DB, has two tables that are quite heavily updated(approx 1,000,000 rows/hour), with the rest of the DB load being around 8,000,000 row inserts/hour.

Previously, when I was watching pg_activity on this DB, the autovacuum process seemed to be always vacuuming one or the other of those heavily updated tables(typically for an hour or more at a time).

Could another explanation for what happened be that the tables being inserted to, were not getting their XIDs updated quickly enough by the autovacuum process on the primary DB because it was spending all its time on those two tables? (ie. the XID wraparound problem would have ocurred even without the failover) .

If thats the case, then once I get this standby DB vacuumed and back up, I will set up a cron job to look at how the max_age of the relation XIDs changes over time.

Thanks,

Mark Sherwood

> To: arctic_toucan(at)hotmail(dot)com> CC: pgsql-admin(at)postgresql(dot)org> Subject: Re: [ADMIN] How can I avoid Frozenxid wraparound on failover to a standby(PITR) database? > Date: Fri, 5 Sep 2008 12:52:17 -0400> From: tgl(at)sss(dot)pgh(dot)pa(dot)us> > Arctic Toucan <arctic_toucan(at)hotmail(dot)com> writes:> > I next started looking at the age(refrozenxid) of the tables in my DB, and was surprised to see that over 4000 of the 5000 tables in this DB had an age over 2Billion. So thats 4000 tables representing over a terabyte of data that need to be vacuumed! I am now vacuuming those tables one at a time, which is taking a long time(This is a scripted process). So there is no way I could have vacuumed the tables quickly enough even given a warning of impending XID wraparound.> > > Looking through the support mailing lists(Bugs) I see some discussion about the frozenxid updates on the master not being propogated to the slave through the WAL logs, and comments from Tom, Alvaro and Heikki suggesting that they were looking into a solution for PG 8.3 and needed a way around the problem in PG 8.2. > > Hmm ... that did get fixed in 8.2> http://archives.postgresql.org/pgsql-committers/2006-11/msg00026.php> so I'm a bit confused about what happened here. What were your> autovacuum settings on the old master? Particularly> autovacuum_freeze_max_age?> > regards, tom lane
_________________________________________________________________