How do I use logical replication to upgrade my Aurora PostgreSQL-Compatible DB cluster?

Short description

To use logical replication to upgrade versions for Aurora PostgreSQL-Compatible DB clusters, set up replication between the original source cluster and the new target cluster. Then, gradually migrate the data changes, and switch applications to the new cluster.

Resolution

Prepare the source cluster

Complete the following steps:

1. Set rds.logical_replication parameter to 1 in your custom DB cluster parameter group.

2. Run the following query to verify that wal_level is logical and rds.logical_replication is on:

   SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');

   Example output:

   name                    | setting
   ------------------------+---------
   rds.logical_replication | on
   wal_level              | logical
   (2 rows)

3. Run the following command to create a publication in your database:

   CREATE PUBLICATION my_publication FOR ALL TABLES;

   Note: Replace my_publication with your publication name.

4. Run the following query to retrieve information from the publication:

   SELECT * FROM pg_publication;

   Example output:

    oid   | pubname        | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
   -------+----------------+----------+--------------+-----------+-----------+-----------+-------------+------------
    20493 | my_publication | 16400    | t            | t         | t         | t         | t           | f
   (1 row)

5. Run the following command to create a replication slot in the database:

   SELECT pg_create_logical_replication_slot('my_replication_slot', 'pgoutput');

   Example output for pg_create_logical_replication_slot:

    pg_create_logical_replication_slot
   ------------------------------------
    (my_replication_slot,0/6EC7260)
   (1 row)

   Note: Replace my_replication_slot with your replication slot name.

6. Run the following command to get the list of replication slots that are currently in the DB cluster and their status:

   SELECT * FROM pg_replication_slots;

   Example output for pg_replication_slots:

    slot_name            | plugin   | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
   ---------------------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------
    my_replication_slot | pgoutput | logical   | 16401  | test     | f         | f      |            |      | 308479       | 0/6EC7208  | 0/6EC7260          | reserved   |               | f
   (1 row)

Clone the source cluster

Use the Amazon Relational Database Service (Amazon RDS) console or the AWS Command Line Interface (AWS CLI) to create a clone of the source Aurora PostgreSQL-Compatible DB cluster.

Prepare the clone for an upgrade

For more information, see To prepare the clone for an upgrade in Upgrading Aurora PostgreSQL to a new major version.

Upgrade the cluster to a new version

For more information, see To upgrade the cluster to a new major version in Upgrading Aurora PostgreSQL to a new major version.

Prepare the clone for replication

Complete the following steps:

1. When the clone becomes available after the upgrade, use psql to connect to the cluster, and then run the following command to create the subscription:

   CREATE SUBSCRIPTION my_subscription 
   CONNECTION 'postgres://admin_user_name:admin_user_password@source_instance_URL/database' 
   PUBLICATION my_publication 
   WITH (copy_data = false, 
         create_slot = false, 
         enabled = false, 
         connect = true, 
         slot_name = 'my_replication_slot');

   Note: Replace admin_user_name with your source cluster username and admin_user_password with your source cluster password. Also, replace source_instance_URL with your source cluster endpoint and database with your database name.

2. Run the following query to retrieve the replication origin that you created:

   SELECT * FROM pg_replication_origin;

   Example output for pg_replication_origin:

    roident | roname
   ---------+----------
    1       | pg_32783
   (1 row)

3. Run the following query to retrieve information about the logical replication subscription:

   SELECT * FROM pg_subscription;

   Example output for pg_subscription:

    oid   | subdbid | subskiplsn | subname          | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpasswordrequired | subrunasowner | subconninfo                                                                              | subslotname           | subsynccommit | subpublications    | suborigin
   -------+---------+------------+-----------------+----------+------------+-----------+-----------+------------------+-----------------+---------------------+---------------+------------------------------------------------------------------------------------------------+---------------------+---------------+------------------+-----------
    32783 | 16401   | 0/0        | my_subscription | 16400    | f          | f         | f         | d                | f               | t                   | f             | postgres://admin_user_name:admin_user_password@source_instance_URL/database                  | my_replication_slot   | off           | {my_publication}  | any
   (1 row)

4. Run the following command to specify the start point in the log sequence for replication:

   SELECT pg_replication_origin_advance('roname', 'log_sequence_number');

   Note: Replace roname with the identifier that the pg_replication_origin view returned. Replace log_sequence_number with your log sequence number.
   Example output:

    pg_replication_origin_advance
   ------------------------------
    
   (1 row)

5. Run the following command to turn on logical replication:

   ALTER SUBSCRIPTION my_subscription ENABLE;

   Note: Replace my_subscription with your subscription name.
   Example output:

   ALTER SUBSCRIPTION

6. Run the following command to confirm that the replication works:

   SELECT now() AS CURRENT_TIME, 
          slot_name, 
          active, 
          active_pid, 
          pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS diff_size,
          pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS diff_bytes 
   FROM pg_replication_slots 
   WHERE slot_type = 'logical';

   Example output:

        current_time      |      slot_name       | active | active_pid | diff_size | diff_bytes
   ----------------------+---------------------+--------+------------+-----------+------------
    2024-01-20 10:30:45 | my_replication_slot | t      | 12345      | 16 MB     | 16777216
   (1 row)

7. (Optional) Run the following command to monitor the replication lag:

   SELECT now() AS CURRENT_TIME, 
          slot_name, 
          active, 
          active_pid, 
          pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS diff_size,
          pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS diff_bytes 
   FROM pg_replication_slots 
   WHERE slot_type = 'logical';

   Example output:

   -[ RECORD 1 ]+------------------------------ 
   current_time | 2025-10-05 22:07:16.077928+00
   slot_name    | my_replication_slot
   active       | t
   active_pid   | 5392
   diff_size    | 0 bytes
   diff_bytes   | 0

   Note: When the preceding values reach 0, the replica matches the source DB instance.

Perform post-upgrade tasks

For more information, see Performing post-upgrade tasks.

Related information

How do I use logical replication to replicate tables between my Amazon RDS for PostgreSQL DB instances?

Using logical replication to replicate managed Amazon RDS for PostgreSQL and Amazon Aurora to self-managed PostgreSQL