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?