Context : I have a legacy PostgreSQL database (currently 9.5 but can be any newer version) and a tons of script / program acceding it. All this script can do various operations on the table's row (READ, INSERT, UPDATE, DELETE).

Problem : I don't know which script do what on which table, and it become really problematic when I when to modify some table structure (like make the primary key a SERIAL and not a plain bigint with "SELECT MAX(id) + 1" to have the next id ...). I have also some "ninja" script that connect to the database using god know forsaken login/pwd, and for legacy reason, I can't just change the pwd.

What I want to do : I want to know which table are "used" by wich connection (I can know which program do which connection).

So far, I tried to apply a trigger on every table that I want to supervise. The main idea is to simply insert into a table "trace" that the "pg_backend_pid()" have do a "INSERT" into the table "foo" if the query "INSERT INTO foo (bar) VALUES (10);" But a trigger on a SELECT event doesn't exist, so it's not enough.

I activated all the logging that PostgreSQL 9.5 can offer

extract of postgresql.conf

- What to Log -

debug_print_parse = on

debug_print_rewritten = on

debug_print_plan = on

debug_pretty_print = on

log_checkpoints = on

log_connections = on

log_disconnections = on

log_duration = on

log_error_verbosity = terse

log_hostname = on

But I can't find a way to get what I want. There is the query plan that seem promising, but this "thing" is so frickkin' huge that I can't begin to understand how to read it (also, I don't find any documentation even is the EXPLAIN keyword documentation on how to read that !).

So I begin to be clueless. Is there a way to do it ? Is the query plan the good lead ?

  • It looks like a typical use case for the pgAudit extension. The oldest version it supports is 9.5. – Daniel Vérité Jun 14 '19 at 23:54
  • set log_min_duration_statement to 0 (or log_statment to all) and look in the logs. – Jasen Jun 17 '19 at 2:58
  • thanks you, I will look into both of your suggestion. – Tom's Jun 17 '19 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.