Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

SET
SET
SET
SET
SET
SET
SET
SET
SET
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
ALTER SEQUENCE
 setval 
--------
      2
(1 row)

ALTER TABLE
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 ?

share|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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.