On 2017-09-01 09:57:52 -0600, Rob Sargent wrote: > 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. > > [and it blew up] > I trust you mean don't use sequences I trust you don't mean what I understood ;-). Seriously: Sequences in general are fine and very useful. I think they should be used where appropriate. Sequences and logical replication don't mix well. That still doesn't mean that you can't use sequences, you just have to be careful how you use them. Since replicating sequence state doesn't really work, I think it is best to use independent sequences on each node and just configure them in a way that they can not produce the same values. A naive approach would be to use MINVALUE/MAXVALUE/START WITH to ensure non-overlapping ranges. A somewhat more elegant approach is to increment by $n$ (the number of nodes in the cluster) and use different start values (I got that idea from http://thedumbtechguy.blogspot.co.at/2017/04/demystifying-pglogical-tutorial.html). There are other ways to get unique ids: A uuid should work pretty well in most cases, and in some even a random 64 bit int might be enough. hp -- _ | Peter J. Holzer | we build much bigger, better disasters now |_|_) | | because we have much more sophisticated | | | hjp@xxxxxx | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Attachment:
signature.asc
Description: Digital signature
