PGHist — History of table changes in PostgreSQL
Tool is described in more detail on a separate website pghist.org
Tool PGHIST keeps history of table changes and allows to get log(audit) of changes by row, list of changes by field indicating user, time of the change, SQL query, transaction, other technical information and table as of date-time in the past (versioning). To display information in user interface, SQL expressions are defined to describe changed table rows and fields. It is possible to override the operation name and username functions.
Design and working principle
PGHIST is a schema with procedures and common tables: transactions, SQL expressions. When history is enabled (procedure pghist.hist_enable), for specified table created additional table, triggers for insert,update,delete,truncate, stored procedures and view for obtaining data. When a table is changed, triggers are fired that modify the history table. There are also event triggers that rebuild the history table and recreate the stored procedures.
Main functions and view
| pghist.hist_enable([schema],[table]) | - | enable history keeping |
| [schema].[table]_hist | - | log(audit) of changes by row, optimized for analysis |
| [schema].[table]_changes | - | list of changes by field, optimized for display to the user |
| [schema].[table]_at_timestamp | - | table at date-time in the past (versioning) |
More details on page Documentation
-- Create table create table example( id int primary key, name varchar(20), number numeric(10,2), date date ); -- Enable keeping history call pghist.hist_enable('example'); -- Change data insert into example values (1, 'Example', 10, current_date); update example set number=20, date=date-1; -- View change log by row select * from example_hist; -- View changes by field select * from example_changes() order by 1,2,3; -- View table at timestamp select * from example_at_timestamp(now()-interval '10 second'); -- drop table example cascade;