Re: How to get alerted automatically whenever a table structure ischanged between Publisher and Subscriber in Logical Replication?

>>You could also use event triggers:
https://www.postgresql.org/docs/current/static/event-triggers.html

Yes, But can I get the exact text so executed into a table. Please consider
the below example which clearly explains my requirement:

Step1: Created a table like below:

CREATE TABLE log_ddl_info(ddl_tag text, ddl_event text, ddl_time timestamp);

Step2: Created a function like below:


CREATE OR REPLACE FUNCTION log_ddl_execution()
RETURNS event_trigger AS $$
DECLARE
insertquery TEXT;
BEGIN
insertquery := 'INSERT INTO log_ddl_info VALUES (''' || tg_tag ||''', ''' ||
tg_event || ''', statement_timestamp())';
EXECUTE insertquery;
RAISE NOTICE 'Recorded execution of command % with event %', tg_tag,
tg_event;
END;
$$ LANGUAGE plpgsql;

Step3: Created event triggers as below:

 CREATE EVENT TRIGGER log_ddl_info_start ON ddl_command_start  EXECUTE
PROCEDURE log_ddl_execution();

CREATE EVENT TRIGGER log_ddl_info_end ON ddl_command_end EXECUTE PROCEDURE
log_ddl_execution();

Step4: Triggered a DDL statement as:

 create table aa (a int);


Finally issued select on the ddl history table:

 select * from log_ddl_info;

   ddl_tag    |     ddl_event     |        ddl_time
--------------+-------------------+-------------------------
 CREATE TABLE | ddl_command_start | 2018-08-28 18:47:55.745
 CREATE TABLE | ddl_command_end   | 2018-08-28 18:47:55.745

But from the above output I also need the exact DDL statement text and which
db user triggered it. Is it possible??

Looking forward to hear from you!

Regards,
Pavan





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html