Migrate Postgres 9.4.5 to 11.1

Question

Looking for a way to migrate Postgres 9.4.5 to the latest version 11.1 with minimum possible downtime.

What we have is:

• Database size - 1.2TB, total # of rows > 4B
• (primary) Postgres 9.4.5 running on AWS (self-managed), this is a primary instance.
• (secondary standby) Secondary instance replicated using Streaming Replication
• Regular disk snapshots and WAL archiving
• Daily logical backups (pg_dump)

We tested two options so far: pg_dumpall/pg_restore and pg_upgrade, both options work ok but take more than 48hr to finish.

According to the documentation https://www.postgresql.org/docs/current/upgrading.html#UPGRADING-VIA-REPLICATION there is also Logical Replication option. However, it is my understanding that 9.4 only has a Logical Decoding (not the replication). So this option is not available in our case, correct?

Are there any other options to migrate to v11 with a minimum downtime as possible?

Answer 1

Update: I have successfully upgraded 9.4.20 to 11.5 without any issues.

But can we just rely on belief? In my case application have good test coverage and I have reviwed release notes for breaking changes.

In general for any significant part of system I prefer multi-step upgrades from major to major releases.

For 9.4.x to 11.6 (by today on AWS) it like 4 steps (at least in development): * 9.5.20 * 9.6.16 * 10.11 * 11.6

Every step should be performed with respect of certain Release Notes and warnings during application testing.

Except pg_upgrade it is possible to use replication.

Answer 2

I believe that pg_upgrade is your best option. You don't mention anything specifically that should present a problem. Logical backups are still supported.