In pgadmin, I selected a table from my production database I want to export. I right clicked and selected backup. I set the filename to devices.sql and selected plain as format. Then I clicked the backup button and it created a sql file on my computer. Now I want to import the contents of that table into my development database. So I run the psql utility and enter the following:

\i /Users/GuardDog/Documents/devices.sql

But it gives me this error:

psql:/Users/GuardDog/Documents/devices.sql:34: ERROR:  relation "devices" already exists
psql:/Users/GuardDog/Documents/devices.sql:37: ERROR:  role "xtremek" does not exist
psql:/Users/GuardDog/Documents/devices.sql:50: ERROR:  relation "devices_id_seq" already exists
psql:/Users/GuardDog/Documents/devices.sql:53: ERROR:  role "xtremek" does not exist
(1 row)

psql:/Users/GuardDog/Documents/devices.sql:101: ERROR:  multiple primary keys for table "devices" are not allowed

I get quite a few errors here. How can I import the data from my production server to development server in pgadmin/PostgreSQL 9.1.2 ?

| improve this question | | | | |
  • Either do a data-only dump on the origin server, or use "custom" format and restore just the data with the pg_restore command on the destination server. – Craig Ringer Dec 9 '13 at 4:48
  • @CraigRinger And how do I do data only dump? Thats what I thought I was doing. I followed this: stackoverflow.com/questions/11257132/… – JohnMerlino Dec 9 '13 at 5:40
  • 2
    You want the --data-only option to pg_dump. Unsure how to get that from PgAdmin, as I don't use it much. What I'd do personally is either use psql's \copy command to copy the table contents to a CSV file, or use PgAdmin-III's Export option (a right click option on a table) to do the same. Then import with psql's \copy tablename from ... command. – Craig Ringer Dec 9 '13 at 7:11
  • Look around the various options here. – Milen A. Radev Dec 9 '13 at 8:59
  • @CraigRinger I see no "export" option on a pgadmin table – JohnMerlino Dec 10 '13 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.