Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
  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?

share|improve this question
    
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'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 '11 at 12:30
    
You are not limited to two nodes. –  foocorpluser Sep 7 '12 at 15:11
2  
Fall 2012, PG 9.2: devopsangle.com/2012/08/24/… –  Neil McGuigan Dec 16 '12 at 23:38
add comment

7 Answers 7

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.

share|improve this answer
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
add comment

The answer to that is a resounding no.

share|improve this answer
    
its been a few years since i did research, but my company came to this conclusion when we tried. –  GruffTech Jan 13 '11 at 22:01
add comment

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.

share|improve this answer
    
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 5 at 8:31
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
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 '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
add comment

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

share|improve this answer
add comment
up vote -2 down vote accepted

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!

share|improve this answer
2  
@mrafk - would you post/link the "working code" you have? –  warren Aug 8 '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 8 '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 5 '12 at 13:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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