Postgres major version upgrade using replication server

Question

I am planning to upgrade my database from Postgres 10 to Posgres 14. To reduce the downtime, I plan to use the replication for the same. Plan (in short) is to:

1. Dump the data using pg_dumpall
2. Kill the Postgres service running on replication
3. Setup new Postgres 14 service
4. Restore the data from the dump created in Step 1
5. Setup logical replication with the primary to get the data up to date
6. Make replication the primary, and applications start connecting to the replication.
7. Kill the Postgres service running on the old primary, and upgrade it too to 14.
8. Setup streaming replication between the new primary (old replication) and new replication.

I have a few questions regarding this:

1. Is it advisable to take a dump and restore, or let logical replication handle complete replication (apart from the DDL's) from scratch? In terms of time that it could take, which would be the better option? We have close to 650GB of data. I would think the dump is compressed, but restoring them (on the same machine) could still take time I believe.
2. Is there something I need to setup to make sure that the replication starts from after the dump point or will it be taken care?
3. One limitation I see with the logical replication is that it does not replicate sequences. For this I believe, before the switch over - I could increment the sequence by a very large number to make sure that there are no conflicts?
4. I have also seen an extension pglogical that I could use for the setup. Although, I am yet to dig deeper. From your experience, does it give any added advantages \ would it make the setup easier?
5. Although I would think it depends on different parameters, how long it could take typically to complete the restore\replication for a database of this much size, until the primary and master are in sync?

• user1822: Did you consider pg_upgrade using the --link option? That will be quite fast independently of the size of the instance
• user1583803: We did, and was our initial plan. But it doesn't seem to be very straightforward to use it for upgrading postgres running on docker containers and couldn't find a reliable guide to do it.
• user1822: I never run Postgres (or any production database) in docker, so running pg_upgrade is an extremely convenient and easy way to upgrade.

Answer 1

Ad 1 and 2:

The easy thing would be not to copy the data yourself, but let logical replication handle that.

If you insist in using dump/restore, you will have to proceed like this:

• create a logical replication slot with the CREATE_REPLICATION_SLOT command and the pgoutput plugin in a replication connection, which will export a snapshot

• while the replication connection is still open, run pg_dump and pass the snapshot name with the --snapshot option

• use that replication slot for logical replication

Otherwise, replication will not start at the right point.

Ad 3:

Setting the sequences to a high value is fine.

Ad 4:

I have no opinion there

Ad 5:

Nobody can predict that. You will have to do a test run.

• user1583803: I wanted to dump and restore the initial data. And then let logical replication replicate rest of the data including the changes made on primary before replication was started. So just trying to make speed up the replication, not having it replicate everything but only what's required after the point of dump.
• Laurenz Albe: I see. I have added instructions how this must be done to make it work properly.