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

From: John Naylor <john(dot)naylor(at)enterprisedb(dot)com>
To: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>, "Bossart, Nathan" <bossartn(at)amazon(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie>, Andres Freund <andres(at)anarazel(dot)de>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: do only critical work during single-user vacuum?
Date: 2022-02-03 16:18:43
Message-ID: CAFBsxsGNQavwfbTP1UqRgCLZBDGHMwGLHuqd2_z3MkNRnPfFTw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Thinking further about the use of emergency mode, we have this:

"If for some reason autovacuum fails to clear old XIDs from a table,
the system will begin to emit warning messages like this when the
database's oldest XIDs reach forty million transactions from the
wraparound point:

WARNING: database "mydb" must be vacuumed within 39985967 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in
that database.
"

It seems people tend not to see these warnings if they didn't already
have some kind of monitoring which would prevent them from getting
here in the first place. But if they do, the hint should mention the
emergency option here, too. This puts Justin's idea upthread in a new
light -- if the admin does notice this warning, then emergency mode
should indeed vacuum the oldest tables first, since autovacuum is not
(yet) smart enough to do that. I'll pursue that as a follow-up.

--
John Naylor
EDB: http://www.enterprisedb.com

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Dagfinn Ilmari Mannsåker 2022-02-03 16:20:11 Re: Server-side base backup: why superuser, not pg_write_server_files?
Previous Message Tom Lane 2022-02-03 16:04:33 Re: libpq async duplicate error results