Re: database must be vacuumed with <N> transactions

On Thu, Jun 25, 2015 at 2:42 PM, Dave Johansen <davejohansen(at)gmail(dot)com>
wrote:

> On Thu, Jun 25, 2015 at 11:38 AM, John Scalia <jayknowsunix(at)gmail(dot)com>
> wrote:
>
>> 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.
>>
>
> Yes, but I need to see which process ID is doing a TON of small
> transactions which is causing the XID to increment at an unexpectedly high
> rate and so my question is "if there's a way for me to get the XID for
> every connection/query?".
>

Look at the log_line_prefix options. To get everything you're looking for
to try and identify where this is coming from look at using at a minimum: %p,
%r, %m, %x, %u, %d

http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#GUC-LOG-LINE-PREFIX

You'll also have to make sure log_min_duration_statement is set to low
enough to log the queries you're looking for. Beware setting disabling it
or setting it too low, though, if you have a very high number of queries
being run. This can quickly overload your IO and disk space.

If you get the database running again, you can also look at the
pg_stat_statements contrib module to log query counts and parameterized
versions of all queries being run.

http://www.postgresql.org/docs/8.4/static/pgstatstatements.html

And I can't let this email go without say you REALLY need to look at
upgrading your database. 8.4 is no longer being supported and receives no
security or bug fixes.