Re: [GENERAL] pglogical bidirectional replication of sequences

On 09/01/2017 02:29 AM, Peter J. Holzer wrote:
> 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
I trust you mean don't use sequences