From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Loles <lolesft@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Avoid Wraparound Failures
Date: Sat, 26 Mar 2022 05:34:14 +0100
Message-ID: <965a52fe849ec6e5759d349b4063b5077353f24b.camel@cybertec.at> (raw)
In-Reply-To: <CA+FWGK6HmRx8=-2wZkw-9a6a36f6iEGgAH=bN1rNicbFgzas5A@mail.gmail.com>
References: <CA+FWGK6HmRx8=-2wZkw-9a6a36f6iEGgAH=bN1rNicbFgzas5A@mail.gmail.com>
On Fri, 2022-03-25 at 23:16 +0100, Loles wrote:
> Suppose the databases on my instance are near to have a wraparound failure.
>
> (I think so, from what I see, but in the PostgreSQL log I haven't seen any warning about It yet).
>
> What do I have to do?
>
> vacuum freeze;
>
> better than,
>
> vacuum analyze;
>
> Or both?
>
> If the autovacuum_freeze configuration parameters have defaults values, should I modify any first?
>
> More I read of this topic, more confused I am.
>
> Please, I need simple and wise advice :)
DON'T PANIC
If what you see is the age of your oldest unfrozen rows approaching 200 million,
everything is just normal. That's when anti-wraparound autovacuum *begins*.
Normally, you have nothing to do, except perhaps make sure than autovacuum is
fast enough (autovacuum_vacuum_cost_delay = 2).
If you want to prevent that autovacuum run from happening while your system is
busy (which normally also is no problem), you can trigger a manual VACUUM at
a time of lower database activity. But make it a plain VACUUM, not a
VACUUM (FREEZE) or (god forbid) VACUUM (FULL), and only VACUUM those tables
that are large and approaching the threshold. And don't VACUUM them all at the
same time.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, lolesft@gmail.com
Subject: Re: Avoid Wraparound Failures
In-Reply-To: <965a52fe849ec6e5759d349b4063b5077353f24b.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox