Saturday, January 16, 2016

postgresql corruption: transaction wrapped around



postmaster down with the following errors repetitively at the lower section of the log.

Mild wraparound issue

ERROR:  database is not accepting commands to avoid wraparound data loss in database "VCDB"
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.


VACUUM FULL ANALYZE <entire database>


Changes are, this issue may not be able to cure. 

Severe wraparound issue

In an extreme case, the wraparound already happened. From my experience, VACUUM FULL or FREEZE or VACUUM on specific offending table (e.g VACUUM VPX_VM) did not work always work.  

ODBC error: (08001) - [unixODBC]FATAL: MultiXactId 1085312546 has not been created yet -- apparent wraparound

Ideally, DBA should restore a good backup. Usually, by the time encountering this type of issue, the database is beyond repaired and no whole lot of luck with it. DBA should really constantly watch out for this type of issue and performing VACUUM FULL at the database as part of maintenance as current postgresql have 4 billion of it before too late. Perhaps, do it once a year ?

DBA can track the current and used up/frozed xid. there should be fairly closed and far from 4 billion or 2^32.

select txid_current(), txid_current_snapshot();

SELECT max(age(datfrozenxid)) FROM pg_database;