TLDR: Don't.
I'm currently conducting tests which should eventually lead to a 2 node
cluster with working bidirectional logical replication.
(Postgresql 9.6.4-1.pgdg90+1, pglogical 2.0.1-1.jessie+1 on Debian 9
(Stretch))
pglogical supports replication of sequences, and although the way it
does this suggests that it can't really work in both directions
(actually I'm sceptical that it works reliably in one direction), of
course I had to try it.
So I created a sequence on both nodes and called
select pglogical.replication_set_add_sequence('default', 'test_sequence');
on both nodes.
The result was ... interesting.
First I got the same sequence (1, 2, 3, 4, 5) on both nodes.
After a few seconds the replication kicked in, and then I got the same
value (1005) on both nodes most of the time, with a few variants (2005,
3005) thrown in.
In a word, the sequence was completely unusable.
Experiment completed, so I removed the sequence from the replication
set:
select pglogical.replication_set_remove_sequence('default', 'test_sequence');
on both nodes.
But the behaviour of the sequence doesn't change. It still returns 1005
most of the time, and sometimes 2005 or 3005. This is true even after
restarting both nodes.
Plus, I can't drop the sequence any more (as the user who created the
sequence):
wds=> drop sequence public.test_sequence ;
ERROR: permission denied for schema pglogical
So, clearly, pglogical is still managing that sequence.
If I drop the sequence as postgres and then recreate it, it works
normally for some time (also the sequence on the other node now works
normally), but after some time, the replication kicks in again and the
sequence is stuck again at 1005.
So, is there a way to recover from this situation without drastic
measures like nuking the whole database.
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
