Warning "database XXXX must be vacuumed within YYYY transactions"
Article ID: 295913
Updated On:
Products
Issue/Introduction
All commands executed on the cluster return a WARNING message similar to the one shown below:
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres". WARNING: database "postgres" must be vacuumed within XYZ transactions
Environment
Cause
This warning is being displayed to notify the database users/admins that the database is getting close to the wraparound limit. In GPDB, all transactions get assigned a transaction ID (XID). This value increases with every query or transaction.
Resolution
Option 1:
Run VACUUM FREEZE on all tables in specific database showing high age.
Note: Connecting to a specific database and running "VACUUM FREEZE" is possible. However, it is recommended to run this command through nohup to avoid risks of the session being disconnected.
For example: (Change the "<database_name>" to the correct database)
nohup psql -c " VACUUM FREEZE " <database_name> 2>&1 &
This can take a long time to run and the age of the database may not reduce quickly.
Option 2:
To find and VACUUM the oldest tables in a database see article Vacuuming old tables in a database to reduce its age
This will target the oldest tables and should reduce the age quicker.
Additional Information
See Routine System Maintenance Tasks documentation for required routine tasks to avoid the age of the databases getting too high.
Feedback
