Thread: Tracking DDL and DML changes in Postgresql and different versions ofdatabase (advance)
Tracking DDL and DML changes in Postgresql and different versions ofdatabase (advance)
From
Łukasz Jarych
Date:
Hi Guys,
i am using Access FE and Postgresql BE.
To track DML changes i have history log table wotking like here:
To track DML changes i have history log table wotking like here:
It is ok but this doesnt solve the problem with trakich DDL changes.
For DDL changes i can use flyway system but how to combine this with my DML trackinh system?
Do you have any solutions for this?
My boss wants to have possibility to save the current version (state) of database with DML and DDL changes. And to easily revert and go back to previous version (rollback all changes).
I do not think so that postgresql has triggers on DDL changes and - this is the most optimal solution?
Best Wishes,
Luke
Re: Tracking DDL and DML changes in Postgresql and different versionsof database (advance)
From
Pavan Teja
Date:
On Wed, May 30, 2018, 11:03 AM Łukasz Jarych <jaryszek@gmail.com> wrote:
Hi Guys,i am using Access FE and Postgresql BE.
To track DML changes i have history log table wotking like here:It is ok but this doesnt solve the problem with trakich DDL changes.For DDL changes i can use flyway system but how to combine this with my DML trackinh system?Do you have any solutions for this?My boss wants to have possibility to save the current version (state) of database with DML and DDL changes. And to easily revert and go back to previous version (rollback all changes).I do not think so that postgresql has triggers on DDL changes and - this is the most optimal solution?Best Wishes,Luke
Hi Lukasz,
I think by using triggers(for DML-> Insert, Update, Delete, Truncate), you can achieve this. You can log the entire query which does this change.
And regarding DDL(Create, Alter), you can use concept of event trigger where the tags like "ddl_command_start","table_rewrite","ddl_command_end" are available which let's you to get all the ddl changes into a table.
So by using above things, I hope you can achieve. Feel free to ask if anything is unclear.
Regards,
Pavan
Fwd: Tracking DDL and DML changes in Postgresql and differentversions of database (advance)
From
Łukasz Jarych
Date:
---------- Forwarded message ----------
From: Łukasz Jarych <jaryszek@gmail.com>
Date: 2018-05-30 9:35 GMT+02:00
Subject: Re: Tracking DDL and DML changes in Postgresql and different versions of database (advance)
To: Pavan Teja <pavan.postgresdba@gmail.com>
From: Łukasz Jarych <jaryszek@gmail.com>
Date: 2018-05-30 9:35 GMT+02:00
Subject: Re: Tracking DDL and DML changes in Postgresql and different versions of database (advance)
To: Pavan Teja <pavan.postgresdba@gmail.com>
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?
And regarding changes, how to create exact state of database and give the version for it?
Best,
Luke
2018-05-30 8:11 GMT+02:00 Pavan Teja <pavan.postgresdba@gmail.com>:
On Wed, May 30, 2018, 11:03 AM Łukasz Jarych <jaryszek@gmail.com> wrote:Hi Guys,i am using Access FE and Postgresql BE.
To track DML changes i have history log table wotking like here:It is ok but this doesnt solve the problem with trakich DDL changes.For DDL changes i can use flyway system but how to combine this with my DML trackinh system?Do you have any solutions for this?My boss wants to have possibility to save the current version (state) of database with DML and DDL changes. And to easily revert and go back to previous version (rollback all changes).I do not think so that postgresql has triggers on DDL changes and - this is the most optimal solution?Best Wishes,LukeHi Lukasz,I think by using triggers(for DML-> Insert, Update, Delete, Truncate), you can achieve this. You can log the entire query which does this change.And regarding DDL(Create, Alter), you can use concept of event trigger where the tags like "ddl_command_start","table_rewrite","ddl_command_end" are available which let's you to get all the ddl changes into a table. So by using above things, I hope you can achieve. Feel free to ask if anything is unclear.Regards,Pavan
Re: Fwd: Tracking DDL and DML changes in Postgresql and differentversions of database (advance)
From
pavan95
Date:
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