使 Postgres 數據庫臨時只讀（用於執行卷快照）

PostgreSQL 內置的備份機制並不總是很合適。有時，您希望將應用程序置於靜止狀態，因為在備份 PG 數據的同時，它具有您要備份的外部數據。但是將應用程序置於靜止狀態的唯一方法是也“鎖定”數據庫。PG 缺少數據庫範圍或集群範圍的鎖定機制。將 PG 置於只讀狀態將是以下解決方案中的一部分：

1. 靜默應用程序數據（禁用登錄）
2. Quiesce 數據庫（通過使其只讀）
3. 執行 PG 檢查點或 pg_xlog_switch()
4. 創建應用和數據卷的快照
5. 恢復數據庫（再次設為 RW）
6. 繼續申請
7. 備份快照

在網際網路上其他地方挑選答案後，我設計了一個解決方案。其他答案本身是不完整的。所以我在這裡提出一個答案，希望它對其他人有所幫助。

戰略

1. 禁用與數據庫（而不是集群）的連接。
2. 將數據庫的default_transaction_read_only設置設置為true.
3. 終止與該數據庫的現有連接。
4. 重新啟用（只讀）連接。

完成後，您將（在我的解決方案中）：

5. 執行CHECKPOINT（我認為這是最安全的，但pg_xlog_switch()適用於負載非常高的伺服器）
6. 拍攝卷快照
7. 顛倒前面的步驟。（但這很棘手！）

陷阱

1. 在交易過程中終止連接可能是個壞主意。最好殺死空閒連接，等待幾秒鐘，然後殺死空閒連接，再等幾個，重複直到它們都消失。

2. 在某些時候，您將不得不終止打開/掛起的查詢或中止備份。

3. 在會話事務開始時，Postgresql 會拍攝程序表的一種快照。每次去檢查不需要的程序是否仍然存在時，您都必須重置此快照。看pg_stat_clear_snapshot()

4. 恢復讀寫狀態並不是那麼簡單。如果現在存在只讀連接，您必須終止它們才能使新的讀寫狀態生效。但是新連接可能會在終止現有連接的同時到達。再說一次，你必須

5. 禁用與數據庫的連接

6. 將 default_transaction_read_only 狀態更改為false

7. 殺死現有的連接

8. 重新啟用 (r/w) 到數據庫的連接

替代策略

另一種策略是更改應用程序使用的角色的權限。這可能會很混亂，而且不太通用。

例如，您不僅要撤銷/重新授予表，還要撤銷/重新授予序列、大型對象，可能還有模式本身。此外，當您更改訪問權限時，現有連接的行為究竟是什麼？可能沒有影響，這意味著您還需要殺死那些後端。最後，假設應用程序對大多數表具有讀寫訪問權限，但對架構中的其他表沒有權限。您必須確保您的重新授予也不包括這些對象。

另一種可能性是通過查詢目錄並執行動態查詢來鎖定所有表。這對我的口味來說似乎很危險。

執行

暫停服務

數據庫實例名稱是“gitlabhq”，應用程序的使用者名是“gitlab”。將其替換為您自己的：

 psql -Upostgres  <<'PAUSE_DB'
   -- 1. disable new connections
   alter database gitlabhq_production with allow_connections = off;
   -- 2. Make DB read-only
   alter database gitlabhq set default_transaction_read_only = true;
   -- 3. Inobtrusively but safely terminate current connections
   DO $X$ BEGIN
       -- kill open idle connections, try up to 9x. Last time, kill regardless
       FOR i IN 1..10 LOOP
         PERFORM pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab'
           and (i >= 10 OR state in ('idle', 'disabled' ));
         PERFORM pg_stat_clear_snapshot();
         EXIT WHEN NOT EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' );
         RAISE NOTICE 'pg backends still open: sleeping 2 seconds';
         PERFORM pg_sleep(2);
         PERFORM pg_stat_clear_snapshot();
       END LOOP;
       -- send notice if still open connections
       IF EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' ) THEN
           RAISE NOTICE 'Hung backends. Backup might not be 100%% consistent';
       END IF;
   END;$X$;
   -- 4. Allow read-only connections while checkpointing/snapshotting
   alter database gitlabhq with allow_connections = on;
   CHECKPOINT;

恢復

   alter database gitlabhq_production with allow_connections = off;
   alter database gitlabhq set default_transaction_read_only = false;
   SELECT pg_stat_clear_snapshot();
   SELECT pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab';
   alter database gitlabhq with allow_connections = on;

在這最後一步中，您可能會終止長時間執行的只讀/SELECT 查詢，但根據我的經驗，這種長時間執行的查詢可能會持續幾分鐘甚至幾小時，並且為了確保正常執行時間而終止這些查詢是可以接受的其他所有人。

我個人認為將此功能作為官方 PostgreSQL 功能是可取的。

簡單地做

如果您不想為 PostgreSQL 擴展使用 C 編碼，您可以在 PostgreSQL 前面放置一個連接池。就像 pgBouncer。

pgBouncer具有暫停應用程序活動內置的能力。儘管為了使它非常有用，您需要直接連接（而不是通過 pgbouncer）並在暫停新連接後取消活動連接。只需select pg_terminate_backend(pid) from pg_stat_activity where pid <> pg_backend_pid().

做對了

但是，如果您願意親自動手，可以使用 C 擴展來完成。擴展必須：

• 被載入，shared_preload_libraries以便它可以註冊一個帶有布爾標誌的小型靜態共享記憶體段，如db_is_locked。
• 寄存器 aProcessUtility_hook並ExecutorStart_hook測試 shmem 中的 is-locked 標誌，如果設置，則在WaitLatch循環中休眠，直到它看到標誌再次被清除。（您可以改用解析器掛鉤）。
• 用 C 語言編寫兩個 SQL 可呼叫函式。一個設置標誌。另一個清除標誌並通過PGPROC設置所有使用者程序的鎖存器進行迭代，因此它們知道立即喚醒。
• 可選地編寫第三個函式，如果設置了標誌，則迭代PGXACT以查找打開的寫入事務並發出信號以終止它們。

所有這些都已作為BDR 擴展的一部分實施，但它是更大系統的一部分。您很可能會將相關部分提取到您自己的擴展中。參見bdr_locks.c, bdr_commandfilter.c, bdr_executor.c,bdr.c等。

請注意，這不會使 PostgreSQL在磁碟上只讀- 檢查指針將繼續執行，bgwriter 將繼續執行，存檔器將繼續執行，等等。因此，讓您在沒有原子的情況下進行數據庫備份是不夠的文件系統快照或pg_start_backup()/ pg_stop_backup()。但這對您的案例來說很好，在數據庫中暫停應用程序活動。

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