適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的邏輯複寫和邏輯解碼

發行項
08/15/2024

適用範圍：適用於 PostgreSQL 的 Azure 資料庫 - 彈性伺服器

適用於 PostgreSQL 的 Azure 資料庫彈性伺服器支援下列邏輯資料擷取和複寫方法：

邏輯複寫
1. 使用 PostgreSQL 原生邏輯複寫來複寫資料物件。邏輯複寫可讓您更精細地控制資料複寫，包括資料表層級的資料複寫。
2. 使用可提供邏輯串流複寫和更多功能的 pglogical 延伸模組，例如複製資料庫的初始結構描述、截斷的支援、複寫 DDL 的功能等等。
邏輯解碼，藉由解碼預寫記錄檔 (WAL) 的內容來實作。

比較邏輯複寫和邏輯解碼

邏輯複寫和邏輯解碼有數個相似之處。兩者皆是：

可讓您從 Postgres 複寫資料。
使用預寫記錄檔 (WAL) 作為變更來源。
使用邏輯複寫位置來傳送資料。位置代表變更的資料流。
使用資料表的 REPLICA IDENTITY 屬性來判斷可以傳送哪些變更。
請勿複寫 DDL 變更。

這兩種技術有其差異：

邏輯複寫：

可讓您指定要複寫的資料表或一組資料表。

邏輯解碼：

擷取資料庫中所有資料表的變更。

邏輯複寫和邏輯解碼的必要條件

移至入口網站上的伺服器參數頁面。
將伺服器參數 wal_level 設定為 logical。
如果您想要使用 pglogical 延伸模組，請搜尋 shared_preload_libraries 和 azure.extensions 參數，然後從下拉式清單方塊選取 pglogical。
將 max_worker_processes 參數值更新為至少 16。否則，您可能會遇到類似 WARNING: out of background worker slots 的問題。
儲存變更並重新啟動伺服器以套用變更。
確認適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體允許來自連線資源的網路流量。

授與管理使用者複寫權限。

ALTER ROLE <adminname> WITH REPLICATION;

您可能會想要確定您使用的角色，對於您要複寫的結構描述具有權限。否則，您可能會遇到 Permission denied for schema 之類的錯誤。

注意

最好將複寫使用者與一般系統管理員帳戶分開。

使用邏輯複寫和邏輯解碼

要從適用於 PostgreSQL 的 Azure 資料庫彈性伺服器複寫資料，最簡單的方式是使用原生的邏輯複寫。您可以使用 SQL 介面或串流通訊協定來取用變更。您也可以使用 SQL 介面透過邏輯解碼來取用變更。

原生邏輯複寫

邏輯複寫使用「發行者」和「訂閱者」詞彙。

發行者是您在傳送資料時作為來源的適用於 PostgreSQL 的 Azure 資料庫彈性伺服器資料庫。
訂閱者是您在傳送資料時作為目的地的適用於 PostgreSQL 的 Azure 資料庫彈性伺服器資料庫。

以下是一些可用來試用邏輯複寫的範例程式碼。

連線至發行者資料庫。建立資料表並新增一些資料。

CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
INSERT INTO basic VALUES (1, 'apple');
INSERT INTO basic VALUES (2, 'banana');

建立資料表的發行集。

CREATE PUBLICATION pub FOR TABLE basic;

連線到訂閱者資料庫。使用與發行者上相同的結構描述來建立資料表。
```
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);
```

建立會連線到您稍早所建立發行集的訂用帳戶。

CREATE SUBSCRIPTION sub CONNECTION 'host=<server>.postgres.database.azure.com user=<rep_user> dbname=<dbname> password=<password>' PUBLICATION pub;

您現在可以查詢訂閱者上的資料表。您會看到其已從發行者接收資料。
```
SELECT * FROM basic;
```
您可以將更多資料列新增至發行者的資料表，並檢視訂閱者上的變更。

如果您看不到資料，請啟用 azure_pg_admin 的登入權限，並檢查資料表內容。
```
ALTER ROLE azure_pg_admin login;
```

請造訪 PostgreSQL 文件，以深入了解邏輯複寫。

在相同伺服器上的資料庫之間使用邏輯複寫

當您的目標是在位於相同「適用於 PostgreSQL 的 Azure 資料庫」彈性伺服器執行個體的不同資料庫之間設定邏輯複寫時，請務必遵循特定指導方針，以免遇到目前存在的實作限制。到目前為止，只有在未於相同命令內建立複寫位置時，才能成功地建立會連線到相同資料庫叢集的訂用帳戶；否則，CREATE SUBSCRIPTION 呼叫會在 LibPQWalReceiverReceive 等候事件上停止回應。會這樣是 Postgres 引擎內的現有限制所導致，未來的版本可能會移除此限制。

