On Thu, Mar 18, 2021 at 12:14 PM Luca Ferrari <fluca1978@xxxxxxxxx> wrote:
> testdb=> select datname, datfrozenxid, age(datfrozenxid) from pg_database;
> datname | datfrozenxid | age
> -----------+--------------+----------
> postgres | 3318163526 | 50000002
> backupdb | 3318163526 | 50000002
> template1 | 3368163526 | 2
> template0 | 3368163526 | 2
> testdb | 3318163526 | 50000002
> pgbench | 3318163526 | 50000002
>
I did it again: I provoked another wraparound and entered the single
user mode to vacuum. This is the situation before:
backend> select age(datfrozenxid), datname from pg_database
1: age (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname (typeid = 19, len = 64, typmod = -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "postgres" (typeid = 19, len = 64, typmod
= -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "backupdb" (typeid = 19, len = 64, typmod
= -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "template1" (typeid = 19, len = 64, typmod
= -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "template0" (typeid = 19, len = 64, typmod
= -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "testdb" (typeid = 19, len = 64, typmod = -1, byval = f)
----
1: age = "2146483647" (typeid = 23, len = 4, typmod = -1, byval = t)
2: datname = "pgbench" (typeid = 19, len = 64, typmod = -1, byval = f)
backend> vacuum
2021-03-20 11:54:44.878 CET [87179] WARNING: database "backupdb" must
be vacuumed within 1000000 transactions
2021-03-20 11:54:44.878 CET [87179] HINT: To avoid a database
shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared
transactions, or drop stale replication slots.
and after the vacuum I was able to start other in normal mode, without
having to vacuum again another database as suggested by the HINT.
However, templates have a zero age instead of the other databases:
% psql -U luca -c "SELECT age(datfrozenxid), datname from pg_database;" testdb
age | datname
----------+-----------
50000000 | postgres
50000000 | backupdb
0 | template1
0 | template0
50000000 | testdb
50000000 | pgbench
I suspect freezing is doing it "totally" for a idatistemplate
database, even if I don't understand why.
