How can I use a PostgreSQL triggers to store changes (SQL statements and row changes)

Question

Using PostgreSQL triggers, is it possible to record the changes that have happened to a table due to INSERT or UPDATE SQL statements and log them to a file for later execution.

This is only to be used temporally so just something quick and dirty would do.

Answer 1

example of an audit trigger from https://www.postgresql.org/docs/current/static/plpgsql-trigger.html

CREATE TABLE emp (     empname           text NOT NULL,     salary            integer );  CREATE TABLE emp_audit(     operation         char(1)   NOT NULL,     stamp             timestamp NOT NULL,     userid            text      NOT NULL,     empname           text      NOT NULL,     salary integer );  CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$     BEGIN         --         -- Create a row in emp_audit to reflect the operation performed on emp,         -- make use of the special variable TG_OP to work out the operation.         --         IF (TG_OP = 'DELETE') THEN             INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;             RETURN OLD;         ELSIF (TG_OP = 'UPDATE') THEN             INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;             RETURN NEW;         ELSIF (TG_OP = 'INSERT') THEN             INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;             RETURN NEW;         END IF;         RETURN NULL; -- result is ignored since this is an AFTER trigger     END; $emp_audit$ LANGUAGE plpgsql;  CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp     FOR EACH ROW EXECUTE PROCEDURE process_emp_audit(); 

Answer 2

Do you actually need the audit log of queries stored in a table? The easiest way to get a file with all the queries that have been executed is to use postgresql's built-in logging.

In your postgresql.conf (usually in the $PG_DATA dir), set the following options appropriately:

log_directory '/path/to/log/dir' log_filename = 'filename.log' log_statement = 'mod' 

That last option makes it log all the INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM statements.

More details in the Postgres docs: http://www.postgresql.org/docs/current/static/runtime-config-logging.html