OCI Database 中使用 pglogical 進行 PostgreSQL 的雙向複製

簡介

OCI PostgreSQL 中的邏輯複製允許根據表格的複製識別 (通常是主索引鍵) 來複製資料物件及其變更。它使用 publish-subscribe 模型，其中一或多個訂戶訂閱發布者節點上定義的發布。訂閱者可從這些出版品中提取資料，並可選擇重新發布變更，以啟用層疊複製或更複雜的拓樸。

邏輯複製串流資料列層級變更— INSERT、UPDATE 和 DELETE 作業—使用邏輯解碼解碼 WAL (預寫日誌) 。這些解碼的變更獨立於 OCI PostgreSQL 的實體儲存格式，僅代表邏輯資料作業，為下游系統提供更大的彈性。

在本教學課程中，我們將探討如何使用 pglogical 擴充功能進行邏輯複製，以及如何在 OCI Database for PostgreSQL 中的兩個資料庫系統之間啟用雙向複製。

雙向複製

雙向複製 (BDR) ( pglogical) 表示兩個或更多的 OCI PostgreSQL 資料庫系統都會發布及訂閱彼此的變更。換句話說，每個 OCI PostgreSQL 資料庫系統都可以讀取和寫入，並以這兩個方向複製所有變更。因此，在任一 OCI PostgreSQL 資料庫系統上執行的任何修改都將鏡射並導入兩者。

一般建議您在需要區域或全域資料庫架構時使用雙向複製，以便為多個位置的使用者提供低延遲讀取和寫入存取權。藉由將讀取 / 寫入執行處理共置於每個區域中的使用者附近，即可在本機進行變更，然後複製到其他區域，以確保資料會在所有節點間同步。

顯示兩個具有 PostgreSQL 執行處理的 OCI 資料庫，其中以兩個方向執行複製。

在實務上，大多數使用案例都只需要一小部分的表格即可從多個區域寫入，而大多數資料庫都可以透過單向複製保持唯讀或集中管理。

但是，雙向複製帶來了複雜性。維持資料一致性和管理複寫拓樸可能會在系統成長時變得具有挑戰性。

如果您的應用程式主要目標是閱讀區域中斷的可擴展性和復原性，則較簡單且更健全的方法是在相同區域或跨區域部署具有多個讀取複本的適用於 PostgreSQL 執行處理的高可用性 OCI 資料庫。

雙向 pglogical 工作的方式

在口語中，您可以設定多個訂閱和發布，如下所示：

資料庫系統 1 會發佈變更 → 資料庫系統 2 訂閱
資料庫系統 2 會發佈變更 → 資料庫系統 1 訂閱

每個節點：

傳送變更給其他
套用其他變更
使用 pglogical 的衝突解決設定值來處理潛在的衝突

若要深入瞭解 OCI PostgreSQL 中跨區域的單向 pglogical 複製，請參閱此部落格：跨區域的單向 pglogical 。

管理衝突與組態

發佈者 (來源資料庫) 定義一或多個發佈，並將變更資料 (DML 作業) 傳送至訂戶 (目標資料庫)。訂戶可以連線至多個發佈者，並將其變更套用至其本機資料庫。

pglogical 擴充功能透過 pglogical.conflict_resolution 參數提供進階衝突解決，支援下列模式：

apply_remote (預設為 PostgreSQL 的 OCI 資料庫)
error
keep_local
last_update_wins
first_update_wins

如需有關 conflict_resolution 語言的詳細資訊，請參閱 github 的官方文件：第 2 個象限組態選項。

使用 keep_local、last_update_wins 或 first_update_wins 時，必須同時在發佈者和訂戶上啟用 track_commit_timestamps。請記住，啟用此設定可能會根據您的工作負載產生可測量的效能負荷。強烈建議您先評估對生產環境的影響再進行部署。

在 OCI PostgreSQL 中設定雙向複製

若要在 OCI 資料庫中啟用 PostgreSQL 的 pglogical 擴充，請參閱此教學課程：在 OCI PostgreSQL 中啟用 pglogical 擴充。

工作 1：先決條件

在您用於 PostgreSQL 的 OCI 資料庫中建立 pglogical 擴充之後，請授予資料庫系統 #1 和資料庫系統 #2 的下列權限，

ALTER ROLE psql WITH REPLICATION;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_reset() TO psql; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql; 
GRANT ALL ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO psql;

注意：psql 是專為本教學課程中的示範用途而建立的範例複製使用者。

工作 2：設定單向邏輯複製

資料庫系統 1：

