Currently my db has a couple of triggers for some (not all) tables that get executed when a specific number of columns has been updated. I keep track if the triggers for those rows on a boolean column in the tables.

Id like to be able to log UPDATE and INSERT queries and the errors/warnings coming from them onto different files in my postgresql server as a singular log file can get quite big.

Ive thought of a couple of approaches to this problem and am looking for a better approach than these;

  • have the triggers (plpython3) create seperate log files when they only get called (not a lot but they insert millions of rows onto different tables.

  • write additional triggers that insert logging information onto another table (either by explicit means or a single JSON column)

thank you for your time and answers!

1 Answer 1


There is only a single PostgreSQL log file per database cluster, so you cannot have that out of the box.

Adding triggers that log to a file is an option, but a trigger (and the logging itself) can add significant performance overhead, certainly more than the PostgreSQL logging itself.

The route I would explore is this:

  • Try to perform the actions you want to log with a different database user. If that is not easy, be creative: perhaps you can execute SET ROLE/RESET ROLE commands at strategic points in debug mode to become a different user.

  • Enable logging for that user only:

    ALTER ROLE myuser SET log_min_duration_statement = 0;

Then it will be easier to evaluate the log, because it will be smaller.

  • Aha thanks, thats a creative solution, Ill try to make something like this happen and if all else fails fallback to writing a table but doing that Id definitely need to use UUIDs and/or md5 hashing of some sort, which might potentially be a question for another time !
    – Eiron
    May 29, 2019 at 14:25
  • I was looking at this question which led me here. Is there not a way of (at least partially) doing what the OPs want to do using the log_line_prefix (string) which seems to have a large number of parameters which may be of use?
    – Vérace
    Apr 17, 2022 at 13:34
  • @Vérace You can configure the log format with that parameter, but not the log file. Of course you can use tools like grep to extract specific Information, but that's not what OP asked. Apr 19, 2022 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.