From: | Fabrízio de Royes Mello <fabriziomello(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
Cc: | Rich Shepard <rshepard(at)appl-ecosys(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Database Design: Maintain Audit Trail of Changes |
Date: | 2013-01-03 16:19:07 |
Message-ID: | CAFcNs+o48MAv9P9QyKiDBkmpNKqk-cJ7g25y-YykEYHCywXy4Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jan 3, 2013 at 2:09 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
wrote:
>
> On 01/03/2013 07:38 AM, Rich Shepard wrote:
>
>> The middleware of the application needs to check this table when data
>> are
>> to be viewed in the UI and present only the current row contents. A
>> separate
>> view would display a history of changes for that row.
>>
>> All thoughts, suggestions, and recommendations based on your
>> expertise and
>> experience will be most welcome.
>
>
> As a matter of course I include fields to record the timestamp and user
for insert of records and last update of record on my tables.
>
> For a relatively simple solution see this blog post I put up this summer:
>
> http://aklaver.org/wordpress/2012/06/23/postgres-and-hstore/
>
> I have since expanded that to include updates by using TG_OP to determine
the operation being done on the table.
>
> There is also pg_audit
> :
> https://github.com/jcasanov/pg_audit
>
And keep in mind that kind of table tend to grow quickly, so you must use
some strategy to purge old historical data or make your audit table
partitioned...
I implemented the same think in our ERP a long time ago using partitioned
approach, because its easy to purge old historical data.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
From | Date | Subject | |
---|---|---|---|
Next Message | Jose Martinez | 2013-01-03 16:25:41 | Unable to reload postgresql.conf without restarting |
Previous Message | James Cowell | 2013-01-03 16:12:50 | Re: Corrupt indexes on slave when using pg_bulkload on master |