- Pivotal Greenplum 4
- Pivotal Greenplum 5
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
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.
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