設定邏輯複製和解碼

透過集合功能整理內容 你可以依據偏好儲存及分類內容。

您可以在 PostgreSQL 適用的 Cloud SQL 中使用邏輯複製和解碼功能。這些功能可啟用邏輯複製工作流程和變更資料擷取 (CDC) 工作流程。

如需複製作業的一般資訊，請參閱「關於 Cloud SQL 中的複製作業」。

簡介

PostgreSQL 執行邏輯複製時，會使用邏輯解碼功能，從 WAL 記錄檔擷取傳送至副本的變更。解碼後的變更與基礎實體儲存格式無關。這些變更僅反映 SQL 層級的資料變更，包括插入、更新和刪除。這種與儲存層獨立的設計可提供極高的彈性，讓變更資料流的使用者享有多樣化的功能。

邏輯複製是邏輯解碼的旗艦功能。

與 PostgreSQL 的實體複製功能不同，實體複製功能要求來源和目的地資料庫必須是相同版本，而邏輯複製功能則可跨 PostgreSQL 主要版本進行複製。Cloud SQL 中的邏輯複寫功能支援 pglogical 擴充功能，後者適用於所有 PostgreSQL 版本，以及 PostgreSQL 10 中新增的 PostgreSQL 原生邏輯複寫功能。

您可以使用不同的外掛程式設定變更的串流格式。這可讓您靈活運用變更資料擷取 (CDC) 架構。舉例來說，wal2json 擴充功能可讓您將資料庫中的所有變更串流傳送至消費者，並以 JSON 格式呈現。Cloud SQL 支援內建的 pgoutput 解碼器、test_decoding contrib 模組和 wal2json。Cloud SQL 目前支援 wal2json 的兩種 JSON 輸出方式：format-version 1 會將整個交易編碼為單一 JSON 物件，而 format-version 2 則會為每個指令輸出一個 JSON 物件。這些外掛程式可讓您將資料複製到非 PostgreSQL 資料庫。

設定 PostgreSQL 執行個體

PostgreSQL 會將額外資訊寫入預先寫入記錄檔 (WAL)，以支援邏輯解碼。

在 Cloud SQL 中，您可以將 cloudsql.logical_decoding 標記設為 on，啟用這項功能。這項設定與標準 PostgreSQL 中使用的設定不同。如果您變更外部 PostgreSQL 例項，請將 wal_level 設定參數設為 logical，即可啟用這項功能。

如果您打算使用 pglogical 擴充功能，請務必將 pglogical 新增至 shared_preload_libraries。由於 Cloud SQL 不允許直接修改這個標記，因此您可以將 cloudsql.enable_pglogical 設為 on，啟用 pglogical。(在 VM 上，請執行 sudo apt-get install postgresql-13-pglogical)，然後重新啟動資料庫。

如果您使用 pglogical 在兩個 PostgreSQL 執行個體之間複製資料，則只需在主要執行個體上啟用邏輯解碼功能，而非在複本執行個體上啟用 (除非該執行個體本身是其他複本的主要執行個體)。不過，您必須在兩個執行個體上啟用 pglogical 擴充功能。如需「主要」和「備援」的用法和含義的範例，請參閱「關於 Cloud SQL 中的複寫」。

啟用網路連線

確認主要執行個體接受複本執行個體的連線。

取得複本執行個體的傳出 IP 位址

如果備用執行個體是 Cloud SQL 執行個體，且具有公開 IP 位址，請執行下列步驟取得傳出 IP 位址。

gcloud sql instances describe [REPLICA_NAME] --format="default(ipAddresses)"

允許連線

如果主要執行個體是 Cloud SQL 執行個體，您可以將複本的傳出 IP 位址新增為已授權網路，允許複本存取資料。

為 PostgreSQL 9.6 以下版本啟用複製作業連線

