Обсуждение: Re: How to get alerted automatically whenever a table structure ischanged between Publisher and Subscriber in Logical Replication?
Hi Team, I wanted to know if there is a way to get alerted automatically when a table on the primary undergoes structural changes(i.e, adding/dropping some columns ). Due to this in Logical Replication the below error arises and replication stops for the changed table until structure on both sides are the same. *ERROR: logical replication target relation "public.est" is missing some replicated columns * So I wanted to get notified whenever a table structure has been changed on the Publisher Side. Is this possible? Looking forward to hear from you!! Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
On 8/28/18 7:48 AM, pavan95 wrote: > Hi Team, > > I wanted to know if there is a way to get alerted automatically when a table > on the primary undergoes structural changes(i.e, adding/dropping some > columns ). > > Due to this in Logical Replication the below error arises and replication > stops for the changed table until structure on both sides are the same. > > *ERROR: logical replication target relation "public.est" is missing some > replicated columns > * > So I wanted to get notified whenever a table structure has been changed on > the Publisher Side. Is this possible? > > Looking forward to hear from you!! > > Regards, > Pavan > Hi, Maybe log_statement = ddl ? You will have informations in logs. Regards,
Hi Adrien, Thanks for your prompt response. In my case I'm not interested in capturing DDL's instead I'm interested in capturing the altered tables, I mean to say if any column was added/removed from the table, it causes replication to stop and trigger continuous errors in the log file as said before. So looking for a any table structure modifications(mainly alter DDL) if changed in a publisher I need to receive an alert(through any custom script or via a mail). Hope you now got my requirement. Looking for any alerting system whenever a table structure gets altered in the publisher so that it can be done the same at the subscriber too. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
On 8/28/18 10:53 AM, pavan95 wrote: > Hi Adrien, > > Thanks for your prompt response. > > In my case I'm not interested in capturing DDL's instead I'm interested in > capturing the altered tables, I mean to say if any column was added/removed > from the table, it causes replication to stop and trigger continuous errors > in the log file as said before. > > So looking for a any table structure modifications(mainly alter DDL) if > changed in a publisher I need to receive an alert(through any custom script > or via a mail). > I do not test, may check_postgres do the job: https://bucardo.org/check_postgres/check_postgres.pl.html#same_schema
You could do that by logging DDL as suggested, and monitoring the log. You could also use event triggers: https://www.postgresql.org/docs/current/static/event-triggers.html -- Scott Ribe scott_ribe@elevated-dev.com https://www.linkedin.com/in/scottribe/ > On Aug 28, 2018, at 2:53 AM, pavan95 <pavan.postgresdba@gmail.com> wrote: > > So looking for a any table structure modifications(mainly alter DDL) if > changed in a publisher I need to receive an alert(through any custom script > or via a mail).
>>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
Team, Any advices on this?? Looking for alerting mechanism and in the case of event triggers can I capture the exact statement text so that it can be executed on the subscriber. *Note*: The DDL commands are not executed by the any one by connecting to the database. They are executed by the Application Odoo in the front end. So in this scenario how will I get benefitted to capture the same. Looking forward to hear from you. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
You've gotten the answer, multiple times. Just because you don't like, doesn't mean that asking the question over and overwill change the answer. You'll have to get the DDL from the logs. -- Scott Ribe scott_ribe@elevated-dev.com https://www.linkedin.com/in/scottribe/ > On Sep 5, 2018, at 3:42 AM, pavan95 <pavan.postgresdba@gmail.com> wrote: > > Team, > > Any advices on this?? Looking for alerting mechanism and in the case of > event triggers can I capture the exact statement text so that it can be > executed on the subscriber. > > *Note*: The DDL commands are not executed by the any one by connecting to > the database. They are executed by the Application Odoo in the front end. So > in this scenario how will I get benefitted to capture the same. > > Looking forward to hear from you. > > Regards, > Pavan > > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html >
I just wanted to confirm if there is any other way to achieve this. It's not like I don't like that answer. Anyways, thanks for answering. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
Pavan, One more thought: Using a log aggregation and analysis tool like ELK would provide a simple way to alert and trigger on DDLactions captured in the log record — as well as give you a solid monitoring solution for Odoop — a Java web app that givesu=you almost no production monitoring out of the box. Cheers, - Evan Evan Bauer eb@evanbauer.com +1 646 641 2973 Skype: evanbauer > On Sep 5, 2018, at 07:14, pavan95 <pavan.postgresdba@gmail.com> wrote: > > I just wanted to confirm if there is any other way to achieve this. It's not > like I don't like that answer. > > Anyways, thanks for answering. > > Regards, > Pavan > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html >