psql ERROR: relation already exists
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.
Comments
-
Tony Han almost 2 years
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.
-
JohnEye over 2 yearsWhat does this option do in
pg_dump
? I would expect the option to be passed topg_restore
instead. -
Mike Sherrill 'Cat Recall' over 2 years@JohnEye: "Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.) This option is ignored when emitting an archive (non-text) output file. For the archive formats, you can specify the option when you call pg_restore." Current docs