Re: Fwd: Tracking DDL and DML changes in Postgresql and different versions of database (advance)

Hi Luke,

>Thank you Pavan,

>I have a few questions.

>Could you please describe in more details about event triggers? Maybe some
examples how to start with this?

Please refer the below link for more detailed information about "Event
Triggers".

Link: https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
<https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html>

You can find the below sample procedure along with the event trigger which
executes for every DDL against the database.

--Procedure
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;

--Event Trigger
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE PROCEDURE snitch();

If you execute above code you can find that the event trigger snitch raises
a notice message whenever ddl fires on the database. Do let me know if you
face any problem.

>And regarding changes, how to create exact state of database and give the
version for it?

Instead of the notice message in the above procedure, you can simple insert
user info, timestamp at the time of ddl command start, actual query, and the
tag name(tg_tag=> whether to determine it is ALTER/CREATE/SECURITY
LABEL/DROP/COMMENT/GRANT or REVOKE).

Please feel free to write up if any queries persists.

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html