gprestore reports : ERROR: unrecognized parameter "blocksize" (SQLSTATE 22023) for AO materialized views

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:

20240810:19:35:53 gprestore:gpadmin:tgbb-mdw.ruv.de:463734-[INFO]:-Restore Key = 20240810160007
20240810:19:35:53 gprestore:gpadmin:tgbb-mdw.ruv.de:463734-[INFO]:-gpbackup version = 1.30.6
20240810:19:35:53 gprestore:gpadmin:tgbb-mdw.ruv.de:463734-[INFO]:-gprestore version = 1.30.6
20240810:19:35:53 gprestore:gpadmin:tgbb-mdw.ruv.de:463734-[INFO]:-Greenplum Database Version = 7.3.0 build commit:ebf6c7db0034f8928f20252a306c4ecdb9324d81
20240810:19:35:58 gprestore:gpadmin:tgbb-mdw.ruv.de:463734-[INFO]:-Creating database
20240810:19:35:58 gprestore:gpadmin:tgbb-mdw.ruv.de:463734-[INFO]:-Database creation complete for: dssprod
20240810:19:35:59 gprestore:gpadmin:tgbb-mdw.ruv.de:463734-[INFO]:-Restoring pre-data metadata
20240810:20:08:10 gprestore:gpadmin:tgbb-mdw.ruv.de:463734-[ERROR]:-Error encountered when executing statement: CREATE MATERIALIZED VIEW schema01.matvw_test WITH (blocksize=32768, compresslevel=3, compresstype=zstd, checksum=true) AS  
    SELECT t.col01,
           t.col02,
           t.col03,
             :
             :
           t.coln
     FROM table01 t
  WHERE ((1 = 1) AND (t.col01 <> t.col10))
WITH NO DATA
DISTRIBUTED RANDOMLY; Error was: ERROR: unrecognized parameter "blocksize" (SQLSTATE 22023)
   :
   :

Cause

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

There were catalog changes materialized views being stored with AppendOptimised storage.

The gpbackup utility fails to capture the "appendoptimised=true" option DDL for the materialized views.

Resolution

Workaround

Use pg_dump to backup the materialized views

Fix

The fix is expected in gpbackup 1.31.0.

Upgrade to gpbackup/gprestore to 1.31.0 or above and take another backup and restore the new backup.