How to log data change in postgresql?

Question

This question may seem to be a possible duplicate of some other questions that are related to this topic. I've found some similar questions(some questions were asked years back and discussion on the topic seemed to be almost over). But no feasible solutions were found for my problem. I've a database with plenty of tables with huge amount of data in it. I need to log each and every changes that is happening to the datas that are stored in the tables of the particular database.

For example, I have a table for storing employee details.

id    employeename
 1    ab

And, this data is changed to

id    employeename
 1    cd

So i need to log this data.

ie, employeename

ab

is changed to

cd

in the table employee details

I need to log the data every time a change is made to the contents stored in the tables. Is it really possible? If so, how can I do that? What are the steps involved in it? I'm pretty concerned about the size of the log files in such a case. In such a situation what can be a good alternative? I'm using postgresql8.4. Any good suggestion will help me a lot. Thanks in advance.

You might want to learn about triggers: http://www.postgresql.org/docs/8.4/interactive/triggers.html — mu is too short, Dec 09 '12 at 08:51
Here is the example of a trigger, than logs such changes: [Audit trigger](http://wiki.postgresql.org/wiki/Audit_trigger). — Ihor Romanchenko, Dec 09 '12 at 08:57

score 19 · Answer 1 · answered Apr 17 '18 at 07:29

Very generic trigger function, found there: https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/

Table to store the history:

CREATE SCHEMA logging;
CREATE TABLE logging.t_history (
        id             serial,
        tstamp         timestamp DEFAULT now(),
        schemaname     text,
        tabname        text,
        operation      text,
        who            text DEFAULT current_user,
        new_val        json,
        old_val        json
);

The trigger:

CREATE FUNCTION change_trigger() RETURNS trigger AS $$
       BEGIN
         IF TG_OP = 'INSERT'
         THEN INSERT INTO logging.t_history (
                tabname, schemaname, operation, new_val
              ) VALUES (
                TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW)
              );
           RETURN NEW;
         ELSIF  TG_OP = 'UPDATE'
         THEN
           INSERT INTO logging.t_history (
             tabname, schemaname, operation, new_val, old_val
           )
           VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD));
           RETURN NEW;
         ELSIF TG_OP = 'DELETE'
         THEN
           INSERT INTO logging.t_history
             (tabname, schemaname, operation, old_val)
             VALUES (
               TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD)
             );
             RETURN OLD;
         END IF;
       END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

Apply the trigger:

CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON your_table
        FOR EACH ROW EXECUTE PROCEDURE change_trigger();

Thanks for this. I adapted it to my needs and it works well. I found out that I needed to save `session_user` rather than `current_user` when the function is a `SECURITY DEFINER` as the latter is the owner of the function, not the connected user. — Deepstop, Nov 06 '19 at 19:15
In case of an **upsert**, say `INSERT INTO t (x, y) VALUES (x, y) ON CONFLICT (x) DO UPDATE SET y = EXCLUDED.y;` 2 rows are written in table `logging.t_history` with `BEFORE`. Using `AFTER` instead seems to be a possible solution if I haven't overlooked any issue. — jgran, Sep 17 '20 at 17:12
follow up of previous comment : I hadn't seen this question: [How can I use a PostgreSQL triggers to store changes (SQL statements and row changes)](https://stackoverflow.com/questions/1295795/how-can-i-use-a-postgresql-triggers-to-store-changes-sql-statements-and-row-cha) in which similar solutions are proposed that use `AFTER`. — jgran, Sep 18 '20 at 07:41

How to log data change in postgresql?

1 Answers1

Linked