Re: Patch for pg_upgrade to turn off autovacuum

Bruce Momjian wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > > I thought some more about this and I don't want autovacuum to run on the
> > > > old server. This is because pg_dumpall --binary-upgrade --schema-only
> > > > grabs the datfrozenxid for all the databases at the start, then connects
> > > > to each database to gets the relfrozenxids. I don't want to risk any
> > > > advancement of either of those during the pg_dumpall run.
> > >
> > > Why? It doesn't really matter --- if you grab a value that is older
> > > than the latest, it's still valid. As Robert said, you're
> > > over-engineering this, and thereby introducing potential failure modes,
> > > for no gain.
> >
> > Uh, I am kind of paranoid about pg_upgrade because it is trying to do
> > something Postgres was never designed to do. I am a little worried that
> > we would be assuming that pg_dumpall always does the datfrozenxid first
> > and if we ever did it last we would have relfrozenxids before the
> > datfrozenxid. I am worried if we don't prevent autovacuum on the old
> > server that pg_upgrade will be more fragile to changes in other parts of
> > the system.
>
> Hold, I overstated the fragility issue above. I now realize that the
> old system is not going to change and that I only need to worry about
> future changes, where are handled by the new -b flag, so maybe we can
> get away with only stopping autovacuum on the new server, but I would
> need someone to verify that, and this would be a change in the way 9.0
> pg_upgrade operated because it did disable autovacuum on the old and new
> servers with 99.9% reliability.

Well, having seen no replies, I am going to apply the version of the
patch in a few days that keeps the old vacuum-disable behavior for older
releases, and uses the -b flag for newer ones by testing the catalog
version, e.g.:

snprintf(cmd, sizeof(cmd),
SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "
"-o \"-p %d %s\" start >> \"%s\" 2>&1" SYSTEMQUOTE,
bindir, output_filename, datadir, port,
(cluster->controldata.cat_ver >=
BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? "-b" :
"-c autovacuum=off -c autovacuum_freeze_max_age=2000000000",
log_opts.filename);

I know people like that pg_upgrade doesn't care much about what version
it is running on, but it is really the ability of pg_upgrade to ignore
changes made to the server that is really why pg_upgrade is useful, and
this change makes pg_upgrade even more immune to such changes.

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

+ It's impossible for everything to be true. +