3 ms·

The ability to spin up and sync a follower db, for eventual cut over... man I'd kill for that in MySQL.

gtCameron 12y ago
The feature you are looking for is replication, I have used that in MySQL many times to do the exact thing you are talking about, works great. Example - http://blog.onetechnical.com/2012/05/29/quick-mysql-replication-to-move-a-database/ http://blog.onetechnical.com/2012/05/29/quick-mysql-replicat...
vidarh 12y ago
Replication is an area where MySQL is still far ahead of Postgres. And I say that as someone who has almost exclusively used Postgres for the last 6 years or so. Postgres is finally starting to catch up, but it's taken forever.
- jeltz 12y ago
  Not really. PostgreSQL has excellent synchronous replication which can be controlled per transaction. This is missing in MySQL. What PostgreSQL is missing is logical replication (both master-slave and master-master. So depending on your use case either could be ahead of the other. I am almost 100% certain PostgreSQL will have have stable master-master replication within 1.5 years. The team working on it has come really far along.
  - vidarh 12y ago
    Lacking master-master is a huge thing. And I still curse at Postgres whenever I set up replication. There's no good reason why I should have to worry about wal-archiving, for example. I'll admit I've never, ever wanted synchronous replication, so that's not a feature I've cared about. I agree it will probably get there. But from my point of view it is still substantially lagging.
    - jeltz 12y ago
      You will not have to worry about WAL archiving anymore in 9.4 with the addition of replication slots.
bryantraywick 12y ago
Others have replied with basically the same thing but replication has been in MySQL for a long time. Just last night I set up replication for a single MySQL server to a new 3-node Galera cluster then switched over the Galera cluster as the primary with only a few seconds of downtime. The downtime could have been entirely eliminated if the standalone MySQL server had binary logging enabled already. Galera cluster is an awesome multi-master solution and I've had nothing but excellent experience with it in production environments. We currently typically direct writes at a single node and spread reads in round-robin to all three nodes using an HAProxy front-end to the cluster, but there is nothing preventing us from spreading writes across the cluster if it was needed. If the current write node goes down there is only a couple seconds of interruption as a new write node is elected and the application reconnects. Furthermore, if a node goes down rejoining is completely automatic once it starts back up with no slowdown in reads and writes. I don't have near as much PostgreSQL experience but replication and failover have been serious pain points from an operations stand-point when I have had to use it. I appreciate the end-user features of PostgreSQL, but operationally MySQL/MariaDB (plus Galera cluster) are miles ahead of anything I've seen for PostgreSQL.

The feature you are looking for is replication, I have used that in MySQL many times to do the exact thing you are talking about, works great. Example - http://blog.onetechnical.com/2012/05/29/quick-mysql-replication-to-move-a-database/ http://blog.onetechnical.com/2012/05/29/quick-mysql-replicat...

vidarh 12y ago

Replication is an area where MySQL is still far ahead of Postgres. And I say that as someone who has almost exclusively used Postgres for the last 6 years or so. Postgres is finally starting to catch up, but it's taken forever.

jeltz 12y ago
Not really. PostgreSQL has excellent synchronous replication which can be controlled per transaction. This is missing in MySQL. What PostgreSQL is missing is logical replication (both master-slave and master-master. So depending on your use case either could be ahead of the other. I am almost 100% certain PostgreSQL will have have stable master-master replication within 1.5 years. The team working on it has come really far along.
- vidarh 12y ago
  Lacking master-master is a huge thing. And I still curse at Postgres whenever I set up replication. There's no good reason why I should have to worry about wal-archiving, for example. I'll admit I've never, ever wanted synchronous replication, so that's not a feature I've cared about. I agree it will probably get there. But from my point of view it is still substantially lagging.
  - jeltz 12y ago
    You will not have to worry about WAL archiving anymore in 9.4 with the addition of replication slots.

bryantraywick 12y ago

Others have replied with basically the same thing but replication has been in MySQL for a long time. Just last night I set up replication for a single MySQL server to a new 3-node Galera cluster then switched over the Galera cluster as the primary with only a few seconds of downtime. The downtime could have been entirely eliminated if the standalone MySQL server had binary logging enabled already. Galera cluster is an awesome multi-master solution and I've had nothing but excellent experience with it in production environments. We currently typically direct writes at a single node and spread reads in round-robin to all three nodes using an HAProxy front-end to the cluster, but there is nothing preventing us from spreading writes across the cluster if it was needed. If the current write node goes down there is only a couple seconds of interruption as a new write node is elected and the application reconnects. Furthermore, if a node goes down rejoining is completely automatic once it starts back up with no slowdown in reads and writes. I don't have near as much PostgreSQL experience but replication and failover have been serious pain points from an operations stand-point when I have had to use it. I appreciate the end-user features of PostgreSQL, but operationally MySQL/MariaDB (plus Galera cluster) are miles ahead of anything I've seen for PostgreSQL.