Re:Better Database Migrations in Postgres
by zmoazeni on Wednesday September 13, 2017 @01:58PM
(#15240814)
No. https://githubengineering.com/gh-ost-github-s-online-migrati... has an excellent section on why triggers fail (at least in the MySQL world. I imagine the locking story is similar in postgresql).
I don't work for GitHub but we had the same problems using pt-online-schema-change independently of their issues (lots of locking contention affecting the app negatively). We're finally moving to gh-ost for our large/risky migrations and so far it's amazing.
Re:Better Database Migrations in Postgres
by qaq on Wednesday September 13, 2017 @08:34AM
(#15237649)
and now say you have a 1TB table still want to go this route ?
Re:Better Database Migrations in Postgres
by notyourday on Wednesday September 13, 2017 @08:50AM
(#15237749)
Yes, the bigger the table the safer this route is for online migrations.
* create a new schema in a table.new
* install update triggers on table.old to write the same content into table.new
* backfill table.new from table.old
* swap table.new and table.old