若要有效地在相同伺服器上的「來源」和「目標」資料庫之間設定邏輯複寫，同時又規避這項限制，請遵循下列步驟：

首先，在來源和目標資料庫中建立名為「basic」且具有相同結構描述的資料表：

-- Run this on both source and target databases
CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);

接下來，在來源資料庫中，建立資料表的發行集，並使用 pg_create_logical_replication_slot 函式另外建立一個邏輯複寫位置，這有助於避免發生在與訂用帳戶相同的命令中建立位置時常會發生的問題。您必須使用 pgoutput 外掛程式：

-- Run this on the source database
CREATE PUBLICATION pub FOR TABLE basic;
SELECT pg_create_logical_replication_slot('myslot', 'pgoutput');

之後，在您的目標資料庫中，建立先前建立之發行集的訂用帳戶，並確保 create_slot 設定為 false，以防止適用於 PostgreSQL 的 Azure 資料庫彈性伺服器建立新的位置，並正確地指定在上一個步驟中建立的位置名稱。在執行命令之前，請先將連接字串中的預留位置取代為您實際的資料庫認證：

-- Run this on the target database
CREATE SUBSCRIPTION sub
   CONNECTION 'dbname=<source dbname> host=<server>.postgres.database.azure.com port=5432 user=<rep_user> password=<password>'
   PUBLICATION pub
   WITH (create_slot = false, slot_name='myslot');

在設定好邏輯複寫後，現在可以進行測試了，方法是將新記錄插入到來源資料庫中的「basic」資料表，然後驗證其會複寫至目標資料庫：

-- Run this on the source database
INSERT INTO basic SELECT 3, 'mango';

-- Run this on the target database
TABLE basic;

如果一切都已正確設定，您應該會看見目標資料庫中出現來源資料庫中的新記錄，從而確認邏輯複寫設定已經成功。

pglogical 擴充功能

以下是在提供者資料庫伺服器和訂閱者上設定 pglogical 的範例。如需詳細資訊，請參閱 pglogical 擴充功能文件。此外，請確定您已執行上述必要工作。

在提供者和訂閱者資料庫伺服器的資料庫中安裝 pglogical 擴充功能。
```
\c myDB
CREATE EXTENSION pglogical;
```
如果複寫使用者不是伺服器系統管理員使用者 (其建立了伺服器)，請務必向使用者授與角色 azure_pg_admin 中的成員資格，並向使用者指派 REPLICATION 和 LOGIN 屬性。如需詳細資訊，請參閱 pglogical 文件。
```
GRANT azure_pg_admin to myUser;
ALTER ROLE myUser REPLICATION LOGIN;
```

在提供者 (來源/發行者) 資料庫伺服器上，建立提供者節點。

select pglogical.create_node( node_name := 'provider1',
dsn := ' host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>');

建立複寫集。

select pglogical.create_replication_set('myreplicationset');

將資料庫中的所有資料表新增至複寫集。

SELECT pglogical.replication_set_add_all_tables('myreplicationset', '{public}'::text[]);

作為替代方法，您也可以從特定的結構描述 (例如 testUser) 將資料表新增至預設複寫集。

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

在訂閱者資料庫伺服器上，建立訂閱者節點。

select pglogical.create_node( node_name := 'subscriber1',
dsn := ' host=mySubscriberServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>' );

建立訂用帳戶以啟動同步處理和複寫程序。

select pglogical.create_subscription (
subscription_name := 'subscription1',
replication_sets := array['myreplicationset'],
provider_dsn := 'host=myProviderServer.postgres.database.azure.com port=5432 dbname=myDB user=myUser password=<password>');

然後，您可以驗證訂用帳戶狀態。

SELECT subscription_name, status FROM pglogical.show_subscription_status();

警告

Pglogical 目前不支援進行自動 DDL 複寫。您可以使用 pg_dump --schema-only 手動複製初始結構描述。您可以使用 pglogical.replicate_ddl_command 函式，同時在提供者和訂閱者上執行 DDL 陳述式。請注意這裡所列延伸模組的其他限制。

邏輯解碼

邏輯解碼可透過串流通訊協定或 SQL 介面來取用。

串流通訊協定

