Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux