Which Postgresql Replication Solution to Use? (Asynchronous Multimaster / Bucardo?)

Question

First, I'm not a DBA, so pardon me if any of this question seems "off."

I've written a peer-to-peer multiplayer game (the client) which connects to one of multiple servers for match making.

Currently, there is only one server (a linode, let's call it Server 1) which runs the game's custom matchmaking process and PostgreSQL 8.4 (I will be upgrading this to 9.1, 9.2 or 9.3 if necessary).

The matchmaking process uses libpq asynchronously for all SQL statements. Statements are not too complex, so load balancing is not an issue.

I plan to add more linodes (call them Servers 2, 3, 4, etc.) that run the matchmaking process and PostreSQL as necessary. The challenge is that I want high-availability for all clients. If server 1 is unreachable, then server 2 can be used instead, with access to all the same data.

The original plan was to have all servers connect to Server 1's database and asynchronously send SQL statements via libpq. The problem there is that if Server 1 is ever temporarily offline or unreachable, then every other server will fail.

The "simplest" solution I can imagine is for each of the servers to completely mirror the database. If Server 1 is down, clients can connect to Server 2 which reads and writes to its own database, replicating any changes to Servers 3 and 4 immediately, and Server 1 once it comes back online.

In this fashion, every server would hold an entire "mirrored" copy of the database.

After reading through the introductory sections of PostgreSQL 9.3's documentation on replication, it seems like way to implement this solution would be asynchronous multi-master replication. (Is Bucardo the only choice here?)

The thing I'm worried about with asynchronous replication is SQL inserts. When a new client plays for the first time, a player database entry is created. If Servers 2, 3, and 4 are online and Server 1 is offline, will there be any issues with 1, 3 or 4 if 2 inserts a new player row? (Imagine 1 coming back online and immediately trying to insert another player row.)

Is asynchronous multi-master the right way to go for the above mentioned scenario?

Or, is there a simpler or easier solution that I am overlooking? Perhaps one that doesn't require middleware, but just uses PostgreSQL 9.3's built in replication?

Answer 1

If you're not a DBA - i.e. you're not experienced with the complexities of replication, failover, subtleties of transaction isolation, etc - then if possible do not use multi-master.

There's no multi-master clustering for PostgreSQL, only multi-master replication via 3rd party add-ons. These add-ons have a significant performance impact, but more importantly they don't deal with inter-node locking and transaction isolation. Concurrent transactions on different nodes can see different data, can both modify the same rows, etc.

Frankly, multi-master clustering is pretty underwhelming even in products that do have it available. Performance tends to be pretty poor and scale badly. This isn't a deficiency in the products, it's just that MM clustering is hard and you pretty much get to pick either correct or fast.

Personally, I recommend using two big nodes. Have one node running a streaming replica and the other running as master. If one goes down, fail over to the other. Tune your app and queries properly before adding more capacity.

Answer 2

I was also looking for information concerning this matter. I came across the following open source project.

http://2ndquadrant.com/en/resources/bdr/

They also offer some benchmarks of the various solutions available under the performance section.