完成前面概述的必備條件後，請建立測試表並插入一些記錄以驗證邏輯工作流程。

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
INSERT INTO test_table
    (id, data)
VALUES
    (generate_series(1, 1000), 'Test');

建立提供程式節點：

下一步是使用 pglogical 函數 create_node 來建立節點。

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxx
            dbname=postgres'
);

以下是 create_node 函數的參數定義：

node_name：指定要在資料庫系統 #1 上建立的發佈者名稱。
host：輸入資料庫系統 #1 的完整網域名稱 (FQDN)。
port_number：提供執行資料庫系統 #1 的連接埠。
database_name：指定將設定 pglogical 複製的資料庫名稱。

下一步是識別複製中需要包含的表格。pglogical 使用複製集來管理要複製的表格和動作 (INSERT、UPDATE、DELETE)。您可以使用 create_replication_set 函數建立自訂複製集，或使用預先定義的預設集複製其指派表格上的所有動作。若要包括表格，請使用個別表格的 replication_set_add_table 或 replication_set_add_all_tables 來新增特定綱要的所有表格。

下列命令會將公用綱要的所有表格新增至預設複製集：

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

若要進一步瞭解複製集，請參閱複製集。

資料庫系統 2：

建立與資料庫系統 #1 相符的表格：

在資料庫系統 #2 中建立符合資料庫系統 #1 中表格結構的測試表格。

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);

建立訂閱者節點：

接下來，使用 pglogical 函數 create_node 在資料庫系統 #2 上建立節點，就像在資料庫系統 #1 上一樣：

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=primary.xxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
            port=5432
            user=psql
            password=xxxxxx
            dbname=postgres'
);

以下是 create_node 函數的參數定義：

node_name：定義資料庫系統 #2 上的訂戶名稱。
host：輸入資料庫系統 #2 的完整網域名稱 (FQDN)。
port_number：輸入執行資料庫系統 #2 的連接埠。
database_name：提供將建立訂閱的資料庫名稱。

建立虛擬訂閱：

下一步是建立訂閱，以使用 create_subscription 函數從資料庫系統 #1 開始複製資料。

subscription_name 參數可以是您為訂閱選擇的任何名稱。provider_dsn 是「資料庫系統 #1」的連線字串，而 replication_sets 參數指定要使用的複製集。在此範例中，我們使用的是預設的複製集。

synchronize_data 引數會告知 pglogical 是否要從來源複製現有資料。在測試中，它會設為 true，因為我們希望複製所有資料列以及未來的任何變更。

forward_origins 參數對於啟用雙向複製至關重要。設定 forward_origins := '{}' 可確保只會轉送源自節點本身的變更，不會轉送已從其他節點複製的變更。這可防止複寫處理作業進入無限迴圈，讓變更持續回複寫。

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=primary.xxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := true,
    forward_origins := '{}'
);

檢查訂閱狀態：

使用下列命令檢查資料庫系統 #2 的訂閱狀態和基本資訊：

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription1
status            | replicating
provider_node     | provider1
provider_dsn      | host=primary.xxxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxxx
slot_name         | pgl_postgres_provider1_subscription1
replication_sets  | {default}
forward_origins   |

作業 3：測試從資料庫系統 #1 複製到資料庫系統 #2

現在資料庫系統 #1 (來源) 與資料庫系統 #2 (目標) 之間已設定複製，讓我們測試資料庫系統 #1 中所做的變更是否自動複製到資料庫系統 #2。

驗證初始資料

在資料庫系統 #2 上，檢查 test_table 表格：
```
postgres=> SELECT COUNT(*) FROM test_table;
 count 
-------
  1000
(1 row)
```
這會確認表格已經有 1000 個資料列。

測試 INSERT

在資料庫系統 #1 中新增一列：

postgres=> INSERT INTO test_table VALUES (10000, 'initial load');

將它存入資料庫系統 #1：

postgres=> SELECT * FROM test_table WHERE id = 10000;
  id   |     data     
-------+--------------
10000 | initial load

現在請檢查資料庫系統 #2。此處會顯示相同的資料列，顯示複製的運作方式：

postgres=> SELECT * FROM test_table WHERE id = 10000;
  id   |     data     
-------+--------------
10000 | initial load

測試 DELETE

刪除資料庫系統 #1 中的相同資料列：
```
postgres=> DELETE FROM test_table WHERE id = 10000;
```
在這兩個系統中驗證 — 這兩個系統都會產生一列。
```
(0 rows)
```
測試 UPDATE

更新資料庫系統 #1 中現有的資料列：
```
postgres=> UPDATE test_table SET data = 'Initial load' WHERE id = 1;
```
檢查資料庫系統 #1：
```
  id   |     data     
-------+--------------
  1 | Initial load
```
檢查資料庫系統 #2 — 更新的值也在此。

