Re: Speeding up pg_upgrade

On Tue, Dec 5, 2017 at 09:16:02AM -0500, Stephen Frost wrote:
> Bruce,
>
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> > As part of PGConf.Asia 2017 in Tokyo, we had an unconference topic about
> > zero-downtime upgrades. After the usual discussion of using logical
> > replication, Slony, and perhaps having the server be able to read old
> > and new system catalogs, we discussed speeding up pg_upgrade.
>
> Sounds familiar.

Yeah. :-|

> > There are clusters that take a long time to dump the schema from the old
> > cluster and recreate it in the new cluster. One idea of speeding up
> > pg_upgrade would be to allow pg_upgrade to be run in two stages:
> >
> > 1. prevent system catalog changes while the old cluster is running, and
> > dump the old cluster's schema and restore it in the new cluster
> >
> > 2. shut down the old cluster and copy/link the data files
>
> Perhaps a bit more complicated, but couldn't we copy/link while the
> old cluster is online and in backup mode, finish backup mode, shut down
> the old cluster, and then play forward the WAL to catch any relation
> extents being added or similar, and then flip to the new PG version?

Well, that would require reading the old WAL, which would add an
additional compibility requirement that seems unwise.

> > My question is whether the schema dump/restore is time-consuming enough
> > to warrant this optional more complex API, and whether people would
> > support adding a server setting that prevented all system table changes?
>
> When you say 'system table changes', you're referring to basically all
> DDL, right? Just wish to clarify as there might be some confusion
> between the terminology you're using here and allow_system_table_mods.

Not only all DDL, but even updating them for the internal stuff, like
pg_class.relfrozenxid.

> Would we need to have autovacuum shut down too..?

Yes.

> The other concern is if there's changes made to the catalogs by non-DDL
> activity that needs to be addressed too (logical replication?); nothing
> definite springs to mind off-hand for me, but perhaps others will think
> of things.

Yes, it could extend to many parts of the system, which is why I am
asking if it is worth it.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +