Re: Cancelling "vacuum full" in single user mode?

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