How do I use logical replication to replicate tables between my Amazon RDS for PostgreSQL DB instances?

Resolution

Amazon RDS for PostgreSQL supports logical replication with PostgreSQL 10.4 and later. Amazon Aurora PostgreSQL-Compatible Edition version 2.2.0 and later supports logical replication with PostgreSQL 10.6 and later.

For more information, see Performing logical replication for Amazon RDSD for PostgreSQL. 

The following resolution replicates two source tables to two target tables.

Turn on logical replication

Complete the following steps:

1. Create a custom parameter group, and set rds.logical_replication parameter to 1.
   Note: Because the rds.logical_replication parameter is a static parameter, you must reboot the DB instance. After you reboot the DB instance, the wal_level parameter changes to logical.
2. Associate the parameter group with your DB instance.
3. Run the following query to verify that wal_level is logical and rds.logical_replication is on:

   SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');

   Expected output:

             name           | setting
   -------------------------+---------
    rds.logical_replication | on
    wal_level               | logical
   (2 rows)

Create the source tables and insert data

Connect to the PostgreSQL DB instance, and then complete the following steps in the source database:

1. Run the following commands to create the source tables:

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

2. Run the following commands to add data into the source tables:

   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);

Create a publication for the source tables

In the source database, complete the following steps:

1. Run the following command to create a publication of the two tables:

   CREATE PUBLICATION testpub FOR TABLE reptab1, reptab2;

2. Run the following query to verify that the details of the publication are correct:

   SELECT * FROM pg_publication;

   Expected output:

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

3. Run the following query to verify that the source tables are in the publication:

   SELECT * FROM pg_publication;

   Expected output:

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

Connect to the target database and create the target tables

In the target database, complete the following steps:

1. Run the following commands to create the target tables:

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

   Note: Use the same names as the source tables.

2. Run the following queries to verify that data isn't in the target tables.
   Table one:

   SELECT count(*) FROM reptab1;

   Expected output:

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

   Table two:

   SELECT count(*) FROM reptab2;

   Expected output:

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

Create a subscription in the target database

Before you create the subscription, run the following commands to confirm that you didn't store a plaintext version of your username and password in the database logs:

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

To create the subscription in the target database, complete the following steps:

1. Run the following command to create the subscription:

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

   Note: Replace source RDS/host endpoint with the endpoint of your source DB instance and source_db_name with the name of your DB instance. Replace user with your username and password with your password.

2. Run the following query to verify that the subscription is active:

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

   Expected output:

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

3. Run the following queries to verify that the data is in the target tables.
   Table one:

   SELECT count(*) FROM reptab1;

   Expected output:

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

   Table two:

   SELECT count(*) FROM reptab2;

   Expected output:

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

Verify the replication slot details in the source database

When you create a subscription in the target database, the target database creates a replication slot in the source database.

To verify the details of the replication slot, run the following query on the source database:

SELECT * FROM pg_replication_slots;

Expected output:

 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)

Test the replication from the source tables

In the source database, complete the following steps:

1. Run the following commands to add rows into the source tables:

   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. Run the following queries to verify that the data changed in the source tables.
   Table one:

   SELECT count(*) FROM reptab1;

   Expected output:

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

   Table two:

   SELECT count(*) FROM reptab2;

   Expected output:

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

Verify that the data on the source tables replicated to the target tables

In the target database, run the following queries to confirm that the data from the source tables replicated to the target tables.

Table one:

SELECT count(*) FROM reptab1;

Expected output:

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

Table two:

SELECT count(*) FROM reptab2;

Expected output:

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

Clear the replication slots and turn off logical replication

After you completed the replication and you no longer need it, clear the slots and turn off logical replication. Inactive replication slots cause Write-Ahead Logging (WAL) files to accumulate on the source DB instance. WAL files might fill storage and cause downtime.

Complete the following steps:

1. On the target database, run the following command to remove the subscription:

   DROP SUBSCRIPTION testsub;

2. On the target database, run the following query to verify that the subscription is removed:

   SELECT * FROM pg_subscription;

   Expected output:

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

   Note: When you remove the subscription on the target database, the target database also removes the replication slots in the source database.

3. On the source database, run the following query to verify that the replication slots are removed from the source database:

   SELECT * FROM pg_replication_slots;

   Expected output:

   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. On the source database, run the following command to remove the publication:

   DROP PUBLICATION testpub;

5. On the source database, run the following queries to verify that the publication is removed:

   SELECT * FROM pg_publication;  
   SELECT * FROM pg_publication_tables;

   Expected output:

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

6. In the custom parameter group of your DB instance, set the rds.logical_replication parameter to 0. 
   Note: Reboot the DB instance for the changes to apply.

7. Review the max_replication_slots, max_wal_senders, max_logical_replication_workers, max_worker_processes, and max_sync_workers_per_subscription parameters based on your usage.

8. Run the following query to check whether there are inactive replication slots and the size of the slots:

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

9. (Optional) Run the following command to remove the replication slots:

   SELECT pg_drop_replication_slot('Your_slotname_name')

Related information

Replication on the PostgreSQL website

Logical replication on the PostgreSQL website