如何使用邏輯複寫對 RDS for PostgreSQL 中的資料表執行複寫？

4 分的閱讀內容

我想在 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 中的資料庫之間複寫資料表，而無需使用任何擴展。

解決方案

邏輯複寫的典型使用案例是在兩個 Amazon RDS for PostgreSQL 資料庫執行個體之間複寫一組資料表。RDS for PostgreSQL 支援使用 PostgreSQL 10.4 及更新版本進行邏輯複寫。Amazon Aurora PostgreSQL 相容版本 2.2.0 及更新版本支援使用 PostgreSQL 10.6 及更新版本進行邏輯複寫。

在提供的解決方案中，會使用 RDS for PostgreSQL 中的邏輯複寫，將兩個來源資料表複寫到兩個目標資料表。邏輯複寫會先執行來源資料表中已存在的資料的初始載入，然後繼續複寫進行中的變更。

開啟邏輯複寫

若要在 RDS for PostgreSQL 中開啟邏輯複寫，請修改自訂參數群組以將 rds.logical_replication 設定為 1，然後將 rds.logical_replication 連接至資料庫執行個體。如果自訂參數群組已連接至資料庫執行個體，請更新參數群組以將 rds.logical_replication 設定為 1。rds.logical_replication 參數是靜態參數，需要重新啟動資料庫執行個體才能生效。資料庫執行個體重新啟動時，wal_level 參數會設定為 logical。

驗證 wal_level 和 rds.logical_replication 的值：

postgres=> SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');
          name           | setting
-------------------------+---------
 rds.logical_replication | on
 wal_level               | logical
(2 rows)

連線到來源資料庫執行個體中的來源資料庫

連線至來源 RDS for PostgreSQL 資料庫執行個體中的來源資料庫。建立來源資料表：

source=> CREATE TABLE reptab1 (slno int primary key);
CREATE TABLE
source=> CREATE TABLE reptab2 (name varchar(20));
CREATE TABLE

將資料插入來源資料表：

source=> INSERT INTO reptab1 VALUES (generate_series(1,1000));
INSERT 0 1000
source=> INSERT INTO reptab2 SELECT SUBSTR ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);
INSERT 0 50

建立來源資料表的發佈

建立來源資料表的發佈。使用 SELECT 查詢驗證已建立發佈的詳細資訊：

source=> CREATE PUBLICATION testpub FOR TABLE reptab1,reptab2;
CREATE PUBLICATION
source=> SELECT * FROM pg_publication;
  oid   | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
--------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 115069 | testpub |    16395 | f            | t         | t         | t         | t           | f
(1 row)

確認來源資料表已新增至發佈：

source=> SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
 testpub | public     | reptab1
 testpub | public     | reptab2
(2 rows)

附註：若要複寫資料庫中的所有資料表，請執行下列命令：

CREATE PUBLICATION testpub FOR ALL TABLES;

連線至目標資料庫並建立目標資料表

連線至目標資料庫執行個體中的目標資料庫。建立具有與來源資料表相同名稱的目標資料表。透過在目標資料表上執行 SELECT 查詢，確保目標資料表中沒有資料存在：

target=> CREATE TABLE reptab1 (slno int primary key);
CREATE TABLE
target=> CREATE TABLE reptab2 (name varchar(20));
CREATE TABLE
target=> SELECT count(*) FROM reptab1;
 count
-------
     0
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
     0
(1 row)

在目標資料庫中建立和驗證訂閱

在目標資料庫中建立訂閱。使用 SELECT 查詢來確認訂閱是否已啟用：

target=> CREATE SUBSCRIPTION testsub CONNECTION 'host=<source RDS/host endpoint> port=5432 dbname=<source_db_name> user=<user> password=<password>' PUBLICATION testpub;
NOTICE:  Created replication slot "testsub" on publisher
CREATE SUBSCRIPTION
target=> SELECT oid,subname,subenabled,subslotname,subpublications FROM pg_subscription;
  oid  | subname | subenabled | subslotname | subpublications
-------+---------+------------+-------------+-----------------
 16434 | testsub | t          | testsub     | {testpub}
(1 row)

重要事項：若要避免在資料庫記錄中儲存使用者名稱和密碼的純文字版本，請在建立訂閱之前執行下列命令：

target=> SET log_min_messages to 'PANIC';
SET
target=> SET log_statement to NONE;
SET

建立訂閱時，訂閱會將來源資料表中存在的所有資料載入至目標資料表。在目標資料表上執行 SELECT 查詢，以確認初始資料載入：

target=> SELECT count(*) FROM reptab1;
 count
-------
  1000
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
    50
(1 row)

確認來源資料庫中的複寫時段

在目標資料庫中建立訂閱會在來源資料庫中建立複寫時段。在來源資料庫上執行下列 SELECT 查詢，以驗證複寫時段詳細資訊：

source=> SELECT * FROM pg_replication_slots;
 slot_name |  plugin  | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size
 ----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------
 testsub   | pgoutput | logical   | 115048 | source   | f         | t      |        846 |      |         6945 | 58/B4000568 | 58/B40005A0         | reserved   |
(1 row)

從來源資料表測試複寫

將資料列插入來源資料表，以測試是否將來源資料表中的資料變更複寫到目標資料表：

source=> INSERT INTO reptab1 VALUES(generate_series(1001,2000));
INSERT 0 1000
source=> INSERT INTO reptab2 SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);
INSERT 0 50
source=> SELECT count(*) FROM reptab1;
 count
-------
  2000
(1 row)
source=> SELECT count(*) FROM reptab2; count
-------
   100
(1 row)

透過驗證目標資料表中的資料列數來測試複寫

驗證目標資料表中的資料列數，以確認新的插入會複寫到目標資料表：

target=> SELECT count(*) FROM reptab1;
 count
-------
  2000
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
   100
(1 row)

清理並關閉邏輯複寫

當複寫完成且不再需要時，請清理並關閉邏輯複寫。非作用中的複寫時段會導致來源資料庫執行個體上的 WAL 檔案累積。WAL 檔案可能會填滿儲存空間並導致中斷。

卸除目標資料庫上的訂閱：

target=> DROP SUBSCRIPTION testsub;
NOTICE:  Dropped replication slot "testsub" on publisher
DROP SUBSCRIPTION
target=> SELECT * FROM pg_subscription;
oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
----+---------+---------+----------+------------+-------------+-------------+---------------+-----------------
(0 rows)

附註：卸除訂閱也會刪除所建立訂閱的複寫位置。

在來源資料庫上執行下列 SELECT 查詢陳述式，確認已從來源資料庫卸除複寫時段：

source=> SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size
----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+--------------
(0 rows)

卸除發佈。確認已成功卸除發佈：

source=> DROP PUBLICATION testpub;
DROP PUBLICATION
source=> SELECT * FROM pg_publication;
 oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
(0 rows)
source=> SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
(0 rows)

在連線至資料庫執行個體的自訂參數群組中，將 rds.logical_replication 修改為 0。如果資料庫執行個體未使用邏輯複寫，請視需要重新啟動資料庫執行個體。

根據您的使用情況，檢閱 max_replication_slots、max_wal_senders、max_logical_replication_workers、max_worker_processes 和 max_sync_workers_per_subscription。

附註：下列命令會檢查是否有任何非作用中複寫時段、判斷各個時段的大小，然後視需要卸除時段。

SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots ;

SELECT pg_drop_replication_slot('Your_slotname_name');