2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Caterina's PlanetPostgreSQL3 / How to migrate from inheritance-based partitioning to declarative partitioning...
Caterina Magini

How to migrate from inheritance-based partitioning to declarative partitioning in PostgreSQL

April 30, 2020/1 Comment/in Caterina's PlanetPostgreSQL /by Caterina Magini

Partitioning is one of the coolest features in the latest PostgreSQL versions.
PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature.

This article provides a guide to move from inheritance based partitioning to declarative partitioning, using the native features found in PostgreSQL 11+.

The process itself moves the child tables, detaching them from the inheritance partition and attaching to the new declarative partitioned table. Note these instructions do not cover moving to declarative partitioning on PostgreSQL 10 since some key features that make this migration easier were not yet implemented.
Also, this procedure assumes that the parent table in the inheritance partitioning is empty.
The following steps are tested on a PostgreSQL 12 instance, but can also be used on PostgreSQL 11.

Initial notes used in this article

Suppose we have a table partitioned with the old method of inheritance, with child tables, triggers, constraints and so on, as in the following example:

sales=# \d+ orders
                                               Table "public.orders"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 product_id | integer                     |           | not null |         | plain    |              |
 address    | text                        |           | not null |         | extended |              |
 order_time | timestamp without time zone |           | not null |         | plain    |              |
Triggers:
    orders_insert_trigger BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION orders_insert()
Child tables: orders_2018,
              orders_2019,
              orders_2020
Access method: heap

A child table looks like this:

sales=# \d+ orders_2018
                                            Table "public.orders_2018"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 product_id | integer                     |           | not null |         | plain    |              |
 address    | text                        |           | not null |         | extended |              |
 order_time | timestamp without time zone |           | not null |         | plain    |              |
Indexes:
    "orders_2018_idx" btree (order_time)
Check constraints:
    "orders_2018_order_time_check" CHECK (order_time >= '2018-01-01'::date AND order_time < '2019-01-01'::date)
Inherits: orders
Access method: heap

Overview of the steps to move child partitions

The way we recommend moving this partitioning schema to a declarative one is by following these steps:

  1. Create a new table with the same columns as the parent table from the inheritance schema
  2. For each child table in the inheritance schema perform these steps
    • Detach the child table from its parent using ALTER TABLE with NO INHERIT
    • Attach the former child table to the new declarative partition using the ATTACH PARTITION keyword
  3. Create needed indexes on the new parent table. Also views, functions and other objects
  4. Drop the original parent table used with inheritance partitioning
  5. Rename the new parent table so it replaces the old one

Preparing for the migration to declarative partitioning

Before starting the migration process, it’s important to halt any processes that may be automatically maintaining your partitions sets (creating new child tables and/or applying retention policies).
Make sure you disable all maintenance jobs that could perform changes directly or indirectly on the set of tables to migrate, be that through cronjobs or execution of tools like pg_partman.

As first step, we suggest to take a logical backup at database level of the table partitioned through inheritance and all the child tables. For example, you could run something like that:

pg_dump -F c -f sales.dmp sales -v

This is not mandatory, but it’s strongly suggested in order to be able to restore the data in case something goes wrong while performing the next steps.

Initial step

Next, we need to create a new parent table using native partitioning, taking care to use the same definition of the old parent table.
We have to specify the column we want to use as the partition key in the PARTITION BY clause. This choice should be natural, once we have checked the constraints on the child tables.

In the example above the constraints are based on the order_time field. So we can partition FOR RANGE using order_time as the partition key, in this way:

CREATE TABLE orders_part (
     product_id    INT NOT NULL,
     address       TEXT NOT NULL,
     order_time    TIMESTAMP NOT NULL
 ) PARTITION BY RANGE (order_time);

Moving child tables to the new declarative schema

It is a good idea to take the next steps in a single transaction. By doing so, if you run into any issues before you’ve completed the migration process, you can simply rollback and return to the state your database was before the transaction started.
So, please, run:

BEGIN;

The first major step in this migration process now is to remove all the child tables from the old parent, using the NO INHERIT clause, using the following statement in the case of our example above:

ALTER TABLE orders_2018 NO INHERIT orders;

This has to be done for each child table.
Now we need to insert the boundary values for the existing child tables using the ATTACH PARTITION command in the declarative partitioning, as below:

ALTER TABLE orders_part
ATTACH PARTITION orders_2018 
FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00');

Again, we need to perform such a step for each child table for our declarative partitioning.

You may also need to create indexes on the new parent table. From PostgreSQL 11 this can be done by adding the index only once for the partitioned table and it automatically applies to all partitions, existing and future.
In this example, all the child-tables already had the required index and PostgreSQL was intelligent enough not to create a new index similar to another index already existing. So, it will suffice just creating an index on the partitioned table, so that the optimizer knows about it.

CREATE INDEX orders_time ON orders (order_time);

If you have some other SQL objects (such as views, constraints, etc…) on the inheritance-based parent table, don’t forget to recreate them on the new parent table.

And remember that if you still have data in the inheritance parent table you will have to move those rows separately. You can set a DEFAULT partition for that, although that’s discouraged. Instead we suggest finding such rows and creating new child tables which they can fit in. You’ll have to move them manually to the appropriate partition.

Finally, you can drop the inheritance parent table and all the objects created on it, and rename the new partitioned table with the same name of the inheritance parent, in order that all the queries, functions, etc. continue to work properly on the new partitioned table instead of on the old one:

DROP TABLE orders CASCADE;
ALTER TABLE orders_part RENAME TO orders;

At this point you can drop the constraints on the child tables:

ALTER TABLE orders_2018 DROP CONSTRAINT orders_2018_order_time_check;

Run the \d+ command on the parent table and on the individuals partitions to check that everything went fine:

sales_part=# \d+ orders
                                         Partitioned table "public.orders"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 product_id | integer                     |           | not null |         | plain    |              |
 address    | text                        |           | not null |         | extended |              |
 order_time | timestamp without time zone |           | not null |         | plain    |              |
Partition key: RANGE (order_time)
Indexes:
    "orders_time" btree (order_time)
Partitions: orders_2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'),
            orders_2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00'),
            orders_2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00')

The migration process should be completed now but don’t forget to commit your work:

COMMIT;

A special “Thank you” to my new colleague Ashutosh Bapat, who wrote this blog article, from which I took a cue and that I adapted to PostgreSQL 12.

Tags: constraints, inheritance, migration, native, Partitioning, trigger
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
1 reply
  1. Ted Kim
    Ted Kim says:
    November 15, 2020 at 12:19 am

    Much appreciate for the valuable article, Caterina

    It is nice guide to implement declarative partitioning migration from inheritance-based one.

    For next step, I’m looking for automated adding partition solution that’s hard to find.

    Keep inspiring PostgreSQL users with your expertise…

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
How to use Tree Machine Learning model with 2UDA – PostgreSQL and Orange (Part... Webinar: KNN Indexing in PostgreSQL [Follow Up] Webinar: KNN Indexing in PostgreSQL [Follow Up]
Scroll to top
×