如果主要執行個體並未在 Cloud SQL 中執行，且執行的是 PostgreSQL 9.6 以下版本，您必須確認執行個體的 pg_hba.conf 檔案已設為接受複寫連線。請在該檔案中加入下列程式碼行，並使用 all all 進行初始測試。為了提高安全性，請只限制必要的使用者和 IP 位址，如以下範例所示：

host replication all all md5

如需更多資訊，請參閱「pg_hba.conf 檔案」。

建立複製使用者

如要使用邏輯解碼功能，請使用 REPLICATION 屬性建立 PostgreSQL 使用者。

範例

CREATE USER replication_user WITH REPLICATION
IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'secret';

或者，您也可以為現有使用者設定這項屬性：

ALTER USER existing_user WITH REPLICATION;

PostgreSQL 資源

使用邏輯解碼功能時，主要 PostgreSQL 執行個體的背景程序會使用所選解碼外掛程式，將 WAL 變更轉換為邏輯變更，並將這些變更轉送至消費者 (甚至可以是 PostgreSQL 以外的執行個體)。這項背景程序稱為 WAL 傳送器。在 PostgreSQL 例項中，可同時啟用的 WAL 傳送器數量受 max_wal_senders 標記限制。這個標記的預設值為 10，其上限會隨著 Cloud SQL 執行個體的記憶體量線性增加，每 GB 記憶體可允許 8 個 WAL 傳送器。

為確保 WAL 區段不會在傳送至所有使用者前遭到捨棄，PostgreSQL 會使用邏輯複製運算單元追蹤哪些資料傳送給哪些使用者 (以及用於讀取副本的物理複製運算單元)。您為 PostgreSQL 例項建立的複製運算單元數量，受 max_replication_slots 標記限制。這個標記的預設值為 10，上限會隨著 Cloud SQL 執行個體的記憶體而增加，每 GB 記憶體可允許 2 到 8 個複製作業間隔。

下表顯示 Cloud SQL 執行個體的記憶體上限與執行個體的複製工作階段上限之間的關係。

每個消費者通常會有一個複製時段和 WAL 傳送器，因此這些標記應設為大致相同的值。不過，PostgreSQL 建議為 max_wal_senders 提供一個小型緩衝區，以便在連線意外中斷且建立新連線時處理。物理複製功能 (Cloud SQL 唯讀備用資源所使用的功能) 也會使用複製時段和 WAL 傳送器，因此在計算需要的各項資源數量時，請將這些項目納入計算。

PostgreSQL 原生邏輯複製和 pglogical 都需要在主要和備用資源執行個體上執行額外的背景程序。可執行的背景處理程序數量受 max_worker_processes 標記限制。預設值為八，且上限會隨著 Cloud SQL 執行個體的記憶體量線性增加，每 GB 記憶體可額外增加兩個程序。在各個部分中，我們會說明這些方法所使用的確切工作者程序數量。

如果這項旗標設定過低，且複製作業失敗，並在記錄中顯示錯誤訊息 worker registration failed，您可能需要調高 max_worker_processes 設定。

請注意，WAL 傳送器不計入為 worker 程序。為並行查詢執行作業產生的 worker 會計入，因此如果 max_worker_processes 的值設得太低，PostgreSQL 無法利用並行查詢執行作業，可能會導致效能不佳。

您可以使用 pg_ls_waldir () 函式判斷 WAL 磁碟用量。這項功能僅限 cloudsqlsuperuser 使用者使用，例如預設管理員使用者 postgres。這項功能僅適用於 PostgreSQL 10 以上版本。

如要計算 WAL 磁碟總用量，請按照下列步驟操作：

postgres=> select * from pg_ls_waldir();

(2 列)

postgres=> select pg_size_pretty(sum(size)) as "Total WAL disk usage" from pg_ls_waldir();

(1 列)

使用外部備用資源設定邏輯複製

如需使用 pglogical 和邏輯解碼的完整範例，請參閱「 設定外部備用資源」。

