psql ERROR: relation already exists

Question

I'm writing a rails project using postgres and there is some data in the server. And I want dump the data from the remote end to the local, so I write script to do it, but some errors come out.

This's the dump script:

run "PGPASSWORD='#{remote_settings['password']}' 
pg_dump -U #{remote_settings["username"]} #{"-h '#{remote_settings["host"]}'" 
if remote_settings["host"]} 
'#{remote_settings["database"]}' > #{remote_sql_file_path}"

There's some codes to transport..

Transport codes

And this's the restore script:

run_locally "PGPASSWORD='#{local_settings['password']}' psql -U 
#{local_settings["username"]} -d #{local_settings["database"]} 
-f #{local_sql_file_path}"

I get the data file successfully, but when there're some ERRORs* when **restore script is run:

psql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:46: ERROR:  relation        "refinery_images" already exists
psql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:49: ERROR:  role "ib5k" does not exist
psql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:60: ERROR:  relation "refinery_images_id_seq" already exists
psql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:63: ERROR:  role "ib5k" does not exist
psql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:83: ERROR:  relation "refinery_page_part_translations" already exists
psql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:86: ERROR:  role "ib5k" does not exist
...
sql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:525: ERROR:  duplicate key  value violates unique constraint "refinery_images_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY refinery_images, line 2: ""
psql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:547: ERROR:  duplicate key value violates unique constraint "refinery_page_part_translations_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY refinery_page_part_translations, line 8: ""
psql:tmp/production-ib5k_production-2013-02-21_18:42:09.sql:569: ERROR:  duplicate key value violates unique constraint "refinery_page_parts_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY refinery_page_parts, line 8: ""
...

And the database in local will not be updated. I want to know how to solve it? Adding some arguments? Thank you in advance.

Answer 1

You can use the -c or --clean argument to pg_dump. That argument will drop existing database objects before running the commands to create them.

An alternative is to drop those objects yourself before restoring. (Probably using drop schema or drop database.)

Use with caution.