Tell me more ×
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.
up vote 4 down vote favorite

How does PostgreSQL native replication compare to MySQL?

I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects?

share|improve this question

1 Answer

up vote 12 down vote accepted

Production ready?

Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example.

Setup of replication isn't exactly lovely, but tools like repmgr help somewhat with that, and it's improving slowly with each major release. The ability for pg_basebackup to take a copy of the system using streaming replication is a big help.

In general, a feature simply won't be released in PostgreSQL until it's production ready. Bugs happen, like in any software, but they're typically fixed shortly after they're identified. Really major new features sometimes have bugs and issues discovered after the .0 release, but if so fixing them is a high priority; bugs aren't just left around.

I'm not aware of any issues with streaming replication - sync or async - nor have I seen any reported for quite a while. They were less stable than Pg's usual standard in the .0 releases of the major versions they were introduced in, but both matured quickly and are thoroughly production-ready.

Compare to MySQL?

Pg's native replication is quite different to MySQL's.

MySQL uses logical replication where it sends the logical changes made to table data, table structure, etc, and the replica applies those changes.

PostgreSQL's replication is lower level. It sends the blocks that changed in the tables. It's simpler, easier to get right, and imposes lower load on the replica server, but consumes more network bandwidth and requires more storage on the master to hold not-yet-replicated changes. It's best configured to use streaming replication with WAL-archiving fallback, making it more complex to configure than MySQL's. It replicates low-level changes like VACUUM activity, not just tuple changes, keeping the on-disk state of the replica the same as that of the master. It is incapable of replicating only one database; the whole system must be replicated, which can be frustrating if you have one big, high churn and unimportant database and one small, low-churn and vital database.

All in all, it depends on what you want to do with it.

I view PostgreSQL's replication as considerably better for replicas used for backup, high availability and disaster recovery. Particularly so when combined with point in time recovery (PITR).

On the other hand, it's not as good for read-only reporting replicas because the need to delay the application of replicated data while running long transactions means that you need to either let it cancel very long running queries or fall greatly behind the master, consuming more disk space on the master and forcing it to work harder to keep up.

There's ongoing work to enable logical replication in PostgreSQL, where the logical changes to table structure, table contents, etc are replicated, rather than their on-disk state. Pg's catalog design and support for user-defined everything makes this quite a complex task. Some of the groundwork is being put in place for 9.3, but full logical replication is unlikely to be usable before 9.4.

share|improve this answer
Great answer to a question I also had. Thanks Craig. – swasheck Feb 10 at 3:33
2  
There is one thing about sync rep which surprises some users, but really makes sense if you think about it: the commit of a transaction which is subject to synchronous replication will not return until the transaction has been persisted on at least one cluster besides the master. People coming from some other systems feel that should happen unless the replication attempt takes too long, which would mean "it's synchronous unless it's not," which is not an acceptable guarantee to the PostgreSQL community. Use multiple sync targets to avoid a stall if the replica fails, or use async. – kgrittn Feb 10 at 15:01
@kgrittn Good point re multiple targets. I'm mildly horrified that anyone would want/expect commit to return before the transaction is replicated in synchronous replication; sounds like they really want async replication with a maximum follower gap limit that pauses writes on the master until the followers catch up enough? A perfectly reasonable thing to want, but not sync rep. – Craig Ringer Feb 10 at 22:49
@CraigRinger: What I have seen people ask for is not quite what you stated, they sometimes request "Use sync rep but automatically fall back to async rep if the sync is taking too long." So they do not want to pause the master if it falls too far behind the replica -- that's exactly the case where they want it to confirm commits quickly, without any write to another site. To me that sounds like a case of promising more than is being delivered. They want up-front "Yeah, you have sync rep; you're safe." and after a crash "That committed data is gone; it wasn't really written anywhere else." – kgrittn Feb 18 at 20:21

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.