Thread: Selective logical replication
Hi, I want to use logical replication for implementing multimaster (so all nodes are both sending and receiving changes). But there is one "stupid" problem: how to prevent infinite recursion and not to rereplicate replicated data. I.e. node receives change set from some other node, applies it within some transaction, it is written to the log and ...is replicated to other nodes. In my experiments with receiver_raw/decoder_raw infinite recursion actually doesn't happen because unique constraint violation. But it is neither nice, neither efficient way of stopping infinite recursion. I wonder if there is some better way to prevent some particular transaction from been replicated? Thanks in advance, Konstantin
On 2015/11/19 17:48, konstantin knizhnik wrote: > Hi, > > I want to use logical replication for implementing multimaster (so all nodes are both sending and receiving changes). > But there is one "stupid" problem: how to prevent infinite recursion and not to rereplicate replicated data. > I.e. node receives change set from some other node, applies it within some transaction, it is written to the log and ...is replicated to other nodes. > In my experiments with receiver_raw/decoder_raw infinite recursion actually doesn't happen because unique constraint violation. > But it is neither nice, neither efficient way of stopping infinite recursion. > > I wonder if there is some better way to prevent some particular transaction from been replicated? I wonder if stuff in src/backend/replication/logical/origin.c has anything to do with being able to help with kind of problems you mention. The file header comment has the following text in it: * * This infrastructure is intended to be used in cooperation with logical * decoding. When replaying from a remote system the configured origin is * provided to output plugins, allowing prevention of replication loops and * other filtering. * Thanks, Amit
On Thu, Nov 19, 2015 at 5:48 PM, konstantin knizhnik <k.knizhnik@postgrespro.ru> wrote: > I want to use logical replication for implementing multimaster (so all nodes are both sending and receiving changes). > But there is one "stupid" problem: how to prevent infinite recursion and not to rereplicate replicated data. > I.e. node receives change set from some other node, applies it within some transaction, it is written to the log and ...is replicated to other nodes. > In my experiments with receiver_raw/decoder_raw infinite recursion actually doesn't happen because unique constraint violation. > But it is neither nice, neither efficient way of stopping infinite recursion. > > I wonder if there is some better way to prevent some particular transaction from been replicated? What you are looking for to prevent this infinite recursion is the concept of replication origin: http://www.postgresql.org/docs/devel/static/replication-origins.html Regarding receiver_raw/decoder_raw, I never got around to use that and prevent the problem you are seeing :) But patches are welcome if provided. Regards, -- Michael
On 19 November 2015 at 16:48, konstantin knizhnik <k.knizhnik@postgrespro.ru> wrote:
Hi,
I want to use logical replication for implementing multimaster (so all nodes are both sending and receiving changes).
Like http://bdr-project.org/ ?
But there is one "stupid" problem: how to prevent infinite recursion and not to rereplicate replicated data.
You need replication origins, so you can tell where a change came from and in a mesh topology only send locally-originated tuples to peers.
In a circular forwarding topology you instead filter out a peer's changes when they come back around the circle to you and forward everything else.
That's exactly what the replication origins feature in 9.5 is for. It lets you associate a tuple with the node it came from. When doing logical decoding you can make decisions about whether to forward it based on your knowledge of the tuple's origin and the peer node(s).
This is trivial to implement this on top of the pglogical output plugin - we already have the hooks for origin filtering in place. All you have to do is pass information about which nodes you want to filter out or retain.
See https://github.com/2ndQuadrant/pglogical_output and the unit tests, especially the hook tests.
I wonder if there is some better way to prevent some particular transaction from been replicated?
Replication origins.
On 20 November 2015 at 22:03, Craig Ringer <craig@2ndquadrant.com> wrote:
On 19 November 2015 at 16:48, konstantin knizhnik <k.knizhnik@postgrespro.ru> wrote:Hi,
I want to use logical replication for implementing multimaster (so all nodes are both sending and receiving changes).Like http://bdr-project.org/ ?But there is one "stupid" problem: how to prevent infinite recursion and not to rereplicate replicated data.You need replication origins, so you can tell where a change came from and in a mesh topology only send locally-originated tuples to peers.In a circular forwarding topology you instead filter out a peer's changes when they come back around the circle to you and forward everything else.That's exactly what the replication origins feature in 9.5 is for. It lets you associate a tuple with the node it came from. When doing logical decoding you can make decisions about whether to forward it based on your knowledge of the tuple's origin and the peer node(s).This is trivial to implement this on top of the pglogical output plugin - we already have the hooks for origin filtering in place. All you have to do is pass information about which nodes you want to filter out or retain.
Oh, see also the submission for 9.6.
That said: Multimaster is a lot more complicated than just avoiding circular replication. That's one of a very many complex issues. Dealing with schema changes is way harder than you think because of the queued-but-not-yet-replicated changes in an asynchronous system. That's why we have that horrid global DDL lock in BDR (though it's being improved to be a bit less extreme).
Conflict handling is hard, especially when foreign keys, multiple constraints, etc get involved. There are tons and tons of hairy corner cases.
Handling full table rewrites is a problem because the way Pg performs them internally is currently hard for logical decoding to handle. You don't have a good mapping from the pg_temp table being written to during the rewrite to the final target table. It also means you lose the original replication origin and commit timestamp info. So you need a way to make sure there are no outstanding writes to replicate on that table when you do the full rewrite, like a mini global checkpoint for just one table.
There are lots of things that can break an asynchronous multimaster replication setup, often in subtle ways, that won't break standalone Pg. Want to create an exclusion constraint? Think again. You can't do that safely. Not without just discarding changed tuples that violate the constraint and introducing divergence between node states, anyway. Secondary unique indexes, FK constraints, etc, can all be challenging. A naïve multimaster system is easy to deadlock with simple foreign key use. There are still cases BDR doesn't handle.
I'd really, really love more involvement from others in doing multi-master replication with PostgresQL using logical decoding, replication origins, etc. We've got some amazing infrastructure now, and are in a position to deliver some really cool technology if there's more uptake and interest. So I'd love to work with you to whatever extent it's possible. Lets try to share effort rather than reinvent. Feel free to get in touch off-list if that's better for you at the current stage of your investigations.
(BTW, I'm quite interested in what's being discussed with distributed locking and transactions, since that'd help solve some of the issues with DDL in logical replication. Having a good way to get a global exclusive lock before a table rewrite, for example.)