在 AlloyDB Omni 和其他資料庫之間複製資料

選取說明文件版本： 目前版本 (16.8.0)

• 目前 (16.8.0)
• 16.8.0
• 16.3.0
• 15.12.0
• 15.7.1
• 15.7.0
• 15.5.5
• 15.5.4
• 15.5.2

本頁說明如何使用 pglogical 擴充功能，在 AlloyDB Omni 和其他資料庫之間複製資料。

本頁面資訊僅適用於容器專用的 AlloyDB Omni。不適用於 Kubernetes 適用的 AlloyDB Omni。

詳情請參閱「關於 pglogical 擴充功能」和「pglogical 術語和基本元件」。

支援的驗證方法

與 pglogical 擴充功能搭配使用的兩種主要驗證方法是密碼和信任驗證方法。

建議的驗證方式是信任驗證方式。詳情請參閱「支援的驗證方法」。

注意： 信任驗證方法不適用於雲端供應商的受管理服務資料庫，例如 Amazon Aurora (非 AlloyDB 來源)。對於這類連線，請使用密碼驗證方法。

事前準備

您可以在特定資料庫中安裝 pglogical 做為擴充功能。

在 AlloyDB Omni 上實作 pglogical 擴充功能前，請確認您符合下列系統需求：

• 以 superuser 形式存取非 AlloyDB PostgreSQL 叢集。
• pglogical 擴充功能已安裝在非 AlloyDB PostgreSQL 叢集中。如需發布和發行專用的安裝操作說明，請參閱 pglogical。
• 已安裝及設定 AlloyDB Omni 伺服器。如需安裝 AlloyDB Omni 的操作說明，請參閱「安裝 AlloyDB Omni」。
• 非 AlloyDB PostgreSQL 叢集和 AlloyDB Omni 主機伺服器的 IP 位址。
• 非 AlloyDB PostgreSQL 叢集與 AlloyDB Omni 主機伺服器之間已建立安全網路。必須透過標準 PostgreSQL 通訊埠 5432 建立 TCP 連線。

調整非 AlloyDB 供應商的參數

1. 將 wal_level 參數設為 logical，並在 postgresql.conf 檔案中將 pglogical 附加至 shared_preload_libraries 參數。pglogical 擴充功能需要在非 AlloyDB 提供者叢集上進行最少量的參數調整。

   cp postgresql.conf postgresql.bak
   sed -r -i "s|(\#)?wal_level\s*=.*|wal_level=logical|" postgresql.conf
   sed -r -i "s|(\#)?(shared_preload_libraries\s*=\s*)'(.*)'.*$|\2'\3,pglogical'|" postgresql.conf
   sed -r -i "s|',|'|" postgresql.conf

2. 確認參數設定正確無誤：

   grep -iE 'wal_level|shared_preload_libraries' postgresql.conf

3. 重新啟動非 AlloyDB 叢集，參數變更才會生效。

   其他參數可能已設為足夠的值，或可能需要根據非 AlloyDB 發布和版本進行調整。

   請檢查下列參數：

   • max_worker_processes：每個供應商資料庫各一個，且每個訂閱者節點至少一個。這個參數的標準值為至少 10。
   • max_replication_slots：提供者節點上每個節點各一個。
   • max_wal_senders：提供者節點上每個節點各一個。
   • track_commit_timestamp：如果需要解決最後或第一次更新的衝突，請設為 on。
   • listen_addresses：必須包含 AlloyDB Omni IP 位址，或透過涵蓋的 CIDR 區塊提及。

4. (選用) 如果非 AlloyDB 供應商是 Amazon RDS 或 Aurora，則必須啟用 pglogical 擴充功能，並透過 cluster parameter group 調整所需參數。

   1. 在現有或新的叢集參數群組中，設定下列參數：

      • rds.logical_replication 至 1
      • max_replication_slots 至 50
      • max_wal_senders 至 50
      • max_worker_processes 至 64
      • shared_preload_libraries 至 pg_stat_statements, pglogical

   2. 重新啟動 Amazon RDS 或 Aurora 叢集，叢集參數群組調整項目才會生效。

