On 2025-05-15 11:39:39 +0530, veem v wrote: > Hi, > Its postgres database behind the scenes. > > We have a use case in which the customer is planning to migrate data from an > older version (V1) to a newer version (V2). For V2, the tables will be new, > but > their structure will be similar to the V1 version with few changes in > relationship might be there.
Are V1 and V2 different databases or do plan to do this in-place?
> We want to have this migration approach happen in multiple phases
What is the purpose of doing it in multiple phases? Do you have lengthy
acceptance tests during which new data will accumulate?
> in which each time the delta data from version V1 will be moved
> to version- V2 and then final cutover will happen to V2 if all looks good or
> else rollback to V1.
By "rollback" do mean a transaction rollback or some other means of
restoring the previous state?
> The tables are smaller in size like max ~100K records in tables.
>
> My question is, is it a good idea to have an approach in which we will have
> procedures created to move the delta data in every phase and schedule those
> using some tasks for each table. Or any other strategy should we follow?
That sounds definitely doable and I have done similar things in the
past.Especially for a relatively small database getting the diff to
apply is not much of a problem. Doing it in the right order might be a
bit of a challenge but deferring constraints should help. Also be
mindful of what should happen if data in V2 is changed between the
phases (e.g. by a test that creates new records).
> Also another thing to note , we have used sequences as primary keys in some
> tables and they have FK relationships with other tables, so the same sequence
> number in version V2 will cause issues/conflict, so how should we handle this
> scenario? Should we just create new sequences with higher start values?
If you can use the same key values in V2 as in V1, just update the
sequences to the new start point at the end of each migration. If they
are in the same database you could even use the same sequences to avoid
conflicts. If you need to generate new key values (for example, you are
merging two tables into one), you will need a translation table (which
could be just some extra columns in the new table).
hjp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | h...@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