使用 pglogical 設定邏輯複製

如要使用 pglogical 設定邏輯複製，必須在主要執行個體上啟用邏輯解碼。在 Cloud SQL 執行個體上設定 cloudsql.logical_decoding=on，或在外部執行個體上設定 wal_level=logical。此外，您必須在主要和備用資源執行個體上啟用 pglogical；在 Cloud SQL 執行個體上設定 cloudsql.enable_pglogical=on，或在外部執行個體上將 pglogical 新增至 shared_preload_libraries。請注意，變更這些標記時，主執行個體和備援執行個體都必須重新啟動。

如果在執行這些步驟時遇到問題，請參閱排解 pglogical 問題。

建立具有複製權限的使用者

使用 pglogical 時，您需要在主執行個體和備用執行個體上，為使用者授予複製權限和 cloudsqlsuperuser 角色。下列所述的所有指令都應由該使用者執行。

安裝 pglogical 擴充功能

您需要在主要執行個體和複本執行個體上安裝 pglogical 擴充功能。在主要執行個體上，複製使用者 (也就是連線至資料庫的使用者) 必須安裝該更新。

CREATE EXTENSION pglogical;

在每個執行個體上建立 pglogical 節點

pglogical 節點代表實體 PostgreSQL 例項，並儲存該例項的連線詳細資料。主要和備援執行個體都必須將自己註冊為節點：

source-instance$ SELECT pglogical.create_node(
    node_name := 'primary',
    dsn := 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=secret'
);

dest-instance$ SELECT pglogical.create_node(
    node_name := 'replica',
    dsn := 'host=<replica-ip> port=5432 dbname=postgres user=replication_user password=secret'
);

建立要複製資料的資料表

pglogical 擴充功能可讓您只將部分資料表複製到目的地。舉例來說，我們會在主要執行個體上建立虛擬資料表，並填入一些資料進行測試：

CREATE TABLE replica_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO replica_test (data) VALUES ('apple'), ('banana'), ('cherry');

資料表也必須在複本執行個體上建立。

將資料表新增至複製作業

為了支援將不同資料組複製到不同目的地，pglogical 採用了複製組合的概念。我們可以將測試資料表新增至預設複製組合。

SELECT pglogical.replication_set_add_table('default', 'replica_test', true);

建立 pglogical 訂閱

提供主要執行個體的連線詳細資料，在目的地執行個體上建立 pglogical 訂閱。

SELECT pglogical.create_subscription(
    subscription_name := 'test_sub',
    provider_dsn := 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword'
);

SELECT * FROM pglogical.show_subscription_status('test_sub');

如果狀態顯示為「複製中」，表示設定成功。查詢 replica_test 資料表，確認資料已複製。在主要執行個體上插入及修改記錄，並確認這些記錄會顯示在複本執行個體上。

在主機上查詢 pg_replication_slots 資料表，查看訂閱項目建立的複寫時段。

清除所用資源

測試成功後，請使用 pglogical.drop_subscription('test_sub') 在複本上捨棄訂閱項目。確認複製工作階段是否也已在主資料來源上刪除。否則，WAL 區段會繼續在複本執行個體上累積。

如要進一步瞭解複製集、部分資料複製、DDL 複製、其他進階設定和限制，請參閱 pglogical 說明文件。

資源使用情況

pglogical 擴充功能會執行多個背景程序，這些程序會計入 max_worker_processes 限制。

在穩定狀態下，啟用時會執行一個「supervisor」程序，每個已安裝擴充功能的 PostgreSQL 資料庫會執行一個「manager」程序 (例如，可能有 D 個)，每個備援例項的 pglogical 訂閱會執行一個「apply」程序 (例如，可能有 S 個)。不過，擴充功能在執行初始同步作業時，可能會產生額外的 worker 程序，並且實際上會為執行個體中的「每個」資料庫產生「manager」程序，但如果資料庫未安裝擴充功能，則會立即結束。