5. 確認所有參數值都相關：

   SELECT name, setting
   FROM pg_catalog.pg_settings
    WHERE name IN ('listen_addresses',
                   'wal_level',
                   'shared_preload_libraries',
                   'max_worker_processes',
                   'max_replication_slots',
                   'max_wal_senders',
                   'track_commit_timestamp')
    ORDER BY name;
   

對非 AlloyDB Omni 提供者叢集進行主機式驗證調整

pglogical 會與供應商資料庫建立本機 TCP 連線。因此，您必須將主機伺服器的 IP 位址新增至 AlloyDB Omni DATA_DIR/pg_hba.conf 檔案，其中 DATA_DIR 是資料目錄的檔案系統路徑，例如 /home/$USER/alloydb-data。

1. 在 DATA_DIR/pg_hba.conf 檔案中，為新 pglogical_replication 使用者新增本機伺服器的信任驗證項目。

   重要事項： 如要使用密碼驗證，請移除 trust 值，並改用 scram-sha-256。

   此外，訂閱者節點必須能夠向供應商節點進行驗證。將每個訂閱者節點的 IP 位址或適當的 CIDR 區塊 IP 範圍新增至 DATA_DIR/pg_hba.conf 檔案：

   echo -e "# pglogical entries:
   host all pglogical_replication samehost trust
   host all pglogical_replication SERVER_IP_ADDRESS/32 trust
   " | column -t | sudo tee -a DATA_DIR/pg_hba.conf

   將 SERVER_IP_ADDRESS 替換為要從中複製資料的 AlloyDB Omni 主要執行個體 IP 位址。

   注意： 請指定並限制連線至確切的資料庫名稱，而非 all。 此外，您也可以指定訂閱者節點 IP 位址 (例如 /32，而非 IP 範圍)，藉此限制 CIRD 區塊 IP 範圍。

2. 確認輸入內容正確無誤：

   tail -3 DATA_DIR/pg_hba.conf

3. 重新啟動非 AlloyDB 叢集，參數變更才會生效。

注意： Amazon RDS 或 Aurora 等雲端代管服務 PostgreSQL 資料庫，通常不允許手動調整或自訂 DATA_DIR/pg_hba.conf 檔案。在某些情況下，可能不需要調整，或需要調整防火牆和路由。

調整 AlloyDB Omni 訂閱端叢集的參數

pglogical 也需要在 AlloyDB Omni 訂閱端叢集上進行最少的參數調整。您必須在 DATA_DIR/postgresql.conf 檔案中，將 pglogical 附加至 shared_preload_libraries 參數。如果叢集中的任何資料庫充當供應商資料庫，請對供應商資料庫進行必要的參數變更。

1. 調整參數：

   sudo sed -r -i "s|(shared_preload_libraries\s*=\s*)'(.*)'.*$|\1'\2,pglogical'|" DATA_DIR/postgresql.conf

2. 確認參數設定正確無誤：

   grep -iE 'shared_preload_libraries' DATA_DIR/postgresql.conf

3. 重新啟動 AlloyDB Omni，讓參數變更生效：

   Docker

    docker container restart CONTAINER_NAME

   將 CONTAINER_NAME 換成您啟動 AlloyDB Omni 容器時指派的名稱。

   Podman

    podman container restart CONTAINER_NAME

   將 CONTAINER_NAME 換成您啟動 AlloyDB Omni 容器時指派的名稱。

4. 為其他供應商資料庫參數設定 AlloyDB Omni 預設值：

   • max_worker_processes：每個供應商資料庫各一個，每個訂閱者節點各一個。
   • track_commit_timestamp：如果需要解決最後/首次更新勝出的衝突，請設為 on。

5. 確認所有參數值都相關：

   Docker

   docker exec CONTAINER_NAME psql -h localhost -U postgres -c "
   SELECT name, setting
     FROM pg_catalog.pg_settings
    WHERE name IN ('listen_addresses',
                   'wal_level',
                   'shared_preload_libraries',
                   'max_worker_processes',
                   'max_replication_slots',
                   'max_wal_senders',
                   'track_commit_timestamp')
        ORDER BY name;
   "

   Podman

   podman exec CONTAINER_NAME psql -h localhost -U postgres -c "
   SELECT name, setting
     FROM pg_catalog.pg_settings
    WHERE name IN ('listen_addresses',
                   'wal_level',
                   'shared_preload_libraries',
                   'max_worker_processes',
                   'max_replication_slots',
                   'max_wal_senders',
                   'track_commit_timestamp')
        ORDER BY name;
   "

