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).
"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)."
Is there any possible method(maybe by looking into the data dictionary tables/views etc) to see the progress of the Alter statement by which we can estimate the expected completion time of the "Alter" command? I understand pg_stat_activity doesn't show any completion percentage of a statement, but wondering if by any other possible way we can estimate the amount of time it will take in prod for the completion of the ALTER command.
