To: Natalie Wenz <nataliewenz@ebureau.com>
cc: pgsql-admin@postgresql.org
Subject: Re: Vacuum to prevent wraparound data loss 
In-reply-to: <0008A0CD-58B3-4FC8-9976-E5D2B41E5E82@ebureau.com> 
References: <8CC19810-BB3B-4511-9DC4-0C38AE120B79@ebureau.com>
	<0008A0CD-58B3-4FC8-9976-E5D2B41E5E82@ebureau.com>
Comments: In-reply-to Natalie Wenz <nataliewenz@ebureau.com>
	message dated "Thu, 08 Mar 2012 18:50:57 -0600"
Date: Fri, 09 Mar 2012 00:19:34 -0500
Message-ID: <20453.1331270374@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Natalie Wenz <nataliewenz@ebureau.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