It is 2023 and upgrading Postgres is still a pain. For those using AWS, there is hope, as they started to offer blue/green deployments for MySQL. Alas, this is not available for Postgres yet.

In the first part, I exposed the most reasonable options, what was used for the upgrade and how it went. In this post, you will find a lengthy step-by-step on how to achieve a Postgres zero-downtime upgrade.

Observations & Limitations

1. The following step-by-step is for a Postgres instance with only one database. It might work for multiple databases, but logical replications only work against one database at a time.
2. Sequence/series data is not replicated. This means extra steps are required to adjust any column using sequences (included in this guide).
3. The schema and DDL commands (the ones which alter the schema) are not replicated. This means a schema freeze is needed during the database replication.
4. Logical replication will require rows to have a “replica identity”. This should be a primary key and if all your tables have one it will not be a problem. If you find a table missing one, you will need to set the identity manually or create a primary key. Read the documentation to understand the trade-offs.
5. Upgrades can’t happen if the database already has replication slots: The team will need to drop existing replication slots by doing SELECT pg_drop_replication_slot(slot_name).

Pre-setup

There are a few steps you will need to do before even touching Postgres:

1. Get hold of the cluster admin password. This is the user that will be used for all operations. In case of doubt, is the one created by default when you create the RDS Cluster (might be hidden in your Terraform state).
2. Decide which version your team wants to update it to. Most cloud providers allow you to jump multiple versions (in our case, we went from 10 to 14).
3. Run a test suite against the desired version and let it soak in development environments for a while. Although rare, there might be changes that affect your code.
4. Create a new set of parameter groups for the desired version. It can be done in Terraform (cluster and instances) or in the UI. Having it in Terraform makes it easier to replicate these steps later.
5. Ensure SOURCE and TARGET live in the same network and correctly set outbound/inbound firewalls. It’s trivial, but you never know if someone has changed something manually (our case).
6. Ensure all tables have replica identity correctly set or have a primary key.
7. Read the AWS Postgres upgrade guide to get familiarised with its usual process.

Data integrity

The engineering team could only be confident if we proved that the data integrity was kept after the upgrade. We came up with a few scripts which were consolidated in a tool available at processout/pg-upgrade-data-check.

The script compares data from before the replication and after the replication, comparing the hash of all rows in the between the time the database was replicating. It detected issues multiple times in both testing and production rollouts. The caveat is that it relies on an autoincremental key, not working if your tables don’t have one.

In any case, even if this tool does not suit you, it is very important that the team defines a strategy to prove the data has been kept intact.

Preparing the SOURCE for replication

1. The cluster needs to have logical replication enabled. On AWS RDS, set rds.logical_replication=1 in parameter groups. The native equivalent is setting wal_level=logical. Once configured, restart the instances (be careful with the order and failovers).
2. Connect to the SOURCE writer instance and confirm that the WAL level is logical by running show wal_level. If it is not, reboot the SOURCE writer instance.
3. Create a replication role and grant the correct access.

CREATE USER replicator WITH password 'password'; -- replace this
GRANT rds_replication TO replicator; -- AWS RDS specific
GRANT USAGE ON SCHEMA public TO replicator; -- the default for Postgres is 
GRANT <target_database> TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

4. If you have any data integrity script, you should run it now. This is because it will capture the state before you start accumulating WAL.
5. Create a publication: This is used by the target database to subscribe for changes.

CREATE PUBLICATION pub1 FOR ALL TABLES;

6. Create a replication slot: The write operations will be accumulated in this slot. It will spill out the LSN it started to capture, and it might be handy to keep a note of that.

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

7. At this point, you can already see replication slot stats by doing the following queries:

-- General details about it
SELECT * FROM pg_replication_slots;

-- I mostly used the one below to identify the lag
SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots;

Create the target database

The target will be a clone of the source. If you are unfamiliar with this concept, look at the RDS Aurora cloning guide. I haven’t tested it, but it might also work with native restore.

1. Clone the source database: the UI makes it very simple, but check all parameters set up. Another way is