Re: do only critical work during single-user vacuum?

On Thu, Dec 9, 2021 at 5:12 PM Bossart, Nathan <bossartn(at)amazon(dot)com> wrote:
> As Andres noted, such a feature might be useful during normal
> operation, too. Perhaps the vacuumdb --min-xid-age stuff should be
> moved to a new VACUUM option.

I was thinking of something like pg_import_system_collations() for
this: a function that's built-in, and can be called in single user
mode, that nevertheless doesn't make any assumptions about how it may
be called. Nothing stops a superuser from calling
pg_import_system_collations() themselves, outside of initdb. That
isn't particularly common, but it works in the way you'd expect it to
work. It's easy to test.

I imagine that this new function (to handle maintenance tasks in the
event of a wraparound emergency) would output information about its
progress. For example, it would make an up-front decision about which
tables needed to be vacuumed in order for the current DB's
datfrozenxid to be sufficiently new, before it started anything (with
handling for edge-cases with many tables, perhaps). It might also show
the size of each table, and show another line for each table that has
been processed so far, as a rudimentary progress indicator.

We could still have a separate option for the postgres executable,
just to invoke single-user mode and call this function. It would
mostly just be window dressing, of course, but that still seems
useful.

--
Peter Geoghegan