From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: vacuum to prevent wraparound |
Date: | 2023-06-06 16:52:15 |
Message-ID: | f853768ada725f311efc72fd15a6efd918d429f1.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 2023-06-06 at 11:36 -0300, Marcos Pegoraro wrote:
> I have a replica server using Postgres 14.4.
> Replica is done using Publication/Subscription and I have triggers for auditing,
> so every record which comes from production is audited.
> Some months ago I changed the way I was auditing and replaced that audit table
> for a new one, but didn't remove the old table. So I have both, new (AUDIT) and
> old (SYS_AUDIT) tables.
> Then last night I received this message that to prevent wraparound postgres would
> do an aggressive vacuum on (SYS_AUDIT), and that took several hours to complete,
> in a table that is not used for 6 or 8 months. Why ?
Because otherwise the rows in that table would suffer data corruption at some point.
See https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
PostgreSQL doesn't know what you don't need these data any more.
> I know it would be good to run vacuum on that table, it has lots of dead tuples,
> but that table is not used anymore, so why vacuum it ?
This is not about dead tuples at all.
> I have to drop immediately that huge table that is not used anymore because it can
> stop the server to prevent a wraparound some day ?
No, you don't have to drop it.
If you know that the table will not change any more, you can run
VACUUM (FREEZE) tablename;
That will be as intense as the anti-wraparound autovacuum that caused your concern,
but afterwards you will never again have a long-running, intense autovacuum run on
that table.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Stephanie Goulet | 2023-06-06 17:36:01 | Fwd: No prompt for setting up a master password |
Previous Message | Adrian Klaver | 2023-06-06 14:50:35 | Re: No prompt for setting up a master password |