Using logical replication to perform a major version upgrade for Aurora PostgreSQL

In the example that follows, the source writer instance (blue) is an Aurora PostgreSQL DB cluster running PostgreSQL version 11.15. This is the publication node in our replication scenario. For this demonstration, our source writer instance hosts a sample table that holds a series of values:

CREATE TABLE my_table (a int PRIMARY KEY);
INSERT INTO my_table VALUES (generate_series(1,100));

To create a publication on the source instance, connect to the writer node of the instance with psql (the CLI for PostgreSQL) or with the client of your choice). Enter the following command in each database:

CREATE PUBLICATION publication_name FOR ALL TABLES;

The publication_name specifies the name of the publication.

You also need to create a replication slot on the instance. The following command creates a replication slot and loads the pgoutput logical decoding plug-in. The plug-in changes content read from write-ahead logging (WAL) to the logical replication protocol, and filters the data according to the publication specification.

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

Use the Amazon RDS Console to create a clone of the source instance. Highlight the instance name in the Amazon RDS Console, and then choose Create clone in the Actions menu.

Provide a unique name for the instance. Most of the settings are defaults from the source instance. When you’ve made changes required for the new instance, choose Create clone.

While the target instance is initiating, the Status column of the writer node displays Creating in the Status column. When the instance is ready, the status changes to Available.

The clone is the ‘green’ instance in the deployment model. It is the host of the replication subscription node. When the node becomes available, connect with psql and query the new writer node to obtain the log sequence number (LSN). The LSN identifies the beginning of a record in the WAL stream.

SELECT aurora_volume_logical_start_lsn();

In the response from the query, you find the LSN number. You need this number later in the process, so make a note of it.

postgres=> SELECT aurora_volume_logical_start_lsn();
aurora_volume_logical_start_lsn 
---------------
0/402E2F0
(1 row)

Before upgrading the clone, drop the clone's replication slot.

SELECT pg_drop_replication_slot('replication_slot_name');

When the clone becomes available, connect with psql and define the subscription. To do so, you need to specify the following options in the CREATE SUBSCRIPTION command:

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

After creating the subscription, query the pg_replication_origin view to retrieve the roname value, which is the identifier of the replication origin. Each instance has one roname:

SELECT * FROM pg_replication_origin;

For example:

postgres=> 
SELECT * FROM pg_replication_origin;
 
roident | roname 
---------+----------
1 | pg_24586

Provide the LSN that you saved from the earlier query of the publication node and the roname returned from the subscription node [INSTANCE] in the command. This command uses the pg_replication_origin_advance function to specify the starting point in the log sequence for replication.

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

roname is the identifier returned by the pg_replication_origin view.

log_sequence_number is the value returned by the earlier query of the aurora_volume_logical_start_lsn function.

Then, use the ALTER SUBSCRIPTION... ENABLE clause to turn on logical replication.

ALTER SUBSCRIPTION subscription_name ENABLE;

At this point, you can confirm that replication is working. Add a value to the publication instance, then confirm that the value is replicated to the subscription node.

Then, use the following command to monitor replication lag on the publication node:

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';

For example:

postgres=> 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';
 
current_time | slot_name | active | active_pid | diff_size | diff_bytes 
-------------------------------+-----------------------+--------+------------+-----------+------------
2022-04-13 15:11:00.243401+00 | replication_slot_name | t | 21854 | 136 bytes | 136
(1 row)