Thread

1. Cancelling "vacuum full" in single user mode?

   Colin 't Hart <colinthart@gmail.com> — 2023-10-02T12:39:59Z

   Hi,
   
   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?
   
   /Colin
   
   
   
   

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

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-02T13:59:41Z

   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