Re: Selective logical replication - Mailing list pgsql-hackers
| From | Craig Ringer |
|---|---|
| Subject | Re: Selective logical replication |
| Date | |
| Msg-id | CAMsr+YFbmvDNb7zNK3zJOTohEsWNitzOLTTbo2q97TgFEXtwmQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Selective logical replication (Craig Ringer <craig@2ndquadrant.com>) |
| List | pgsql-hackers |
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.)
pgsql-hackers by date: