Postgres replication part 1 (of potentially 1)

As is often the case when dealing with servers, there are outdated versions. The reason is almost always the same: there wasn't enough time to keep things up to date.

It makes sense — sticking to the LTS of anything is hard work. You have a working system, and even if it's a fairly uncomplicated beast, updating dependencies to a new version is rarely simple. It might seem like everything is fine, but then on a Saturday at 01:30, things start breaking down because that once- in-a-blue-moon event without a unit test executes — and it depends on that one deprecated functionality nobody really needed.

In this case, it was Postgres. Postgres 9.6, to be exact. It's been quite a while since it was supported. The reason makes sense: 10 came around, nothing new was needed, everything worked as it should, and other new features had higher priority. Then 11, 12, and 13 came out, and an upgrade was no longer simple because there are extensions and upgrading might break everything. Also, the storage has changed, so who knows if PostGIS will make all the data go bad.

But then, at some point, there's an update that makes everything faster, and the load gets really, really bad. So, what do you do?

You can actually start replication from 9.6 into 17 without any problems. The problem is that when you want to read the data, it'll tell you there's a mismatch of PG versions. It makes complete sense, but it'd also be nice if PG just said something along the lines of: "Hey, you're about to read X TB of data from a legacy system. I can do that, but once it's done, I can't do anything with the data." Instead, it'll tell you after you've synced X TB of data that there's a version incompatibility — and sorry, but it can't start.

In PG's defense, they're not claiming it'll work, and it's not really expected. But when you're tasked with delivering a PG 17 version of PG 9.6 data, you might not have the time to read all the documentation. Instead, you'll discover it after having wasted time setting up replication, copying over the data, and then trying to start things. So, not really a spoiler — but just don't do it. The answer is: it won't work. You can't use built-in replication between major versions.

# max replication clients allowed https://postgresqlco.nf/doc/en/param/max_replication_slots/
max_replication_slots = 10
# Maximum number of simultaneously running WAL sender processes https://postgresqlco.nf/doc/en/param/max_wal_senders/
max_wal_senders = 10

# in the hba configuration
host    replication     replicator      1.2.3.4/24        md5

until pg_basebackup --pgdata=/var/lib/postgresql/data -R --slot='my_replication_test' -h localhost -p 5435 -U user -X stream -P
do
    echo "Waiting for primary to connect..."
    sleep 1
done

• Postgres
• Docker
• Investigation