32

We have a table which has only roughly 500 rows, but it is very important for us.

I want see all changes which happen to this table. The changes should get tracked with a timestamp.

I don't want the tracking to happen in the application code, since I want to track changes which happen via psql shell, too.

I am happy with a PostgreSQL specific solution, since I don't use a different DB in this context.

Paul White
  • 94,921
  • 30
  • 437
  • 687
guettli
  • 1,591
  • 5
  • 25
  • 51

4 Answers4

27

Answer compiled from comments

You would put triggers for any DML event on the table that write information about the event to a log table. If you want to track DDL too, have a look at event triggers - sticky bit.

Some example implementations given by a-horse-with-no-name:

Even if they are several years old, the basic idea hasn't changed and all of them are valid choices. The source for the fourth alternative has been updated recently: https://github.com/2ndQuadrant/audit-trigger

2

Here is a way to create generic triggers, from Tracking changes in PostgreSQL by Hans-Jürgen Schönig and comments on that blog post.

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         jsonb,
        old_val         jsonb
);


CREATE FUNCTION logging.change_trigger() RETURNS trigger AS $$
BEGIN
INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, pg_catalog.row_to_json(NEW), pg_catalog.row_to_json(OLD));
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER
SET search_path = pg_catalog,pg_temp;


CREATE TRIGGER audit_important_table
AFTER INSERT OR UPDATE OR DELETE ON important_table
FOR EACH ROW EXECUTE PROCEDURE logging.change_trigger();

Note that the trigger is marked SECURITY DEFINER which is the moral equivalent of a setuid binary; see Writing SECURITY DEFINER Functions Safely and Abusing SECURITY DEFINER functions in PostgreSQL.

Tobu
  • 101
  • 4
0

A trigger-based approach, as many have mentioned, is the most straightforward solution that should just work fine for 500 rows with infrequent updates.

If you need a more sophisticated and scalable solution, you can use the Change Data Capture (CDC) pattern by connecting to a PostgreSQL replication log (WAL), reading all changes like a read replica, and storing them in a structured format. There are many open-source projects such as Debezium that are often used to implement CDC.

Here are the main advantages of CDC over triggers:

  • No performance overhead. No need to insert additional records on every INSERT, UPDATE, DELETE, TRUNCATE command.
  • Flexibility and scalability. High volumes of data changes can be stored in a separate storage, not just in the original PostgreSQL database.
  • Separation of concerns. No need to manage lots of triggers, which can become cumbersome with many trackable schemas and tables.

The main disadvantage of CDC is its complexity. Setting up and managing such a system can be challenging. You can check out https://github.com/BemiHQ/bemi which integrates Debezium with PostgreSQL for data change tracking at scale (I'm the author).

exAspArk
  • 1
  • 1
-2

An opensource module was created to solve this problem: https://github.com/i-Cell-Mobilsoft-Open-Source/postgres_ddl_change_tracker

Csaba
  • 5
  • 1