因此，請分配比穩定狀態所需多一點的工作站程序。PostgreSQL 會將 worker 程序用於其他用途，例如並行查詢處理。如果 max_worker_processes 設定得太低，複製作業可能會在無聲音訊號的情況下失敗，或是 PostgreSQL 可能無法執行並行查詢處理作業。

總結來說，建議您採用下列設定：

max_worker_processes
  >= 1 + D + 8 (on the source instance)
  >= 1 + D + S + 8 (on the destination instance)
max_wal_senders >= S + 2 (on the source instance)
max_replication_slots >= S (on the source instance)

排解 pglogical 問題

無法建立 pglogical 擴充功能

嘗試安裝 pglogical 擴充功能時，您可能會看到以下錯誤：

ERROR:  pglogical is not in shared_preload_libraries

在 Cloud SQL 執行個體上安裝 pglogical 時，請務必設定 cloudsql.enable_pglogical=on。如果使用外部執行個體，請直接將其新增至 shared_preload_libraries 旗標，例如 shared_preload_libraries=pg_stat_statements,pglogical。這些修改需要重新啟動主要執行個體。

無法建立 pglogical 訂閱

建立訂閱時，pglogical 會先檢查是否能使用連線詳細資料連線至執行個體。它會先嘗試建立一般連線，如果失敗，就會發生錯誤：ERROR: could not connect to the postgresql server。

如果發生這個錯誤，請確認主要執行個體已設定為允許複本執行個體的連線，並確認您提供的連線詳細資料是否正確。並提供 PostgreSQL 無法建立連線的原因。

建立一般連線後，pglogical 會嘗試建立特殊的複寫連線。在 PostgreSQL 9.6 及更早版本中，這類連線可能會有不同的驗證設定。如果您看到以下錯誤訊息：ERROR: could not connect to the postgresql server in replication mode，就需要在來源執行個體上更新 pg_hba.conf 檔案。

Cloud SQL 使用的 pg_hba.conf 檔案已完成必要變更；只有在連線至未由 Cloud SQL 管理的外部執行個體時，才會發生這個錯誤。

或者，如果來源執行個體不允許足夠的 WAL 傳送端，複製模式連線可能會失敗。如果看到 FATAL: number of requested standby connections exceeds max_wal_senders，請在主要執行個體上增加 max_wal_senders。

pglogical 訂閱項目已停止運作

pglogical 訂閱項目可能無法複製。如要解決這個問題，請先確認背景程序是否在複本執行個體上執行。查詢 pg_stat_activity，確認 pglogical apply 程序是否正在執行。如果沒有，請檢查目的地節點上的記錄。如果您看到「worker registration failed,」訊息，可以提高「max_worker_processes」設定。

接著，請確認主要執行個體上已建立複製作業時段。在複本執行個體上，pglogical.subscription 中的資料列包含訂閱嘗試建立的空白名稱，您可以在主要執行個體上查詢 pg_replication_slots，驗證是否已成功建立空白。

如果沒有建立複製時段，請檢查主要執行個體的記錄。

ERROR: logical decoding requires wal_level >= logical 錯誤表示 wal_level 標記未設為 logical。如要解決這個問題，請在主要執行個體上設定 cloudsql.logical_decoding=on (如果是 Cloud SQL 執行個體)。

或者，如果執行個體是外部執行個體，請設定 wal_level=logical。

否則，您可能會看到 ERROR: all replication slots are in use，以及實用的 HINT: Free one or increase max_replication_slots。

設定原生 PostgreSQL 邏輯複寫

自 PostgreSQL 10 起，PostgreSQL 就支援原生內建邏輯複製功能。如要設定原生邏輯複製功能，必須在主要執行個體上啟用邏輯解碼功能，方法是在 Cloud SQL 執行個體上設定 cloudsql.logical_decoding=on，或在外部執行個體上設定 wal_level=logical。請注意，修改這些標記需要重新啟動主要執行個體。

