On May 12, 2016 17:36, "Bruce Momjian" <bruce@momjian.us> wrote:
>
> On Tue, May 10, 2016 at 05:21:24PM -0400, Bruce Momjian wrote:
> > If we are going to focus on update method _change_ rather than just
> > upgrade _breakage_, the inclusion of pg_logical in Postgres core would
> > be a reason to go to 10.0 because it allows zero-downtime upgrades. I
> > think this would be larger upgrade-wise than anything in 9.6.
> >
> > Currently users are using high-overhead trigger-based replication to
> > achieve zero-downtime upgrades, and using streaming replication with
> > pg_logical would be a game-changer.
>
> I would like to clarify something I said above.
>
> In a master/slave setup with pg_logical, a major upgrade is _near_
> zero-downtime, because you have to switch over all write transactions at
> a single point in time when you promote the slave to master. So you
> have to either prevent new write transactions from going to the slave
> while you wait for the master transactions to finish, or (more likely)
> you have to terminate the write transactions on the master and then
> promote the slave to master and allow everything to reconnect.
>
> (In practice, you can't change a read/write server to read-only without
> a restart, so effectively all old-master transactions have to be drained
> at some point.)
You can make it closer to, or completely zero, if you combine it with pgbouncer in transaction pooling mode. There will be a performance hiccup, but it should work.
/Magnus