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?

4
  • 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, 2011 at 21:58
  • 2
    PostgreSQL'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 9, 2011 at 12:30
  • You are not limited to two nodes. Sep 7, 2012 at 15:11

7 Answers 7

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.

3
  • 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, 2011 at 22:18
  • 4
    Only if your definition of "multi" is "2"!
    – hmallett
    Jan 13, 2011 at 23:49
  • Please note that starting with Bucardo 5 the limitation of just 2 masters has been lifted
    – Joril
    Jul 14, 2015 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.

3
  • Wow, just reading that list causes shock and terror for me. :) Jan 13, 2011 at 22:36
  • For me it's depression and loathing :-)
    – voretaq7
    Jan 14, 2011 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 5, 2014 at 8: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.

2

The answer to that is a resounding no.

1
  • its been a few years since i did research, but my company came to this conclusion when we tried.
    – grufftech
    Jan 13, 2011 at 22:01
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 :)

3
  • Hmm that's interesting, but according to what I've seen here: wiki.postgresql.org/wiki/… , 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, 2011 at 14:16
  • This replication is post-transactional otherwise it would be quite slow
    – lynxman
    Jan 24, 2011 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, 2011 at 17:58
1

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

-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!

3
  • 2
    @mrafk - would you post/link the "working code" you have?
    – warren
    Aug 8, 2011 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 8, 2011 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. Nov 5, 2012 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.