I have a database hosted using Amazon's RDS service and I am attempting to write a web service that will update said database. The problem I am having is that it will not let me use the COPY command as I get this error: "ERROR: must be superuser to COPY to or from a file". I am using the only user I have made for the database and I am fairly certain it has superuser access. I can, however, use PGAdmin's import tool to import the data which, when looking at the log, uses almost the exact same command as I do. The only difference is instead of the file path it has stdin. How can I fix this error?
1
-
Thanks for showing the exact error message text and that you're using RDS. In future, please also show the exact text of the command that generated the error.Craig Ringer– Craig Ringer2014-08-09 01:30:38 +00:00Commented Aug 9, 2014 at 1:30
Add a comment
|
2 Answers
You're using:
COPY tablename FROM 'filename';
this won't work - RDS has no idea what 'filename' is.
You must use the psql command's \copy, which copies from the local client, or PgAdmin-III's "import data" option.
Comments
Workaround without using psql
1) Import data locally to a temporary table using simple copy command
2) Right click the table in the pgAdmin III object browser and select "Backup..."
3) Set the format to Plain and save the file as a .sql file
4) Click on the Dump Options #1 Tab and check Data
5) Click on the Dump Options #2 Tab and check Use Column Inserts and Use Insert Commands
6) Click Backup button
7) Now you can open the sql file and run it in your RDS server
Alternatively you can use the below command to generate the sql file
pg_dump --host localhost --port 5432 --username "postgres" --no-password --format plain --section data --inserts --column-inserts --file "C:\test\test.sql" --table "public.envelopes" "testdb"
2 Comments
Chris Johnson
Your solution has nothing to do with uploading a CSV file to a remote RDS server. The reason people want to do that is, it's dramatically faster than running SQL inserts. Changing what could have been a CSV upload to a series of inserts doesn't solve that particular problem.
Christopher Yang
Although this solution didn't solve my problem but it gave me ideas to use another tool to solve the problem. I was able to circumvent the permission issue associated with the copy command, thanks.