CODEWITHSUNDEEP
sqlpostgresql
Nbajam 2015
Nbajam 2015

Reputation: 360

How to know all the actions taken on postgres SQL table?

I am looking for a way to know all the events occurred on a specific table without enabling postgres logs.

Just want to know weather the sequence of addition/deletion/ Modification.

Thanks

Upvotes: 0

Views: 980

Answers (2)

user3837299
user3837299

Reputation: 339

For audit trail in postgres you have to write an function and call it in a trigger, please have a look at wiki.postgresql.org/wiki/Audit_trigger , You will have to write a function stating that if an update or delete or insert is happening on a table it will trigger an action updating a audit table capturing required information such as ip address, query, old data, new data, timestamp of the action that has occured etc..

Upvotes: 1

clemens
clemens

Reputation: 17721

You can create triggers (see postgres docs) to observe all changes, e.g.:

CREATE TRIGGER insert_trigger AFTER INSERT ON my_table
EXECUTE PROCEDURE insert_function();
CREATE TRIGGER update_trigger AFTER UPDATE ON my_table
EXECUTE PROCEDURE update_function();
CREATE TRIGGER delete_trigger AFTER DELETE ON my_table
EXECUTE PROCEDURE delete_function();

or do this with just one function:

CREATE TRIGGER universal_trigger AFTER INSERT OR UPDATE OR DELETE ON my_table
EXECUTE PROCEDURE universal_function();

Upvotes: 0

Related Questions