PG 9.6 (9.6.8). Got an alert today when template0 and template1 were both over 50% towards TXID wraparound. I could vacuum template1 but couldn't vacuum template0 without first allowing connections. This is what it looked like before:
# SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database
ORDER BY 2 DESC;
datname | age | current_setting
--------------------+------------+-----------------
foo_db | 1022106099 | 200000000
template0 | 1000278345 | 200000000
postgres | 643729 | 200000000
template1 | 643729 | 200000000
(4 rows)
I've since allowed connections and ran "vacuumdb --freeze" on it and then immediately disabled the connections to it again. But I'm curious how template0 would be growing in age like this. Even now I see the template0 age growing. I can say that these DB has previously been altered for locale changes as well.
I'm also running a long "vacuum freeze" on foo_db that will take a few days after seeing that autovacuum on a big table had been running on it since Feb 2 and making no progress, with over 850M dead tuples according to pg_stat_all_tables. I estimate 3-4 more days to go on that one. Once that's done I'll be scheduling manual vacuum jobs. Just wondering if that would somehow affect regular template0 cleanup though.
I don't see anything in postgres log related to template0 other than my manual interactions today.
--