Re: pglogical bidirectional replication of sequences

On 2017-09-01 10:29:51 +0200, Peter J. Holzer wrote:
> 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.

[...some failed attempts to recover...]

> So, is there a way to recover from this situation without drastic
> measures like nuking the whole database.

To answer my own question:

delete from pglogical.queue where message_type='S';
on both nodes seems to have the desired effect.
A vacuum full pglogical.queue afterwards is a good idea to get the
bloated table back to a reasonable size.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>