Our great sponsors
-
pg-online-schema-change
Discontinued Easy CLI tool for making zero downtime schema changes and backfills in PostgreSQL [Moved to: https://github.com/shayonj/pg-osc]
-
InfluxDB
Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.
This is very interesting, thanks for sharing. I had been noodling on the concept of zero downtime schema changes in postgres as well, and recently started https://github.com/shayonj/pg-online-schema-change. Its inspired by pg_repack and pt-online-schema-change (mysql).
I will give this a spin! Kudos
> We have migrated a few fields from varchar to integer. How would your solution deal with this
There is actually an example in the README on how to change a column from TEXT to INTEGER, the technique would be the same the other way around: https://github.com/fabianlindfors/reshape#alter-column
For the cases that require manual handling, that is a bit tricky. I'm not sure Reshape would be able to automate that in any meaningful way so the best thing might just be to fall back to a standard procedure of making the changes in two separate migrations, where the manual changes are done in between.
> Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.
I have given this some thought before as I wanted to add a migration which can add new foreign keys. I think it can be done by writing some migrations first which update the existing data to conform to the constraint, for example adding missing values. This can be done with `alter_column` right now and there will be more comprehensive migrations for data changes in the future.