Track all modifications to a PostgreSQL table

Question

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.

Answer 1

_{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:

• Tracking changes in PostgreSQL by Hans-Jürgen Schönig
• Impossibly Lean Audit System for Postgres with hstore
• Copying Pavel Stehule’s simple history table but with the jsonb type by Glyn Astill
• Audit trigger in the PostgreSQL Wiki

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