請參閱「設定 PostgreSQL 執行個體」一文中的各個部分，確保已正確設定執行個體 (例如網路連線等)。本頁面提供概念驗證的步驟。如果在執行這些部分的步驟時遇到任何問題，請參閱pglogical 疑難排解。詳情請參閱 PostgreSQL 說明文件中的「邏輯複寫」一節。

建立要複製資料的資料表

原生 PostgreSQL 邏輯複寫功能支援整個資料庫或個別資料表。舉例來說，我們會在主要執行個體上建立虛擬資料表，並填入資料進行測試。

CREATE TABLE native_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO native_test (data) VALUES ('apple'), ('banana'), ('cherry');

資料表也必須在複本執行個體上建立。

在主要執行個體上建立發布內容

原生 PostgreSQL 邏輯複製功能會處理發布者和訂閱者。在 native_test 中建立資料的發布作業：

CREATE PUBLICATION pub FOR TABLE native_test;

在備用資源執行個體上建立訂閱

以下是如何在複本執行個體上建立訂閱項目的範例：

CREATE SUBSCRIPTION sub
    CONNECTION 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword'
    PUBLICATION pub;

您必須具備 cloudsqlsuperuser 角色，才能在複本執行個體上建立訂閱項目。建立訂閱項目後，請查詢 native_test 資料表，確認資料已出現在複本執行個體中。

在主資料來源上，您可以查詢 pg_replication_slots 資料表，查看訂閱項目建立的複寫時段。

清除所用資源

測試成功後，請使用 DROP SUBSCRIPTION sub; 在複本上放棄訂閱。確認複製時段也已在主機上刪除。否則 WAL 區段會繼續在主要執行個體上累積。

原生 PostgreSQL 邏輯複製功能的限制

無法存取 pg_subscription 系統資料表的 subconninfo 欄。

執行 pg_dump 無法轉儲訂閱資訊，因為它會檢查連線使用者是否具有超級使用者權限。

接收變更資料擷取 (CDC) 的解碼 WAL 變更

邏輯解碼可從 PostgreSQL 執行個體串流變更，這也是 CDC 的另一種用途。這項作業的標準工具是 pg_recvlogical。

您可以使用 pg_recvlogical 工具建立複製時段，並透過該時段追蹤變更串流。變更的格式取決於您選擇的解碼外掛程式。例如：

• wal2json，用於以 JSON 格式串流變更，或

• test_decoding，用於串流格式為精簡文字格式的變更

建立複製作業

如要建立複製運算單元，請執行：

pg_recvlogical
  -h <instance_ip> \
  -U <replication_user> \
  -p 5432 \
  -d postgres \
  --slot test_slot \
  --create-slot \
  -P <decoder_plugin>

串流變更

在一個 Cloud Shell 終端機中執行以下指令：

pg_recvlogical
  -h <instance_ip> \
  -U <replication_user> \
  -p 5432 \
  -d postgres \
  --slot test_slot \
  --start \
  -f -

在另一個 Cloud Shell 終端機中，連線至資料庫並執行下列指令：

CREATE TABLE cdc_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO cdc_test (data) VALUES ('apple', 'banana');
UPDATE cdc_test SET data = 'cherry' WHERE id = 2;
DELETE FROM cdc_test WHERE id = 1;
DROP TABLE cdc_test;

如果您使用 wal2json 解碼器外掛程式，第一個 Cloud Shell 終端機會顯示類似以下的輸出內容：

