From: | Keith <keith(at)keithf4(dot)com> |
---|---|
To: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Moving from PG 9-era table inheritance to PG 12 range partioning |
Date: | 2019-11-01 00:35:57 |
Message-ID: | CAHw75vv82OS7uBFupTpCq-kCUBjt0aajCbeDDk2hRinj0YPP-A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Thu, Oct 31, 2019 at 7:27 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)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?
>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Wells Oliver | 2019-11-01 18:28:17 | to_tsvector() chopping off trailing 's' characters |
Previous Message | Wells Oliver | 2019-10-31 23:26:14 | Moving from PG 9-era table inheritance to PG 12 range partioning |