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()
;