調整 AlloyDB Omni 訂閱者叢集的主機型驗證

pglogical 會建立與 AlloyDB Omni 訂閱端資料庫的本機 TCP 連線。因此，您必須將訂閱者的主機伺服器 IP 位址新增至 AlloyDB Omni DATA_DIR/pg_hba.conf 檔案。

1. 在 DATA_DIR/pg_hba.conf 檔案中，為新 pglogical_replication 使用者新增本機伺服器的信任驗證項目：

   echo -e "# pglogical entries:
   host all pglogical_replication samehost trust
   " | column -t | sudo tee -a DATA_DIR/pg_hba.conf

   注意： 請指定並限制連線至確切的資料庫名稱，而非 all。

2. 確認輸入的內容正確無誤：

   tail -2 DATA_DIR/pg_hba.conf

3. 重新啟動 AlloyDB Omni，讓驗證變更生效：

   docker container restart CONTAINER_NAME

在供應商和訂閱者叢集中建立 pglogical 使用者

您必須在供應商和訂閱者叢集中建立新使用者。 pglogical 必須同時具備 superuser 和 replication 屬性。

1. 在 PostgreSQL 適用的 AlloyDB 供應商叢集中，建立使用者角色：

   CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
   ALTER USER pglogical_replication WITH replication;
   ALTER USER pglogical_replication WITH superuser;
   

2. (選用) 如果非 AlloyDB 供應商是 Amazon RDS 或 Aurora，則必須授予下列角色：

   GRANT rds_superuser TO replication_user;
   

   注意： 如果您的非 AlloyDB 資料庫來自其他雲端管理服務，可能需要服務專屬的自訂授權。

將 pglogical 和節點新增至非 AlloyDB 供應商資料庫

1. 授予必要權限。

   您必須在每個資料庫中安裝 pglogical 擴充功能，並將 usage 權限授予 pglogical 資料庫使用者。

   舉例來說，如果資料庫是 my_test_db，請執行下列指令：

   CREATE EXTENSION IF NOT EXISTS pglogical;
   GRANT usage ON SCHEMA pglogical TO pglogical_replication;
   

