Criando um histórico de modificações de tabelas no PostgreSQL

O PostgreSQL não guarda as modificações feitas nas tabelas! Uma vez que uma linha é apagada/alterada, ela é apagada/alterada pra sempre!

Mas e se você precisar guardar um histórico de tudo que foi feito? Se você precisar fazer uma auditoria de todas as operações que foram feitas no banco?

Uma maneira comum de voltar atrás depois de uma modificação destrutiva seria fazer backups regularmente e restaurá-lo. Mas há dois problemas nessa abordagem:
1) O backup faz uma cópia completa da tabela a cada vez que é feito, isso é custoso
2) O backup é feito de tempos em tempos, ou seja, tudo que foi inserido, apagado ou atualizado após o backup é desconhecido.

Qual a solução então?

Opção 1 - Criar histórico via TRIGGERs

Crie uma trigger que seja ativada a cada INSERT, UPDATE ou DELETE, e salve a versão pré-modificada da linha em uma outra tabela chamada tabela de histórico.

Um tutorial maravilhoso que explica passo-a-passo é este aqui:
https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/

Criando a trigger function

Fortemente inspirado no artigo da cybertec.

CREATE FUNCTION change_trigger() RETURNS trigger AS $$BEGINIF 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' /* This SECURITY DEFINER is needed because the user modifying the source table may not have sufficient permissions in the history table*/SECURITY DEFINER;

Criando uma função para criar a tabela de histórico e adicionar a trigger na tabela origem.

CREATE OR REPLACE FUNCTION create_history_table(	"schema_name" TEXT,	"table_name" TEXT) RETURNS voidLANGUAGE 'plpgsql'VOLATILEAS $$BEGINEXECUTE format('CREATE TABLE IF NOT EXISTS %I._%I ( '			   'modified_at TIMESTAMPTZ NOT NULL DEFAULT now(), '			   'operation TEXT NOT NULL, '			   '"user" TEXT NOT NULL DEFAULT current_user, '			   '"new" JSONB, '			   '"old" JSONB '			   ');', 			   "schema_name", 			   "table_name"			  );EXECUTE format('CREATE TRIGGER t '			   'BEFORE INSERT OR UPDATE OR DELETE ON %I.%I '			   'FOR EACH ROW EXECUTE PROCEDURE add_to_history_table();',			   "schema_name",			   "table_name"			  );END;$$;

Opção 2 - Criar uma tabela que seja compatível com históricos em sua definição

E que tal criar uma tabela pensando comportar históricos em sua estrutura? Essa abordagem pode funcionar bem em alguns casos, mas em outros pode ser complicado.

Extensões que fazem histórico automaticamente

Há algumas extensões para PostgreSQL que se propõe a criar históricos automaticamente (usando a Opção 1). Como prefiro ter controle das modificações, acho mais interessante fazer via trigger, mas fique a vontade, o banco é todo seu!

temporal_tables
https://pgxn.org/dist/temporal_tables/

table_version
https://pgxn.org/dist/table_version/

Referências

Rastreando mudanças em tabelas no PostgreSQL (é o melhor artigo que achei até agora)
https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/

Trigger Genérica

CREATE OR REPLACE FUNCTION history_table_change_trigger() RETURNS trigger AS $$DECLARE	new_value JSONB;	old_value JSONB;BEGINIF TG_OP = 'INSERT' THEN	EXECUTE format('INSERT INTO %I.%I (operation, "new") VALUES (''INSERT'', %L);', TG_TABLE_SCHEMA, TG_RELNAME||'_history', row_to_json(NEW));RETURN NEW;ELSIF TG_OP = 'UPDATE' THEN    EXECUTE format('INSERT INTO %I.%I (operation, "new", "old") VALUES (''UPDATE'', %L, %L);', TG_TABLE_SCHEMA, TG_RELNAME||'_history', row_to_json(NEW), row_to_json(OLD));RETURN NEW;ELSIF TG_OP = 'DELETE' THEN    EXECUTE format('INSERT INTO %I.%I (operation, "old") VALUES (''DELETE'', %L);', TG_TABLE_SCHEMA, TG_RELNAME||'_history', row_to_json(OLD));RETURN OLD;END IF;END;$$ LANGUAGE 'plpgsql' /* SECURITY DEFINER;This SECURITY DEFINER is needed when the user modifying the source table do not have sufficient permissions in the history table*/;

Função que cria uma tabela de histórico com o nome tabela_original_history, no mesmo esquema da tabela_original e amarra todos os UPDATES, INSERTS e DELETES com a TRIGGER descrita anteriormente, que atualiza a tabela history.

CREATE OR REPLACE FUNCTION create_history_table(	"schema_name" TEXT,	"table_name" TEXT) RETURNS voidLANGUAGE 'plpgsql'VOLATILEAS $$BEGINEXECUTE format('CREATE TABLE IF NOT EXISTS %I.%I_history ( '			   'modified_at TIMESTAMPTZ NOT NULL DEFAULT now(), '			   'operation TEXT NOT NULL, '			   '"user" TEXT NOT NULL DEFAULT current_user, '			   '"new" JSONB, '			   '"old" JSONB '			   ');', 			   "schema_name", 			   "table_name"			  );EXECUTE format('CREATE TRIGGER history_trigger_%I_%I '			   'BEFORE INSERT OR UPDATE OR DELETE ON %I.%I '			   'FOR EACH ROW EXECUTE PROCEDURE history_table_change_trigger();',			   "schema_name",			   "table_name",			   "schema_name",			   "table_name"			  );END;$$;

Criando a tabela e as triggers

SELECT create_history_table('public','users');

Pronto, está criado.

A tabela de histórico tem o seguinte formato:

modified_at TIMESTAMPTZ -- O horário de modificação da linhaoperation TEXT -- (INSERT, UPDATE, DELETE)user TEXT -- usuário do banco que executou a modificaçãoold JSONB -- o valor antigo, antes da modificaçãonew JSONB -- o valor novo, depois da modificação