| I upgraded from 9.3 → 11.2 a few months ago using pg_upgrade[1], on a master+slave database with 150GB of data. I did a fair amount of testing, but the final procedure was very fast and smooth. 1. Test the upgrade: set up an additional secondary (9.3), break the replication link (promote it to a master). Test the upgrade on that. It was really fast, under 30 seconds to shut down the old DB, run the in-place upgrade, and start up the new DB. 2a. In production: set up an additional secondary (9.3). Make the primary read-only. Promote the new secondary to a master. Shut down, upgrade to 11.2, restart. Point applications at it. 2b. Backout plan: leave the applications pointing at the original database server, make it read-write. There are other options, including with only seconds of downtime, but <1 minute with pg_upgrade was simple and very acceptable for us. [1] https://www.postgresql.org/docs/current/pgupgrade.html [2] https://www.postgresql.org/docs/current/upgrading.html |
Consider the situation when you're adding thousands of new records per seconds, and the database is being used every second (quite literally: to compute per seconds statistics).
A better solution is to have triggers on the old master, to do the same inserts on the new master (after copying the data/promoting a replica/whatever), and have similar triggers on the new master when the IP is not the old master (to be able to backout to the old server)
Then both the new and the old master run "in parallel", with the same data, and you can have the apps use the new server (on a new domain name, new ip, new port, whatever) when you want - on a app by app basis if you want. You can keep both until you decide to decommission the old master.