透過這些測試，我們確認已從資料庫系統 #1 → 資料庫系統 #2 正確複製 INSERT、UPDATE 和 DELETE 作業。

工作 4：設定雙向邏輯複製

先前的工作會設定從資料庫系統 #1 至資料庫系統 #2 的單向複製。接下來，我們也會執行其他命令，以反向方式啟用複製。

資料庫系統 2：

如同您在資料庫系統 #1 所做的一樣，設定複製集：

SELECT pglogical.replication_set_add_all_tables(
    'default',
    ARRAY['public']
);

資料庫系統 #1：

在資料庫系統 #1 上建立訂閱，以便開始從資料庫系統 #2 擷取變更：

SELECT pglogical.create_subscription(
    subscription_name := 'subscription2',
    provider_dsn := 'host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com
                     port=5432
                     sslmode=require
                     dbname=postgres
                     user=psql
                     password=xxxxx',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}'
);

在上一個命令中，synchronize_data 引數已設為 false，與之前不同。這是因為我們不需要將現有的資料庫系統 #2 資料複製到資料庫系統 #1，因為這兩個表格都已經包含相同的資料。

使用此命令，雙向複製設定現在已完成，對一個伺服器所做的任何變更都將複製到另一個伺服器。接著，檢查資料庫系統 #1 的訂閱狀態。

檢查資料庫系統 #1 的訂閱狀態：

使用下列命令檢查資料庫系統 #1 的訂閱狀態和基本資訊：

select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------
subscription_name | subscription2
status            | replicating
provider_node     | subscriber1
provider_dsn      | host=primary.xxxxxxxx.postgresql.us-phoenix-1.oci.oraclecloud.com port=5432 sslmode=require dbname=postgres user=psql password=xxxxx
slot_name         | pgl_postgres_subscriber1_subscription2
replication_sets  | {default}
forward_origins   |

工作 5：測試雙向複製

首先，先在兩個資料庫系統新增一列以驗證雙向複製是否正常運作。

在資料庫系統 #1 中插入一列：

在「資料庫系統 #1」上執行下列命令：
```
postgres=# INSERT INTO test_table VALUES (1001, 'Initial_load');
INSERT 0 1
```

驗證資料庫系統 #1 中插入的資料列：

postgres=# SELECT * FROM test_table WHERE id = 1001;
  id  |     data     
------+--------------
1001 | Initial_load
(1 row)

驗證資料庫系統 #2 上的複製：

現在，請檢查資料庫系統 #2，以確認資料列已經複製：
```
postgres=> SELECT * FROM test_table WHERE id = 1001;
  id  |     data     
------+--------------
1001 | Initial_load
(1 row)
```
這顯示「資料庫系統 #1」上的插入項目已複製到「資料庫系統 #2」。
在資料庫系統 #2 中插入一列：

在「資料庫系統 #2」上執行下列命令：
```
postgres=> INSERT INTO test_table VALUES (1002, 'Initial_load');
INSERT 0 1
```

請檢查在資料庫系統 #2 中插入的資料列：

postgres=> SELECT * FROM test_table WHERE id = 1002;
  id  |     data     
------+--------------
1002 | Initial_load
(1 row)

驗證資料庫系統 #1 上的複製：

現在，請檢查資料庫系統 #1 以確認資料列已經重新複製：
```
postgres=# SELECT * FROM test_table WHERE id = 1002;
  id  |     data     
------+--------------
1002 | Initial_load
(1 row)
```
此測試會確認資料變更在資料庫系統 #1 與資料庫系統 #2 之間的兩個方向均順利複製。

監控邏輯複寫延遲

邏輯設定完成後，您應該監督複製日誌以追蹤複製插槽、延遲及其他相關度量。

select * from pg_stat_replication;

此命令會傳回 OCI PostgreSQL 中複製的目前狀態。

結論

本教學課程逐步引導您使用 pglogical 完成 PostgreSQL 雙向複製的組態設定。雙向複製會增加資料庫基礎架構的複雜性，並且需要額外的工作，因此請務必確認它符合您的使用案例。如果您的應用程式需要跨不同區域的多個寫入器，且資料庫分區不適用，則雙向複製是理想的解決方案。

確認

作者 - Kaviya Selvaraj (資深會員技術人員)

其他學習資源

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

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

標題與著作權資訊

Bi-directional replication in OCI Database for PostgreSQL using pglogical

G43217-01

Oracle 和 (或) 其關係企業。