pg_restore重复键和无效命令错误

Question

Postgres版本：

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

我们使用虚拟机转换器将系统从一台服务器传输到另一台服务器(使用更好的cpu和ram)，当我尝试备份数据库时出现错误：

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

在我尝试了pg_dump -U postgres my_db > /home/mydb.backup并成功之后，我用reindexdb解决了这个问题。然后，我尝试恢复数据库以确保备份有效

psql -U postgres new_db < /home/mydb.backup

并且有一个错误：

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: ""

如何解决？

Answer 1

在导出数据时，请使用带有-Fc选项的pg_dump。

输出适合输入到pg_restore中的自定义格式的归档文件。与目录输出格式一起，这是最灵活的输出格式，因为它允许在还原期间手动选择和重新排序存档项目。默认情况下，此格式也是压缩的。

紧凑的格式使得服务器之间的传输变得更容易，而且根据IO负载的不同，转储和恢复也可能更快。

如果您打算将数据导入到已经包含一些表的数据库中，那么复杂的情况不可避免。您可以通过使用--clean选项进行转储来克服这种情况。

Answer 2

下面的命令帮助我摆脱了这些错误：

pg_dump：

运行

pg_dump -O -d database_name > file_name.sql

• 使用-O选项(以便消除与所有者相关的错误。)

pg_restore：

• 在运行数据库还原命令之前，请确保数据库中不存在任何表。(否则这会导致许多错误，包括违反键约束等)

RUN

psql -1 database_name < file_name.sql

• 使用-1选项确保要么完全导入数据库，要么根本不导入数据库。
• 还会在出现第一个错误时停止执行，这样可以更轻松地一次处理一个错误。