Pivotal Knowledge Base

Follow

gptransfer Failed with "ERROR: permission denied for relation"

Environment

  • Pivotal Greenplum Database (GPDB) 4.3.x
  • Operating System- Red Hat Enterprise Linux (RHEL) 6.x

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