Re: questions about wraparound - Mailing list pgsql-general

On Thu, Mar 18, 2021 at 9:56 AM Luca Ferrari <fluca1978@gmail.com> wrote:
>
> What puzzles me is that I'm somehow "locking" the testdb.wa table (by
> inserting a tuple every 20 minutes), so all other tables and databases
> are free to be frozen by an emergency autovacuum. And I was expecting
> the problem to happen due to the testdb.wa table, and therefore the
> hint message to be related to "testdb", not "postgres" database.
> Digging I found that all the database are becoming old, and all the
> tables in every database has the same age.
> Therefore my question is: shouldn't autovacuum be able to freeze other
> tables/databases? I mean, the wraparound problem in this scenario will
> cause problems, but I was expecting different numbers for different
> tables/databases.


And I hit the 1 million transaction theshold, so I tunrned off the
cluster, gone into single user mode and vacuumed the database postgres
(oid = 13811).
Then the system asked me to vacuum another database, on which I've not
generated traffic.

% sudo -u postgres postgres --single -D /postgres/12/data postgres
 WARNING:  database with OID 13811 must be
           vacuumed within 1000000 transactions
 HINT:  To avoid a database shutdown,
        execute a database-wide VACUUM in that database.

PostgreSQL stand-alone backend 12.5
backend> VACUUM VERBOSE
WARNING:  database "backupdb" must be vacuumed within 1000000 transactions

Instead I restarted the multiuser postgres, and I was able to connect
to the problematic database testdb and issue read-write transacctions.
And now the situation is:

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 don't understand why template0 and template1 are two transactions
old (I did two read-write transactions) while all other databases
including the vacuumed postgres are 50 millions old. Clearly that
number is the manual vacuum freeze age:

testdb=> show vacuum_freeze_min_age;
 vacuum_freeze_min_age
-----------------------
 50000000

but (i) why templates have different values and (ii) why vacuuming a
database has changed the situation of all the other databases?
I digged in the logs, and at server restart (after single user mode),
I found a lot of rows related to "aggressive automatic vacuum" that
involved all databases, including templtes:

% sudo grep automatic $PGDATA/log/postgresql.log | grep template1 | head -n 5
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_statistic": index scans: 0
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_type": index scans: 0
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_foreign_server": index scans: 0
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_authid": index scans: 0
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_statistic_ext_data": index scans: 0

and similar rows about other databases exist. Again, I'm not able to
figure out the differences in ages then.

Thanks,
Luca