I have a fairly large database(approx. 1.5TB) that is backed up by a warm standby database using log shipping(PITR). This setup had been running for a couple of months when I ran into a problem on the primary DB and had to failover to the standby DB. This worked as expected.
Shortly thereafter(Sometime over the long weekend of course), Postgres shutdown the database to avoid XID wraparound data loss. I presume there were warnings in the log about running out of XIDs, but nobody noticed in time and given what transpired after that I don't think it would have mattered if they had.
As per the documentation, I started the DB in single user mode and attempted to do a full database vacuum. After this ran for about 12hours the pg_xlog directory ran out of disk space. I'm not sure I understand why anything is written to pg_xlog as part of the vacuum process, perhaps someone can enlighten me.
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.
I am currently running PG 8.2.4 on FreeBSD.
So my questions are:
1) What is the recommended way to either solve or get around this problem in PG 8.2.4?
2) Is this "problem" fixed in some more current version of Postgres? I didn't see any mention of it in release notes up to PG 8.3.3?
3) Does this mean that if you are trying to use a warm standby DB with PITR, you need to make a new base backup of your primary DB every 1.5billion transactions, or there abouts, to avoid the problem. If so, I think this should be documented in the "Caveats" section of "Continuous Archiving and Point-in-time-recovery(PITR)" section of the manual.
Regards...
Mark Sherwood
Use Windows Live Messenger to send messages to your buddies on their mobile phones
Find out more on our PC to Mobile website