pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (3+ messages)

Message-ID: <584a8f4d0effccd69b9ed83248cd432bd08a7bc2.camel@cybertec.at>
Permalink:  ../584a8f4d0effccd69b9ed83248cd432bd08a7bc2.camel@cybertec.at/
Also on:    postgresql.org/message-id/584a8f4d0effccd69b9ed83248cd432bd08a7bc2.camel@cybertec.at

 · 

reply

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