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
Any ideas on how we could handle this with causing it to increment the XID?
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.
This probably isn't feasible, because we run a lot of queries with most of them being very short duration.
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.
We got it up and we will be turning this module on to help diagnose issues like this in the future.
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.
We are looking into upgrading to RHEL 7 which comes with Postgres 9.2 but that's a SLOW process.
