Thread: Updating a pre-10 partitioned table to use PG 10 partitioning
I'm trying to update an existing table that was created in PG9.6 using the old approach with manual inheritance and check constraints to make use of the new approach in PG 10 using 'partitioned by', 'attach partition', etc. I ran into some how-to's on the internet, but they all assume you start partitioning from a new table instead of modifying an existing table into partitions. It seems to me that would be a fairly common requirement, no? I realise that I skip over the fact that I'm not attempting to partition an unpartitioned table, but instead attempt to update the existing partitioning to the new approach. Currently the table in question really only has 1 partition filtered on a type column (we plan to create 1 partition per type), but this table is already 1.5GB on disk and has a number of indexes and views associated to it. Creating a new table out of it and recreating all the related stuff seems a bit roundabout... It would be nice if I could update the table to use PG 10 partitioning using just a simple alter table, but I can't seem to find the right syntax. What is a good approach here? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
-----Original Message-----
From: Alban Hertroys [mailto:haramrae@gmail.com]
Sent: Thursday, January 11, 2018 10:41 AM
To: Postgres General <pgsql-general@postgresql.org>
Subject: Updating a pre-10 partitioned table to use PG 10 partitioning
I'm trying to update an existing table that was created in PG9.6 using the old approach with manual inheritance and check constraints to make use of the new approach in PG 10 using 'partitioned by', 'attach partition', etc.
I ran into some how-to's on the internet, but they all assume you start partitioning from a new table instead of modifying an existing table into partitions. It seems to me that would be a fairly common requirement, no?
I realise that I skip over the fact that I'm not attempting to partition an unpartitioned table, but instead attempt to update the existing partitioning to the new approach.
Currently the table in question really only has 1 partition filtered on a type column (we plan to create 1 partition per type), but this table is already 1.5GB on disk and has a number of indexes and views associated to it. Creating a new table out of it and recreating all the related stuff seems a bit roundabout...
It would be nice if I could update the table to use PG 10 partitioning using just a simple alter table, but I can't seem to find the right syntax.
What is a good approach here?
______________________________________________________________________________________________________________________
Not in one step, but it’s doable.
Here is what I do when switching from inheritance to declarative partitioning.
1. Alter all partitions to "NO INHERIT:
ALTER <partition_table_name> NO INHERIT <parent_table_name>
2. Create new partitioned table just like old “parent” table with different name using “PARTITION BY …” clause.
3. Drop old “parent” table.
4. Rename new partitioned table giving it <parent_table_name>.
5. Finally, “old” partitions to new partitioned table (previously “parent”):
ALTER TABLE < parent_table_name > ATTACHE PARTITION <partition_table_name> FOR VALUES …
Regards,
Igor Neyman
On Thu, Jan 11, 2018 at 04:40:30PM +0100, Alban Hertroys wrote: > What is a good approach here? By having a some point an origin and target table in the same cluster, you would just need to define things properly. I have not checked, but perhaps this is the kind of use cases where pg_partman (https://github.com/keithf4/pg_partman/) can become handy? Perhaps Keith, who maintains the tool, has some insight on the matter. -- Michael
Attachment
On Thu, Jan 11, 2018 at 04:40:30PM +0100, Alban Hertroys wrote:
> What is a good approach here?
By having a some point an origin and target table in the same cluster,
you would just need to define things properly. I have not checked, but
perhaps this is the kind of use cases where pg_partman
(https://github.com/keithf4/pg_partman/) can become handy? Perhaps
Keith, who maintains the tool, has some insight on the matter.
--
Michael