Pivotal Knowledge Base

Follow

Warning "database XXXX must be vacuumed within YYYY transactions"

Environment

  • Pivotal Greenplum 4
  • Pivotal Greenplum 5

Symptom

All the 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

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. All this, including potential scenarios and workarounds, is explained in detail here.

Use the following article to know 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. 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 i.e.

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 getting 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 

Additional Information

[1] About XIDs and XID wraparound in Greenplum
[2] Database age in Greenplum
[3] Routine maintenance tasks for GPDB
[4] Related resolved issues number 23647 at 4.3.2.1 release notes

 

Comments

Powered by Zendesk