使用 PostgreSQL 管理 OCI 資料庫中災害復原設定的 pglogical Replication 序列

簡介

本教學課程概述自訂解決方案，以在主要和災害復原 (DR) 系統之間的邏輯雙向複製設定中同步 PostgreSQL 順序值。

問題陳述：

在目前的設定中，我們已設定雙向複寫。部分複製的表格依賴序列；不過，我們觀察到這些序列的 last_value 並不會在主要和 DR 環境之間複製。

這是預期的行為，並不被視為語彙中的錯誤。依據設計，不會複製順序值，因為順序會被視為非交易，且原本為節點特定，以避免衝突。

為了解決此差距，我們已導入自訂解決方案來同步主要與災難復原系統之間的順序值。具體而言，我們正在制定以下程序：

從主要資料庫擷取每個相關順序的 last_value。
更新 DR 端上對應的順序，以符合主要順序。
使用 pg_cron 副檔名每隔一分鐘自動執行一次。

此方法可確保序列值在兩個系統中保持一致，從而避免主要在 DR 環境中造成潛在的資料完整性或衝突問題。

目標

使用 PostgreSQL 管理 OCI 資料庫中 DR 設定的 pglogical 複製序列。

作業 1：設定 pglogical 雙向複製

確定主要系統和 DR 系統之間已經設定 pglogical 雙向複製。

若尚未設定，請依照官方文件設定 pglogical：在來源資料庫中設定 pglogical Extension Replication 。

任務 2：啟用必要延伸

在主要和 DR 系統中，透過從 OCI 主控台建立組態來啟用下列擴充。

pg_cron
dblink

在 DBSystems 上套用組態檔之後，請連線至每個 DBSystem 上的 postgres 資料庫，然後執行下列查詢。

CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS dblink;

任務 3：建立程序

在 postgres 資料庫中的主要和 DR 系統上建立程序。

程序定義：

CREATE OR REPLACE PROCEDURE sync_sequences_from_remote()
LANGUAGE plpgsql
AS $$
DECLARE
  -- Update the connection string with correct credentials and hostname
  conn_str TEXT := 'host=<hostname> user=<username> password=<password> dbname=postgres';

  row RECORD;
  remote_seq_query TEXT :=
    $_$SELECT schemaname, sequencename, last_value
       FROM pg_sequences
       WHERE schemaname !~ 'pg_catalog|cron|information_schema|pg_toast|pg_temp'
         AND last_value IS NOT NULL$_$;
  full_seq_name TEXT;
  setval_sql TEXT;
BEGIN
  FOR row IN
    SELECT * FROM dblink(conn_str, remote_seq_query)
      AS t(schemaname TEXT, sequencename TEXT, last_value BIGINT)
  LOOP
    full_seq_name := quote_ident(row.schemaname) || '.' || quote_ident(row.sequencename);
    setval_sql := FORMAT('SELECT setval(%L, %s, true);', full_seq_name, row.last_value);
    RAISE NOTICE 'Setting sequence: %', setval_sql;
    EXECUTE setval_sql;
  END LOOP;
END;
$$;

注意：

安全地以實際值取代 <hostname>、<username> 和 <password>。

使用 <hostname> 代表資料庫系統的 FQDN。

作業 4：在 DR 系統中建立 `pg_cron` 工作

執行下列查詢以建立 pg_cron 工作，並排定程序在 DR 資料庫系統上每隔一分鐘執行一次。

SELECT cron.schedule('UPDATE SEQUENCE', '*/1 * * * *', $$ CALL sync_sequences_from_remote(); $$);

附註：

確定 pg_cron 工作僅在 DR 系統上啟用。

視需要調整工作排程。

工作 5：在主要系統中建立 `pg_cron` 工作並加以停用

執行下列查詢以建立 pg_cron 工作並將其停用。

SELECT cron.schedule('*/1 * * * *', $$ CALL sync_sequences_from_remote() $$);

-- Disable the job (on Primary)
SELECT cron.alter_job(jobid := <job_id>, active := false);

工作 6：處理容錯移轉

發生容錯移轉事件時，請遵循下列步驟：

在新的 DR (先前為主要) 中，啟用 pg_cron 工作。
在新的主要 (先前為 DR) 中，停用 pg_cron 工作。

這可確保同步處理程序永遠只會在目前的 DR 系統上執行。

注意：
有用的命令：
-- List all scheduled jobs and get job ID
SELECT jobid, schedule, command, active FROM cron.job;

-- Disable a job
SELECT cron.alter_job(jobid := <job_id>, active := false);

-- Enable a job
SELECT cron.alter_job(jobid := <job_id>, active := true);
根據您的環境特定需求調整程序與工作定義。

請先在開發環境中徹底測試程序，再將其部署至生產環境。

確認

作者 - Arvind Yadav (主要會員技術人員)

其他學習資源

在 docs.oracle.com/learn 上探索其他實驗室，或在 Oracle Learning YouTube 頻道上存取更多免費學習內容。此外，請造訪 education.oracle.com/learning-explorer 以成為 Oracle Learning Explorer。

如需產品文件，請造訪 Oracle Help Center 。

標題與著作權資訊

Manage Sequences in pglogical Replication for Disaster Recovery Setup in OCI Database with PostgreSQL

G39334-01