Upgrading Postgres major version using logical replication

Question

We have considered some options to upgrade our Postgres servers from the current (10) version to the latest (14). We decided no to do on the primary - since we need to do with the link option (so in place, not copying) and we cannot rollback if we see issues after startup of the new postgres server. And we consider it to be safer to do on the replication first.

So we are planning to do the upgrade using pg_upgrade on the replication. This is what we have planned so far:

1. Setup logical replication between the primary and replication (so change from the current streaming replication)
2. Once replication is completed and they are in sync, stop the replication postgres server - running Postgres 10.
3. Perform pg_upgrade on the replication and then start it up (logical replication will continue)
4. Once the replication is up to date, switch the replication to primary and primary to replication.
5. Now, all the clients will connect to the replication which is on Postgres 14
6. Carry out the same steps for the new replication\old primary (stop, pg_upgrade, start and finally switch back again)

I have some questions though before I proceed with this:

1. Is it possible to move from streaming replication to logical replication mechanism before the upgrade? Are there anything to consider before doing this?
2. We were thinking of keeping the replication mechanism as logical even post upgrade. Is this advisable?
3. How long is it expected to take, for the pg_upgrade action to complete? I understand it depends on various parameters, but any rough estimation based on the fact that the data size is around 600 GB? As that's the downtime expected for replication - so without a backup.
4. Is this a good strategy - or is there a better (safe, minimal downtime) option?

Looking for some guidance on these questions before we proceed with the upgrade.

Answer 1

Not everything is replicated with logical replication: only INSERT, UPDATE and DELETE (from v11 on also TRUNCATE). So it is definitely not a substitute for streaming replication. Also, streaming replication performs better.

Switching from streaming replication to logical replication is possible with little down time if you suspend the application on the primary, wait until the standby has caught up, then promote the standby and create the subscription with copy_data = FALSE.

pg_upgrade with the --link option at most takes a few minutes, since only the metadata are migrated.

You should consider following the instructions to upgrade both servers with pg_upgrade - the down time will just be a little longer, and it is simpler.

• Paul White: Comments moved to chat.

Answer 2

You can try this pg_upgrade2o. It is a more optimized option. pg_upgrade2o is tested on clone but you can also try link or clone. https://github.com/wasiualhasib/pg_upgrade2o