Re: Creating complex track changes database - challenge!

[Alan Gano <alangano@xxxxxxxxx>]

I've got a manual method (though it's probably wise to go with a vendor product), that I will just dump here.

It tracks all configured tables into a single table containing before/after record images in jsonb.

create table aud_audit
(
   id                               serial8,
   timestamp                        timestamptz default now() NOT NULL,
   app_user_id                      int8 NOT NULL,
   operation                        varchar(8) NOT NULL,
   table_name                       varchar(100) NOT NULL,
   before_image                     jsonb,
   after_image                      jsonb,
   ----
   constraint aud_audit_pk primary key(id)
)
;

create or replace function audit_all() returns trigger as
$$
declare
   t_before       jsonb := NULL;
   t_after        jsonb := NULL;
   t_user_id      int8;
begin
   begin
      t_user_id := current_setting('app.user_id')::int8;
   exception
      when OTHERS then
         t_user_id := -1;
   end;

   case tg_op
      when 'INSERT' then
         t_after := row_to_json(new.*);
      when 'UPDATE' then
         t_before := row_to_json(old.*);
         t_after := row_to_json(new.*);
      when 'DELETE' then
         t_before := row_to_json(old.*);
      when 'TRUNCATE' then
         t_before := row_to_json(old.*);
   end case;

   insert into aud_audit
   (
      app_user_id,
      operation,
      table_name,
      before_image,
      after_image
   )
   values(
      t_user_id,
      tg_op,
      tg_table_name,
      t_before,
      t_after
   );

   return
      case tg_op
         when 'INSERT' then new
         when 'UPDATE' then new
         when 'DELETE' then old
         when 'TRUNCATE' then old
      end;
end;
$$
language plpgsql
;

for each table you want to track ...

create trigger <table_name>_audit_t01
   before insert or update or delete
   on <table_name>
   for each row execute procedure audit_all()
;

On Mon, Feb 26, 2018 at 7:43 AM, geoff hoffman <geoff@xxxxxxxx> wrote:

There’s https://flywaydb.org/

and http://www.liquibase.org/

More: https://dbmstools.com/version-control-tools 

Also, if you know PHP, Laravel database migrations have worked great for us!

https://laravel.com/docs/5.6/migrations

On Feb 26, 2018, at 3:44 AM, Łukasz Jarych <jaryszek@xxxxxxxxx> wrote:

i would like to ask you for help with track changes to my database. 

I am new to PosgtreeSQL but i have to learn it quickly because of my boss. 

I have to:

1. Keep all changes within table including:

-adding rows

-deleting

-editing

2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.

3. Track all DLL and DML changes with possibility to ho back to previous version.