From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: How to get alerted automatically whenever a table structure is changed between Publisher and Subscriber in Logical Replication?
Date: Tue, 28 Aug 2018 06:24:55 -0700 (MST)
Message-ID: <1535462695414-0.post@n3.nabble.com> (raw)
In-Reply-To: <394C9189-C119-4845-9D60-906C9018604C@elevated-dev.com>
References: <1535435281653-0.post@n3.nabble.com>
<7f636d45-36db-d87b-171a-519e21690574@anayrat.info>
<1535446386936-0.post@n3.nabble.com>
<394C9189-C119-4845-9D60-906C9018604C@elevated-dev.com>
>>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pavan.postgresdba@gmail.com
Subject: Re: How to get alerted automatically whenever a table structure is changed between Publisher and Subscriber in Logical Replication?
In-Reply-To: <1535462695414-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox