Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

PostgreSQL History Tracker

branch: master

Fetching latest commit…

Octocat-spinner-32-eaf2f5

Cannot retrieve the latest commit at this time

Octocat-spinner-32 compat
Octocat-spinner-32 test
Octocat-spinner-32 Makefile
Octocat-spinner-32 README
Octocat-spinner-32 drop_schema.sql
Octocat-spinner-32 init_schema.sql
Octocat-spinner-32 install_tracker.sql
Octocat-spinner-32 uninstall_tracker.sql
README
*** POSTGRESQL HISTORY TRACKER ***

AUTHOR
Ivan Mincik, Gista s.r.o., ivan.mincik@gista.sk


ABOUT
This project is set of functions which adds possibility to store full editing 
history of Your database tables, recover its state to any time, visualize diffs 
and place tags to mark particular table state. Inspiration was 
taken from versioning functions created by Horst Duester and Andreas Neumann, 
but implemented in other way. (http://www.kappasys.ch/pgtools/pghistory/index.html)


LICENSE
The GNU General Public License version 2 (GPLv2)


PREREQUISITES
All tables MUST contain primary key (currently only one-column primary key is supported).


INSTALLATION 
Prerequisites:
Assuming you have already working PostgreSQL database server. 
In addition PL/Python procedural language is required. Under Debian install 
package 'postgresql-plpython-<pg version>'.

Preparing database:
1. Adding PL/PgSQL language support: 'createlang plpgsql <database>'
2. Adding PL/Python language support: 'createlang plpythonu <database>'
3. Init tracker: 'psql <database> -f init_schema.sql'
4. Loading functions: 'psql <database> -f install_tracker.sql'

Compatibility:
For using with PostgreSQL 8.3 and below load also 'compat/array_agg.sql' file to Your database. 


USAGE
1. Adding versioning support to table: 'SELECT HT_Init('<schema>', '<table>');'
2. Retrieving table state in particulal time: 'SELECT * FROM <schema>.<table>_AtTime('<timestamp>');'
3. Retrieving table changes to particulal time: 'SELECT * FROM <schema>.<table>_Diff('<timestamp>');'
4. Retrieving table changes to particulal tag: 'SELECT * FROM <schema>.<table>_DiffToTag(<tag number>);'


More examples of retrieving table history:
1. Current state: 'SELECT * FROM <schema>.<table>_AtTime(localtimestamp);'
2. Five minutes ago: 'SELECT * FROM <schema>.<table>_AtTime(localtimestamp - INTERVAL '5 minutes');'
3. One hour ago: 'SELECT * FROM <schema>.<table>_AtTime(localtimestamp - INTERVAL '1 hour');'


SUPPORTED POSTGRESQL VERSIONS
Project is currently tested in PostgreSQL 8.3 and 8.4 versions.


HOW IT WORKS
After running 'HT_Init' function, 'history table' is created in 'history_tracker' schema. This table will
save all editing history. To achieve this, set of trigger functions is appended to your original table to enable
forwarding of all editing information to 'history table'. No other changes to your original table. Table structure
can not be changed after running 'HT_Init'.

Adding and removing history should be fully invisible for any client software.

NOTES
When running tests against PostgreSQL 8.3 load PgTAP compatibility patch 'compat/pgtap-8.3.patch' and
uncomment line in Makefile to load 'compat/array_agg.sql' file.


Good resource on retrieving PostgreSQL table metadata:
http://www.alberton.info/postgresql_meta_info.html
Something went wrong with that request. Please try again.