Обсуждение: Change from BIGINT to INT in prod.
Dear All,
I am trying to change some specific columns on some tables in prod and looking at the best ways to do it with very minimal downtime.
So when i try running alter table table_name alter column column_name type INT; , it is hanging and starts providing a lock on the table.
I have this option in mind with but with a doubt.
I have this option in mind with but with a doubt.
- Stop postgres and application connections
- take a schema dump of the specific tables (it takes few seconds)
- take a complete dump of those table or take just a data dump only (-a option) -- (This takes 3 to 5 mins)
- drop all the specific tables from prod DB
- restore the schema dump
- run the script to change from BIGINT to INT
- Then restore the data. Since the data will take more than 30 mins to restore, i wanted to allow the application to start accessing the DB while the restore is ongoing.
So my question is will it be a good idea to bring back up the DB with applications connecting and same time restoring the data from those specific tables?
So my question is will it be a good idea to bring back up the DB with applications connecting and same time restoring the data from those specific tables?
If this is not a good idea...any suggestions on how i can make these changes to the LIVE DB with minimal downtime?
Many thanks
Ndoh
Ndoh
On Thu, Nov 5, 2020 at 11:03 AM Walters Che Ndoh <chendohw@gmail.com> wrote:
Dear All,I am trying to change some specific columns on some tables in prod and looking at the best ways to do it with very minimal downtime.So when i try running alter table table_name alter column column_name type INT; , it is hanging and starts providing a lock on the table.
I'm not seeing any practical difference between the table being locked and the table being empty. I'm not seeing the point of doing this specific conversion at all really - and without understanding how these tables fit into the bigger scheme of things it is difficult to provide useful suggestions.
So my question is will it be a good idea to bring back up the DB with applications connecting and same time restoring the data from those specific tables?
If this is not a good idea...any suggestions on how i can make these changes to the LIVE DB with minimal downtime?
IMO this is impossible to answer generically. Downtime is probably not the only measure you care about - if queries start taking 10 times as long to complete than before, but you are still "up", you may very well still have issues.
If you cannot avoid doing this marginally useful exercise consider whether it can be done in stages. Setup things so the new state and the current state can run concurrently and then slowly move records from the current state to the new one. Triggers and views can help here.
David J.
On Thu, 2020-11-05 at 11:23 -0700, David G. Johnston wrote: > > So when i try running alter table table_name alter column column_name type INT; , it is hanging and starts providinga lock on the table. It has to rewrite the table and needs an AccessExclusive lock. You have to suspend all concurrent activity on that table. > I'm not seeing the point of doing this specific conversion at all really Exactly; this exercise is not necessary. The few bytes you are saving are not worth the trouble. Yours, Laurenz Albe -- +43-670-6056265 CYBERTEC PostgreSQL International GmbH Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: https://www.cybertec-postgresql.com
On Thu, Nov 5, 2020 at 1:03 PM Walters Che Ndoh <chendohw@gmail.com> wrote: > > Dear All, > > I am trying to change some specific columns on some tables in prod and looking at the best ways to do it with very minimaldowntime. > > So when i try running alter table table_name alter column column_name type INT; , it is hanging and starts providing alock on the table. > > I have this option in mind with but with a doubt. > > - Stop postgres and application connections > - take a schema dump of the specific tables (it takes few seconds) > - take a complete dump of those table or take just a data dump only (-a option) -- (This takes 3 to 5 mins) > - drop all the specific tables from prod DB > - restore the schema dump > - run the script to change from BIGINT to INT > - Then restore the data. Since the data will take more than 30 mins to restore, i wanted to allow the application to startaccessing the DB while the restore is ongoing. > > So my question is will it be a good idea to bring back up the DB with applications connecting and same time restoring thedata from those specific tables? > > If this is not a good idea...any suggestions on how i can make these changes to the LIVE DB with minimal downtime? > If you really want to do it with minimal downtime, I'd suggest following a process more like this: https://doordash.engineering/2020/10/21/hot-swapping-production-data-tables/ Robert Treat https://xzilla.net