Postgres Transaction ID Wraparound and Autovacuum

Question

I have the postgres (9.3) logs showing

database xxx must be vacuumed within 3017422 transactions

My database age is as shown below

 datname   |    age     
 ----------+------------
 template1 | 1642961202
 template0 | 1642946792
 postgres  | 1692961164
 xxx       | 2144467989

Note :

• We know the exact table which has aged, it is an audit log table.
• We also do not mind losing all the data in that table, if deleting or dropping that table could help

The Problem:

Autovacuum is always running (as soon as we start postgres autovacuum starts off). Therefore, we cannot run Vacuum Freeze on the table - This is blocked on autovacuum.

datid            | 16384
datname          | xxx
pid              | 11587
usesysid         | 10
usename          | postgres
application_name | 
client_addr      | 
client_hostname  | 
client_port      | 
backend_start    | 2018-01-27 11:28:27.288297+05:30
xact_start       | 2018-01-27 11:28:27.313791+05:30
query_start      | 2018-01-27 11:28:27.313791+05:30
state_change     | 2018-01-27 11:28:27.313795+05:30
waiting          | f
state            | active
query            | autovacuum: VACUUM myschema.mytable (to prevent wraparound)

Also, the size of the table in question and its index is unusually high compared to another table which has same structure and content.

• What is the best way to bail out before the transaction wraparound limit runs out?
• Is it OK to drop the table - will it cause any inconsistency with respect to transaction ID?
• Is it OK delete records from the table (2.5 million) with only 3 million transactions left before the shutdown? Will it just consume all remaining transactions?

Answer 1

You can either drop or truncate the table. They will initially block on the autovac job, so you will need to identify that process and then kill it using your system tools, or pg_terminate_backend. At that point, the waiting drop or truncate should be able to acquire the lock faster than that autovac can relaunch.

Answer 2

This is what finally worked for me

1. Stop postgres
2. Launch postgres in single user mode (postgres --single -D /path/to/data/directory dbname). The order of arguments seem to be important. In my case I had to copy the postgresql.conf under the data directory since it was elsewhere.
3. Truncate the table ( I chose truncate over delete or drop table, since it would retain the table structures, other dependencies and reclaim the space without needing a vacuum)
4. Exit postgres.
5. Restart postgres in normal mode. At this point the autovacuum did not relaunch itself.
6. login as postgres user. execute "vacuum freeze;" on the affected database. vacuum freeze will reclaim the transaction IDs but may not rewrite the entire table as a "vacuum full" would, so would be faster. Also vacuum freeze does not lock the tables exclusively, so normal operations against the database could continue.

Some observations - a) although the age of the table frozenxid was reset almost immediately after the truncate, the age of the database frozenxid did no change. b) postgres continued to emit warnings about the wraparound. c) once the vacuum freeze completed on pg_database, the age(datfrozenxid) came down and the warnings stopped.