Subject: How to have a smooth migration

From: veem v <[email-masked]>
Date: Thu, 15 May 2025 11:39:39 +0530

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. We want to have this migration approach
happen in multiple phases 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. 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?

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?

Regards
Veem

---

From: "Peter J. Holzer" <[email-masked]>
Date: Thu, 15 May 2025 10:21:36 +0200

On 2025-05-15 11:39:39 +0530, veem v wrote:
> 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.
> 

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).

> 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.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 15 May 2025 09:13:56 -0700

On 5/14/25 23:09, veem v wrote:
> 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. We want to have this 
> migration approach happen in multiple phases 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. 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?

This is what Sqitch(https://sqitch.org/) was designed for.

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?

> 
> 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?
> 
> Regards
> Veem

-- 
Adrian Klaver
[email-masked]

---

From: veem v <[email-masked]>
Date: Thu, 15 May 2025 21:59:26 +0530

> 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.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 15 May 2025 10:02:59 -0700

On 5/15/25 09:29, veem v wrote:
> 
> 
>     This is what Sqitch(https://sqitch.org/ <https://sqitch.org/>) was
>     designed for.
> 
> 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.
> 

1) For Postgres Sqitch uses psql as the client for making the changes. 
Therefore you only need access to psql. Also the deployments can be run 
from a machine that is not in the Cloud, as long as you have remote 
access to the Postgres instance.

2) With Sqitch you have:

a) Deploy/verify/revert actions. The verify helps keep out erroneous 
deployments and revert can take you back to a known prior state. Caveat 
the actions are based on SQL/psql scripts you create, they are only 
useful to the extent you make them so.

b) Targets, which are different instances of Postgres you can 
deploy/verify/revert against independently of each other. Useful to try 
your changes against a dev instance before deploying to production.

3) I would strongly suggest:

a) Breaking the changes down into smaller portions, probably best around 
tables having relationships.

b) Create a dev/test Postgres instance to trial changes and test them.

Sqitch is not the only database changes management system out there, it 
is just the one I found to be useful for my needs.