GPCOPY fails with "pq: relation "xxxxxxxx" already exists" when migrating from 5x to 6x

Products

• VMware Tanzu Greenplum

Issue/Introduction

When using gpcopy to migrate from 5x to 6x there are tables or views that fail to copy due to the following error:

20220713:14:56:52 gpcopy:gpadmin:mdw:009022-[ERROR]:-[Worker 0] [Progress: (0/1) DBs, (895/4640) tables done] Failed to copy table "pedw"."guidedrepair_datamart"."gr_ro_withunit_filtered_base_12142021_1_prt_p201406" => "pedw1"."guidedrepair_datamart"."gr_ro_withunit_filtered_base_12142021_1_prt_p201406"
20220713:14:56:52 gpcopy:gpadmin:mdw:009022-[ERROR]:-[Worker 0] Finished task 3473037_P_DA_V_ with error:
 pq: relation "gr_ro_withunit_filtered_base_pkey" already exists

+ Confirmed that the DB was being copied into a clean empty DB

Environment

Product Version: 6.21

Resolution

Reason is that there is a CONSTRIANT or UNQUIE INDEX of the table has the same name as a CONSTRAINT from another table in the same DB and same schema.  The name of the CONSTRAINT is given in the error.

In 5x you can have 2 or more tables use the same constraint name, but in 6x you can not therefore the gpcopy will fail when it tries to create the second table. 

To find the tables which share the constraint name: 
+ Take a metadata only backup of the DB. "gpbackup --dbname <DB_NAME> --metadata-only"
+ grep the relation definied in the error on the metadata output

gpadmin@gpdb-single-m ~]$ grep gr_ro_withunit_filtered_base_pkey gpbackup_20220713131819_metadata.sql
ALTER TABLE guidedrepair_datamart.gr_ro_withunit_filtered_base_12142021 ADD CONSTRAINT gr_ro_withunit_filtered_base_pkey PRIMARY KEY (ro_num, ro_seq_num, partition_key, repairing_location_key, ro_processed_date_key, ent_admin_cntl);
ALTER TABLE guidedrepair_datamart.gr_ro_withunit_filtered_base ADD CONSTRAINT gr_ro_withunit_filtered_base_pkey PRIMARY KEY (ro_num, ro_seq_num, partition_key, repairing_location_key, ro_processed_date_key, ent_admin_cntl);

+ This will show the tables which are using the same CONSTRAINT.

To resolve this issue, rename the CONSTRAINT of the tables on the 5x system so that there are no duplicate names. 

ALTER TABLE guidedrepair_datamart.gr_ro_withunit_filtered_base_12142021 DROP CONSTRAINT gr_ro_withunit_filtered_base_pkey; 
ALTER TABLE guidedrepair_datamart.gr_ro_withunit_filtered_base_12142021 ADD CONSTRAINT gr_ro_withunit_filtered_base_12142021_pkey PRIMARY KEY (ro_num, ro_seq_num, partition_key, repairing_location_key, ro_processed_date_key, ent_admin_cntl);

Rerun the gpcopy