我該如何使用邏輯複寫，在 Amazon RDS for PostgreSQL 資料庫執行個體之間複製資料表？

解決方法

Amazon RDS for PostgreSQL 支援與 PostgreSQL 10.4 及更新版本進行邏輯複寫。Amazon Aurora PostgreSQL 相容版本 2.2.0 及更新版本支援與 PostgreSQL 10.6 及更新版本進行邏輯複寫。

如需詳細資訊，請參閱執行 Amazon RDSD for PostgreSQL 的邏輯複寫。

以下解決方案會將兩個來源資料表複寫到兩個目標資料表。

啟用邏輯複寫

請完成下列步驟：

1. 建立自訂參數群組，並將 rds.logical_replication 參數設為 1。
   **注意：**由於 rds.logical_replication 參數是靜態參數，您必須重新啟動資料庫執行個體。重新啟動資料庫執行個體後，wal_level 參數將會變更為 logical。
2. 將參數群組與您的資料庫執行個體建立關聯。
3. 執行下列查詢，以確認 wal_level 是否為 logical，以及 rds.logical_replication 是否已開啟：

   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)

建立來源資料表並插入資料

連線到 PostgreSQL 資料庫執行個體，然後在來源資料庫中完成以下步驟：

1. 執行以下命令來建立來源資料表：

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

2. 執行以下命令將資料新增至來源資料表：

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

為來源資料表建立發佈項目

在來源資料庫中，完成以下步驟：

1. 執行以下命令，建立這兩個資料表的發佈項目：

   CREATE PUBLICATION testpub FOR TABLE reptab1, reptab2;

2. 執行以下查詢來確認發佈項目的詳細資訊正確無誤：

   SELECT * FROM pg_publication;

   預期輸出：

     oid   | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot  
   --------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------  
    115069 | testpub |    16395 | f            | t         | t         | t         | t           | f  
   (1 row)

3. 執行以下查詢來確認來源資料表位於發佈項目中：

   SELECT * FROM pg_publication;

   預期輸出：

    pubname | schemaname | tablename  
   
   ---------+------------+-----------  
   
    testpub | public | reptab1  
   
    testpub | public | reptab2  
   
   (2 rows)

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

在目標資料庫中，完成以下步驟：

1. 執行以下命令，建立目標資料表：

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

   **注意：**使用與來源資料表相同的名稱。

2. 執行以下查詢來確認目標資料表中沒有資料。
   表一：

   SELECT count(*) FROM reptab1;

   預期輸出：

    count  
   -------  
        0  
   (1 row)

   表二：

   SELECT count(*) FROM reptab2;

   預期輸出：

    count  
   -------  
        0  
   (1 row)

在目標資料庫中建立一個訂閱

在建立訂閱之前，請執行以下命令，確認您確認您未將使用者名稱和密碼的純文字版本儲存在資料庫日誌中：

SET log_min_messages to 'PANIC';  
SET log_statement to NONE;

若要在目標資料庫中建立訂閱，請完成以下步驟：

1. 執行以下命令來建立訂閱：

   CREATE SUBSCRIPTION testsub CONNECTION 'host=source RDS/host endpoint port=5432 dbname=source_db_name user=user password=password' PUBLICATION testpub;

   **注意：**將 source RDS/host endpoint 替換為您的來源資料庫執行個體的端點，並將 source_db_name 替換為您資料庫執行個體的名稱。將 user 替換為您的使用者名稱，將 password 替換為您的密碼。

2. 執行以下查詢來確認訂閱有效：

   SELECT oid,subname,subenabled,subslotname,subpublications FROM pg_subscription;

   預期輸出：

     oid  | subname | subenabled | subslotname | subpublications  
   -------+---------+------------+-------------+-----------------  
    16434 | testsub | t          | testsub     | {testpub}  
   (1 row)

3. 執行以下查詢來確認資料位於目標資料表中。
   表一：

   SELECT count(*) FROM reptab1;

   預期輸出：

    count  
   -------  
     1000  
   (1 row)

   表二：

   SELECT count(*) FROM reptab2;

   預期輸出：

    count  
   -------  
       50  
   (1 row)

確認來源資料庫中的複寫插槽詳細資訊

當您在目標資料庫中建立訂閱時，目標資料庫會在來源資料庫中建立複寫插槽。

若要確認複寫插槽的詳細資訊，請在來源資料庫上執行以下查詢：

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)

測試來源資料表的複寫

在來源資料庫中，完成以下步驟：

1. 執行以下命令將資料列新增至來源資料表：

   INSERT INTO reptab1 VALUES(generate_series(1001,2000));  
   INSERT INTO reptab2 SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);

2. 執行以下查詢來確認來源資料表中的資料是否發生了變化。
   表一：

   SELECT count(*) FROM reptab1;

   預期輸出：

    count  
   -------  
      2000  
   (1 row)

   表二：

   SELECT count(*) FROM reptab2;

   預期輸出：

    count  
   -------  
      100  
   (1 row)

確認來源資料表上的資料是否已複寫到目標資料表

在目標資料庫中，執行以下查詢以確認來源資料表中的資料已複寫到目標資料表。

表一：

SELECT count(*) FROM reptab1;

預期輸出：

count  
-------  
   2000  
(1 row)

表二：

SELECT count(*) FROM reptab2;

預期輸出：

 count  
-------  
   100  
(1 row)

清除複寫插槽並關閉邏輯複寫功能

完成複寫後，如果不再需要，請清除插槽並關閉邏輯複寫功能。非作用中的複寫插槽會導致來源資料庫執行個體上累積大量的預先寫入日誌 (WAL) 檔案。WAL 檔案可能會佔滿儲存空間，進而導致停機。

請完成下列步驟：

1. 在目標資料庫上，執行以下命令來移除訂閱：

   DROP SUBSCRIPTION testsub;

2. 在目標資料庫上，執行以下查詢以確認訂閱是否已移除：

   SELECT * FROM pg_subscription;

   預期輸出：

   oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications  
   ----+---------+---------+----------+------------+-------------+-------------+---------------+-----------------  
   (0 rows)

   **注意：**當您移除目標資料庫上的訂閱時，目標資料庫也會移除來源資料庫中的複寫插槽。

3. 在來源資料庫上，執行下列查詢以確認複寫插槽是否已從來源資料庫中移除：

   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)

4. 在來源資料庫上，執行以下命令來移除發佈項目：

   DROP PUBLICATION testpub;

5. 在來源資料庫上，執行以下查詢以確認發佈項目是否已移除：

   SELECT * FROM pg_publication;  
   SELECT * FROM pg_publication_tables;

   預期輸出：

    oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot  
   -----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------  
   (0 rows)  
   pubname | schemaname | tablename  
   ---------+------------+-----------  
   (0 rows)

6. 在資料庫執行個體的自訂參數群組中，將 rds.logical_replication 參數設為 0。 
   **注意：**重新啟動資料庫執行個體以使變更生效。

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

8. 執行以下查詢，檢查是否存在非作用中的複寫插槽及插槽大小：

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

9. (選用) 執行以下命令來移除複寫插槽：

   SELECT pg_drop_replication_slot('Your_slotname_name')

相關資訊

PostgreSQL 網站上的複寫

PostgreSQL 網站上的邏輯複寫