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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
