XID wraparound. Overflow fix manual

Intro

ERROR: database is not accepting commands to avoid wraparound data loss in database «my_database»
Hint: Stop the postmaster and use a standalone backend to vacuum that database.
You might also need to commit or roll back old prepared transactions.

If you see that message you are screwed. Your sequental transaction id (XID) growed to MAXINT.

To find out the problem table run

SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') order by age desc;

Maximum XID is 2147483648. The closer value table have, the close database stuck moment.

If you don’t know (OMG) which database caused an error run the following script

SELECT datname, age(datfrozenxid) FROM pg_database;

Fixing

The only way to fix it is to run VACUUM on the problem table. You have some tweaks to speed up the process:

1. Remove delays in vacuum process by running before vacuum:

   set autovacuum_vacuum_cost_delay to 0;

2. Run postgres single mode

   postgres --single -D /usr/local/pgsql/data -S 10GB my_database

   Key -S specifies work-mem size.

3. Set in postgres.conf parameter vacuum_freeze_min_age to 1000000000 (1 billion). It tells postgres to reduce XID during vacuum to 1 billion, not to zero.

WARNING. VACUUM without FREEZE do not recalc indexes, so work faster.

 

Prevention

1. First of all enable autovacuum or schedule manual vacuum procedures.
2. Set parameter vacuum_freeze_table_age to less value: 10-100 millions. Posgres forces freeze recalc, when table XID grows greater than vacuum_freeze_table_age.