Re: Moving from PG 9-era table inheritance to PG 12 range partioning

Поиск
Список
Период
Сортировка
От Keith
Тема Re: Moving from PG 9-era table inheritance to PG 12 range partioning
Дата
Msg-id CAHw75vv82OS7uBFupTpCq-kCUBjt0aajCbeDDk2hRinj0YPP-A@mail.gmail.com
обсуждение исходный текст
Ответ на Moving from PG 9-era table inheritance to PG 12 range partioning  (Wells Oliver <wells.oliver@gmail.com>)
Список pgsql-admin
Дерево обсуждения


On Thu, Oct 31, 2019 at 7:27 PM Wells Oliver <wells.oliver@gmail.com> wrote:
I was thinking of moving a set of partitioned tables using inheritance (done in PG 96.) to the new(ish) range partitioning available in 10/11/12...

My plan was to make another schema and create the new range-partitioned tables, then copy the data into them, then drop the older tables and re-set the schema for the new partitioned tables to the old schema, essentially meaning the new partitioned tables will take the place of the older tables when I am ready. Same exact structure, just different under the hood.

Any issues with this plan? Will changing the schema of the new partitioned table break anything?

--


You can probably just un-inherit the tables and attach them to the new partitioned parent vs having to reinsert all the data. Just have to set the constraints properly in the attach command to match the data they have. Then rename the parent tables to swap the names around. Can probably do it all in a single transaction as well so it's transparent to anyone using the table. Just may take a while if it has to revalidate the data in the tables and it will be locked out for reads/writes for the duration. But should still be quicker than rewriting all the data.

Keith

В списке pgsql-admin по дате отправления:

Предыдущее
От: Wells Oliver
Дата:
Сообщение: Moving from PG 9-era table inheritance to PG 12 range partioning
Следующее
От: Wells Oliver
Дата:
Сообщение: to_tsvector() chopping off trailing 's' characters