Hi John,
The problem seems to be that database 'hcatalog' has connections
disabled in pg_database. If you run 'select * from pg_database', the
column 'datallowconn' should be 'f'. I think a regular user is not
allowed to make modification to the system tables. There was a hidden
GUC to allow DML operations on the catalog, but I can't remember from
the top of my head.
If you manage to get DML modification on the catalog, running something
like `UPDATE pg_database SET datallowconn = 't' where datname =
'hcatalog'` should allow you to access the database and run VACUUM FREEZE.
Best regards,
Aitor
On 17/10/2018 20:26, John Glorioso wrote:
Digging deeper on this, I found that HAWQ has been trying to auto
vacuum hcatalog for months now with the following error:
2018-09-16 14:19:01.646399
UTC,"xxx","yyy",p252354,th493275424,"11.22.33.44","58083",2018-09-16
14:18:57
UTC,0,con433331,cmd114,seg0,slice1,,,sx1,"LOG","00000","GetNewTransactionId:
requesting autovac (xid 1932591104 xidVacLimit
200000003)",,,,,,"select
Ta.client_commissioned_measurement_methodology as v,
count(Ta.client_commissioned_measurement_methodology)::float4/588.000000::float4
as f from my_schema.my_table as Ta where
Ta.client_commissioned_measurement_methodology is not null group by
(Ta.client_commissioned_measurement_methodology) order by f desc limit
25",0,,"varsup.c",98,
2018-09-16 14:19:01.663978
UTC,,,p252782,th493275424,,,,0,,,seg-10000,,,,,"ERROR","3D000","""hcatalog""
database is only for system use",,,,,,,0,,"postinit.c",386,
hcatalog being a system database (and one that isn't even real
technically) seems to be causing the issue. I have successfully
vacuumed the others.
John Glorioso
Lead Engineer
[email protected] <mailto:[email protected]>
On Wed, Oct 17, 2018 at 9:19 AM John Glorioso <[email protected]
<mailto:[email protected]>> wrote:
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]
<mailto:[email protected]>> wrote:
Looks like HAWQ should do auto-vacuum for those databases
which DBA can not connect?
John Glorioso <[email protected] <mailto:[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
<http://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] <mailto:[email protected]>
