pg_dumpall Fails with Syntax Error

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When pg_dumpall calls pg_dump internally, it fails while collecting data from catalog tables when there is data corruption for an object. The following error is then issued in the pg_dumpall output:

pg_dump: Error message from server: ERROR: syntax error at or near "," 
LINE 1: ..._policy as p WHERE c.relname = 'institute_markets','transact... 
^ 
pg_dump: The command was: SELECT attrnums from pg_namespace as n, pg_class as c, gp_distribution_policy as p WHERE c.relname = 'ins\ 
titute_markets','transactions_days_to_request' AND n.nspname='public' AND c.relnamespace=n.oid AND c.oid = p.localoid 
pg_dumpall: pg_dump failed on database "non_pii", exiting

In the error shown above, institute_markets is schema and transactions_days_to_request is the table, but the error is misleading in that it is considering both of them as relations.

 

Environment

Cause

This issue happens because of data corruption for the object in the database. This usually happens for various reasons such as data movement between databases etc.

 

Resolution

Take a backup of the identified corrupted object. Recreate and reload the object to the database and try pg_dumpall again.

One way to identify the corrupted object is to copy the data to /dev/null; an example is shown below:

COPY (select * from schema_name.<table_name>) to '/dev/null'