Hacker News Reader

Comment by thaumaturgy | Hacker News Reader

Could someone that's really familiar with Postgres give me the skinny on replication? I like Postgres a lot, but finally moved all of our data storage to MySQL because, according to the documentation I read just a couple months ago, Postgres couldn't really do multi-master replication.

This post, dated prior to the documentation I read before the switch, seems to suggest it would do it just fine as long as it was asynchronous. I'm not clear though on just how ugly its async replication might be (http://www.postgresql.org/docs/8.4/static/high-availability....).

I'd like to use dbmail to store mail in a database replicated across multiple servers (which would need fairly reliable replication), as well as syslog and other logging facilities to sql (where reliability isn't quite so big of a deal).

Is Postgres up to this now, or not?

View on HN

No, Postgres' built-in replication doesn't support multi-master.

With one of the many third-party replication packages that have grown around PostgreSQL (specifically, Bucardo v5 which is in beta right now), you can do multiple — as in more than two — writeable masters, though, as the linked page notes, the replication is asynchronous. With older versions of Bucardo, you can do two masters — again, asynchronously (though you can technically get more than two if you want to engage in some significant configuration gymnastics).

What they're calling "synchronous multi-master" replication is actually implemented using "two-phase commit", which isn't replication per se, but rather application code using a database feature that allows specifically crafted database interactions to be written to multiple databases, and only successfully committing if the participating nodes agree that they've all committed.

> What they're calling "synchronous multi-master" replication is actually implemented using "two-phase commit", which isn't replication per se, but rather application code using a database feature that allows specifically crafted database interactions to be written to multiple databases, and only successfully committing if the participating nodes agree that they've all committed.

I am not sure I understood, what is the difference between the two?

As commonly defined, "replication" is something that happens in, and is managed by the database itself, or a daemon written for that purpose (whether the daemon works at the logical level, replicating database tuples, or at the physical level, replicating disk blocks), and of which an application can be largely, if not wholly agnostic. With two-phase commit, OTOH, an application has to be specifically written to take advantage of that functionality.

What happens if one of the nodes goes offline? System stops accepting requests or it keeps going and ends up in an inconsistent state?

Failure modes are defined by the two-phase commit protocol, the standard way of managing distributed database changes:

http://en.wikipedia.org/wiki/Two-phase_commit_protocol

You should use the "this page in other versions" mechanism at the top of the page you linked to so you can see more recent documentation: most of what PostgreSQL has with regards to replication is from versions more recent than 8.4 (the version you were reading).

http://www.postgresql.org/docs/9.2/static/high-availability....

(I'm curious why you want to do replication for dbmail: it would seem like what you really need/want is partitioning; e-mail metadata, especially with effective indexes, requires a lot of write throughput, and synchronous replication is going to largely cause the same write load on all systems.)

Thanks, I hadn't even noticed that link at the top.

(To answer your other question: at the moment our mail load isn't severe enough to pose a problem for synchronous replication, and I'm just aiming for the ability to have multiple mail hosts share the same mail data with no single point of failure. As the mail load outgrows current infrastructure, I'll start partitioning -- although I haven't figured out how to do that just yet. I've considered a distributed file system, but hammer has only just recently started to look like it's up to the task.)

You can accomplish that with synchronous multi-slave replication with failover: as far as I can work out in my head right now, the result will be similar to the behavior you will get from a synchronous multi-master setup assuming you can work things out so the slave can be used for read-only queries.

I don't think I can do that without writing my own mail daemons for pop and imap, unfortunately, which I'd rather not do at this stage. All of the ones that I know of expect to be able to read/write metadata over the same connection to the same database or filesystem.

Failover setups aren't my favorite option. They seem to be easy to get wrong, and when you get them wrong, they only do wrong things at the exact moment that you most need them to be doing right things.

So, if you are doing synchronous multi-master you will also need to do explicit failover (in this case, not from server A to server B, but from server A/B to only server B): otherwise, a partition between the servers would be catastrophic to your data integrity (as there would be no way for the servers to know whether they should start accepting data that might be different from its buddy, as both think the other is offline).

Once you start thinking in terms of multiple servers (whether it be based on replication or partitioning) you have to start thinking about these kinds of complex corner case issues, as you have moved from working with "a server" to "a distributed system", with all of the associated theoretical limits (such as CAP).

You're right, although the MySQL binary log used for replication handles this fairly gracefully for reasonable outage periods. Collisions are still possible after a resync, but it tries pretty hard to resolve them using timestamps on the transactions.

I'm working on some software to automatically manage outages, deploying new server instances, re-syncing databases, etc., but that's quite a few steps away from where we're at right now. For near-term purposes, anything that could do as good of a job at multi-master replication as MySQL can would be just fine.

I can then only wish you luck in your attempt to take an off-the-shelf system (dbmail) that was designed to be used with an ACID database and plop it on top of what is now an only eventually consistent data store without first rewriting it to tolerate those semantics ;P.

> You should use the "this page in other versions" mechanism at the top of the page you linked to so you can see more recent documentation: most of what PostgreSQL has with regards to replication is from versions more recent than 8.4 (the version you were reading).

I wonder if there's a way the Postgres guys could fix that with some Google-bot directives or something. Usually, the linked page when searching is for an older version, and you have to go click 9.X yourself if you're interested in the latest and greatest.

I'd say that all in all that multi-master is still generally pretty hard to use -- there are packages that do work very well (consider the .org registry, which is slony), but I can't really say with conviction that they are cohesive and easy to use.

You can use synchronous replication in the new version to ensure commits have been flushed to standby before getting a commit ACK. You should run at least two standbys, because otherwise the standby going down halts progress on the primary (after all, it cannot guarantee 2-safety if the one and only secondary is down).

But as for multi-master for scale-out, it's a no-go without some whacking. It may still be worth it, but it's definitely not The Best Thing about Postgres today.

The plain-old replication -- both synchronous and asychronous -- though, works wonderfully. If you just need read scale-out and can tolerate some staleness in results, usually measured in milliseconds (but certain circumstances can make it greater, and these are knowable/measurable in real time) then I think you'd be served pretty well.

You might take a look at Postgres-XC (http://postgres-xc.sourceforge.net/), and see if it meets your needs.

Oooh. That looks really nice, thank you very much for pointing me to that.

as already mentioned there's no in core multi master replication, but other then the already listed solutions, you can have look at :

http://postgres-xc.sourceforge.net/

the project is sponsored by Enterprisedb and NTT

a quote from the home page:

"Postgres-XC is an open source project to provide a write-scalable, synchronous multi-master, transparent PostgreSQL cluster solution. It is a collection if tightly coupled database components which can be installed in more than one hardware or virtual machines."

[edit: sorry for the noise I did't notice that jeff davis already comment about postgres-xc]

Reply on news.ycombinator.com