Using PostgreSQL logical replication with Multi-AZ DB clusters - Amazon Relational Database Service
PrerequisitesSetting up logical replication
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Using PostgreSQL logical replication with Multi-AZ DB clusters

By using PostgreSQL logical replication with your Multi-AZ DB cluster, you can replicate and synchronize individual tables rather than the entire database instance. Logical replication uses a publish and subscribe model to replicate changes from a source to one or more recipients. It works by using change records from the PostgreSQL write-ahead log (WAL). For more information, see Performing logical replication for Amazon RDS for PostgreSQL.

When you create a new logical replication slot on the writer DB instance of a Multi-AZ DB cluster, the slot is asynchronously copied to each reader DB instance in the cluster. The slots on the reader DB instances are continuously synchronized with those on the writer DB instance.

Logical replication is supported for Multi-AZ DB clusters running RDS for PostgreSQL version 14.8-R2 and higher, and 15.3-R2 and higher.

Note

In addition to the native PostgreSQL logical replication feature, Multi-AZ DB clusters running RDS for PostgreSQL also support the pglogical extension.

For more information about PostgreSQL logical replication, see Logical replication in the PostgreSQL documentation.

Prerequisites

To configure PostgreSQL logical replication for Multi-AZ DB clusters, you must meet the following prerequisites.

Setting up logical replication

To set up logical replication for a Multi-AZ DB cluster, you enable specific parameters within the associated DB cluster parameter group, then create logical replication slots.

To set up logical replication for an RDS for PostgreSQL Multi-AZ DB cluster

  1. Open the custom DB cluster parameter group associated with your RDS for PostgreSQL Multi-AZ DB cluster.

  2. In the Parameters search field, locate the rds.logical_replication static parameter and set its value to 1. This parameter change can increase WAL generation, so enable it only when you’re using logical slots.

  3. As part of this change, configure the following DB cluster parameters.

    • max_wal_senders

    • max_replication_slots

    • max_connections

    Depending on your expected usage, you might also need to change the values of the following parameters. However, in many cases, the default values are sufficient.

    • max_logical_replication_workers

    • max_sync_workers_per_subscription

  4. Reboot the Multi-AZ DB cluster for the parameter values to take effect. For instructions, see Rebooting a Multi-AZ DB cluster and reader DB instances.

  5. Create a logical replication slot on the writer DB instance of the Multi-AZ DB cluster as explained in Working with logical replication slots. This process requires that you specify a decoding plugin. Currently, RDS for PostgreSQL supports the test_decoding, wal2json, and pgoutput plugins that ship with PostgreSQL.

    The slot is asynchronously copied to each reader DB instance in the cluster.

  6. Verify the state of the slot on all reader DB instances of the Multi-AZ DB cluster. To do so, inspect the pg_replication_slots view on all reader DB instances and make sure that the confirmed_flush_lsn state is making progress while the application is actively consuming logical changes.

    The following commands demonstrate how to inspect the replication state on the reader DB instances.

    % psql -h test-postgres-instance-2.abcdefabcdef.us-west-2.rds.amazonaws.com

postgres=> select slot_name, slot_type, confirmed_flush_lsn from pg_replication_slots;
  slot_name   | slot_type | confirmed_flush_lsn
--------------+-----------+---------------------
 logical_slot | logical   | 32/D0001700
(1 row)

postgres=> select slot_name, slot_type, confirmed_flush_lsn from pg_replication_slots;
  slot_name   | slot_type | confirmed_flush_lsn
--------------+-----------+---------------------
 logical_slot | logical   | 32/D8003628
(1 row)

% psql -h test-postgres-instance-3.abcdefabcdef.us-west-2.rds.amazonaws.com

postgres=> select slot_name, slot_type, confirmed_flush_lsn from pg_replication_slots;
  slot_name   | slot_type | confirmed_flush_lsn
--------------+-----------+---------------------
 logical_slot | logical   | 32/D0001700
(1 row)

postgres=> select slot_name, slot_type, confirmed_flush_lsn from pg_replication_slots;
  slot_name   | slot_type | confirmed_flush_lsn
--------------+-----------+---------------------
 logical_slot | logical   | 32/D8003628
(1 row)

After you complete your replication tasks, stop the replication process, drop replication slots, and turn off logical replication. To turn off logical replication, modify your DB cluster parameter group and set the value of rds.logical_replication back to 0. Reboot the cluster for the parameter change to take effect.