Thread: Audit trail
Hi all, I am wondering if anyone has any good solutions for having postgres leave an audit trail. Specifically I would like it to copy rows to a ghost table without constraints every time a row is modified, deleted etc. I don't really want to have to do the work of creating said ghost tables, but if that's the only way, then that's ok. I know about Audit Trail, and it seems ok, but it's not suitable here because: 1. It doesn't copy whole rows 2. It requires that your tables adhere to a particular structure (I'm new in this company and I'm slowly migrating tables to a structure that is close enough that it could be made to work, but for now I'm stuck with what's here) Is it best to write some triggers or has someone already done this stuff? Thanks, James.
I'd recommend using rules to improve your efficiency for queries affect more than one row.. Josh. On March 13, 2003 09:33 am, James Gregory wrote: > Hi all, > > I am wondering if anyone has any good solutions for having postgres > leave an audit trail. Specifically I would like it to copy rows to a > ghost table without constraints every time a row is modified, deleted > etc. I don't really want to have to do the work of creating said ghost > tables, but if that's the only way, then that's ok. > > I know about Audit Trail, and it seems ok, but it's not suitable here > because: > > 1. It doesn't copy whole rows > 2. It requires that your tables adhere to a particular structure (I'm > new in this company and I'm slowly migrating tables to a structure that > is close enough that it could be made to work, but for now I'm stuck > with what's here) > > Is it best to write some triggers or has someone already done this > stuff? > > Thanks, > > James. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
> I am wondering if anyone has any good solutions for having postgres > leave an audit trail. I had 2 tables that needed a simple audit trail. It was very easy to set up using php. A few lines of code within transactions: When a row is created, log it. When any changes happen, log them, who did them and why. I never felt I needed all the info for each row - just the changes. Jeff
On Thu, 2003-03-13 at 04:18, Jeff Fitzmyers wrote: > > I am wondering if anyone has any good solutions for having postgres > > leave an audit trail. > > I had 2 tables that needed a simple audit trail. It was very easy to > set up using php. I considered that. Trouble is that I'd have to write this code in 5 or 6 different languages, one of which is pl/pgsql. Likewise I'd need to test all those different implementations (and it would be impractical to retrofit this to the pl/pgsql stuff). So yeah, I really need the database to do it itself. Thanks, James.
James Gregory wrote: > I considered that. Trouble is that I'd have to write this code in 5 or 6 > different languages, one of which is pl/pgsql. Likewise I'd need to test > all those different implementations (and it would be impractical to > retrofit this to the pl/pgsql stuff). > > So yeah, I really need the database to do it itself. > There is no "native" audit feature built in to Postgres, but it is easy enough to implement using audit tables and plpgsql function triggers on the application tables. See: http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/triggers.html http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-trigger.html Joe
On Wed, 12 Mar 2003, Joshua Moore-Oliva wrote: > I'd recommend using rules to improve your efficiency for queries affect more > than one row.. > > Josh. I agree with Josh that rules are an easy way to accomplish what you are after. The best example I've found on using rules is http://www.postgresql.org/docs/aw_pgsql_book/node124.html Take Care, James ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 Kansas State University Department of Mathematics ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
Look at the /contrib/spi package, which implements "time travel" as well as other features. It basically does what you're talking about. I'm not sure if it's a good fit or not though. On Wed, 12 Mar 2003, Joshua Moore-Oliva wrote: > I'd recommend using rules to improve your efficiency for queries affect more > than one row.. > > Josh. > > On March 13, 2003 09:33 am, James Gregory wrote: > > Hi all, > > > > I am wondering if anyone has any good solutions for having postgres > > leave an audit trail. Specifically I would like it to copy rows to a > > ghost table without constraints every time a row is modified, deleted > > etc. I don't really want to have to do the work of creating said ghost > > tables, but if that's the only way, then that's ok. > > > > I know about Audit Trail, and it seems ok, but it's not suitable here > > because: > > > > 1. It doesn't copy whole rows > > 2. It requires that your tables adhere to a particular structure (I'm > > new in this company and I'm slowly migrating tables to a structure that > > is close enough that it could be made to work, but for now I'm stuck > > with what's here) > > > > Is it best to write some triggers or has someone already done this > > stuff? > > > > Thanks, > > > > James. > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >