Preparing Your Azure 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 Azure Database for PostgreSQL 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.

The steps presented in this guide cover:

• Configuring your Azure Database instance to support logical replication.
• Ensuring secure network access from Linode.
• Creating a dedicated replication user.
• Setting up a publication for the tables you wish to replicate.

After completing these steps, return to the main replication guide 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 host.

2. Ensure your Azure account has permissions to modify PostgreSQL server parameters, networking settings, and firewall rules.

3. Install and authenticate the Azure CLI on your local machine:

   az login
   az account set --subscription YOUR_AZURE_SUBSCRIPTION_ID

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 Server Parameters

To support logical replication, you’ll need to adjust a few parameters on your Azure Database for PostgreSQL instance.

Configure Network Access

Before the Linode Managed Database can connect to your Azure Database instance, ensure that the instance allows network access from the Linode database host.

1. Navigate to the Settings > Networking page for the instance. Make sure that the Public access option is checked.

   

2. In the list of firewall rules, add a rule to allow access to your Linode Managed Database. Specify a name for the firewall rule. Enter the IP address of your Linode Managed Database host as both the Start IP address and the End IP address:

3. Click Save at the top of the page.

With network access configured, your Linode Managed Database can reach the Azure Database instance during the subscription creation step in the main guide.

Create a Replication User

The source database should have a dedicated replication user with the REPLICATION attribute and SELECT privileges to the tables to be replicated. While logical replication can be performed as administrator, it’s a security best practice to create a dedicated user.

Follow the steps below to create this scope-limited user on your Azure Database instance.

1. Connect to your instance using the psql client and the connection string information found on the Settings > Connect page, replacing any placeholders with your own values:

   psql "host=AZURE_SERVER_NAME.postgres.database.azure.com \
         port=5432 \
         dbname=SOURCE_DB \
         user=SOURCE_USER \
         password=SOURCE_PASSWORD \
         sslmode=require"

2. After connecting successfully, create a user with replication privileges (e.g., linode_replicator), provide a password (e.g., thisismyreplicatorpassword), then grant SELECT privileges for the tables you plan to replicate. For simplicity, this example assumes a public schema and three tables typically found in an ecommerce database (e.g., customers, products, and orders). Replace the table names with your actual schema as needed:

   Source psql Prompt

   CREATE ROLE linode_replicator
          WITH REPLICATION
          LOGIN PASSWORD 'thisismyreplicatorpassword';
   GRANT SELECT ON customers, products, orders TO linode_replicator;

   CREATE ROLE
   GRANT

   Note

   Alternatively, you can grant privileges on all tables with the following command:

   Source psql Prompt

   GRANT SELECT ON ALL TABLES in SCHEMA public to linode_replicator;

The newly created user is referenced by the Linode Managed Database when creating the subscription.

Create a Publication

A publication defines which tables and changes (inserts, updates, deletes) should be streamed to the subscriber. You need at least one publication for logical replication.

1. While still connected via the psql client, create a publication (e.g., my_publication) for specific tables (e.g., customers , products, and orders):

   Source psql Prompt

   CREATE PUBLICATION PUBLICATION_NAME FOR TABLE customers, products, orders;
   Note

   The subscriber must have matching tables with compatible schemas for replication to succeed. Alternatively, you can create a publication for all current and future tables in the database:

   Source psql Prompt

   CREATE PUBLICATION PUBLICATION_NAME FOR ALL TABLES;

2. Run the following command to view any created publications:

   Source psql Prompt

   SELECT * FROM pg_publication_tables;

   -[ RECORD 1 ]-----------------------------------------------
   pubname    | my_publication
   schemaname | public
   tablename  | customers
   attnames   | {id,name,email,created_at}
   rowfilter  |
   -[ RECORD 2 ]-----------------------------------------------
   pubname    | my_publication
   schemaname | public
   tablename  | products
   attnames   | {id,name,price,in_stock}
   rowfilter  |
   -[ RECORD 3 ]-----------------------------------------------
   pubname    | my_publication
   schemaname | public
   tablename  | orders
   attnames   | {id,customer_id,product_id,quantity,order_date}
   rowfilter  |

Your Azure source database is now ready for logical replication. Return to the main guide 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.

• Logical replication and logical decoding in Azure Database for PostgreSQL
• Server parameters in Azure Database for PostgreSQL
• Azure CLI Documentation for az postgres