Re: Creating complex track changes database - challenge!

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(at)rxmg(dot)com> 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(at)gmail(dot)com> 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.
>
>
>