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

On Wed, May 30, 2018, 11:03 AM Łukasz Jarych <jaryszek(at)gmail(dot)com> wrote:

> Hi Guys,
>
> i am using Access FE and Postgresql BE.
> To track DML changes i have history log table wotking like here:
>
> https://www.fontstuff.com/access/acctut21.htm
>
> 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

>