The biggest issue is that the data will be incrementing while you do the structural changes. How you handle that is going to depend on the question raised by Peter J. Holzer: Is this being done in place on one Postgres instance or between separate Postgres instances?
Thank you. Yes, these tables are going to be part of the same database. Never use sqitch though , but was wondering if we can do it with the stored simple proc as the number of table is very small <20 and also the max size of table in <50MB. Also , missed to add , this is a cloud RDS database and so not sure we can have this tool there.
To answer the questions specifically raised by Peter J. Holzer .
Are V1 and V2 different databases or do plan to do this in-place? Answer:- Yes both the versions of the table are in the same database.
What is the purpose of doing it in multiple phases? Do you have lengthy acceptance tests during which new data will accumulate? Answer:- Yes. Actually there will be a test kind of thing happen with the new code pointing to version V2 tables and to get comfortable. But teh delta data will be very small.
By "rollback" do mean a transaction rollback or some other means of restoring the previous state? Answer:- "rollbak" means pointing the old code back to the version V1 tables.
