Re: How to have a smooth migration

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
adrian.klaver@aklaver.com