I believe the issue is the FREEZE vacuum option. Is there a setting in config that I can verify? Better yet since we no longer use hcatalog to connect to hive is there a way to just simply disable or clear datfrozenxid? I don't imagine dropping hcatalog would work and I am hesitant to try to manually update that value to null.
-John On Wed, Oct 17, 2018, 1:49 AM Paul Guo <[email protected]> wrote: > Looks like HAWQ should do auto-vacuum for those databases which DBA can > not connect? > > > John Glorioso <[email protected]> 于2018年10月17日周三 上午6:32写道： > >> 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] >> >> >>
