Skip to content

Log Table Changes in PostgreSQL with tablelog

Ever had the need to know what happened with your data? Tablelog could be an answer.

The usage is easy: you create a second table which has the same format like your table you want to keep an eye on. Plus you need some additional columns to maintain the logged data.

But step by step:

First thing: you need the software. Get it from pgfoundry: http://pgfoundry.org/projects/tablelog/

Unpack the archive, join the table_log-version directory and type in:
"make -f Makefile.nocontrib".
This should work without problems. If you get an error about missing include files, please install the PostgreSQL development packages from your distribution.

If compilation was done successfully, install the library. You have to be a superuser to do that, so first become root with "su". Type in: "make -f Makefile.nocontrib install".
This will install the library into the PostgreSQL library directory.

Now we have to tell your database that we want to use tablelog, so make sure, you have support for plpgsql enabled (with createlang). Connect as a superuser to the database which contains your tables and execute the following commands (you can find them in the file table_log.sql):

-- create function
CREATE FUNCTION table_log ()
RETURNS TRIGGER
AS '$libdir/table_log' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT)
RETURNS VARCHAR
AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT)
RETURNS VARCHAR
AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR)
RETURNS VARCHAR
AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ)
RETURNS VARCHAR
AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';


After this you may add some supporting functions which will make life more easy, you can find them in the file table_log_init.sql. Just paste the content into your database shell or include it using the \i command in psql.

Thats all, now you can use tablelog in your database.


Here's an example (taken from table_log.sql):

-- create demo table
CREATE TABLE test (
id INT NOT NULL
PRIMARY KEY,
name VARCHAR(20) NOT NULL
);

-- create the table without data from demo table
SELECT * INTO test_log FROM test LIMIT 0;
ALTER TABLE test_log ADD COLUMN trigger_mode VARCHAR(10);
ALTER TABLE test_log ADD COLUMN trigger_tuple VARCHAR(5);
ALTER TABLE test_log ADD COLUMN trigger_changed TIMESTAMPTZ;
ALTER TABLE test_log ADD COLUMN trigger_id BIGINT;
CREATE SEQUENCE test_log_id;
SELECT SETVAL('test_log_id', 1, FALSE);
ALTER TABLE test_log ALTER COLUMN trigger_id SET DEFAULT NEXTVAL('test_log_id');

-- create trigger
CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test FOR EACH ROW
EXECUTE PROCEDURE table_log();

-- test trigger
INSERT INTO test VALUES (1, 'name');
SELECT * FROM test;
SELECT * FROM test_log;
UPDATE test SET name='other name' WHERE id=1;
SELECT * FROM test;
SELECT * FROM test_log;



You will see that the test_log table contains all changes which happened to the original table. If you want to avoid the overhead for creating the logtable, take a look into the support functions created by table_log_init.sql.

Ok, but what use would be the logging data if we have no good chance of using it?
So the next example will show you, how you can take a look into the state of your table at any time
in the past:

-- create restore table
SELECT table_log_restore_table('test', 'id', 'test_log', 'test_log_id', 'test_recover', NOW());
SELECT * FROM test_recover;


The meaning of the arguments, from left to right: name of the original table, primary key of the original table, name of the logging table, primary key of the logging table, recovery table to create by table_log_restore_table(), timestamp for the lookup. Some more arguments are available, take a look into the README for a full description.

Have fun with your table history.

If you want to provide feedback, you will find contact infos at the end of the README file.
  • Twitter
  • Bookmark Log Table Changes in PostgreSQL with tablelog at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg Log Table Changes in PostgreSQL with tablelog
  • Bloglines Log Table Changes in PostgreSQL with tablelog
  • Technorati Log Table Changes in PostgreSQL with tablelog
  • Fark this: Log Table Changes in PostgreSQL with tablelog
  • Bookmark Log Table Changes in PostgreSQL with tablelog at YahooMyWeb
  • Bookmark Log Table Changes in PostgreSQL with tablelog at Furl.net
  • Bookmark Log Table Changes in PostgreSQL with tablelog at reddit.com
  • Bookmark Log Table Changes in PostgreSQL with tablelog at blinklist.com
  • Bookmark Log Table Changes in PostgreSQL with tablelog at Spurl.net
  • Bookmark Log Table Changes in PostgreSQL with tablelog at Simpy.com
  • Bookmark Log Table Changes in PostgreSQL with tablelog at blogmarks
  • Bookmark Log Table Changes in PostgreSQL with tablelog with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Trackbacks

www.pg-forum.de on : PingBack

Show preview
Comments ()

www.pg-forum.de on : PingBack

Show preview
Comments ()

www.pg-forum.de on : PingBack

Show preview
Comments ()

Comments

Display comments as Linear | Threaded

postgresql >=8.2 on :

For PostgreSQL >=8.2 you need this simple patch: http://pgfoundry.org/tracker/index.php?func=detail&aid=1000870&group_id=1000074&atid=360
Comments ()

security model on :

