Warning "database XXXX must be vacuumed within YYYY transactions"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

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. More information on wraparound limits, potential scenarios and workarounds is found here.

Refer to this article for instructions on how to check the current database age or to scan a database for relations with high age.

Resolution

Run VACUUM FREEZE for all the databases or for a 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.

nohup psql -c " VACUUM FREEZE " postgres 2>&1 & 

VACUUM FREEZE can also be run against specific objects that have a high age. If the age is not reducing even after running VACUUM FREEZE, then a severity 2 support case needs to be opened with Pivotal Support.

 

Important note about persistent tables

Persistent tables do not need to be vacuumed as they use their own free lists. These tables will have no impact on the database age, and warnings will not be generated because of these tables. 

gp_global_sequence
gp_persistent_relation_node
gp_persistent_database_node
gp_persistent_tablespace_node
gp_persistent_filespace_node

If a VACUUM/VACUUM FULL/VACUUM FREEZE is run against any of the above tables, it is expected that a warning message similar to the following will be displayed:

WARNING: skipping "gp_persistent_database_node" --- cannot vacuum indexes, views, external tables, or special system tables