2. 為供應商資料庫建立 pglogical 節點。 node_name 是任意值，而 dsn 字串必須是連回相同資料庫的有效 TCP 連線。

   重要事項： 如果您使用雲端代管服務資料庫，則必須進行密碼驗證。您可以在自行管理的 PostgreSQL 部署作業中選擇使用密碼驗證。在這種情況下，請在 dsn` 字串中加入密碼。如果您使用自行管理的安裝程序，則可以選擇使用密碼或信任驗證。

   舉例來說，如要使用 my_test_db 資料庫，請執行下列指令：

   SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
   

   注意： 如果非 AlloyDB 供應商是 Amazon RDS 或 Aurora，請使用提供的 AWS 端點，而非 dsn 參數中的主機 IP 位址。如果非 AlloyDB 資料庫來自其他雲端管理服務，則必須使用服務專屬的端點或 IP 位址。

建立資料表並新增至預設複寫集

建立資料表，並將其新增至非 AlloyDB 提供者資料庫的預設複寫集。

1. 在供應商資料庫中建立名為 test_table_1 的測試資料表：

   CREATE TABLE test_table_1 (col1 INT PRIMARY KEY);
   INSERT INTO test_table_1 VALUES (1),(2),(3);
   

2. 手動將測試資料表新增至預設複寫集。 您可以建立自訂的 pglogical 複寫集，也可以使用預設的複寫集。建立擴充功能時，系統會建立多個預設複寫集，例如 default、default_insert_only 和 ddl_sql。您可以個別將表格和序列新增至複寫集，也可以一次將指定結構定義的所有表格和序列新增至複寫集。

   -- Add the specified table to the default replication set:
   SELECT pglogical.replication_set_add_table(set_name := 'default', relation := 'test_table_1', synchronize_data := TRUE);
   
   -- Check which tables have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_table;
   

3. (選用) 新增指定結構定義中的所有資料表，例如 public：

   -- Add all "public" schema tables to the default replication set:
   SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
   
   -- Check which tables have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_table;
   
   -- Add all "public" schema sequences to the default replication:
   SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);
   
   -- Check which sequences have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_seq;
   

4. 從 default 複寫集移除資料表。 如果結構定義中有任何資料表沒有主鍵或副本身分，則只能複製 INSERT 陳述式。如果您已透過 replication_set_add_all_tables 函式自動將這些資料表新增至 default 複寫集，則必須手動從該複寫集中移除這些資料表，並將其新增至 default_insert_only 集。

   -- Remove the table from the **default** replication set:
   SELECT pglogical.replication_set_remove_table(set_name := 'default', relation := 'test_table_2');
   
   -- Manually add to the **default_insert_only** replication set:
   SELECT pglogical.replication_set_add_table(set_name := 'default_insert_only', relation := 'test_table_2');
   

   或者，如要將新建立的資料表自動新增至複寫集，請按照pglogical來源中的建議新增 pglogical_assign_repset 觸發條件。

將資料庫複製到 AlloyDB Omni 訂閱者叢集

1. 使用 pg_dump 公用程式，建立來源資料庫的僅限結構定義備份。

2. 使用非 AlloyDB 伺服器的 IP 位址或端點，從 AlloyDB Omni 訂閱者伺服器執行 pg_dump 指令。

   注意： 您必須從 AlloyDB Omni 容器外部執行 pg_dump 指令。建議您從 AlloyDB Omni 主機伺服器執行 pg_dump 指令 (如果可以安裝 PostgreSQL 用戶端)。如有需要，您也可以從其他伺服器執行指令。

   pg_dump -h SERVER_IP_ADDRESS -U postgres --create --schema-only my_test_db > my_test_db.schema-only.sql

   注意： 如果非 AlloyDB 供應商是 Amazon RDS 或 Aurora，請使用提供的 AWS 端點，而非 dsn 參數中的主機 IP 位址。如果非 AlloyDB 資料庫來自其他雲端代管服務，則必須提供服務專屬的端點或 IP 位址。

3. 將備份檔匯入訂閱端 AlloyDB Omni 伺服器的訂閱端資料庫：

   Docker

   docker exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

   Podman

   podman exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

這項操作會建立資料庫和結構定義，但不包含任何資料列資料。pglogical 擴充功能會複製資料列。手動複製或重新建立任何其他必要的使用者或角色。

在 AlloyDB Omni 訂閱者資料庫上建立節點和訂閱項目

1. 在 AlloyDB Omni 訂閱端資料庫上建立節點。如果選擇使用密碼驗證，請將密碼新增至 dsn。

   Docker

   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');
   "

   Podman

   podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');
   "

2. 在訂閱者資料庫中建立訂閱項目，指向 AlloyDB Omni 供應商伺服器中的供應商資料庫。

   注意： 信任驗證不適用於雲端管理服務 PostgreSQL 資料庫。您必須在 dsn 參數中加入密碼引數。
   注意： 請確保 dsn 參數中的連線詳細資料正確無誤。

   Docker

   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
   "

   Podman

   podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
   "

   注意： 如果非 AlloyDB 供應商是 Amazon RDS 或 Aurora，請使用提供的 AWS 端點，而非 dsn 參數中的主機 IP 位址。如果非 AlloyDB 資料庫來自其他雲端代管服務，則必須提供服務專屬的端點或 IP 位址。

3. 幾秒或幾分鐘後，初始資料應該會從供應商複製到訂閱者：

   Docker

   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT * FROM test_table_1 ORDER BY 1;
   "

   Podman

   podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT * FROM test_table_1 ORDER BY 1;
   "

   新增至供應商資料庫的額外資料列，也會在幾秒內即時複製。

其他 pglogical 部署注意事項

pglogical 擴充功能提供許多進階功能，本文未涵蓋這些功能。其中許多功能都適用於您的實作項目。您可以考慮使用下列進階功能：

• 解決衝突
• 多主機和雙向複製
• 納入序列
• 切換和容錯移轉程序

後續步驟

• 在 PostgreSQL 適用的 AlloyDB 與 AlloyDB Omni 之間複製資料
• 使用 pglogical 複寫進行切換和容錯移轉