Hi Kevin, This sounds like a good idea, I will work hard on this idea and let you know the result.
Most appreciated. Kevin Brannen <kbran...@efji.com> 于2020年12月5日周六 上午12:04写道： > *>From:* Olivier Gautherot <ogauthe...@gautherot.net> > > >>5) If you're brave enough, convert your current table as a partition > (rename it to something like table_hist), duplicate the table model under > the same name as now (adjusting the primary key type) and set the INHERITS > on the primary key range. The inheritance should take care of the type > conversion (haven't tried it but it's worth a try). If it works, you will > reach your goal without downtime or significant overhead. > > >Sorry, just tried this one and it failed: type mismatch. > > > > Seems like a sound idea in general. I’d probably rename the tables, let’s > call them “big_hist” for the old big table and “big_split” for the new > partitioned table that being used go forward – assuming the original table > was called “big”. Then create a View that will look at both of those but > call it the same as the old table, and let the view do a type cast on the > old key like big_hist.id::bigint so it matches the new type, because the > view will probably be a union and the type need to match. That way your > application only has to pause long enough to do a few meta-commands then it > all can resume, and like Olivier pointed you, you can fix the data by > moving it from big_hist to big_split in the background as you have time. > > > > I’d probably put it all in a transaction too: > > > > Create table … -- all the commands to create your patitioned table > big_split here > > Begin; > > Alter table big rename to big_hist; > > Create view big select * from big_split union select id::bigint, /* other > cols */ from big_hist; > > Commit; > > > > Try it on a dev system and if it works you’re off and running. I’d expect > the view to slow things down a little, but probably not too much if you > have good indexes. But at least you could transition without major downtime > and then rename “big_split” back to “big” and drop “big_hist” when you’ve > finished the transition. I might even be tempted to add a trigger so that > all new inserts into “big” really go into “big_split” so “big_hist” doesn’t > grow any more. Your imagination is probably the limit. 😊 > > > > HTH, > > Kevin > > . > > > This e-mail transmission, and any documents, files or previous e-mail > messages attached to it, may contain confidential information. If you are > not the intended recipient, or a person responsible for delivering it to > the intended recipient, you are hereby notified that any disclosure, > distribution, review, copy or use of any of the information contained in or > attached to this message is STRICTLY PROHIBITED. If you have received this > transmission in error, please immediately notify us by reply e-mail, and > destroy the original transmission and its attachments without reading them > or saving them to disk. Thank you. >