{"change":[]}
{"change":[{"kind":"insert","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[1,"apple"]},{"kind":"insert","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[2,"banana"]}]}
{"change":[{"kind":"update","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[2,"cherry"],"oldkeys":{"keynames":["id"],"keytypes":["integer"],"keyvalues":[2]}}]}
{"change":[{"kind":"delete","schema":"public","table":"cdc_test","oldkeys":{"keynames":["id"],"keytypes":["integer"],"keyvalues":[1]}}]}
{"change":[]}

如果您使用的是 test_decoding 解碼器外掛程式，第一個 Cloud Shell 終端機會顯示類似下列的輸出內容：

BEGIN 19460
COMMIT 19460
BEGIN 19461
table public.cdc_test: INSERT: id[integer]:1 data[text]:'apple'
table public.cdc_test: INSERT: id[integer]:2 data[text]:'banana'
COMMIT 19461
BEGIN 19462
table public.cdc_test: UPDATE: id[integer]:2 data[text]:'cherry'
COMMIT 19462
BEGIN 19463
table public.cdc_test: DELETE: id[integer]:1
COMMIT 19463
BEGIN 19464
COMMIT 19464

(您的交易 ID 可能不同)。

清除所用資源

完成測試後，請執行下列指令，捨棄您建立的複寫時段：

pg_recvlogical
  -h <instance_ip> \
  -U <replication_user> \
  -p 5432 \
  -d postgres \
  --slot test_slot \
  --drop-slot

注意事項和限制

本節的附註和限制適用於 PostgreSQL 適用的 Cloud SQL 的邏輯複製和解碼功能。

• pglogical 擴充功能無法在已啟用連接器強制執行的執行個體中運作。這項限制不適用於已設定私人服務存取權的執行個體。

• 當您還原已啟用 cloudsql.logical_decoding 或 cloudsql.enable_pglogical 的執行個體，且目前做為邏輯複寫的發布者時，您必須先停用對所有目標執行個體的複寫作業。否則，復原至執行個體會失敗並顯示錯誤，但目前無法查看錯誤詳細資料。

• 當您還原已啟用 cloudsql.logical_decoding 或 cloudsql.enable_pglogical (在備份時) 的執行個體備份，並將備份還原至新執行個體時，複寫狀態不會還原至新執行個體。之後必須手動重新設定複寫。

• 在含有一或多個 Cloud SQL 唯讀備用資源 (使用實體複製) 的 Cloud SQL 執行個體中，如果您啟用 cloudsql.logical_decoding 或 cloudsql.enable_pglogical，這些旗標也會在唯讀備用資源上啟用。

  • 針對 PostgreSQL 適用的 Cloud SQL 15 以下版本，Cloud SQL 讀取複本執行個體無法做為邏輯複製的發布端，因為 PostgreSQL 不支援在這些舊版的讀取複本上進行邏輯解碼。不過，為了確保在升級時，執行個體可以取代主要執行個體，這些舊版的唯讀備用資源執行個體仍會啟用邏輯旗標。

  • 從 PostgreSQL 適用的 Cloud SQL 16 版開始，如果主要執行個體已設定邏輯標記，Cloud SQL 唯讀備用資源執行個體可做為邏輯複寫的發布端。邏輯訂閱者可以是 Cloud SQL 執行個體或外部執行個體。不過，主要資料表上的資料列刪除和真空操作可能會刪除讀取副本的邏輯解碼仍需要的元組。在這種情況下，讀取用複本上的邏輯複製時段會失效，以免發生不一致的情況。

  • 在主要執行個體上啟用 cloudsql.logical_decoding 或 cloudsql.enable_pglogical 會導致所有唯讀備用資源都啟用標記，進而導致主要執行個體和唯讀備用資源會在短時間內重新啟動。為避免發生這種情況，並控制各個執行個體何時重新啟動，您可以 (1) 依序設定各個唯讀備用資源的標記，然後 (2) 設定主要執行個體的標記。

  • 在主要執行個體上停用 cloudsql.logical_decoding 或 cloudsql.enable_pglogical 不會導致所有讀取副本上的標記遭到停用。如要停用各執行個體的標記，您必須執行上述步驟的反向操作：(1) 停用主要執行個體的標記，然後 (2) 依序停用每個唯讀備用資源的標記。