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 support logical replication with PostgreSQL 10.6 and later.

For more information, see Performing logical replication for Amazon RDS 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 the 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 turned 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

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. To verify that the details of the publication are correct, run the following query :

   SELECT * FROM pg_publication;

   Expected output:

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

3. To verify that the source tables are in the publication, run the following query:

   SELECT * FROM pg_publication_tables;

   Expected output:

    pubname | schemaname | tablename
   
   ---------+------------+-----------
   
    testpub | public | reptab1
   
    testpub | public | reptab2

Connect to the target database and create the target tables

In the target database, complete the following steps:

1. To create the target tables, run the following commands:

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

   Note: Use the same names as the source tables.
2. To verify that data isn't in the target tables, run the following queries:
   Table one:

   SELECT count(*) FROM reptab1;

   Expected output:

    count
   -------
        0

   Table two:

   SELECT count(*) FROM reptab2;

   Expected output:

    count
   -------
        0

Create a subscription in the target database

Before you create a 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. To create the subscription, run the following command:

   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. Replace source_db_name with the name of your DB instance. Replace user with your username and password with your password.

2. To verify that the subscription is active, run the following query:

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

   Expected output:

     oid  | subname | subenabled | subslotname | subpublications
   -------+---------+------------+-------------+-----------------
    16434 | testsub | t          | testsub     | {testpub}

3. To verify that the data is in the target tables, run the following queries.
   Table one:

   SELECT count(*) FROM reptab1;

   Expected output:

    count
   -------
     1000

   Table two:

   SELECT count(*) FROM reptab2;

   Expected output:

    count
   -------
       50

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   |

Test the replication from the source tables

In the source database, complete the following steps:

1. To add rows into the source tables, run the following commands:

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

   SELECT count(*) FROM reptab1;

   Expected output:

    count
   -------
      2000

   Table two:

   SELECT count(*) FROM reptab2;

   Expected output:

    count
   -------
      100

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

Table two:

SELECT count(*) FROM reptab2;

Expected output:

 count
-------
   100

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
   ----+---------+---------+----------+------------+-------------+-------------+---------------+-----------------

   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
   ----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+--------------

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
   -----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
   
   pubname | schemaname | tablename
   ---------+------------+-----------

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

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. To check whether there are inactive replication slots and the size of the slots, run the following command:

   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) To remove the replication slots, run the following command:

   SELECT pg_drop_replication_slot('Your_slotname_name')

Related information

Replication on the PostgreSQL website

Logical replication on the PostgreSQL website