gprestore reports "ERROR: syntax error at or near "format_type" (SQLSTATE 42601)"

Products

• VMware Tanzu Data Suite
• VMware Tanzu Data Suite
• Greenplum
• VMware Tanzu Greenplum

Issue/Introduction

After successfully backing up a database with gpbackup, the gprestore fails when restoring an "EXTERNAL WEB TABLE":

20240806:21:22:07 gprestore:gpadmin:mdw:1053331-[INFO]:-Restore Key = 20240806203015
20240806:21:22:07 gprestore:gpadmin:mdw:1053331-[INFO]:-gpbackup version = 1.30.6
20240806:21:22:07 gprestore:gpadmin:mdw:1053331-[INFO]:-gprestore version = 1.30.6
20240806:21:22:07 gprestore:gpadmin:mdw:1053331-[INFO]:-Greenplum Database Version = 7.3.0 build commit:ebf6c7db0034f8928f20252a306c4ecdb9324d81
20240806:21:22:10 gprestore:gpadmin:mdw:1053331-[INFO]:-Creating database
20240806:21:22:11 gprestore:gpadmin:mdw:1053331-[INFO]:-Database creation complete for: db01
20240806:21:22:11 gprestore:gpadmin:mdw:1053331-[INFO]:-Restoring pre-data metadata
20240806:21:55:50 gprestore:gpadmin:mdw:1053331-[ERROR]:-Error encountered when executing statement: CREATE READABLE EXTERNAL WEB TABLE schema01.table01 (
        myint smallint,
        myvarchar character varying,
        mydate date
) EXECUTE 'zstd -dcq /backups/db01/gpseg$GP_SEGMENT_ID/backups/20240719/20240719154009/gpbackup_*_20240719154009_522070895.zst'
FORMAT 'CSV' (delimiter E',' null E'' escape E'"' quote E'"' format_type E'c')
ENCODING 'UTF8'; Error was: ERROR: syntax error at or near "format_type" (SQLSTATE 42601)

Cause

This happens when backing up a database in Greenplum 7.x using gpbackup 1.30.6 and below.

The external table was created as a FOREIGN TABLE in the database.

In Greenplum 7.x FOREIGN TABLE and EXTERNAL WEB TABLE are treated as the same.

The gpbackup utility incorrectly saves the "FOREIGN TABLE" as an "EXTERNAL WEB TABLE".

 

Resolution

Workaround

Use pg_dump to backup the FOREIGN TABLES in the database.

Fix

The fix is expected in gpbackup 1.31.0 and above

Upgrade to 1.31.0 or above and take a new backup.