pg_restore duplicate key and invalid command errors
Solution 1
When you are exporting the data use pg_dump with the -Fc option.
Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.
The compact format makes it easier to transfer between servers and depending on your IO load might even be faster to dump and restore as well.
If you intend to import the data into a database that already contains some tables complications are inevitable. You can overcome this situation by dumping with the --clean option.
Solution 2
Below commands helped me to get rid of these errors:
pg_dump:
RUN
pg_dump -O -d database_name > file_name.sql
- use -O option (in order to get rid of owner related errors.)
pg_restore:
- make sure before running the database restoring command, no tables are present in the database. (else this leads to a number of errors including violating key constraints etc.)
RUN
psql -1 database_name < file_name.sql
- using -1 option make sure, either the db is imported completely or nothing at all.
- also the execution stops at the first error, makes it easier to tackle one error at a time.
GeoVIP
Updated on August 12, 2022Comments
-
GeoVIP almost 2 years
Postgres version :
PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
We transfer from one server to another (with better cpu an ram) our system using vm converter and when I try to backup database have an error :
pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading extensions pg_dump: reading user-defined functions pg_dump: reading user-defined types pg_dump: reading procedural languages pg_dump: reading user-defined aggregate functions pg_dump: reading user-defined operators pg_dump: reading user-defined operator classes pg_dump: reading user-defined operator families pg_dump: reading user-defined text search parsers pg_dump: reading user-defined text search templates pg_dump: reading user-defined text search dictionaries pg_dump: reading user-defined text search configurations pg_dump: reading user-defined foreign-data wrappers pg_dump: reading user-defined foreign servers pg_dump: reading default privileges pg_dump: reading user-defined collations pg_dump: reading user-defined conversions pg_dump: reading type casts pg_dump: reading table inheritance information pg_dump: reading rewrite rules pg_dump: finding extension members pg_dump: finding inheritance relationships pg_dump: reading column info for interesting tables pg_dump: finding the columns and types of table "account_account" pg_dump: [archiver (db)] query failed: ERROR: missing chunk number 0 for toast value 3297740 in pg_toast_2619 pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign, a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation, pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E', ') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '274619'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY a.attrelid, a.attnum
I solve it using
reindexdb
,after I trypg_dump -U postgres my_db > /home/mydb.backup
and it was successfull. Then I try to restore database to ensure that backup is validpsql -U postgres new_db < /home/mydb.backup
And have an errors :
ERROR : extra data after last expected column Context: COPY tbl1, line1: "1 2013-12-02 2013-12-02 9387.74 9775.46211485490864940000" 6180.9500000000 80262 ...." ERROR : column "id" of relation "tbl1" does not exists invalid command \N invalid command \N invalid command \N ..... invalid command \N invalid command \. ERROR: syntax error at or near "87685" LINE 1: 87685 SO87690 1 170468 2015-05-30 2015 05 30 ^ invalid command \. ERROR: duplicate key value violates unique constraint "ir_act_client_pkey" DETAIL: Key (id)=(103) already exists. CONTEXT: COPY ir_act_client, line 21: "" ERROR: duplicate key value violates unique constraint "ir_act_report_xml_pkey" DETAIL: Key (id)=(733) already exists. CONTEXT: COPY ir_act_report_xml, line 59: "" ERROR: duplicate key value violates unique constraint "ir_act_server_pkey" DETAIL: Key (id)=(703) already exists. CONTEXT: COPY ir_act_server, line 6: "" ERROR: duplicate key value violates unique constraint "ir_act_window_pkey" DETAIL: Key (id)=(1) already exists. CONTEXT: COPY ir_act_window, line 235: "334 Last Product Inventories ir.actions.act_window \N 1 2013-07-03 10:39:48.399509 2013-12-16 16:38:..." ERROR: duplicate key value violates unique constraint "ir_act_window_group_rel_act_id_gid_key" DETAIL: Key (act_id, gid)=(76, 1) already exists. CONTEXT: COPY ir_act_window_group_rel, line 14: "" ERROR: duplicate key value violates unique constraint "ir_act_window_view_pkey" DETAIL: Key (id)=(100) already exists. CONTEXT: COPY ir_act_window_view, line 88: ""
How to solve it ?