On 07/27/2018 04:43 PM, from_postg...@safetyphil.com wrote:
We have been using our own trigger-based audit system at my firm
successfully for some years, but the performance penalty is
starting to grate a bit and so I have been tasked with seeing if
we can make use of the new logical decoding functions to achieve
the same thing. I thought that someone must already have written
something that would satisfy our use-case but my internet
searches have come up short so far so I am considering writing a
logical decoding plugin to do what we want.
Have you checked pgaudit [1]? I haven't checked if it matches all
your requirements, but considering it's an extension aimed at
auditing use cases it might. And it's already available, of
course.
Actually no, I hadn't come across this before, thanks for the heads
up. It is important for us to be able to get the audit data back into
a different database, but it looks like I could scrape the logs and
do that. At the very least it shows that it is possible to hook into
postgres in the right places to emit extra logical messages if that
turns out to be the better way to do it.
It doesn't appear to support application_name, nor the connection
details of the user doing the update - but perhaps that would be just
as sensible to add it here than in a logical output plugin.
It's a bit tricky coming to a big codebase like postgres and trying
to decide the best route of doing something; I don't have much of a
mental model about how complicated the various systems are :)
It may not support everything, but in general when a feature can be done
in an extension, it's generally faster to deliver that way. One of the
reasons is the ad hoc release cycle (compared to the 1-per-year cycle of
PostgreSQL). So I'd suggest investigating this option first.
It may not meet all your requirements immediately (or at all), but well,
no solution probably will.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
