Re: database must be vacuumed with transactions - Mailing list pgsql-admin

On Thu, Jun 25, 2015 at 8:49 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Thu, Jun 25, 2015 at 8:49 AM, Dave Johansen <davejohansen@gmail.com> wrote:

http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

The database I work on started spitting out the following message last night:
WARNING:  database "ops" must be vacuumed within 9361597 transaction

I ran VACUUM as soon as I got in, but then shortly after, it stopped accepting transactions. I stopped the database and opened single user mode to run VACUUM. So my questions are:

1) How long should I expect this to take (database has about 5 TB of data)?

2) Is there a faster way to make the database be operational again?

Sorry for the multiple emails, but I forgot to mention that I'm using 8.4 on RHEL 6.4.

Once the VACUUM was started to get the database back online, we did some diagnostics and it appears that there was a VERY large number of transactions done in the last few weeks and that was the source of this problem. It appears that there's a txid_current() function ( http://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-TXID-SNAPSHOT ), but is there a way to get the XID for all open connections so we can find the offending connection and fix the issue so this won't happen again?

Thanks,

Dave