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
Prerequisites
To configure PostgreSQL logical replication for Multi-AZ DB clusters, you must meet the following prerequisites.
-
Your user account must be a member of the
rds_superuser
group and haverds_superuser
privileges. For more information, see Understanding PostgreSQL roles and permissions. -
Your Multi-AZ DB cluster must be associated with a custom DB cluster parameter group so that you can configure the parameter values described in the following procedure. For more information, see Working with DB cluster parameter groups for Multi-AZ DB clusters.
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
-
Open the custom DB cluster parameter group associated with your RDS for PostgreSQL Multi-AZ DB cluster.
In the Parameters search field, locate the
rds.logical_replication
static parameter and set its value to1
. This parameter change can increase WAL generation, so enable it only when you’re using logical slots.-
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
-
-
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.
-
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
, andpgoutput
plugins that ship with PostgreSQL.The slot is asynchronously copied to each reader DB instance in the cluster.
-
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 theconfirmed_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.