All Products
Search
Document Center

ApsaraDB RDS:How do I fix the error that occurs when ApsaraDB RDS for PostgreSQL uses the copy command to import data?

Last Updated:Dec 20, 2022

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

Note

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

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