PostgreSQL 9 Administration Cookbook - Second Edition

Avoiding transaction wraparound

To many users, transaction wraparound sounds like a disease from space. Mentioning transaction wraparound usually earns the speaker points for technical merit. Let's take a look at it and how to avoid it.

Getting ready

First of all, have you ever seen this message?

WARNING: database "postgres" must be vacuumed within XXX transactions.
HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions.

Even worse is the following message:

ERROR: database is not accepting commands to avoid wraparound data loss in database "template0"
HINT:  Stop the postmaster and use a standalone backend to vacuum that database.
You might also need ...