Ever since I started working with PostgreSQL I've heard the need to watch transaction IDs. The phrase "transaction ID wraparound" still gives me a shiver. Attached it a short script that works with the monitoring system Nagios to keep an eye on transaction IDs. It should be easy to adapt to any other monitoring system.
It runs the textbook query below and reports how close you are to wraparound.
SELECT datname, age(datfrozenxid) FROM pg_database;
The script detects a wrap at 2 billion. It starts warning once one or
more databases show an age over 1 billion transactions. It reports
critical at 1.5B transactions. I hope everyone out there is vacuuming
*all* databases often.
Hope some of you can use this script!
Tony Wasson
check_pg_transactionids.pl
Description: Perl program
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
