What is the best way to migrate & upgrade a extremely large postgreSQL server to another hardware whilst minimising downtime

Question

I am trying to migrate and upgrade an extremely large on-premise live postgreSQL to another hardware whilst minimising downtime.

Even trying to do a backup requires more than 1 day.

Points:

• On-Premise live PostgreSQL is 9; to upgrade to 11 on-premise on a new hardware.
• a few databases in v9, total size about 10TB.
• need to minimise downtime, once new 11 is up, to switch over as new live Prod. Otherwise, need to find way to load delta to the V11.

What is the best way to do this? Any advice.

Answer 1

Do not upgrade to any version below v14.

You can use streaming replication and switch-over to move the database to a different machine. Upgrade with pg_upgrade -k requires very little down time. Combine these two things in the order that makes most sense in your situation.