pglogical and slony

From: "Armand Pirvu (home)" <armand(dot)pirvu(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: pglogical and slony
Date: 2017-05-11 21:00:45
Message-ID: 93D6393E-4EB0-4C69-8609-99799938AF30@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thanks to Adrian I got pointed in the direction of enabling triggers to use with pglogical

In other words, whatever comes down the pglogical in terms of inserts/updates/deletes, I can track those in some audit table or do something else

That got me thinking why not putting at the end of pglogical, Slony which is trigger based ?

Postgres 9.5.5

edb1 192.168.5.251 - master pglogical
edb2 192.168.5.252 - slave pglogical

edb2 192.168.5.252 - master slony
edb3 192.168.5.253 - slave slony

sudo -u postgres /usr/pgsql-9.5/bin/slonik << _EOF_
cluster name=armandpc123;
node 1 admin conninfo = 'dbname=csidb host=192.168.5.252 user=postgres';
node 2 admin conninfo = 'dbname=csidb host=192.168.5.253 user=postgres';
init cluster (id=1, comment='Node 1');
store node (id=2, comment='Node 2',event node=1);
store path (server=1, client=2, conninfo='dbname=csidb host=192.168.5.252 user=postgres', connretry=10);
store path (server=2, client=1, conninfo='dbname=csidb host=192.168.5.253 user=postgres', connretry=10);
create set (id=1, origin=1, comment='some test tables');
set add table (id=1, set id=1, origin=1, fully qualified name='public.tbl2', comment='tbl2 table', key='tbl2_pkey');
_EOF_

- master (edb2 192.168.5.252)
sudo -u postgres /usr/pgsql-9.5/bin/slon armandpc123 "dbname=csidb user=postgres host=192.168.5.252"
- slave (edb3 192.168.5.253)
sudo -u postgres /usr/pgsql-9.5/bin/slon armandpc123 "dbname=csidb user=postgres host=192.168.5.253"

sudo -u postgres /usr/pgsql-9.5/bin/slonik << _EOF_
cluster name=armandpc123;
node 1 admin conninfo = 'dbname=csidb host=192.168.5.252 user=postgres';
node 2 admin conninfo = 'dbname=csidb host=192.168.5.253 user=postgres';
subscribe set (id=1, provider=1, receiver=2, forward=no);
_EOF_

csidb=# \d tbl2;
Table "public.tbl2"
Column | Type | Modifiers
--------+---------------+-----------
col1 | integer | not null
col2 | character(10) |
Indexes:
"tbl2_pkey" PRIMARY KEY, btree (col1)
Triggers:
_armandpc123_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH ROW EXECUTE PROCEDURE _armandpc123.denyaccess('_armandpc123')
_armandpc123_truncatedeny BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT EXECUTE PROCEDURE _armandpc123.deny_truncate()
Disabled user triggers:
_armandpc123_logtrigger AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH ROW EXECUTE PROCEDURE _armandpc123.logtrigger('_armandpc123', '1', 'k')
_armandpc123_truncatetrigger BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT EXECUTE PROCEDURE _armandpc123.log_truncate('1')

csidb=# alter table tbl2 ENABLE ALWAYS TRIGGER _armandpc123_logtrigger;
ALTER TABLE
csidb=# \d tbl2
Table "public.tbl2"
Column | Type | Modifiers
--------+-----------------------+-----------
col1 | integer | not null
col2 | character varying(10) |
Indexes:
"tbl2_pkey" PRIMARY KEY, btree (col1)
Triggers:
_armandpc123_truncatetrigger BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT EXECUTE PROCEDURE _armandpc123.log_truncate('1')
Disabled user triggers:
_armandpc123_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH ROW EXECUTE PROCEDURE _armandpc123.denyaccess('_armandpc123')
_armandpc123_truncatedeny BEFORE TRUNCATE ON tbl2 FOR EACH STATEMENT EXECUTE PROCEDURE _armandpc123.deny_truncate()
Triggers firing always:
_armandpc123_logtrigger AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH ROW EXECUTE PROCEDURE _armandpc123.logtrigger('_armandpc123', '1', 'k')
trig_hist_aud_prev_tbl2 AFTER INSERT OR DELETE OR UPDATE ON tbl2 FOR EACH ROW EXECUTE PROCEDURE func_audit_tname()

csidb=# alter table tbl2 disable trigger _armandpc123_denyaccess;

Manual insert in edb2.csidb.tbl2 replicates fine to edb3.csidb.tbl2, but nothing coming in say like replicating from edb1.csidb.tbl2 via pglogical although triggers are enabled

Is this possible ?

Thank you

Armand

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2017-05-11 22:46:07 Re: Request to add feature to the Position function
Previous Message Gavin Flower 2017-05-11 20:38:17 Re: Top posting....