Backup taken with gpbackup in Greenplum 4.3.x failed to be restored to Greenplum 6.x with error 'relation "xxx" already exists'

Products

• VMware Tanzu Greenplum

Issue/Introduction

The error can occur if there is a table in Greenplum 4.3.x with an index named similar to <table_name>_pkey and a primary key with a different name. For example:

test_backup=# CREATE TABLE public.test3(
test_backup(# id SERIAL,
test_backup(# name TEXT,
test_backup(# test_date date
test_backup(# )
test_backup-# DISTRIBUTED BY (id);
NOTICE:  CREATE TABLE will create implicit sequence "test3_id_seq" for serial column "test3.id"
CREATE TABLE
test_backup=# 
test_backup=# create unique index test3_pkey ON public.test3 USING btree(id);
CREATE INDEX
test_backup=# 
test_backup=# alter table public.test3 ADD PRIMARY KEY(id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "test3_pkey1" for table "test3"
ALTER TABLE

By default the primary key would have the name "<tablename>_pkey", but as that name is already in use, a "1" is added to the end of the name, "test3_pkey1" in the above example.

When the table is backed up by gpbackup in Greenplum 4.3.x and restored with gprestore the following error is reported:

20220421:15:36:22 gprestore:gpadmin:support-gpddb6-mdw:032733-[INFO]:-Restoring post-data metadata
Post-data objects restored:  0 / 2 [-------------------------------------------------------]   0.00%
20220421:15:36:22 gprestore:gpadmin:support-gpddb6-mdw:032733-[CRITICAL]:-ERROR: relation "test3_pkey" already exists (SQLSTATE 42P07)

Checking the file "gpbackup_20220421152912_metadata.sql" on master, it shows that is the unique key and primary key share the same name:

ALTER TABLE ONLY public.test3 ADD CONSTRAINT test3_pkey PRIMARY KEY (id);
CREATE UNIQUE INDEX test3_pkey ON public.test3 USING btree (id);

This issue should only occur when the source cluster is Greenplum 4.3.x.
The error will be reported regardless of the Greenplum version  on the target cluster as the dump information will try to create two indices with the same name.

There is no issue if the source cluster is running Greenplum 5.x or 6.x as the dump will record the correct name for the primary key.

Environment

Product Version: 4.3.33

Resolution

Workarounds

• Avoid creating non-primary key index with name like "<tablename>_pkey" in Greenplum 4.3.x.
• Drop the non-primary key before backup using gpbackup. It can be re-created after the restore if needed.
• Restore with the option "--on-error-continue" and manually create the missing index.