Home / server / Questions / 222238
Accepted
mrkafk
Asked: 2011-01-14 13:53:15 +0800 CST

USABLE multi-master replication for Postgres?

  • 772

  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?

replication postgresql

7 Answers

  1. 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.

    • 6

  2. 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

  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.

    • 3

  4. The answer to that is a resounding no.

    • 2

  5. 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 :)

    • 1

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

    • 1
  7. Best Answer

    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!

    • -2