On Thu, Jun 25, 2015 at 2:42 PM, Dave Johansen <davejohansen@xxxxxxxxx> wrote:
On Thu, Jun 25, 2015 at 11:38 AM, John Scalia <jayknowsunix@xxxxxxxxx> 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
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
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.
