Pivotal Knowledge Base

Follow

Restore Fails with Error "schema ... does not exist"

Environment

Pivotal Greenplum Database (GPDB)

Symptom

Restore fails with an error.

Error Message

20150522:08:30:06|gp_restore-[ERROR]:-Error executing query SELECT * FROM gp_read_backup_file('db_dumps/20150521', '20150521150629', 2) : ERROR:  Backup File /data/master/gpseg-1/db_dumps/20150521/gp_restore_status_1_1_20150521150629 Type 2 could not be be found

And, the master log reports DDL are failing because schema that is needed for the table creation does not exist. 

Cause

Backup was created with option -C (clean objects before restoring).

From the documentation

-C (clean catalog before restore)

Clean out the catalog schema prior to restoring database objects. gpcrondump adds the DROP command to the SQL script files when creating the backup files. When the script files are used by the gpdbrestore utility to restore database objects, the DROP commands remove existing database objects before restoring them.

The objects are supposed to be recreated when restore is running. In some GPDB versions, the backup SQL file is restored improperly and the "set search_path" statements referring to some schemas are executed before the schema is created. Example from the backup dump file on the master segment:

DROP TABLE public.gp_conf_hc;
DROP TABLE public.edjus_dim_restriction_excp;

SET search_path = edjus_hold, pg_catalog;
...
...
CREATE SCHEMA edjus_hold;
ALTER SCHEMA edjus_hold OWNER TO gpadmin;

The issue has been worked on the bug MPP-25495.

Resolution

Upgrade Pivotal Greenplum to 4.3.4.2 where this is fixed.

Comments

Powered by Zendesk