17

  1. I tried Postgres-XC and it doesn't implement full SQL yet (like SERIAL)

  2. Postgres-R looks interesting but it's "not production ready" according to developers.

So I used pgpool-II 3.0.1. Yes, it works nicely. But as far as I can see it's only for 2 PG nodes.

Is there anything out there that is actually production ready AND capable of working with multiple PG nodes?

mrkafk
  • 379
  • 1
  • 2
  • 7
  • A few years ago we landed up with the same problem. Eventually we moved all our stuff to Oracle. Hopefully you can find usable multimaster replication these days, i haven't looked... Good luck, none the less. – grufftech Jan 13 '11 at 21:58
  • 2
    [PostgreSQL](http://www.postgresql.org/docs/8.3/static/high-availability.html)'s own documentation says to use a middleware application :) .."**Synchronous Multimaster Replication** .. PostgreSQL does not offer this type of replication, though PostgreSQL two-phase commit (PREPARE TRANSACTION and COMMIT PREPARED) can be used to implement this in application code or middleware" – warren Aug 09 '11 at 12:30
  • You are not limited to two nodes. – foocorpluser Sep 07 '12 at 15:11
  • http://blog.2ndquadrant.com/bdr-is-coming-to-postgresql-9-6/ – Neil McGuigan Sep 27 '16 at 21:44

7 Answers7

6

Have you considered Bucardo? It's asynchronous multimaster. It hasn't completely caught on and is not a general solution, but it might be worth a try.

Peter Eisentraut
  • 3,575
  • 1
  • 23
  • 21
  • 1
    I wasn't specific enough apparently: I need synchronous replication. Besides, what is the meaning of this in FAQ? "Can Bucardo replicate between more than two masters? No. Currently, Bucardo only supports master to master (as well as master to many slaves of course)." So is it multi-master or not? – mrkafk Jan 13 '11 at 22:18
  • 4
    Only if your definition of "multi" is "2"! – hmallett Jan 13 '11 at 23:49
  • Please note that starting with Bucardo 5 the limitation of just 2 masters has been lifted – Joril Jul 14 '15 at 13:32
3

I have to agree with Peter's assessment: There's no really good multi-master replication for Postgres right now. (Doing true multi-master replication is a very difficult problem, and I'm not enamored with any of the available solutions.)

Cribbing Wikipedia's list of potential solutions you may want to investigate:

PostgreSQL offers multiple solutions for multi-master replication, including solutions based on two phase commit. There's Bucardo, rubyrep, PgPool and PgPool-II, PgCluster and Sequoia as well as some proprietary solutions. Another promising approach, implementing eager (synchronous) replication is Postgres-R, however it is still in development. Yet another project, implementing synchronous replication is Postgres-XC. Postgres-XC also is still under development.

voretaq7
  • 79,345
  • 17
  • 128
  • 213
  • Wow, just reading that list causes shock and terror for me. :) – Peter Eisentraut Jan 13 '11 at 22:36
  • For me it's depression and loathing :-) – voretaq7 Jan 14 '11 at 16:23
  • I would think that using a system similar to etcd for config and communications would be possible, perhaps running any update statement within two phase commit... a hard part would be keeping a node out until it is caught up and matches other nodes... I'd really love a near automagic solution for this – Tracker1 Jul 05 '14 at 08:31
3

This is heavy Java oriented, but native database client APIs can be bridged to JDBC data sources. Tungsten Myosotis is an example for MySQL native to JDBC bridging.

  • Tungsten Enterpriese is good for multi-master asynchronous. I think it works for MySQL, PostgreSQL, and Oracle. It can run standalone or embedded in a Java application. I have seen it work for MySQL, but they claim PostgreSQL. Their Replicator component is open-source, but full solution has more parts and requires licensing costs. Continuent originally had Sequoia for multi-master synchronous but they abandoned it, and created Tungsten instead for multi-master asynchronous - they consider scale out a more strategic business than synchronous ACID consistency. Tungsten is written in Java, hence that is why they offer Myosotis to bridge native database clients.

  • SymmetricDS is good for multi-master asynchronous. It is open-source. It installs/uninstalls triggers to capture updates, instead of bin logging. It can run standalone or embedded in a Java application.

  • HA-JDBC is good for multi-master synchronous. It supercedes older defunct software like C-JDBC and Sequoia. It is open-source. It uses two-phase commit and works for PostgreSQL, MySQL, Oracle, SQL Server, Derby, Sybase, and many others via dialects. It is mainly for embedded, so embed in a Java application to bridge it to PostgreSQL. Distributed locks, sequences, time, rand, and so on are handled by jGroups from Redhat/JBoss. One nice feature is transaction mode "serial" instead of "parallel", if your app experienced deadlocks and does not support rollback. I successfully used this "serial" mode to retrofit a legacy app that was not DB-cluster aware, so it was missing transaction retry code. Serial mode saved the day and avoided a nasty rewrite.

  • H2 is good for multi-master synchronous. It is open-source. It supports standalone databases or clusters using two-phase commit, similar to HA-JDBC architecture, but it is all in one instead of requiring an extra component for two-phase commit. Not sure if it does distributed locks itself, or depends on third-party like jGroups or Hazelcast.

Any JDBC based replication for PostgreSQL and other databases needs a native to JDBC bridge, unless your application is already written in Java. For MySQL, Tungsten Enterprise offers an optional component called Myosotis. I sucessfully used this to bridge PHP/Perl/C/mysqlclient to JDBC, where JDBC data source happened to be a HA-JDBC proxy data source pointing to a 4-node MySQL/InnoDB cluster.

Tungsten supports PostgreSQL in their Replicator and Router components, but not sure about the Myosotis component. Maybe. Tungsten Replicator/Router components are for multi-master asynchronous, but Myosotis can bridge you to an alternative JDBC back-end like HA-JDBC or H2 for synchronous.

If there is a PostgreSQL native to JDBC bridge I would like to hear about it. In theory, any database with a JDBC Type 4 driver can be bridged. Type 4 JDBC speaks native database protocol just like native client interface for that database, so there should be a one-to-one mapping of native calls to JDBC calls.

Justin
  • 131
  • 3
2

The answer to that is a resounding no.

Peter Eisentraut
  • 3,575
  • 1
  • 23
  • 21
1

I've been using londiste for the last 2 years for multi-master replication in postgresql.

You put your tables in queues using pg_queue and you can subscribe as many other databases you want to each queue, the replication is atomical by queue and it's very resiliant.

You can read about londiste here (http://pgfoundry.org/projects/skytools/), this is what the Skype guys use for their cluster, also they created it, so it's double the cool :)

lynxman
  • 9,157
  • 3
  • 24
  • 28
  • Hmm that's interesting, but according to what I've seen here: http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling , Londiste is Master-Slave and Asynchronous? So how can it be multi-master? Additionally, I need synchronous replication really: transaction should fail if any of the (active) cluster nodes fails. – mrkafk Jan 24 '11 at 14:16
  • This replication is post-transactional otherwise it would be quite slow – lynxman Jan 24 '11 at 14:53
  • I don't mean to sound like pain in the ass (nitpicking), but... 1. I've used pgpool-II and transactions went through quite quickly (although I haven't done benchmarks), and 2. even though individual transaction might be slower, I don't see a good reason for the overall throughput of transactions to be low. Anyway, perhaps the more important point is how is Londiste multi-master? Can I write to pg server 1 and have it replicated to 2, and write to pg server 2 and have it replicated to server 1? – mrkafk Jan 24 '11 at 17:58
1

If you're still interested, try this one: http://www.symmetricds.org/ (Java only)

Community
  • 1
Herberth Amaral
  • 163
  • 6
-2

I found usable "multi-master" replication system:

  1. get RabbitMQ http://www.rabbitmq.com/ - it's a message middleware.

  2. configure a Rabbit MQ cluster in Rabbit.

  3. create queue for each node in a cluster and bind them to 'fanout' type exchange.

This way a message sent to any node and any queue gets replicated to all other nodes. I have a working code for this!

mrkafk
  • 379
  • 1
  • 2
  • 7
  • 2
    @mrafk - would you post/link the "working code" you have? – warren Aug 08 '11 at 15:13
  • 2
    What does this have to do with replication with postgres? This will distribute messages, but where are you getting the data messages/updates from the DB and how is it updating the nodes receiving the messages on the message queue? – monksy Aug 08 '11 at 15:14
  • 3
    This may be a solution to the fundamental problem you were facing, but it is *not* an answer to this question. – Tom Anderson Nov 05 '12 at 13:40