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(dot)klaver(at)aklaver(dot)com