gpcopy fails due to sequence already exists

Products

• VMware Tanzu Greenplum

Issue/Introduction

For gpcopy 2.3.0 and below, there are certain times when a table uses a sequence like below:

CREATE TABLE foo (
    id bigint DEFAULT nextval('foo_id_seq'),
    source character varying(20) NOT NULL,
    name character varying(20)
)
DISTRIBUTED BY (id) PARTITION BY LIST(source) 
          (
          PARTITION bps VALUES('RETAIL', 'BPS') WITH (tablename='foo_1_prt_bps', appendonly='false' ), 
          PARTITION custody VALUES('CUSTODY', 'CUST') WITH (tablename='foo_1_prt_custody', appendonly='false' )
          );

CREATE SEQUENCE foo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE foo OWNER TO testuser1;

When a sequence exists and ALTER TABLE foo OWNER TO testuser1; is used, gpcopy handles implicit and explicit sequences differently when it is restoring to the target database:
 

1. For an implicit sequence, like the sequence in CREATE TABLE t1 (id SEIRAL). GPDB will create a sequence named t1_id_seq automatically, and the pg_dump will dump the DDL of the sequence when dumping the table t1.
   In this case, gpcopy won't create the sequence separately.

2. For an explicit sequence, like CREATE TABLE t1 (id INT DEFAULT nextval('myseq')), pg_dump won't generate the DDL for sequence myseq. So gpcopy needs to create the sequence first before creating t1.
   In this case, an explicit sequence was created, but was then altered. This causes pg_dump to generate the DDL in the wrong order due to trying to satisfy the proper permissions.

When running gpcopy, you will get the following error:

DBs, (1/2) tables done] Failed to copy table "db"."public"."foo_prt_bps" => "db"."foo"."foo_1_prt_bps"
20221021:22:40:08 gpcopy:gpadmin:test2-m:026969-[ERROR]:-[Worker 0] Finished task 95142_P_DA_V_ with error:
 pq: relation "foo_id_seq" already exists
20221021:22:40:08 gpcopy:gpadmin:test2-m:026969-[ERROR]:-[Worker 0] [Progress: (0/1) DBs, (2/2) tables done] Failed to copy table 
"db"."public"."foo_1_prt_custody" => "db"."public"."foo_1_prt_custody"

Environment

Product Version: 6.20

Resolution

This issue is fixed in gpcopy 2.3.1 and above

For workaround in the meantime there's 2 options:
1.) Use gpbackup/gprestore to migrate the table.

2.) Dump the table first and then run gpcopy without the --drop flag
-- Dump the DDL of the tables using pg_dump.
-- Run the DDLs on the destination database
-- Run gpcopy for those tables only.

3.) Change the owner of the sequence:
ALTER SEQUENCE foo_id_seq OWNED BY none;