PostgreSQL 17: Convert a physical replica to a logical replica using pg_createsubscriber

Logical replication in PostgreSQL came with Version 10, which is already out of support. I’ve written about this a long time ago but the basic building blocks are still the same. Setting this up requires a publication on the source and a subscription on the target and a couple of parameters to bet set on both sides. Starting with PostgreSQL 16 you can setup a logical replication from a physical replica, which gives you more flexibility on how you want to distribute the load. You might want to follow this set of slides to get a general impression on how logical replication evolved over the years.

Starting with PostgreSQL 17 there’s the next evolution: Creating a logical replica out of a physical replica, or in other words: Converting a physical replica into a logical replica. Before we dive into that, let’s quickly look at what this tools solves.

When you setup a logical replication between a source and a target all the data needs to be initially copied from the source to the target. Depending on how large the tables in your setup are, this might quite take some time and the longer this process takes, the more WAL needs to be retained on the source for the replica to catch up once the initial data copy is done. With pg_createsubscriber you don’t need the initial data copy anymore, as this was already done when the physical replica was setup. You can take the physical replica as the starting point and transform it into a logical replica. The downside of this is, that the source and the target need to be on the same major version of PostgreSQL (which is obvious as physical replication cannot be done across major versions of PostgreSQL).

To see how this works lets start from scratch and create a brand new PostgreSQL 17 devel instance and prepare it for physical and logical replication:

Once we have that, we need a physical replica which is following this primary:

To confirm that logical replication is actually working later on, lets populate the primary with pgbench:

… and check if the physical replica got the data:

No it is time to convert the physical replica into a logical replica using pg_createsubscriber. The first step is to stop the replica:

In the next step you could either do a dryrun with pg_createsubscriber or directly go for it without a dryrun. Here is a dryrun:

Don’t count too much on the two “FATAL” messages when the walreiver was shutdown. Important is the exit code, and 0 means success.

Doing the same without the “dryrun” option:

There is a lot of output here but if you follow the lines you’ll see the process of converting the physical replica into a logical replica. Lets start it up and check what we’ve got:

We got the publication on the source and the subscription on the target, as expected:

Ongoing logical replication can easily be verified by adding a row into the source and checking the same row in the target:

This is really great stuff and all the credits go to the people involved with this.