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?
Add a comment
|
1 Answer
Production ready?
Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example, as are AWS RDS standbys and read replicas. Streaming replication is used almost universally with PostgreSQL.
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 (and do so from another standby) 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 serious 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.
(Update: There was a specific bug in the new 9.3 version prior to 9.3.4 that did cause replication issues in some cases; users of 9.3 should update to 9.3.4 immediately. Older versions are not affected.)
The only caveat I want to mention is a a minor detail with synchronous replication: If you commit on the master, then cancel the query after it commits while it waits for the replica to confirm, it's treated as commited on the master even before it's replicated. You get the same effect by restarting the master while waiting for the replica to reply. In practice this is an irrelevance, but it's about the only problem I can think of.
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 (in 9.5 and below; future versions may also add logical replication). 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 has been put in place for 9.4, but full logical replication is unlikely to be usable before 9.6 or later.
answered Feb 10, 2013 at 2:09
-
-
6There 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.– kgrittnFeb 10, 2013 at 15:01
-
1@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. Feb 10, 2013 at 22:49
-
1@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."– kgrittnFeb 18, 2013 at 20:21
-