通常最好使用串流通訊協定來取用變更。您可以建立自己的取用者 / 連接器，或使用 Debezium 之類的第三方服務。

如需使用串流通訊協定搭配 pg_recvlogical 的範例，請參閱 wal2json 文件。

SQL 介面

在下列範例中，我們使用 SQL 介面搭配 wal2json 外掛程式。

建立位置。

SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

發出 SQL 命令。例如：

CREATE TABLE a_table (
   id varchar(40) NOT NULL,
   item varchar(40),
   PRIMARY KEY (id)
);

INSERT INTO a_table (id, item) VALUES ('id1', 'item1');
DELETE FROM a_table WHERE id='id1';

取用變更。

SELECT data FROM pg_logical_slot_get_changes('test_slot', NULL, NULL, 'pretty-print', '1');

輸出如下所示：

{
      "change": [
      ]
}
{
      "change": [
               {
                        "kind": "insert",
                        "schema": "public",
                        "table": "a_table",
                        "columnnames": ["id", "item"],
                        "columntypes": ["character varying(40)", "character varying(40)"],
                        "columnvalues": ["id1", "item1"]
               }
      ]
}
{
      "change": [
               {
                        "kind": "delete",
                        "schema": "public",
                        "table": "a_table",
                        "oldkeys": {
                              "keynames": ["id"],
                              "keytypes": ["character varying(40)"],
                              "keyvalues": ["id1"]
                        }
               }
      ]
}

使用完畢後，請卸除該位置。

SELECT pg_drop_replication_slot('test_slot');

請造訪 PostgreSQL 文件，以深入了解邏輯解碼。

監視器

您必須監視邏輯解碼。任何未使用的複寫位置都必須卸除。位置會保留 Postgres WAL 記錄和相關系統目錄，直到讀取變更為止。如果您的訂閱者或取用者失敗或尚未正確設定，則未取用的記錄會堆積並填滿儲存體。此外，未取用的記錄會增加環繞著交易識別碼的風險。這兩種情況都可能導致伺服器變成無法使用。因此，必須持續取用邏輯複寫位置。如果不再使用邏輯複寫位置，請立即卸除。

pg_replication_slots 檢視中的 [使用中] 資料行會指出是否有連線到位置的取用者。

SELECT * FROM pg_replication_slots;

針對適用於 PostgreSQL 的 Azure 資料庫彈性伺服器的 [已使用的交易識別碼上限] 和 [已使用的儲存體] 計量設定警示，以在值增加超過標準閾值時通知您。

限制

邏輯複寫限制會如這裡所記載般套用。
位置和 HA 容錯移轉 - 搭配使用已啟用高可用性 (HA) 的伺服器與適用於 PostgreSQL 的 Azure 資料庫彈性伺服器時，請注意容錯移轉事件發生期間不會保留邏輯複寫位置。若要保有邏輯複寫位置，並確保資料在容錯移轉後保持一致，建議使用 PG 容錯移轉位置延伸模組。如需如何啟用此延伸模組的詳細資訊，請參閱文件。

重要

如果對應的訂閱者已不存在，您必須卸載主伺服器中的邏輯複寫位置。否則，WAL 檔案會堆積在主要伺服器中並填滿儲存體。假設儲存體閾值超過特定閾值，且邏輯複寫位置並未使用 (因為無法使用訂閱者)。在此情況下，適用於 PostgreSQL 的 Azure 資料庫彈性伺服器執行個體會自動卸除未使用的邏輯複寫位置。該動作會釋放累積的 WAL 檔案，且避免伺服器因為儲存體填滿的情況而變得無法使用。

共用方式為

適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的邏輯複寫和邏輯解碼

比較邏輯複寫和邏輯解碼

邏輯複寫和邏輯解碼的必要條件

使用邏輯複寫和邏輯解碼

原生邏輯複寫

在相同伺服器上的資料庫之間使用邏輯複寫

pglogical 擴充功能

邏輯解碼

串流通訊協定

SQL 介面

監視器

限制

意見反應

其他資源

共用方式為

適用於 PostgreSQL 的 Azure 資料庫彈性伺服器中的邏輯複寫和邏輯解碼

比較邏輯複寫和邏輯解碼

邏輯複寫和邏輯解碼的必要條件

使用邏輯複寫和邏輯解碼

原生邏輯複寫

在相同伺服器上的資料庫之間使用邏輯複寫

pglogical 擴充功能

邏輯解碼

串流通訊協定

SQL 介面

監視器

限制

相關內容

意見反應

其他資源