Re: questions about wraparound - Mailing list pgsql-general

On Thu, Mar 18, 2021 at 12:14 PM Luca Ferrari <fluca1978@gmail.com> 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.