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 (25Lakhs) with only 30L transactions left before the shutdown? Will it just consume all remaining transactions ?