gptransfer Failed with "ERROR: permission denied for relation"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When attempting data migration between two databases with gptransfer, it fails with error message “ERROR:  permission denied for relation”.

Command of gptransfer that is executed:

gptransfer -f tablefile_partition_table_1  —dest-user=scott1 --source-user=qin --source-port=3000 --dest-port=3000 --work-base-dir=/data/scott/temp/transfer --truncate --dest-database=test2 -a -v
The following detailed error message will be seen when verbose mode (-v) of gptransfer is enabled.

20160726:01:05:54:008536 gptransfer:mdw:gpadmin-[ERROR]:-Failed to transfer table ub.public.searches_weekly
20160726:01:05:54:008536 gptransfer:mdw:gpadmin-[INFO]:-Remaining 1 of 1 tables
20160726:01:05:54:008536 gptransfer:mdw:gpadmin-[DEBUG]:-[worker1] finished cmd: transfer of ub.public.searches_weekly cmdStr='None' had result: cmd had rc=1 completed=False halted=False
 stdout='error 'ERROR: permission denied for relation searches_weekly
' in 'CREATE WRITABLE EXTERNAL WEB TABLE gptransfer.w_ext_searches_weekly_24333159039ac045fdd23a678dd21d7a (LIKE "public"."searches_weekly")
 EXECUTE 'cat > /data/scott/temp/transfer/gptransfer_8536/ub.public.searches_weekly/ub.public.searches_weekly.pipe.$GP_SEGMENT_ID'
 FORMAT 'CSV' 
 (DELIMITER AS ',' QUOTE AS E'^A') ENCODING 'UTF8' DISTRIBUTED BY (hw_id)''
 stderr='None'
20160726:01:05:54:008536 gptransfer:mdw:gpadmin-[WARNING]:-1 tables failed to transfer. A list of these tables.

Environment

Cause

The Owner of the relation to be transferred is different from that is specified with "--source-user" parameter in gptransfer, as illustrated below:
 

ub=# \dt searches_weekly
 List of relations
 Schema | Name | Type | Owner | Storage 
--------+-----------------+-------+-------+----------------------
 public | searches_weekly | table | gpadmin | append only columnar

 

Resolution

There are two options to resolve the issue:

1. Specify "--source-user" as the owner of relations to be transferred when running gptransfer
2. Change owner of the relations to be transferred to "--source-user" set for gptransfer