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 exi…

Accepted Answer

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.

psql ERROR: relation already exists

Solution

Problem