Skip to content

Setup replication

2. Configure logical replication

2.1 Check if logical replication is enabled

Run the following command to check if your instance is already configured with logical replication:

postgres> SHOW rds.logical_replication;
 rds.logical_replication 
-------------------------
 off
(1 row)
If the result is on (or 1), it means that logical replication is enabled, skip to set up replication.
If not, follow the steps below to enable logical replication. 

postgres> SHOW rds.logical_replication;
 rds.logical_replication 
-------------------------
 off
(1 row)
If the result is on (or 1), it means that logical replication is enabled, skip to set up replication.
If not, follow the steps below to enable logical replication. 

postgres=> SHOW wal_level;
 wal_level 
-----------
 replica
(1 row)
If the result is logical, it means that logical replication is enabled, skip to set up replication. If not, follow the steps below to enable logical replication.

2.2 Enable logical replication

Create a custom RDS parameter group. If your instance already uses a custom parameter group, skip to the next stage.

Custom Parameter Group

Edit the custom parameter group. set the rds.logical_replication parameter to 1.

Edit logical_replication

Optional: Set the max_slot_wal_keep_size parameter to 51200 to limit the amount of WAL data that is retained for logical replication slots. (Postgres 13+)

Associate the custom parameter group with your RDS instance. Go to the RDS management console, select your instance and click on the "Modify" button.

Modify DB

In the "Modify DB Instance" page, select the custom parameter group you created in the previous step.

Update DB Parameter Group

Make sure you choose "Apply Immediately" to apply the changes immediately.

Apply Parameter Group

Wait for the parameter group configuration to change to "Pending reboot" status.

The parameter group status can be found in the "Configuration" tab of your RDS instance. Reset DB Reset DB

Then, reboot the database for the changes to take effect.

You'll know that the changes have taken affect when the status of your DB instance Parameter Group changes to "In Sync".

Parameter Group in Sync

Verify that the rds.logical_replication parameter is set to "on" (or 1). 

SHOW rds.logical_replication;
 rds.logical_replication
-------------------------
    on
    (1 row)

Create a custom Aurora parameter group. If your instance already uses a custom parameter group, skip to the next stage.

Custom Parameter Group

Edit the custom parameter group, set the rds.logical_replication parameter to 1.

Edit Parameter Group

Optional: Set the max_slot_wal_keep_size parameter to 51200 to limit the amount of WAL data that is retained for logical replication slots. (Postgres 13+)

Associate the custom parameter group with your Aurora cluster. Go to the RDS management console, select your instance and click on the "Modify" button.

Modify DB

In the "Modify DB Instance" page, select the custom parameter group you created in the previous step.

Update DB Parameter Group

Make sure you choose "Apply Immediately" to apply the changes immediately.

Apply Parameter Group

Wait for the parameter group configuration to change to "Pending reboot" status.

The parameter group status can be found in the "Configuration" tab of your RDS instance. Reset DB Reset DB

Then, reboot the database for the changes to take effect.

You'll know that the changes have taken affect when the status of your DB instance Parameter Group changes to "In Sync".

Parameter Group in Sync

Verify that the rds.logical_replication parameter is set to "on" (or 1). 

SHOW rds.logical_replication;
 rds.logical_replication
-------------------------
    on
    (1 row)

To enable logical replication in a PostgreSQL database, you need to set the wal_level parameter in your database configuration to logical. For standard PostgreSQL installations, you can do this by either:

  • Method 1: Adding a wal_level = logical line to the postgresql.conf file.
  • Method 2: Running ALTER SYSTEM SET wal_level = logical;;

Optional: Set the max_slot_wal_keep_size parameter to 51200 to limit the amount of WAL data that is retained for logical replication slots. (Postgres 13+)

Restart your database for the changes to take effect.

Verify that the wal_level parameter is set to "logical". 

SHOW wal_level;
 wal_level
-------------------------
    logical
    (1 row)

2.3 Set up replication

Next, you'll need to set up replication by running the following commands in your database:

CREATE PUBLICATION epsio_publication;
CREATE PUBLICATION epsio_command_publication;

GRANT rds_replication TO epsio_user;

CREATE PUBLICATION epsio_publication;
CREATE PUBLICATION epsio_command_publication;

GRANT rds_replication TO epsio_user;

CREATE PUBLICATION epsio_publication;
CREATE PUBLICATION epsio_command_publication;

ALTER USER epsio_user WITH REPLICATION;