gprestore schema restore failed with error [schema "xxx" does not exist (SQLSTATE 3F000)]

Products

• VMware Tanzu Greenplum

Issue/Introduction

When doing a database restore with gprestore, we might see some of the views may fail to be restored with the below error: (ERROR: schema "xxx" does not exist (SQLSTATE 3F000))

20220725:11:42:20 gprestore:gpadmin:gp-aio-01:102030-[DEBUG]:-Error encountered when executing statement: CREATE VIEW a.view1 AS SELECT table1.generate_series FROM b.table1; Error was: ERROR: schema "b" does not exist (SQLSTATE 3F000)
20220725:11:42:20 gprestore:gpadmin:gp-aio-01:102030-[CRITICAL]:-ERROR: schema "b" does not exist (SQLSTATE 3F000)

The issue may be observed when matching the below condition:
1. restore data to a new database
2. the schema we are restoring includes tables from other schema. 

Here is an example:

# create view a.view1 as select * from b.table1 ;
# gpbackup --backup-dir /data/backup/test_0725 --include-schema a --dbname gpadmin
 
# gprestore --create-db --include-schema a --timestamp 20220725114122 --redirect-db gpadmin_new --backup-dir /data/backup/test_0725
20220725:11:42:18 gprestore:gpadmin:gp-aio-01:102030-[INFO]:-Restore Key = 20220725114122
20220725:11:42:18 gprestore:gpadmin:gp-aio-01:102030-[INFO]:-Creating database
20220725:11:42:20 gprestore:gpadmin:gp-aio-01:102030-[INFO]:-Database creation complete for: gpadmin_new
20220725:11:42:20 gprestore:gpadmin:gp-aio-01:102030-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  0 / 4 [--------------------------------------------------------]   0.00%20220725:11:42:20 gprestore:gpadmin:gp-aio-01:102030-[CRITICAL]:-ERROR: schema "b" does not exist (SQLSTATE 3F000)
20220725:11:42:20 gprestore:gpadmin:gp-aio-01:102030-[INFO]:-Found neither /opt/greenplum_5.29.1/bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
20220725:11:42:20 gprestore:gpadmin:gp-aio-01:102030-[INFO]:-Email containing gprestore report /data/backup/test_0725/gpdb_5.29.1_-1/backups/20220725/20220725114122/gprestore_20220725114122_20220725114218_report will not be sent

Environment

Product Version: 5.29

Resolution

the workaround for this issue is to skip this view (--exclude-table) or create all related objects required by that view on the target DB before doing the restore. 

[gpadmin@gp-aio-01][~]$ gprestore  --include-schema a --timestamp 20220725114122 --redirect-db gpadmin_new --backup-dir /data/backup/test_0725 --exclude-table a.view1
20220725:14:34:48 gprestore:gpadmin:gp-aio-01:042732-[INFO]:-Restore Key = 20220725114122
20220725:14:34:49 gprestore:gpadmin:gp-aio-01:042732-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  2 / 2 [=====================================================] 100.00% 0s
20220725:14:34:49 gprestore:gpadmin:gp-aio-01:042732-[INFO]:-Pre-data metadata restore complete
20220725:14:34:49 gprestore:gpadmin:gp-aio-01:042732-[INFO]:-Restoring post-data metadata
20220725:14:34:49 gprestore:gpadmin:gp-aio-01:042732-[INFO]:-Post-data metadata restore complete
20220725:14:34:49 gprestore:gpadmin:gp-aio-01:042732-[INFO]:-Found neither /opt/greenplum_5.29.1/bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
20220725:14:34:49 gprestore:gpadmin:gp-aio-01:042732-[INFO]:-Email containing gprestore report /data/backup/test_0725/gpdb_5.29.1_-1/backups/20220725/20220725114122/gprestore_20220725114122_20220725143448_report will not be sent
20220725:14:34:49 gprestore:gpadmin:gp-aio-01:042732-[INFO]:-Restore completed successfully