Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

share|improve this answer
    
Okay, understood. One master and one slave. Can Postgres 9.3 do this with just built in functionality or is additional middleware required? And can you recommend a weblink guide to setup this way? –  Nat Weiss Sep 10 '13 at 21:51
    
@NatWeiss Built-in. Lots of documentation in the PostgreSQL manual - failover, hot standby, etc. More on the PostgreSQL wiki. See repmgr.org for tools that help. –  Craig Ringer Sep 12 '13 at 20:14

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.