Re: database must be vacuumed with <N> transactions

The pg_stat_activity table will show you which processes are doing what,
and you'll be able to see their process ID from the O/S. That might help
you.

On Thu, Jun 25, 2015 at 11:30 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
wrote:

> On Thu, Jun 25, 2015 at 8:49 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
> wrote:
>
>> On Thu, Jun 25, 2015 at 8:49 AM, Dave Johansen <davejohansen(at)gmail(dot)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
>