As PostgreSQL 18 Beta 1 has been released (link) and is packed with lots of cool features like improved IO on reads for example, I wanted to test out the new pg_createsubscriber and pg_upgrade options and how they are going to help us performing smoother major upgrades using the “blue-green” migration similar to the ones available on AWS (Using Amazon RDS Blue/Green Deployments for database updates – Amazon Relational Database Service).
The underlying technology is still the same, logical replication, and with still the same limitations and constraints but a much smoother setup and implementation to the point that it has never been so easy to automate such tasks.
pg_createsubscriber : simplifying logical replication setup
pg_createsubscriber automates this workflow by converting a physical standby server into a logical replica. It creates the necessary publications and subscriptions for specified databases without copying the initial table data, focusing solely on synchronizing changes from a specific point in time. This approach is particularly nice with large databases, where copying existing data can be resource-intensive and time-consuming.
PostgreSQL 18 Beta 1 introduces an --all flag to pg_createsubscriber, allowing us to create logical replicas for all databases in an instance with a single command. This simplifies the replication setup process, especially in environments with multiple databases, by eliminating the need to specify each database individually.
The latest version of PostgreSQL offers some new pg_upgrade capabilities like the “–swap” option which
a nice speed improvement. If you want to know more about it Daniel Westermann did a nice blog post about it.
PostgreSQL 18 one of the most significant releases in years, packed with improvements. I won’t talk about all the nice new capabilities here. But one small addition caught my attention while preparing this blog. pg_stat_subscription_stats is a new view that allows to track write conflicts that occur during logical replication. This is really nice and means that we get one step closer to have a multi-master capability in PostgreSQL.
Step by step blue-green migration
In my setup for the LAB I have two servers pg-blue (IP: 10.1.0.4) and pg-green (IP: 10.1.0.5). Both servers will start with a PostgreSQL 17.5 instance. The blue server will simulate my production instance that I want to migrate with minimum downtime to a new PostgreSQL 18 beta 1 instance.
🟦 Step 1: Set Up the Blue (Primary) Environment with PostgreSQL 17
Install PostgreSQL 17:
Install the instance using your preferred way, in my case I am going to install from source as I usually do.
Initialize the database and populate with sample data:
sudo -i -u postgres
createdb pgbench_test
pgbench -i -s 500 pgbench_testConfigure replication settings:
Edit postgresql.conf:
# on blue
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10Create a replication user:
# on blue
psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'yourpassword';"Configure pg_hba.conf for replication access:
Edit pg_hba.conf:
# on blue
host replication replicator 10.1.0.5/32 scram-sha-256Restart PostgreSQL to apply changes:
sudo systemctl restart postgresqlVerify replication settings:
psql -c "SELECT * FROM pg_stat_replication;"🟩 Step 2: Create a Physical Replica (Green Environment)
On the replica server:
Stop PostgreSQL service:
sudo systemctl stop postgresqlBackup existing data directory:
sudo cp -rf /u02/pgdata/17/db_5/ /u99/pgdata/17/Remove existing data directory:
sudo rm -rf /u02/pgdata/17/db_5/*Perform base backup from primary:
postgres@PG-green:/u02/pgdata/17/db_5/ [pg175] pg_basebackup -h 10.1.0.4 -D /u02/pgdata/17/db_5 -U replicator -W -P --wal-method=stream
Password:
7687298/7687298 kB (100%), 1/1 tablespaceEnable standby mode:
touch /u02/pgdata/17/db_5/standby.signalConfigure primary connection info:
Edit /etc/postgresql/17/main/postgresql.conf:
primary_conninfo = 'host=10.1.0.4 port=5432 user=replicator password=yourpassword'Start PostgreSQL service:
sudo systemctl start postgresqlVerify replication status:
08:28:41 postgres@PG-blue:/u02/pgdata/17/db_5/ [pg175] sqh
psql (17.5 dbi services build)
Type "help" for help.
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 2014
usesysid | 16388
usename | replicator
application_name | pg17blue
client_addr | 10.1.0.5
client_hostname |
client_port | 45162
backend_start | 2025-05-18 16:34:20.991611+00
backend_xmin | 765
state | streaming
sent_lsn | 1/6D0000A0
write_lsn | 1/6D0000A0
flush_lsn | 1/6D0000A0
replay_lsn | 1/6D0000A0
write_lag | 00:00:00.190877
flush_lag | 00:00:00.195751
replay_lag | 00:00:00.195752
sync_priority | 0
sync_state | async
reply_time | 2025-05-18 16:34:21.207837+00
08:28:59 postgres@PG-green:/u02/pgdata/17/db_5/ [pg175] sqh
psql (17.5 dbi services build)
Type "help" for help.
postgres=# SELECT pg_is_in_recovery()
;
pg_is_in_recovery
-------------------
t
(1 row)
postgres=#🔄 Step 3: Transition from Physical to Logical Replication
On the primary server:
Set wal_level to logical:
# On blue
psql -c "ALTER SYSTEM SET wal_level = logical;"Restart PostgreSQL:
# On blue
sudo systemctl restart postgresqlCreate a publication for all tables:
# On blue
veryolddatabasethatimigratedsincepgversion9=# select * FROM pg_catalog.pg_publication
WHERE pubname = 'my_pub';
-[ RECORD 1 ]+-------
oid | 16413
pubname | my_pub
pubowner | 10
puballtables | t
pubinsert | t
pubupdate | t
pubdelete | t
pubtruncate | t
pubviaroot | fOn the replica server:
Use pg_createsubscriber to set up logical replication:
# On green
17:29:36 postgres@PG-green:/u02/pgdata/17/db_5/ [pg175] /u01/app/postgres/product/17/db_5/bin/pg_createsubscriber -d veryolddatabasethatimigratedsincepgversion9 --pgdata=/u02/pgdata/17/db_5/ --config-file=/u02/pgdata/17/db_5/postgresql.conf --publisher-server='user=replicator password=yourpassword host=10.1.0.4' -p 5432 -U postgres --publication=my_pub --subscription=my_sub
2025-05-18 19:30:04.173 CEST - 1 - 3182 - - @ - 0LOG: redirecting log output to logging collector process
2025-05-18 19:30:04.173 CEST - 2 - 3182 - - @ - 0HINT: Future log output will appear in directory "pg_log".
2025-05-18 19:30:04.682 CEST - 1 - 3195 - - @ - 0LOG: redirecting log output to logging collector process
2025-05-18 19:30:04.682 CEST - 2 - 3195 - - @ - 0HINT: Future log output will appear in directory "pg_log".
17:30:06 postgres@PG-green:/u02/pgdata/17/db_5/ [pg175] pgstart
waiting for server to start.... done
server started
Monitor replication status:
# On green
SELECT * FROM pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid | 24605
subname | my_sub
worker_type | apply
pid | 3229
leader_pid |
relid |
received_lsn | 1/70000CB8
last_msg_send_time | 2025-05-18 17:50:25.344741+00
last_msg_receipt_time | 2025-05-18 17:50:25.345546+00
latest_end_lsn | 1/70000CB8
latest_end_time | 2025-05-18 17:50:25.344741+00
# On Blue
veryolddatabasethatimigratedsincepgversion9=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 2809
usesysid | 16388
usename | replicator
application_name | my_sub
client_addr | 10.1.0.5
client_hostname |
client_port | 33324
backend_start | 2025-05-18 17:30:46.257715+00
backend_xmin |
state | streaming
sent_lsn | 1/70000CB8
write_lsn | 1/70000CB8
flush_lsn | 1/70000CB8
replay_lsn | 1/70000CB8
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2025-05-18 17:48:45.220612+00⚙️ Step 4: Upgrade the Green Environment to PostgreSQL 18 Using pg_upgrade with --swap
On the replica server:
Install PostgreSQL 18 binaries:
You can download the source using the following command to install from source or use the PostgreSQL official repository.
wget https://ftp.postgresql.org/pub/source/v18beta1/postgresql-18beta1.tar.bz2In my case I want to use the new features of PostgreSQL like IO_uring, numa awareness and oauth, so I will use the following options on my meson configure :
...
-Dlibcurl=enabled \
-Dliburing=enabled \
-Dlibnuma=enabled \
...Stop PostgreSQL service:
sudo systemctl stop postgresqlRun pg_upgrade with the --swap method:
export PGDATAOLD=/u02/pgdata/17/db_5
export PGBINOLD=/u01/app/postgres/product/17/db_5/bin
export PGDATANEW=/u02/pgdata/18/db_beta1
export PGBINNEW=/u01/app/postgres/product/18/db_beta1/bin
18:32:55 postgres@PG-green:/home/postgres/build/ [pg175] /u01/app/postgres/product/18/db_beta1/bin/pg_upgrade \
--old-bindir=$PGBINOLD --new-bindir=$PGBINNEW \
--old-datadir=$PGDATAOLD --new-datadir=$PGDATANEW \
--check
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for valid logical replication slots ok
Checking for subscription state ok
Checking data type usage ok
Checking for objects affected by Unicode update ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
Checking for new cluster configuration for subscriptions ok
*Clusters are compatible*
18:33:02 postgres@PG-green:/home/postgres/build/ [pg175] /u01/app/postgres/product/18/db_beta1/bin/pg_upgrade \
--old-bindir=$PGBINOLD --new-bindir=$PGBINNEW \
--old-datadir=$PGDATAOLD --new-datadir=$PGDATANEW \
--swap
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for valid logical replication slots ok
Checking for subscription state ok
Checking data type usage ok
Checking for objects affected by Unicode update ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
Checking for new cluster configuration for subscriptions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Adding ".old" suffix to old "global/pg_control" ok
Because "swap" mode was used, the old cluster can no longer be
safely started.
Swapping data directories
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates ok
Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
/u01/app/postgres/product/18/db_beta1/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
/u01/app/postgres/product/18/db_beta1/bin/vacuumdb --all --analyze-only
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
/u01/app/postgres/product/18/db_beta1/bin/vacuumdb \
--all --analyze-in-stages --missing-stats-only
Note: The --swap option swaps the old and new data directories, potentially offering a faster upgrade process.
Start PostgreSQL 18 service:
18:34:34 postgres@PG-green:/home/postgres/ [pg18b] pgstart
waiting for server to start.... done
server started
18:34:37 postgres@PG-green:/home/postgres/ [pg18b] sqh
psql (18beta1 dbi services build)
Type "help" for help.
postgres=# SELECT * FROM pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid | 16406
subname | my_sub2
worker_type | apply
pid | 22582
leader_pid |
relid |
received_lsn | 1/70000CB8
last_msg_send_time | 2025-05-18 18:35:07.905706+00
last_msg_receipt_time | 2025-05-18 18:35:07.905066+00
latest_end_lsn | 1/70000CB8
latest_end_time | 2025-05-18 18:35:07.905706+00
postgres=#This is one of the key result.
The logical replication setup survives the pg_upgrade process and keeps the synchronization going.
Note though, that on large instances, you still want WAL retention to be the duration of the pg_upgrade process obviously.
🔁 Step 5: Perform Failover and Cutover to the Upgraded Green Environment
On the primary server:
# On blue
veryolddatabasethatimigratedsincepgversion9=# ALTER SYSTEM SET default_transaction_read_only = on;
ALTER SYSTEM
veryolddatabasethatimigratedsincepgversion9=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
veryolddatabasethatimigratedsincepgversion9=# SELECT pg_current_wal_lsn() AS current_lsn;
current_lsn
-------------
1/70009120
(1 row)Here I stopped any new writes on the primary and checked the current_lsn.
On the replica (now upgraded) server:
veryolddatabasethatimigratedsincepgversion9=# SELECT subname,
received_lsn,
latest_end_lsn,
latest_end_time
FROM pg_stat_subscription
WHERE subname = 'my_sub2';
subname | received_lsn | latest_end_lsn | latest_end_time
---------+--------------+----------------+-------------------------------
my_sub | 1/70009120 | 1/70009120 | 2025-05-18 18:51:02.166188+00
(1 row)
veryolddatabasethatimigratedsincepgversion9=# DROP SUBSCRIPTION my_sub;
NOTICE: dropped replication slot "my_sub" on publisher
DROP SUBSCRIPTION
SELECT pg_promote();Once the latest LSN has been sent to my PG18 instance I can now drop my subscription and failover the application traffic towards my new shiny upgraded instance and enjoy all its new features.
Conclusion :
In my demo, I didn’t use the new “–all” flag of pg_createsubscriber allowing to create a logical replication for all databases which is going to be real convenient for instance hosting a lot of databases.
As PostgreSQL 18 moves from Beta to GA, you can adapt this blueprint for any major version jump. Whether you’re on bare metal or in the cloud, blue-green migrations like this give you a safe way to ship critical updates with zero surprises and minimal downtime.
On large infrastructures, I would advice to script and use Ansible playbooks to automate those tasks but integrate health checks at each steps.
Additionally, involve your DEV team to run some tests after the pg_upgrade process to be sure to avoid any surprises on the app side and be aware of the mentioned limitations of logical replication.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2025/03/OBA_web-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/HER_web-min-scaled.jpg)