Re: Vacuum to prevent wraparound data loss

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Natalie Wenz <nataliewenz(at)ebureau(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Vacuum to prevent wraparound data loss
Date: 2012-03-09 05:19:34
Message-ID: 20453.1331270374@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Natalie Wenz <nataliewenz(at)ebureau(dot)com> writes:
> [ waiting for autovacuum to deal with an impending XID wraparound ]

> My first question is: Is it even possible for this vacuum to finish? It began before the database stopped accepting connections, but now that it's in this state, will the database allow the vacuum to complete? Can the vacuum successfully freeze old xids and advance the datfrozenxid? I suspect no, based on

Yes. yes, and yes. But ... the problem with letting autovacuum do this
is that it's intentionally rate-limited so that it doesn't interfere
with foreground query processing. Since you are waiting around for it
to finish before you can use the DB at all, this is a bad thing.

I think it might be possible to disable the autovacuum cost limit
settings in postgresql.conf and SIGHUP the postmaster to make that
update take effect ... but I'm not at all sure that the autovac worker
would notice the changes until it finished the table it's working on.
Try it, but if you don't see either CPU or I/O usage maxed out within a
minute or so, the best bet is likely to kill the autovac worker (with
SIGINT) and let a new one start. Doing that will lose some fraction of
the work done so far, so don't do it unnecessarily --- but if the
machine is just sitting there 90% idle, you need to do something.

regards, tom lane

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message umashankar narayanan 2012-03-09 16:02:41 Re: Postgres server crashing unexpectedly.
Previous Message Natalie Wenz 2012-03-09 00:50:57 Vacuum to prevent wraparound data loss