Re: Upgrading from Postgresql 9.1 to 10

On Wed, Feb 14, 2018 at 07:47:55AM -0700, David G. Johnston wrote:
> Zero downtime is only possible by standing up a hot-standby then failing
> over to it. Same-server upgrade you can do via pg_upgrade but it does
> involve downtime. There are lots of material and options online, including
> the docs, for setting up hot-standby replication.

PostgreSQL 10 adds logical replication which is able to handle this
scenario with close to zero downtime if you use it with synchronous
replication. So with 9.1 as origin server you cannot use that. Logical
decoding facilities being added in 9.4, it could be possible to upgrade
at least from this version using 2nd Quadrant's pglogical (correct me
others here if I am incorrect!).

Now, for older versions, the usual way to do things is by using Slony
which does trigger-based replication. This can help you reach close to
no downtime, way lower than pg_upgrade for example even if you use its
--link mode. pg_upgrade --link can work very quickly as well, so if you
care about being close to zero you may want to consider it.
--
Michael