Configure logical replication for PostgreSQL using pglogical - ApsaraDB RDS

The pglogical extension implements logical streaming replication for ApsaraDB RDS for PostgreSQL using a publish/subscribe pattern. It delivers higher replication speed than Slony, Bucardo, and Londiste, and supports cross-version upgrades. Use it to replicate data across instances, perform cross-version upgrades, or selectively sync specific tables and columns.

Prerequisites

Before you begin, ensure that you have:

An RDS instance running PostgreSQL 10 or later
An RDS instance running minor engine version 20230830 or later (for PostgreSQL 17, minor engine version 20241030 or later)
pglogical added to the shared_preload_libraries parameter — for instructions, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance

Important

Modifying shared_preload_libraries triggers an immediate restart of your RDS instance, which disconnects any active database connections. Plan maintenance windows accordingly, and configure your application to reconnect automatically.

Note

Creating this extension requires minor engine version 20230830 or later, even though the extension itself is present in earlier versions. If your instance already has pglogical installed on an earlier minor engine version, you can continue using it. To install it fresh or reinstall it, update the minor engine version first. For details, see Update the minor engine version.

Use cases

pglogical is suitable for the following scenarios:

Cross-version upgrades: Replicate data from an older PostgreSQL major version to a newer one with minimal downtime.
Full database replication: Replicate all data from a database on one RDS instance to another.
Selective replication: Replicate specific tables, rows, or columns using replication sets.
Data aggregation: Merge data from multiple upstream servers into a single subscriber.

Enable the extension

Run the following command on both the publisher and subscriber databases:

CREATE EXTENSION pglogical;

To disable the extension:

DROP EXTENSION pglogical;

Set up logical streaming replication

The setup involves four steps: create a provider node, configure a replication set, create a subscriber node, and create a subscription. All steps use pglogical functions on the respective database instances.

Step 1: Create a provider node

Run the following on the publisher database. Set host to 127.0.0.1, and run SHOW PORT; to get the port number.

SELECT pglogical.create_node(
    node_name := 'provider',
    dsn := 'host=127.0.0.1 port=<Port number> dbname=test user=provider_user password=provider_pass'
);

The expected output is similar to:

 create_node
-------------
  2949455235
(1 row)

Step 2: Configure a replication set

Add all tables in the public schema to the default replication set:

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Note

A replication set controls which tables and which types of changes (INSERT, UPDATE, DELETE) are replicated to subscribers. The default replication set replicates all tables and all change types. To replicate a subset of tables or columns, create a custom replication set. For details, see the pglogical documentation.

Step 3: Create a subscriber node

Run the following on the subscriber database. Set host to 127.0.0.1, and run SHOW PORT; to get the port number.

SELECT pglogical.create_node(
    node_name := 'subscriber',
    dsn := 'host=127.0.0.1 port=<Port number> dbname=test user=subscriber_user password=subscriber_pass'
);

The expected output is similar to:

 create_node
-------------
  1241562886
(1 row)

Step 4: Create a subscription

Run the following on the subscriber database. Use the internal endpoint and internal port number of the publisher, which you can find in the ApsaraDB RDS console. For details, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription',
    provider_dsn := 'host=<Internal endpoint of the provider> port=<Internal port number of the provider> dbname=test user=provider_user password=provider_pass'
);

After the subscription is created, the initial data sync and ongoing replication start in the background.

Verify replication status

After creating the subscription, confirm that replication is running:

SELECT subscription_name, status FROM pglogical.show_subscription_status();

The expected output when replication is active:

 subscription_name | status
-------------------+--------
 subscription      | replicating
(1 row)

If the status is not replicating, check that the provider connection string is correct and that the publisher database is reachable from the subscriber.

Monitor replication slots

pglogical uses PostgreSQL replication slots to track which changes have been sent to each subscriber. If a subscriber falls behind or disconnects, WAL (Write-Ahead Logging) files accumulate on the publisher until the subscriber catches up. In the worst case, this can fill the publisher's storage and make the instance read-only.

Monitor your replication slots regularly:

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

Drop unused replication slots promptly to release accumulated WAL:

SELECT pg_drop_replication_slot('<slot_name>');

ApsaraDB RDS:Use the pglogical extension