生產數據庫上的大規模 DROP，停機時間最短

我正在重新設計遺留數據庫並將大量表合併為四個，現在我正在尋找一種方法來盡快刪除數千個過時的表。

據我所知，我們沒有任何類似的東西DATABASE LOCK來避免對每個DROP.

就我而言，單使用者模式並不容易，因為數據庫在雲內部的託管環境中執行（但如果這是最可靠的方式，我可能會嘗試尋求雲支持）。

我可能會看到，第三個選項是複制除過時表之外的所有內容，但合併表現在真的很大，轉儲和恢復也可能需要很長時間。

數據庫處於永久負載狀態，並且DROP大部分時間由於鎖定不成功而超時中止。

這些表在高度引用的表上有外鍵，並且DROP.

刪除外鍵也需要在每個ALTER TABLE ... DROP CONSTRAINT.

這些表在高度引用的表上有外鍵，並且 drop 需要鎖定

如果 FK 約束指向要刪除的表，則添加CASCADE以同時刪除任何此類 FK 約束（而不是引用表）。手冊：

（CASCADE將完全刪除一個依賴視圖，但在外鍵情況下，它只會刪除外鍵約束，而不是完全刪除另一個表。）

如果 FK 約束指向要刪除的表（您的情況，如更新中所闡明的那樣），那麼它會隨著表而死。不幸的是，刪除 FK 約束還需要對引用的表進行短暫**ACCESS EXCLUSIVE鎖定。**手冊：

ALTER TABLE更改現有表的定義。

$$ … $$除非 ACCESS EXCLUSIVE明確說明，否則將獲取鎖。

這為解釋留下了空間。實際上，在引用和引用的兩個ACCESS EXCLUSIVE表上都進行了鎖定。（我在 Postgres 14 的快速測試中進行了驗證。）

引用表上的鎖定非常簡短，除非該表上存在大量並發訪問，否則應該不會成為問題 - 特別是如果有長時間執行的事務。

為了避免鎖定引用表的時間超過絕對必要的時間，COMMIT在每個DROP. 喜歡（需要 Postgres 11 或更高版本）：

DO
$do$
DECLARE
  _schema name;
  _tbl name;
  _sql text;
BEGIN
  FOR _schema, _tbl IN 
     SELECT schemaname, tablename
     FROM   pg_catalog.pg_tables
     WHERE  schemaname = 'public'        -- or what you need
     AND    tablename = ANY('{t2, t3}')  -- array of tables to delete
  LOOP
     _sql := format('DROP TABLE %I.%I CASCADE', _schema, _tbl);  -- CASCADE needed?
     RAISE NOTICE '%', _sql;
     -- EXECUTE _sql;        -- un-comment once you are sure
     COMMIT;                 -- !!!
  END LOOP;
END;
$do$

我放入RAISE NOTICE並評論了實際DROP作為兒童安全裝置。取消註釋該EXECUTE行（並可選擇註釋RAISE）以啟動炸彈。

由於在 every 之後送出DROP，因此您不會一路收集鎖。DROP特別是，對於每個命令，您的 FK 約束的那個高度競爭的目標表只會被阻塞一小段時間。

單獨刪除 FK 約束ALTER TABLE ... DROP CONSTRAINT ...幾乎沒有幫助，因為這會遇到同樣的問題。但是，對於來自同一個表的多個FK 約束（在上面的單獨事務中），它可以是一個選項 - 因此您一次只需要等待一個目標表。

如果您仍然卡住，只需再次執行相同的命令：僅刪除仍然存在的表。

您可以添加IF EXISTS到DROP, 但這僅在您期望表並發事務時才有意義DROP，這似乎並不適用。

如果您仍然卡住，請DROP立即對有問題的目標表和同一事務中的所有表進行排他鎖。顯然，在此期間對該表的並發訪問將停止，因此最好在低活動時間或維護視窗期間進行。（OTOH，如果沒有並發訪問，DROP無論如何您都可以簡單地沒有爭用。）

也許您可以辨識並修復不需要長時間保持開放的長時間執行的事務？在任何情況下，這些都是具有並發訪問權限的數據庫的一般負擔。

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