时间戳

首页 > 解决方案 > 有时会发生错误转储/恢复 PostgreSQL

postgresql - 有时会发生错误转储/恢复 PostgreSQL

问题描述

有时当我进行数据库转储和恢复时，会出现以下错误

pg_restore: [archiver (db)] Error during TOC PROCESSING:
pg_restore: [archiver (db)] Error in the item TOC 3569; 2606 4196743 CONSTRAINT uc_xtb_ruleset_key elinex
pg_restore: [archiver (db)] could not execute query: ERROR: creation of the unique index "uc_xtb_ruleset_key" failed
DETAILS: The key (name) = (FEL.DATI_RIEPILOGO) is duplicated.
    The command was: ALTER TABLE ONLY xtb_rulesets
    ADD CONSTRAINT uc_xtb_ruleset_key UNIQUE (name); pg_restore: [archiver (db)] Error in the item TOC 3425; 2606 4196747 CONSTRAINT uq_currency_iso elinex
pg_restore: [archiver (db)] could not execute query: ERROR: creation of the unique index "uq_currency_iso" failed
DETAILS: The key (iso_code) = (SBD) is duplicated.
    The command was: ALTER TABLE ONLY xtb_currencies
    ADD CONSTRAINT uq_currency_iso UNIQUE (iso_code); pg_restore: [archiver (db)] Error in the item TOC 3418; 2606 4196751 CONSTRAINT xtb_countries_pkey elinex
pg_restore: [archiver (db)] could not execute query: ERROR: creation of the unique index "xtb_countries_pkey" failed
DETAILS: The key (uic_code) = (075) is duplicated.
    The command was: ALTER TABLE ONLY xtb_countries
    ADD CONSTRAINT xtb_countries_pkey PRIMARY KEY (uic_code); pg_restore: [archiver (db)] Error in the item TOC 3420; 2606 4196753 CONSTRAINT xtb_cultures_pkey elinex
pg_restore: [archiver (db)] could not execute query: ERROR: creation of the unique index "xtb_cultures_pkey" failed
DETAILS: The key (culture_id) = (IT) is duplicated.
    The command was: ALTER TABLE ONLY xtb_cultures
    ADD CONSTRAINT xtb_cultures_pkey PRIMARY KEY (culture_id); pg_restore: [archiver (db)] Error in the entry TOC 3427; 2606 4196755 CONSTRAINT xtb_currencies_pkey elinex
pg_restore: [archiver (db)] could not execute query: ERROR: creation of the unique index "xtb_currencies_pkey" failed
DETAILS: The key (uic_code) = (206) is duplicated.
    The command was: ALTER TABLE ONLY xtb_currencies
    ADD CONSTRAINT xtb_currencies_pkey PRIMARY KEY (uic_code); pg_restore: [archiver (db)] Error in item TOC 3447; 2606 4196773 CONSTRAINT xtb_email_resources_pkey elinex
[.....]
pg_restore: [archiver (db)] could not execute query: ERROR: there is no unique constraint that matches the keys given for the referenced table "xtb_cultures"
    The command was: ALTER TABLE ONLY xtb_profiles
    ADD CONSTRAINT fk_profile_to_culture FOREIGN KEY (culture_id) REFERENCES xtb_cultures (cult ...
pg_restore: [archiver (db)] Error in the item TOC 3709; 2606 4196955 FK CONSTRAINT fk_users_to_culture elinex
pg_restore: [archiver (db)] could not execute query: ERROR: there is no unique constraint that matches the keys given for the referenced table "xtb_cultures"
    The command was: ALTER TABLE ONLY xtb_users
    ADD CONSTRAINT fk_users_to_culture FOREIGN KEY (culture_id) REFERENCES xtb_cultures (culture_i ...
pg_restore: [archiver (db)] Error in the item TOC 3680; 2606 4197020 FK CONSTRAINT fk_xtb_f_role_2_role elinex
pg_restore: [archiver (db)] could not execute query: ERROR: there is no unique constraint that matches the keys given for the referenced table "xtb_roles"
    The command was: ALTER TABLE ONLY xtb_function_roles
    ADD CONSTRAINT fk_xtb_f_role_2_role FOREIGN KEY (role_id) REFERENCES xtb_roles (role _...
pg_restore: [archiver (db)] Error in the item TOC 3679; 2606 4197030 FK CONSTRAINT fk_xtb_file_ty_2_folder elinex
pg_restore: [archiver (db)] could not execute query: ERROR: there is no unique constraint that matches the keys indicated for the referenced table "xtb_folders"
    The command was: ALTER TABLE ONLY xtb_file_types
    ADD CONSTRAINT fk_xtb_file_ty_2_folder FOREIGN KEY (folder_id) REFERENCES xtb_folders (fo ...
[...]
    WARNING: error ignored during recovery: 24

我确实从 postgreSQL 9.5.6 转储并在 PostgreSQL 9.6.11 上恢复转储

[xxxxx@xxxx ~]$ pg_dump -V
pg_dump (PostgreSQL) 9.5.6

[xxxx@xxxx ~]# pg_restore -V
pg_restore (PostgreSQL) 9.6.11

执行以下转储命令和恢复命令

倾倒

ssh user@remoteserver "pg_dump --format=c -c --if-exists -O --schema=public --exclude-table-data=xtb_process_runs --exclude-table-data=xtb_app_properties --exclude-table-data=xtb_doc_properties --exclude-table-data=xtb_export_properties --exclude-table-data=xtb_file_storage --exclude-table-data=xtb_import_properties --exclude-table-data=xtb_org_prop_template --exclude-table-data=xtb_org_props --exclude-table-data=xtb_process_properties --exclude-table-data=xtb_rule_functions --exclude-table-data=xtb_rules --exclude-table-data=xtb_templates --exclude-table-data=xtb_trigger_properties --exclude-table-data=xtb_triggers --exclude-table-data=xtb_user_preferences --exclude-table-data=xtb_variables --exclude-table-data=xtb_events -d DATABASE_NAME" > DATABASE_DUMP.dmp

恢复

pg_restore --clean --if-exists --no-owner -h localhost -p 5432 -d DATABASE_NAME -U user DATABASE_DUMP.dmp

你能帮帮我吗？

标签： postgresqlpg-dumppg-restore

解决方案

原始数据库很可能正遭受损坏。要验证这一点，请尝试REINDEX使用原始数据库上的这些索引之一。

要解决此问题，请删除表中的重复行和REINDEX.

推荐阅读