From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Colin 't Hart <colinthart@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: Cancelling "vacuum full" in single user mode?
Date: Mon, 02 Oct 2023 15:59:41 +0200
Message-ID: <584a8f4d0effccd69b9ed83248cd432bd08a7bc2.camel@cybertec.at> (raw)
In-Reply-To: <CAMon-aT_uh7ZFsQwHFbMmL+iYMeBbJaprxKVWWcHs9MT-PXBJQ@mail.gmail.com>
References: <CAMon-aT_uh7ZFsQwHFbMmL+iYMeBbJaprxKVWWcHs9MT-PXBJQ@mail.gmail.com>
On Mon, 2023-10-02 at 14:39 +0200, Colin 't Hart wrote:
> I have a customer approaching transaction wraparound, about 3million
> transaction IDs away at the moment.
> Postgres 9.5 (yes, I know...)
>
> Somewhat mislead by the message to vacuum the database in single user
> mode, they are now in single user mode and are running "vacuum full"
> on the "worst" database, which has 18.5 million tables, and is about
> 350GB on disk.
>
> Now we want to cancel the "vacuum full" and run a "vacuum freeze"
> instead... or preferably, start back up in multiuser mode and run
> vacuum there, but that was running very slowly while autovacuum was
> trying to run (so we would need to disable that too, for now).
>
> So,
>
> 1. Can we cancel safely?
> 2. Any other suggestions? Upgrade to a newer version right now?
VACUUM (FULL) is exactly the wrong thing to do, so yes, cancel that.
Normally you don't need single-user mode, since VACUUM does not consume
a transaction ID.
I'd run
SELECT oid::regclass AS table_name, age(relfrozenxid) AS oldest_unfrozen_row
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC;
And VACUUM the tables (FREEZE is not necessary) starting with the top ones
on the list.
Upgrade as soon as possible. Not only are you missing out on tons of fixed
bugs, but VACUUM performance and smartness has greatly improved since 9.5.
Yours,
Laurenz Albe
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: laurenz.albe@cybertec.at, colinthart@gmail.com
Subject: Re: Cancelling "vacuum full" in single user mode?
In-Reply-To: <584a8f4d0effccd69b9ed83248cd432bd08a7bc2.camel@cybertec.at>
References: <CAMon-aT_uh7ZFsQwHFbMmL+iYMeBbJaprxKVWWcHs9MT-PXBJQ@mail.gmail.com> <584a8f4d0effccd69b9ed83248cd432bd08a7bc2.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