public inbox for [email protected]
help / color / mirror / Atom feedHow to have a smooth migration
5+ messages / 3 participants
[nested] [flat]
* How to have a smooth migration
@ 2025-05-15 06:09 veem v <[email protected]>
2025-05-15 08:21 ` Re: How to have a smooth migration Peter J. Holzer <[email protected]>
2025-05-15 16:13 ` Re: How to have a smooth migration Adrian Klaver <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: veem v @ 2025-05-15 06:09 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to have a smooth migration
2025-05-15 06:09 How to have a smooth migration veem v <[email protected]>
@ 2025-05-15 08:21 ` Peter J. Holzer <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Peter J. Holzer @ 2025-05-15 08:21 UTC (permalink / raw)
To: [email protected]
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.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to have a smooth migration
2025-05-15 06:09 How to have a smooth migration veem v <[email protected]>
@ 2025-05-15 16:13 ` Adrian Klaver <[email protected]>
2025-05-15 16:29 ` Re: How to have a smooth migration veem v <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-05-15 16:13 UTC (permalink / raw)
To: veem v <[email protected]>; pgsql-general <[email protected]>
On 5/14/25 23:09, 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. 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 protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to have a smooth migration
2025-05-15 06:09 How to have a smooth migration veem v <[email protected]>
2025-05-15 16:13 ` Re: How to have a smooth migration Adrian Klaver <[email protected]>
@ 2025-05-15 16:29 ` veem v <[email protected]>
2025-05-15 17:02 ` Re: How to have a smooth migration Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: veem v @ 2025-05-15 16:29 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Peter J. Holzer <[email protected]>; +Cc: pgsql-general <[email protected]>
>
>
>
> 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?
>
>
>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to have a smooth migration
2025-05-15 06:09 How to have a smooth migration veem v <[email protected]>
2025-05-15 16:13 ` Re: How to have a smooth migration Adrian Klaver <[email protected]>
2025-05-15 16:29 ` Re: How to have a smooth migration veem v <[email protected]>
@ 2025-05-15 17:02 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2025-05-15 17:02 UTC (permalink / raw)
To: veem v <[email protected]>; Peter J. Holzer <[email protected]>; +Cc: pgsql-general <[email protected]>
On 5/15/25 09:29, veem v wrote:
>
>
> This is what Sqitch(https://sqitch.org/ <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?
>
>
>
> 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.
>
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.
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-05-15 17:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-15 06:09 How to have a smooth migration veem v <[email protected]>
2025-05-15 08:21 ` Peter J. Holzer <[email protected]>
2025-05-15 16:13 ` Adrian Klaver <[email protected]>
2025-05-15 16:29 ` veem v <[email protected]>
2025-05-15 17:02 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox