Pivotal Knowledge Base

Follow

gptransfer failed with "ERROR: permission denied for relation"

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x
Others  

Symptom

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

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

Additional Information

 

Comments

Powered by Zendesk