Preparing Your AWS RDS PostgreSQL Database for Logical Replication to Linode Managed Database

Logical replication continuously synchronizes database tables, allowing you to prepare the destination database in advance. This approach minimizes downtime when you switch application traffic and retire the source database.

This guide explains how to prepare an AWS RDS PostgreSQL database for logical replication to a Linode Managed Database. Follow this guide before returning to the Logical Replication to a Linode Managed PostgreSQL Database guide to create the subscription on Akamai Cloud.

Follow the steps in this guide to:

• Configure your RDS instance to support logical replication.
• Ensure secure network access from Linode.
• Create a dedicated replication user.
• Set up a publication for the tables you wish to replicate.

After completing these steps, return to Logical Replication to a Linode Managed PostgreSQL Database to configure the subscriber and finalize the setup.

Before You Begin

1. Follow the Logical Replication to a Linode Managed PostgreSQL Database guide up to the Prepare the Source Database for Logical Replication section to obtain the public IP address or CIDR range of your Linode Managed Database.

2. Ensure that you have administrative access to your AWS account, including permissions to modify RDS instance settings and security groups.

3. Install the AWS CLI on your local machine and configure it with a user or role that has the necessary privileges listed above.

Placeholders and Examples

The following placeholders and example values are used in commands throughout this guide:

Replace these placeholders with your own connection details when running commands in your environment.

Additionally, the examples used in this guide assume the source database contains three tables (customers, products, and orders) that you want to replicate to a Linode Managed Database.

Configure RDS Parameter Group

To support logical replication, you must configure your RDS instance with the correct parameter settings. Review your RDS instance’s current parameter group settings to determine if changes are required:

In order for logical replication to succeed, these values should be as follows:

• max_replication_slots: Greater than or equal to 1
• max_wal_senders: Greater than or equal to max_replication_slots, depending on expected replication concurrency
• rds.logical_replication: 1 (which sets the PostgreSQL setting of wal_level to logical)

If these values are already set correctly, skip the remainder of this section and continue with Configure Network Access. Otherwise, you need to modify the parameter group using the instructions below.

Create a Custom Parameter Group

The default parameter group cannot be modified. If your RDS instance is using the default parameter group, you must create a custom parameter group to make the necessary modifications.

Configure Network Access

Ensure that the RDS instance allows network access from the Linode Managed Database.

With network access configured, your Linode Managed Database can reach the RDS instance during the subscription creation step in Logical Replication to a Linode Managed PostgreSQL Database.

Create a Replication User

While logical replication can technically be performed using the primary database user, it’s best practice to create a dedicated replication user. This user should have the rds_replication privilege and SELECT access only to the tables being published.

Follow the steps below to create this limited-privileges user on your RDS instance.

1. Before connecting to your source database, determine the RDS endpoint hostname:

   aws rds describe-db-instances \
     --region AWS_REGION \
     --db-instance-identifier DB_INSTANCE_ID \
     --query "DBInstances[0].Endpoint.Address" \
     --output text

   This value is used as SOURCE_HOST in the following command:

   source-postgres.abc123.us-west-2.rds.amazonaws.com

2. Connect to your source PostgreSQL instance using the psql client. Replace SOURCE_HOST (e.g., source-postgres.abc123.us-west-2.rds.amazonaws.com), SOURCE_PORT (e.g., 5432), SOURCE_USER (e.g., democoder), and SOURCE_DB (e.g., postgres) with your own values. You can find the endpoint on the Connectivity & security tab of your RDS instance. The default database name for RDS PostgreSQL instances is postgres.

   psql \
     -h SOURCE_HOST \
     -p SOURCE_PORT \
     -U SOURCE_USER \
     -d SOURCE_DB

   When prompted, enter your SOURCE_PASSWORD (e.g., thisismysourcepassword).

3. Run the following commands from the source psql prompt. Replace REPL_USER (e.g., linode_replicator) and REPL_PASSWORD (e.g., thisismyreplicatorpassword) with your own values. For simplicity, this example assumes a public schema and three sample tables (customers, products, and orders). Replace the table names with your actual schema as needed.

   Source psql Prompt

   CREATE ROLE REPL_USER LOGIN PASSWORD 'REPL_PASSWORD';
   GRANT rds_replication TO REPL_USER;
   GRANT SELECT ON customers, products, orders TO REPL_USER;

   CREATE ROLE
   GRANT ROLE
   GRANT

   Alternative: Grant on All Tables

   You can also grant privileges on all tables with the following command:

   Source psql Prompt

   GRANT SELECT ON ALL TABLES IN SCHEMA public TO REPL_USER;

   GRANT

The newly created user is referenced by the Linode Managed Database when creating the subscription in Logical Replication to a Linode Managed PostgreSQL Database.

Create a Publication

A publication defines which tables and changes (e.g., INSERT, UPDATE, and DELETE) should be streamed to the subscriber. At least one publication is required for logical replication, and the subscriber must have matching tables with compatible schemas for replication to succeed.

1. While still connected to your source database via the psql client, use the following command to create a publication. Replace PUBLICATION_NAME (e.g., my_publication) and the specific tables you want to replicate (e.g., customers, products, and orders):

   Source psql Prompt

   CREATE PUBLICATION PUBLICATION_NAME FOR TABLE customers, products, orders;

   CREATE PUBLICATION

   Alternative: Publish All Tables

   You can also create a publication for all tables in the database:

   Source psql Prompt

   CREATE PUBLICATION PUBLICATION_NAME FOR ALL TABLES;

2. Run the following command to view all existing publications:

   Source psql Prompt

   SELECT * FROM pg_publication_tables;

       pubname     | schemaname | tablename |                       attnames
   ----------------+------------+-----------+-------------------------------------------------------
    my_publication | public     | customers | {customer_id,name,email,created_at}
    my_publication | public     | products  | {product_id,name,price,created_at}
    my_publication | public     | orders    | {order_id,customer_id,product_id,quantity,created_at}
   (3 rows)

3. Type \q and press Enter to exit the source psql shell.

Your RDS source database is now ready for logical replication. Return to Logical Replication to a Linode Managed PostgreSQL Database to configure the Linode Managed Database and create the subscription.

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

• Setting up PostgreSQL logical replication
• Parameter groups for Amazon RDS
• CLI documentation for AWS RDS