I see in the documentation that when you vacuum a db, the "SELECT
datname, age(datfrozenxid) FROM pg_database" query should show "one
billion". When I do vacuums, I end up seeing things much closer to
pow(2,30). Is this the actual 'clean start' age?
I was wanting to start using the following query:
SELECT datname, age(datfrozenxid)-pow(2,30) as transactions FROM
pg_database where datallowconn = true having
(age(datfrozenxid)-pow(2,30)) > 500000000;
This would be placed in our monitoring suite to catch any lack of
vacuuming if it were to happen some how (some how the cron job gets
removed for instance). Is this logic sound?
After a fresh vacuum on a db the query
"SELECT datname, age(datfrozenxid)-pow(2,30) as transactions FROM
pg_database where datallowconn = true"
returns values in the low thousands -- like 1152 for the db I actually
vacuumed.
Wayne