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

  • 發行項

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

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

  1. 邏輯複寫

    1. 使用 PostgreSQL 原生邏輯複 寫來復寫數據物件。 邏輯復寫允許對數據復寫進行更細緻的控制，包括數據表層級的數據複寫。
    2. 使用 pglogical 擴充功能來提供邏輯串流復寫，以及更多功能，例如複製資料庫的初始架構、支援 TRUNCATE、複寫 DDL 等。

  2. 邏輯譯 碼，其實作方式 是譯 碼預先寫入記錄的內容（WAL）。

比較邏輯復寫和邏輯譯碼

邏輯復寫和邏輯譯碼有數個相似之處。 他們兩者：

這兩種技術有其差異：

邏輯複寫：

  • 可讓您指定要復寫的數據表或一組數據表。

邏輯譯碼：

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

邏輯復寫和邏輯譯碼的必要條件

  1. 移至入口網站上的伺服器參數頁面。

  2. 伺服器參數 wal_level 設定為 logical。

  3. 如果您要使用 pglogical 擴充功能，請搜尋 shared_preload_libraries、 和 azure.extensions 參數，然後從下拉式清單框中選取 pglogical 。

  4. 將參數值更新 max_worker_processes 為至少 16。 否則，您可能會遇到類似 的問題 WARNING: out of background worker slots。

  5. 儲存變更並重新啟動伺服器以套用變更。

  6. 確認您的 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器實例允許來自連線資源的網路流量。

  7. 授與系統管理員用戶復寫許可權。

    ALTER ROLE <adminname> WITH REPLICATION;

  8. 您可能想要確定您使用的角色具有 您要復寫之架構的許可權 。 否則，您可能會遇到錯誤，例如 Permission denied for schema。

注意

將復寫使用者與一般系統管理員帳戶分開，一律是很好的作法。

使用邏輯復寫和邏輯譯碼

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

原生邏輯復寫

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

  • 發行者是您從中傳送數據的 適用於 PostgreSQL 的 Azure 資料庫 彈性伺服器資料庫。
  • 訂閱者是您傳送數據的彈性伺服器資料庫 適用於 PostgreSQL 的 Azure 資料庫。

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

  1. 連線 發行者資料庫。 建立數據表並新增一些數據。

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

  2. 建立數據表的發行集。

    CREATE PUBLICATION pub FOR TABLE basic;

  3. 連線 至訂閱者資料庫。 使用與發行者上相同的架構建立數據表。

    CREATE TABLE basic (id INTEGER NOT NULL PRIMARY KEY, a TEXT);

  4. 建立連接到您稍早建立之發行集的訂閱。

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

  5. 您現在可以查詢訂閱者上的數據表。 您會看到它已從發行者接收數據。

    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 擴充功能檔 。 也請確定您已執行上述必要條件工作。

  1. 在提供者和訂閱者資料庫伺服器的資料庫中安裝 pglogical 擴充功能。

    \c myDB
CREATE EXTENSION pglogical;

  2. 如果復寫使用者不是伺服器管理使用者（誰建立伺服器），請確定您授與角色 azure_pg_admin 的成員資格給使用者，並將 REPLICATION 和 LOGIN 屬性指派給使用者。 如需詳細資訊，請參閱 pglogical 檔 。

    GRANT azure_pg_admin to myUser;
ALTER ROLE myUser REPLICATION LOGIN;

  3. 在 提供者 （來源/發行者）資料庫伺服器上，建立提供者節點。

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

  4. 建立複寫集。

    select pglogical.create_replication_set('myreplicationset');

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

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

    作為替代方法，您也可以將數據表從特定架構（例如 testUser）新增至預設的複寫集。

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

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

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

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

    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=myPassword');

  8. 然後，您可以確認訂用帳戶狀態。

    SELECT subscription_name, status FROM pglogical.show_subscription_status();

警告

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

邏輯解碼

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

串流通訊協定

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

如需使用串流通訊協定搭配pg_recvlogical的範例，請流覽 wal2json 檔。

SQL 介面

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

  1. 建立位置。

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

  2. 發出 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';

  3. 取用變更。

    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"]
                        }
               }
      ]
}

  4. 一旦您完成使用它，請卸除位置。

    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 檔案，並避免您的伺服器因為記憶體已滿的情況而變得無法使用。

相關內容