4 ms·
This is one of the areas where Postgres is so far behind MySQL is embarrassing. Zero downtime migrations in MySQL have been a common method for over 10 years. T
by dkhenry 5y ago
This is one of the areas where Postgres is so far behind MySQL is embarrassing. Zero downtime migrations in MySQL have been a common method for over 10 years. This solution is far from ideal due to the use of Triggers which can greatly increase the load on the database and slow down transactions. If you don't have a lot of load on your DB thats fine, but if you are pushing your DB this will bring it down.
In MySQL they started with triggers with PT-OSC, but now there is GH-OST which does it with Replication. You can do something like this with Postgres by using Logical replication, but its still requires hand holding, and to my knowledge there is no way to "throttle" the migration like you can with GH-OST. Where I work now we are building all this out so we can have first class online migrations, but the chasm is still pretty big.
- merb 5y ago> but its still requires hand holding, and to my knowledge there is no way to "throttle" the migration like you can with GH-OST eh? you only do a basebackup and than you can begin the logical replication. at some point you than you do a failover? chtitux basically described the process which is extremly simple.
- dkhenry 5y agoUp until the table is so large that the copy takes longer then your WAL retention so you can't ever catch up. Like all things in Postgres, it works great up to a point, and then you are stuck. You also have to Logically replicate the entire schema because postgres won't logically replicate from table to table
- merb 5y agoif that is the case you should have enough manpower to make use of basebackups+wal-e/wal-g and than once your up, flipping it over to logical. of course it is not as easy as having vitess, but vitess was not built in a day.
- montroser 5y agoYep, we did MySQL dual primary-primary circular replication on a cluster with ten read replicas, thousands of qps per box, all the way back in 2005. We failed over back and forth from one primary to the other on a schedule every few weeks, just to practice and affirm we could.
- ransom1538 5y agoMysql admin here. If i need to create an in sync copy of a mysql db machine i take a snapshot then let replication catch up. Done. I can do whatever I want to this new machine (test alters, drop columns, etc) and let it catch up. If i like the box i just attach a few replicas - promote it to master, done. The Triggers trick is awesome (go percona!) -- but with cloud vms it takes a few seconds to fire up a complete insync replica. I find this safer than triggers running all over a prod database [esp. if you have thousands of vms!]. IMHO. When I read about Postgres it's like being transported to 2005.
- throwdbaaway 5y agoThe article is about migrating from RDS Postgres 9.5 to RDS Postgres 12, which is only feasible with Bucardo. Major version upgrade is less of a pain point these days with logical replication, and even Google Cloud SQL started to support logical replication since about a week ago, after a painfully long wait. Meanwhile, you are talking about schema migration? In that case, gh-ost indeed is the best tool available. With Postgres, most types of schema migration can be done instantly with just a metadata change, or otherwise be done concurrently without locking the table. So a tool like gh-ost is not as vital, but still valuable for edge cases such as: - altering the column type: requires table rewrite - adding an index concurrently onto a busy server: a throttle would be nice