We are running a decent sized cluster (20 nodes) and have started receiving the following error today:
WARNING: database "hcatalog" must be vacuumed within 42324912 transactions (seg27 worker09.xxx.com:40000 pid=689628) HINT: To avoid a database shutdown, execute a full-database VACUUM in "hcatalog". I found this Pivotal article: https://community.pivotal.io/s/article/About-XIDs-and-XID-Wraparound-in-Greenplum which discusses xid wrap around and that led me to running a query which identified tables in each database that need a vacuum freeze run against it. https://community.pivotal.io/s/article/Vacuuming-Old-Tables-in-a-Database-to-Reduce-its-Age The issue, however, is that it is complaining about the hcatalog database which is not an accessible database as it was used to query data in Hive at one point. We have since disabled Hive and no longer need that external PXF link but I am unclear on how to remove it. This article shows how to detect database age and I can run the first part but it is for Greenplum and I do not know the equivalent properties in HAWQ for the case statement to work. https://community.pivotal.io/s/article/How-to-check-database-age-in-Greenplum The results for the following sql statement are below: SELECT gp_segment_id, datname, age(datfrozenxid) age FROM pg_database UNION ALL SELECT gp_segment_id, datname, age(datfrozenxid) age FROM gp_dist_random('pg_database'); gp_segment_id | datname | age ---------------+------------+------------ -1 | hcatalog | 212776719 -1 | template1 | 39104 -1 | template0 | 212775841 -1 | production | 10604565 -1 | postgres | 39104 -1 | gpadmin | 39104 0 | template1 | 2100536422 0 | hcatalog | 2100537300 (8 rows) I cannot access hcatalog or template1 to issue vacuum freeze statements against the tables and am unsure how to proceed. Any help is greatly appreciated! John Glorioso Lead Engineer [email protected]
