gpsd Fails with Message "pg_dumpall: could not connect to database"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Pivotal Support recommends running gpsd to troubleshoot planner and performance related issues. The utility collects the complete schema for a given database along with the catalog values of statistics.usage : gpsd DBNAME -U gpadmin > gpsd_dbname.out

usage : gpsd DBNAME -U gpadmin > gpsd_dbname.out

Running gpsd results in the error "pg_dumpall: could not connect to database <database-name>". Here the error message clearly points towards the connection with the database. You will see this error if the database name used with gpdb does not exist (check if the database is spelled correctly and using the same case-- for uppercase dbname quote it using double quotes"):

[gpadmin@localhost pg_log]$ gpsd test

-- Greenplum database Statistics Dump
-- Copyright (C) 2007 - 2014 Pivotal
-- Database: test
-- Date:     2016-02-11
-- Time:     15:20:15.982000
-- CmdLine:  /usr/local/greenplum-db/./bin/gpsd test
-- Version:  PostgreSQL 8.2.15 (Greenplum Database 4.3.7.1 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Jan 21 2016 15:51:02

Error while dumping globals.

pg_dumpall: could not connect to database "test"

Environment

Cause

The issue you have noticed above was related to the database name. There could be other issues and error messages. This utility, like other GPDB utilities, would expect some of the parameters to be set like PGPORT and assumes that the connection has been made locally. You have to specify the port and host if those are not the default ones using the options with the help as shown below:

[gpadmin@localhost pg_log]$ gpsd --help
Usage: gpsd [options] 

Options:
  --version             show program's version number and exit
  -?, --help            Show this help message and exit
  -h HOST, --host=HOST  Specify a remote host
  -p PORT, --port=PORT  Specify a port other than 5432
  -U USER, --user=USER  Connect as someone other than current user
  -s, --stats-only      Just dump the stats, do not do a schema dump
[gpadmin@localhost pg_log]$

Resolution

Execute gpsd by supplying the parameter to connect the database as indicated below:

gpsd -h <master-hostname> -p <port> -U <gpadmin OR user who created the cluster ><database-name>