template0 needing vacuum freeze?

From: Don Seiler <don(at)seiler(dot)us>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: template0 needing vacuum freeze?
Date: 2020-05-16 17:19:13
Message-ID: CAHJZqBAGheciTgcKZ9J=Q=A+CdtH0LOUbtqsX8nSvBS1RKggQg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

--
Don Seiler
www.seiler.us

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Nolan 2020-05-16 17:29:34 Re: Removing Last field from CSV string
Previous Message Adrian Klaver 2020-05-16 16:53:17 Re: Removing Last field from CSV string