On 28 July 2016 at 04:35, Bruce Momjian <br...@momjian.us> wrote: > On Fri, Jul 8, 2016 at 12:18:28AM +0100, Simon Riggs wrote: > > On 7 July 2016 at 21:10, Robert Haas <robertmh...@gmail.com> wrote: > > > > pg_upgrade does that, kinda. I'd like to have something better, but > > in the absence of that, I think it's quite wrong to think about > > deprecating it, even if we had logical replication fully integrated > > into core today. Which we by no means do. > > > > I don't see any problem with extending pg_upgrade to use logical > replication > > features under the covers. > > > > It seems very smooth to be able to just say > > > > pg_upgrade --online > > > > and then specify whatever other parameters that requires. > > > > It would be much easier to separate out that as a use-case so we can be > sure we > > get that in 10.0, even if nothing else lands. > > Uh, while "pg_upgrade --online" looks cool, I am not sure a solution > based on logical replication would share _any_ code with the existing > pg_upgrade tool, so it seems best to use another binary for this. >
It might, actually. One approach for online upgrade is to: * pg_basebackup the master * start the replica and let it catch up * create a logical replication slot on the master * replace the replication.conf on the basebackup so it stops recovery at the lsn of the replication slot's confirmed_flush_lsn * stop the replica and pg_upgrade it * have the upgraded replica, now a master, replay from the old master over logical replication * once caught up, switch over This means a full dump and reload with a full rebuild of all indexes, etc, isn't needed. All shared catalog stuff is copied (until we switch to logical rep for the final catch-up). I guess we could use the pg_dump/pg_restore pg_upgrade code to create > the objects, and use logical replication to copy the rows, but what does > this gain us that pg_dump/pg_restore doesn't? A consistent switch-over point, where the upgrade can happen while the master is still writing. We create a slot, dump from the slot's exported snapshot, and switch over to logical replication consistently at the end of the dump. That's pretty much what BDR and pglogical do. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
