[Bug] gprestore report : -ERROR: schema "gp_toolkit" does not exist (SQLSTATE 3F000)

[RealGrayRabbit]

Cloudberry Database version

---

PostgreSQL 14.4 (Cloudberry Database 1.6.0 build 1) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit compiled on Sep 3 2024 07:22:14 (1 row)

What happened

System can not use gprestore to restore database. The case is descripted as below: Step 1: gpbackup --dbname cloudberry

20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-gpbackup version = 1.6.0
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Cloudberry Database Version = oudberry Database 1.6.0 build 1
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Starting backup of database cloudberry
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Backup Timestamp = 20241018100852
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Backup Database = cloudberry
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Gathering table state information
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Acquiring ACCESS SHARE locks on tables
Locks acquired:  6 / 6 [================================================================] 100.00% 0s
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Gathering additional table metadata
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Getting storage information
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Metadata will be written to /data/coordinator/gpseg-1/backups/20241018/20241018100852/gpbackup_20241018100852_metadata.sql
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Writing global database metadata
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Global database metadata backup complete
20241018:10:08:52 gpbackup:gpadmin:apm:045737-[INFO]:-Writing pre-data metadata
20241018:10:08:53 gpbackup:gpadmin:apm:045737-[INFO]:-Pre-data metadata metadata backup complete
20241018:10:08:53 gpbackup:gpadmin:apm:045737-[INFO]:-Writing post-data metadata
20241018:10:08:53 gpbackup:gpadmin:apm:045737-[INFO]:-Post-data metadata backup complete
20241018:10:08:53 gpbackup:gpadmin:apm:045737-[INFO]:-Writing data to file
Tables backed up:  3 / 3 [==============================================================] 100.00% 3s
20241018:10:08:56 gpbackup:gpadmin:apm:045737-[INFO]:-Data backup complete
20241018:10:08:57 gpbackup:gpadmin:apm:045737-[INFO]:-/usr/local/cloudberry-db-1.6.0/bin/gp_email_contacts.yaml list found, /data/coordinator/gpseg-1/backups/20241018/20241018100852/gpbackup_20241018100852_report will be sent
20241018:10:08:57 gpbackup:gpadmin:apm:045737-[WARNING]:-Unable to send email report: Error reading email contacts file.
20241018:10:08:57 gpbackup:gpadmin:apm:045737-[WARNING]:-Please ensure that the email contacts file is in valid YAML format.
20241018:10:08:57 gpbackup:gpadmin:apm:045737-[INFO]:-Backup completed 

Step 2: dropdb cloudberry

Step3: gprestore --timestamp 20241018100852 --create-db
20241018:10:12:39 gprestore:gpadmin:apm:046007-[INFO]:-Restore Key = 20241018100852
20241018:10:12:39 gprestore:gpadmin:apm:046007-[INFO]:-gpbackup version = 1.6.0
20241018:10:12:39 gprestore:gpadmin:apm:046007-[INFO]:-gprestore version = 1.6.0
20241018:10:12:39 gprestore:gpadmin:apm:046007-[INFO]:-Database Version = oudberry Database 1.6.0 build 1
20241018:10:12:39 gprestore:gpadmin:apm:046007-[INFO]:-Creating database
20241018:10:12:39 gprestore:gpadmin:apm:046007-[INFO]:-Database creation complete for: cloudberry
20241018:10:12:39 gprestore:gpadmin:apm:046007-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  0 / 17 [-------------------------------------------------------]   0.00%20241018:10:12:39 gprestore:gpadmin:apm:046007-[WARNING]:-Schema pg_ext_aux already exists

20241018:10:12:39 gprestore:gpadmin:apm:046007-[CRITICAL]:-**ERROR: schema "gp_toolkit" does not exist (SQLSTATE 3F000)**
20241018:10:12:39 gprestore:gpadmin:apm:046007-[INFO]:-/usr/local/cloudberry-db-1.6.0/bin/gp_email_contacts.yaml list found, /data/coordinator/gpseg-1/backups/20241018/20241018100852/gprestore_20241018100852_20241018101239_report will be sent
20241018:10:12:39 gprestore:gpadmin:apm:046007-[WARNING]:-Unable to send email report: Error reading email contacts file.
20241018:10:12:39 gprestore:gpadmin:apm:046007-[WARNING]:-Please ensure that the email contacts file is in valid YAML format.

Why and how to resolve it?

What you think should happen instead

Since I use gpbackup and gprestore as user manual, It shall be able to restore the backed database normally. But it seems that gprestore can not create system schema "gp_toolkit"!

How to reproduce

As descripted in "What happen" section.

Operating System

Rocky Linux release 8.10 (Green Obsidian)

Anything else

No response

Are you willing to submit PR?

• [ ] Yes, I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[github-actions[bot]]

Hey, @RealGrayRabbit welcome!🎊 Thanks for taking the time to point this out.🙌

[my-ship-it]

Hi @Terry1504 , could you please have a look at it when have time? Thanks

[yihong0618]

@my-ship-it @RealGrayRabbit

reproduce:

createdb cloudberrydb
psql cloudberrydb -c 'create extension gp_toolkit;'

and use the issue steps

reason:

• its a gpbackup issue for cloudberry/gpbackup
• for gp_toolkit in gp/cloudberry the gp_toolkit maybe installed automatically so when we gpbackup or gprestore the toolkit may not be found

how to solve it:

diff --git a/backup/predata_functions.go b/backup/predata_functions.go
index 61cd31d..e7a9bae 100644
--- a/backup/predata_functions.go
+++ b/backup/predata_functions.go
@@ -278,7 +278,10 @@ func PrintCreateCastStatement(metadataFile *utils.FileWithByteCount, toc *toc.TO
 func PrintCreateExtensionStatements(metadataFile *utils.FileWithByteCount, toc *toc.TOC, extensionDefs []Extension, extensionMetadata MetadataMap) {
        for _, extensionDef := range extensionDefs {
                start := metadataFile.ByteCount
-               metadataFile.MustPrintf("\n\nSET search_path=%s,pg_catalog;\nCREATE EXTENSION IF NOT EXISTS %s WITH SCHEMA %s;\nSET search_path=pg_catalog;", extensionDef.Schema, extensionDef.Name, extensionDef.Schema)
+               metadataFile.MustPrintf(
+                               "\n\nCREATE SCHEMA IF NOT EXISTS %[1]s;\nSET search_path=%[1]s,pg_catalog;\nCREATE EXTENSION IF NOT EXISTS %[2]s WITH SCHEMA %[1]s;\nSET search_path=pg_catalog;\n",
+                               extensionDef.Schema, extensionDef.Name)
+
 
                section, entry := extensionDef.GetMetadataEntry()
                toc.AddMetadataEntry(section, entry, start, metadataFile.ByteCount)

you can change the code in https://github.com/cloudberrydb/gpbackup or cherrypick from gpdb/gpbackup or https://github.com/arenadata/gpbackup then

make build

you can fix the issue

[tuhaihe]

Hi @yihong0618 Great catch! Would you like to help submit one PR to the gpbackup repo to fix this?

[yihong0618]

Hi @yihong0618 Great catch! Would you like to help submit one PR to the gpbackup repo to fix this?

yes but maybe later next week a little busy these days

[my-ship-it]

Great work @yihong0618

[yihong0618]

@my-ship-it @tuhaihe https://github.com/cloudberrydb/gpbackup/pull/9