Re: [GENERAL] Trigger based logging alternative to table_log
> From: Jeff Janes <jeff.ja...@gmail.com> > To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > Sent: Monday, 27 March 2017, 18:08 > Subject: [GENERAL] Trigger based logging alternative to table_log > > I have some code which uses table_log > (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to > selected tables. I don't use the restore part, just the logging part. > > It creates a new table for each table being logged, with several additional > columns, and adds triggers to insert rows in the new table for changes in the > original. > > The problem is that table_log hasn't been maintained in nearly 10 years, and > pgfoundry itself seems to have one foot in the grave and one on a banana peel. > >There are several other systems out there which store the data in hstore or >json, which I would probably use if doing this from scratch. But I'd rather >preserve the existing log tables than either throw away that data, or port it >over to a new format. > >Is there any better-maintained code out there which would be compatible with >the existing schema used by table_log? I was in exactly the same situation a few years ago. As you say ideally we'd move away from table_log - but when the users are used to doing things the table_log way and they like it... I have a slightly more up to date fork (here: https://github.com/glynastill/pg_table_audit), which as I recall works fine with 9.6. In general the whole thing would benefit an overhaul, but I think the effort of moving to a better format would be less. I also wrote a pl/pgsql version as mentioned by Felix, but I wasn't ever particularly happy it so stuck with the above fork with the intention of switching away to a json format eventually. Glyn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger based logging alternative to table_log
Jeff Janes schrieb am 27.03.2017 um 19:07: I have some code which uses table_log (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to selected tables. I don't use the restore part, just the logging part. It creates a new table for each table being logged, with several additional columns, and adds triggers to insert rows in the new table for changes in the original. The problem is that table_log hasn't been maintained in nearly 10 years, and pgfoundry itself seems to have one foot in the grave and one on a banana peel. There are several other systems out there which store the data in hstore or json, which I would probably use if doing this from scratch. But I'd rather preserve the existing log tables than either throw away that data, or port it over to a new format. Is there any better-maintained code out there which would be compatible with the existing schema used by table_log? Logical replication maybe? I don't know which programming language you are using, but the JDBC driver has a nice example: https://jdbc.postgresql.org/documentation/head/replication.html The upside to a trigger based solution is, that it's faster. The downside is, that the replication "consumer" has to be running to ensure the logging -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger based logging alternative to table_log
> I have some code which uses table_log > (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to > selected tables. > I don't use the restore part, just the logging part. > It creates a new table for each table being logged, with several additional > columns, and adds triggers to insert rows in the new table for changes in the > original. > The problem is that table_log hasn't been maintained in nearly 10 years, and > pgfoundry itself seems to have one foot in the grave and one on a banana peel. > There are several other systems out there which store the data in hstore or > json, which I would probably use if doing this from scratch. But I'd rather > preserve the existing log tables than either throw away that data, or port it > over to a new format. > Is there any better-maintained code out there which would be compatible with > the existing schema used by table_log? >Cheers, >Jeff Afaik, there is no compatible solution. If tablelog works for you then keep it. Do you miss a feature or why do you worry about the unmaintained code base? I think, if there would be a problem with any new version that the developer would fix it. There is also an existing github repo (https://github.com/glynastill/table_log_pl). Recently, I have done a comparison of different audit tools to check how good my creation (pgMemento) works compared to the others. So I know how most of them work. tablelog for example logs both OLD and NEW. So you got all your data twice. Other solutions log either OLD of NEW. tablelog uses only one timestamp field whereas others using two (or a range). As tablelog is using history tables with relational layout I would suggest to consider other extensions that do a similar thing. If you are interested in only logging the data you might check out the temporal_tables extension (http://pgxn.org/dist/temporal_tables/). In my test it had the least impact to write operations and disk consumption. Using hstore or json for logging might sound cool in the first place, but it only has its benefits if you don't want to adapt the auditing behaviour to schema changes (like new columns etc.). With pgMemento I decided to go for jsonb but after many hours of programming complex restoring functions I can say that my only real argument of using it now, is that I only log values of changed fields. I like that but it makes the trigger overhead bigger. Greetings from Berlin Felix -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger based logging alternative to table_log
I have some code which uses table_log ( http://pgfoundry.org/projects/tablelog/) to keep a log of changes to selected tables. I don't use the restore part, just the logging part. It creates a new table for each table being logged, with several additional columns, and adds triggers to insert rows in the new table for changes in the original. The problem is that table_log hasn't been maintained in nearly 10 years, and pgfoundry itself seems to have one foot in the grave and one on a banana peel. There are several other systems out there which store the data in hstore or json, which I would probably use if doing this from scratch. But I'd rather preserve the existing log tables than either throw away that data, or port it over to a new format. Is there any better-maintained code out there which would be compatible with the existing schema used by table_log? Cheers, Jeff
