Symptom
In apsaradb RDS for PostgreSQL, run the following SQL statement to import data:
copy mp3 (NAME,city,nation,lat,lng,url,mediatype,type) from '/home/alex/tmp/pos.csv' with csv;The system prompts the following error.
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.Causes
By default, copy requires the superuser permission. Apsaradb for RDS PostgreSQL does not support this permission.
Solution
Alibaba Cloud reminds you that:
When you perform operations that have risks, such as modifying instance configurations or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.
Log on to the local host and run the following command to bypass the restrictions.
cat [$Table_Name].csv | ~/workspace/pg94/bin/psql -h [$Host] -p [$Port] -U [$User] -c "copy [$Table_Name] from stdin"Note:
[$Table_Name] indicates the name of a table in a database.
[$Host] the hostname or IP address of the RDS instance to log on to.
[$Port] indicates the Port number.
[$User] indicates the logon username.
References
for more information about how to migrate data, see use the pspl command to migrate PostgreSQL data.
Application scope
ApsaraDB RDS for PostgreSQL