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

If I recall correctly, the reason we originally set autovacuum_freeze_max_age = 2,000,000,000 was that most of the data in the DB is a rolling window(partitioned tables) and so by having the max age so high most of the tables in the DB would never need to be vacuumed at all thus allowing the autovacuum to concentrate on the high update rate of the two tables of concern.

It seems that we may have gotten to the point where our rolling window exceeds 2billion transactions and that approach is no longer viable so we need to change our max_age as you suggest.

Thanks, as usual, for the help.Regards...

Mark

> 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 14:45:49 -0400> From: tgl(at)sss(dot)pgh(dot)pa(dot)us> > Arctic Toucan <arctic_toucan(at)hotmail(dot)com> writes:> > My standard settings in the config file are:> > autovacuum_freeze_max_age = 2,000,000,000> > vacuum_freeze_min_age = 100,000,000> > Ah, well, there's the issue. It could be expected that no tuple> freezing would happen before autovacuum_freeze_max_age. I'd take a zero> off that value I think --- you're not leaving yourself a lot of daylight> between freeze_max_age and wraparound. You could get away with that> setting in a smaller database that didn't take so long to vacuum, but> in a bigger one you want to freeze a bit more aggressively IMHO.> > regards, tom lane
_________________________________________________________________