Can someone through some light , in case we get 5-6hrs downtime for this change , then what method should we choose for this Alter operation?
We can't really answer that. Only you know what resources you have, what risk/reward you are willing to handle, and how long things may take. For that latter item, your best bet is to try this out on the same/similar hardware and see how long it takes. Do a smaller table and extrapolate if you need to. Or promote one of your replicas offline and modify that. I've given you a low-risk / medium-reward option with check constraints, but for the ALTER TABLE options you really need to try it and see (on non-prod).
it seems the "USING" clause takes more time as compared to normal ALTER. But again I don't see any way to see the progress and estimated completion time. Can you share your thoughts on this?
There should be no difference if they are doing the same conversion.
Will this approach be faster/better as compared to the simple "alter table alter column approach" as above
Seems a lot more complicated to me than a simple ALTER. But measurement is key. Create a new test cluster using pgBackRest or whatever you have. Then run your ALTER TABLE and see how long it takes (remember that multiple columns can be changed in a single ALTER TABLE statement).
