恢復轉儲時禁用所有約束和表檢查

我已經獲得了我的 PostgreSQL 數據庫的轉儲：

pg_dump -U user-name -d db-name -f dumpfile

然後我繼續在另一個數據庫中恢復：

psql X -U postgres  -d db-name-b -f dumpfile

我的問題是數據庫包含引用約束、檢查和触發器，其中一些（特別是檢查似乎）在恢復過程中失敗，因為資訊未按導致這些檢查被兌現的順序載入。例如，在表中插入一行可能與CHECK呼叫一個plpgsql函式相關聯，該函式檢查一個條件是否在其他一些不相關的表中成立。如果後者的表沒有psql在前者之前載入，則會發生錯誤。

以下是一個 SSCCE，它產生了一個一旦轉儲pg_dump就無法恢復的數據庫：

CREATE OR REPLACE FUNCTION fail_if_b_empty () RETURNS BOOLEAN AS $$
   SELECT EXISTS (SELECT 1 FROM b)
$$ LANGUAGE SQL;

CREATE TABLE IF NOT EXISTS a (
    i              INTEGER                    NOT NULL
);

INSERT INTO a(i) VALUES (0),(1);
CREATE TABLE IF NOT EXISTS b (
   i  INTEGER NOT NULL
);
INSERT INTO b(i) VALUES (0);

ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty());

有沒有辦法在轉儲恢復期間禁用（從命令行）所有此類約束並在之後再次啟用它們？我正在執行 PostgreSQL 9.1。

CHECK因此，您在約束中查找其他表。

CHECK約束應該執行IMMUTABLE檢查。一次通過 OK 的東西在任何時候都應該通過 OK。這就是CHECK在 SQL 標準中定義約束的方式。這也是手冊中此限制的原因：

目前，CHECK表達式不能包含子查詢，也不能引用目前行列以外的變數。

儘管如此，CHECK約束中的表達式仍然可以使用函式，甚至是使用者定義的函式。那些應該是IMMUTABLE，但 Postgres 目前不強制執行此操作。根據這個關於 pgsql-hackers 的相關討論，一個原因是允許引用目前時間，這不是IMMUTABLE天生的。

但是您正在查找另一個表的行，這完全違反了CHECK約束的工作方式。我對pg_dump未能提供這一點並不感到驚訝。

將您在另一個表中的檢查移動到觸發器（這是正確的工具），它應該適用於現代版本的 Postgres。

PostgreSQL 9.2 或更高版本

雖然以上對於任何版本的 Postgres 都是正確的，但 Postgres 9.2 引入了幾個工具來幫助您解決問題：

pg_dump 選項--exclude-table-data

一個簡單的解決方案是轉儲沒有違規表數據的數據庫：

--exclude-table-data=my_schema.my_tbl

然後在轉儲末尾僅附加此表的數據：

--data-only --table=my_schema.my_tbl

但是可能會出現同一張表上其他約束的複雜情況。還有一個更好的解決方案：

解決方案：NOT VALID

在 Postgres 9.1 之前，該NOT VALID修飾符僅可用於 FK 約束。這被擴展到CHECKPostgres 9.2 中的約束。手冊：

如果約束標記為NOT VALID，則跳過可能很長的初始檢查以驗證表中的所有行是否滿足約束。約束仍將針對後續的插入或更新強制執行

$$ … $$

一個普通的 Postgres 轉儲文件由三個“部分”組成：

• pre_data
• data
• post-data

Postgres 9.2 還引入了一個選項來單獨轉儲部分-- section=sectionname，但這對解決手頭的問題沒有幫助。

這就是有趣的地方。手冊：

後數據項包括索引、觸發器、規則和 約束的定義，而不是經過驗證的檢查約束。前數據項包括所有其他數據定義項。

大膽強調我的。

您可以將有問題的CHECK約束更改為NOT VALID，這會將約束移動到該post-data部分。刪除並重新創建：

ALTER TABLE a
 DROP CONSTRAINT a_constr_1
, ADD  CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()) NOT VALID;

單個語句是最快的，並且可以排除並發事務的競爭條件。（單個事務中的兩個命令也可以工作。）

這應該可以解決您的問題。您甚至可以將約束留在該狀態，因為這更好地反映了它的實際作用：檢查新行，但不保證現有數據。NOT VALID檢查約束沒有任何問題。如果您願意，可以稍後驗證它：

ALTER TABLE a VALIDATE CONSTRAINT a_constr_1;

但隨後你又回到了原來的狀態。

引用自：https://dba.stackexchange.com/questions/75613