If you want to use this as an audit trail you must stop your users from inserting arbitraty data into _log files. You can achieve this by making another role (let's say - 'auditor') to become these functions owner, creating all the audit tables as a user with such role and issuing: ALTER FUNCTION table_log() security definer; This way only 'auditor' role has access to _log tables and you're preventing your users from faking it.
Comments ()

SunWuKung on :

How could you get the last date a value in a field of a logged table changed? E.g. when did a user changed his password the last time
Comments ()

SunWuKung on :

Sorry, I think I have it: select trigger_changed from ( select DISTINCT ON (subj_upass) * from log.subject_log where subj_id=6) drvdtbl ORDER BY trigger_id DESC LIMIT 1
Comments ()

Andreas Scherbaum on :

Yes, just select the latest matching value from the log table.
Comments ()

glyn on :

How does the table_log_restore_table function work when a table is audited in a separate schema? I have a table called "show" in a test schema called "junk_schema" with primary key called "show_index01", and it is audited as "show" in an audit schema called "audit" with primay key "show_pkey". The following just gives me the error "ERROR: could not check relation: show" select table_log_restore_table('show','show_index01','show','show_pkey','restored_show',NOW()); Does the restore function work across schemas?
Comments ()

Andreas Scherbaum on :

You can no only use the table name but also prepend the table name with the schema name: audit.show
Comments ()

glyn on :

Thanks Andreas, however I get the following; JUNK=# select table_log_restore_table('junk_schema.show','show_index01','audit.show','show_pkey','restored_show',NOW()); ERROR: could not check relation: junk_schema.show If I look at the code (I may be looking in the wrong place) on line 631: / check original table / snprintf(query, 249, "SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = %s AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum", do_quote_literal(table_orig)); Checking against pg_class, so would passing the schema name as above not work?
Comments ()

Andreas Scherbaum on :

Looks like i never thought about this issue. Hmm, don't know a way around this problem, sorry. As a workaround you can use another table name and habe both schemes in your search_path. I'm sorry for this circumstances :-( By the way: would you prefer a C or a pl/pgSQL version of tablelog? I'm thinking about a rewrite, the current code is approx. 8 years old and way behind the current possibilities.
Comments ()

glyn on :

On the whole I prefer C, I think it's neater in the long run. However if plpgsql is the better tool these days so be it. Would I be oversimplifying things by thinking we just need to add in the schema names to the log_table_restore_table function and then add in a check for pg_class.nspname in each of the queries against pg_class?
Comments ()

Andreas Scherbaum on :

That would be possible. May i bug you to send me a patch? ;-)
Comments ()

SunWuKung on :

Is it possible to have only the changes displayed in some way - what values of a record have changed during each step? I guess this would be based on similar technique to restore (or not). Thx. SWK
Comments ()

Andreas Scherbaum on :

You could compare each field from the old and new value - and only display different fields.
Comments ()

SunWuKung on :

Yes, but I don't know any generic way to do that. Maybe it is possible to write such a function in plpgSQL but its certainly beyond my abilities. eg. Select * from change_log("tableName")
Comments ()

Sirp on :

I install postgres 8.3 anda i have this msg when i try to execute CREATE FUNCTION table_log () RETURNS TRIGGER AS '/usr/lib/pgsql/table_log' LANGUAGE 'C'; ERROR: incompatible library "/usr/lib/pgsql/table_log.so": version mismatch DETAIL: Server is version 8.3, library is version 8.2. ********** Error ********** ERROR: incompatible library "/usr/lib/pgsql/table_log.so": version mismatch SQL state: XX000 Detail: Server is version 8.3, library is version 8.2.
Comments ()

Andreas 'ads' Scherbaum on :

DETAIL: Server is version 8.3, library is version 8.2. That's obvious, your module is compiled for 8.2, you have to recompile the module. Oh, and you should check that no libraries, binaries or development stuff from 8.2 is still on your system.
Comments ()

Josh Trutwin on :

"I'm thinking about a rewrite, the current code is approx. 8 years old and way behind the current possibilities." Have you had a chance to look into this yet? I'm interested in looking into this project, hoping it's still maintained... Thanks, Josh
Comments ()

Andreas 'ads' Scherbaum on :

Not yet. I found out that plpgsql is even after 8 years not able to provide all the functionality needed for table_log. Especially it's not possible to do something like: NEW.$column
Comments ()

Tajtelbaum Claudio on :

Hi andreas, this is fantastic contrib, but i need save in audit tables other user than current user. Example: current_user = 'System' but i need to save:'Claudio@atn'. How i can do this? Thnx
Comments ()

Andreas 'ads' Scherbaum on :

Hello Claudio, that's not possible, because the data is written by a trigger and not by the insert itself. You can probably modify the trigger function and extract the data from a setting or environment variable ...
Comments ()

Guest on :

Would it be possible to get a patch of table_log with properly escaped quotes?
Comments ()

Guest on :

I get warnings like: WARNING: nonstandard use of \' in a string literal HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...').
Comments ()

Guest on :

I attempted to build the most recent cvs version hoping it might fix the problem, but that fails to build ( on ubuntu 9.10 w/ postgresql 8.4)
Comments ()

Mic on :

See the following: http://www.postgresql.org/docs/9.1/interactive/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS You should use internal quote_literal_cstr() or add E in front of quoted strings. Now postgresql-9.1 needs E by default to interpret correctly the \ as escape char. Thanks.
Comments ()

Arthur on :

Any chance to get pre-built (Windows) binaries for all those non-developers out there?
Comments ()

Andreas 'ads' Scherbaum on :

Hi, I'm sorry, I have no windows box - and no intention to buy all the required licenses. That's a lot money and i can't use it elsewhere. Maybe someone else can provide pre-built binaries?
Comments ()

Tobias on :

just as a head up: there is a fork of this code on github which is wrapped in an extension: https://github.com/psoo/table_log
Comments ()

akretschmer on :

other nice solution: http://www.postgresonline.com/journal/archives/330-Using-HStore-for-Archiving.html
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options