Feedback-ID: i76984098:Fastmail
Message-ID: <6cfe275f-2d7f-426e-b92f-c23a84d53d75@aklaver.com>
Date: Thu, 15 May 2025 10:02:59 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How to have a smooth migration
To: veem v <veema0000@gmail.com>, "Peter J. Holzer" <hjp-pgsql@hjp.at>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAB+=1TUU2rgjNzz6T05RPwCi1xjKr=9cwhX=Jdyo4zPiakH-zQ@mail.gmail.com>
 <060c8abd-7448-4af9-8265-0ca8b81ccf86@aklaver.com>
 <CAB+=1TV7J-ORtH8EtbuqbheLdSRsw4YBh+Cw38X5cfRbQsTUnQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAB+=1TV7J-ORtH8EtbuqbheLdSRsw4YBh+Cw38X5cfRbQsTUnQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/6cfe275f-2d7f-426e-b92f-c23a84d53d75%40aklaver.com>
Precedence: bulk

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