In this article, we will focus on upgrade from 9.4 (and above) versions to PostgreSQL 10, leveraging the full features of native partitioning.

Test Environment: OS: CentOS 7 PostgreSQL version: 9.6.3 and 10.0 pg_partman version: 3.1.0

Migrating from 9.x version (partitioned table with inheritance) to pg10 (native partitioning)

Consider a database with several tables some of which are quite huge. In 9.6, as known, PostgreSQL has improved the performance involving huge tables by what is called as “partitioning.” These huge tables are managed internally as partitioned tables using inheritance feature. Envisage we have a table “Inventory” with a million records. This table has four child tables (inv_quarter1, inv_quarter2, inv_quarter3, and inv_quarter4) which inherit the properties from parent table Inventory.

postgres=# \d+ test.inventory Table "test.inventory" Column | Type | Modifiers | Storage | Stats target | Description --------------+-----------------------+-----------+----------+--------------+------------- product | character varying(10) | | extended | | units_sold | character varying(10) | | extended | | invoice_date | date | | plain | | units_remain | character varying(10) | | extended | | Triggers: orders_insert_trigger BEFORE INSERT ON test.inventory FOR EACH ROW EXECUTE PROCEDURE orders_insert_simple() Child tables: test.inv_quarter1, test.inv_quarter2, test.inv_quarter3, test.inv_quarter4 postgres=# \d+ test.inv_quarter1 Table "test.inv_quarter1" Column | Type | Modifiers | Storage | Stats target | Description --------------+-----------------------+-----------+----------+--------------+------------- product | character varying(10) | | extended | | units_sold | character varying(10) | | extended | | invoice_date | date | | plain | | units_remain | character varying(10) | | extended | | Check constraints: "inv_quarter1_invoice_date_check" CHECK (invoice_date >= '2017-01-01'::date AND invoice_date < '2017-04-01'::date) Inherits: test.inventory 

To upgrade to pg10, a full database dump and restore is required. In doing so, the restored huge tables will continue to have the inheritance feature.

-bash-4.2$ /usr/pgsql-10/bin/pg_dump -d postgres -p 5433 -Fc  > /tmp/pg96.dump

-bash-4.2$ /usr/pgsql-10/bin/pg_restore -d postgres /tmp/pg96.dump

postgres=# \d+ inventory
                                            Table "test.inventory"
    Column    |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 product      | character varying(10) |           |          |         | extended |              |
 units_sold   | character varying(10) |           |          |         | extended |              |
 invoice_date | date                  |           |          |         | plain    |              |
 units_remain | character varying(10) |           |          |         | extended |              |
Triggers:
    orders_insert_trigger BEFORE INSERT ON inventory FOR EACH ROW EXECUTE PROCEDURE public.orders_insert_simple()
Child tables: inv_quarter1,
              inv_quarter2,
              inv_quarter3,
              inv_quarter4

NB: It is a good practice to do pg_dump and pg_restore using the same version binaries. In these test cases, since we are migrating to pg10, pg10 binaries are being used for both dump and restore.

To exploit the performance benefit of these tables using the pg10 native partitioning, we need to manually create the parent and child tables in the pg10 environment and then do pg_restore of the data only.

create table test.inventory (product varchar(10),
            units_sold varchar(10),
            invoice_date date,
            units_remain varchar(10))
   partition by RANGE (invoice_date);
CREATE TABLE

create table test.inv_quarter1 partition of inventory 
for values from ('2017-01-01') to ('2017-04-01');
CREATE TABLE
create table test.inv_quarter2 partition of inventory 
for values from ('2017-04-01') to ('2017-07-01');
CREATE TABLE
create table test.inv_quarter3 partition of inventory 
for values from ('2017-07-01') to ('2017-10-01');
CREATE TABLE
create table test.inv_quarter4 partition of inventory 
for values from ('2017-10-01') to ('2018-01-01');
CREATE TABLE

-bash-4.2$ /usr/pgsql-10/bin/pg_dump -d postgres -Fc -t test.inventory -t 'test.inv_quarter*' -a  -f /tmp/pg96.dump
-bash-4.2$ /usr/pgsql-10/bin/pg_restore -d postgres  /tmp/pg96.dump
-bash-4.2$ /usr/pgsql-10/bin/psql
psql (10.0)
Type "help" for help.
postgres=# \d+ test.inventory
                                            Table "test.inventory"
    Column    |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 product      | character varying(10) |           |          |         | extended |              |
 units_sold   | character varying(10) |           |          |         | extended |              |
 invoice_date | date                  |           |          |         | plain    |              |
 units_remain | character varying(10) |           |          |         | extended |              |
Partition key: RANGE (invoice_date)
Partitions: test.inv_quarter1 FOR VALUES FROM ('2017-01-01') TO ('2017-04-01'),
            test.inv_quarter2 FOR VALUES FROM ('2017-04-01') TO ('2017-07-01'),
            test.inv_quarter3 FOR VALUES FROM ('2017-07-01') TO ('2017-10-01'),
            test.inv_quarter4 FOR VALUES FROM ('2017-10-01') TO ('2018-01-01')

postgres=# select count(*) from only test.inventory;
 count
-------
     0
(1 row)

postgres=# select count(*) from only test.inv_quarter1;
 count
--------
 246600
(1 row)

postgres=# select count(*) from only test.inv_quarter2;
 count
--------
 249340
(1 row)

postgres=# select count(*) from only test.inv_quarter3;
 count
--------
 252071
(1 row)

postgres=# select count(*) from only test.inv_quarter4;
 count
--------
 251988
(1 row)

The above is an example for RANGE partitioning. Similar steps should be followed for LIST partitioning.

Once the structure of the tables are done, pg_restore of only data would load the records into the appropriate child tables. Upgrade is complete with marrowbone of pg10’s native partitioning.

Migrating from 9.4 version (partitioned tables managed by pg_partman)

Likewise, let us assume a 9.4 environment in which the partitioned tables are being managed pg_partman. For illustration, let us say the table “Sales” has 1 million records and has 12 child tables. Let us see the two methods with which we can upgrade to pg10.

postgres=# \dx
                               List of installed extensions
    Name    | Version |   Schema   |                     Description
------------+---------+------------+------------------------------------------------------
 pg_partman | 3.1.0   | partman    | Extension to manage partitioned tables by time or ID


postgres=# \d+ test.sales
                                                               Table "test.sales"
   Column   |           Type           |                            Modifiers                            | Storage  | Stats target | Description
------------+--------------------------+-----------------------------------------------------------------+----------+--------------+-------------
 inv_number | integer                  | not null default nextval('test.sales_inv_number_seq'::regclass) | plain    |              |
 product    | text                     |                                                                 | extended |              |
 inv_date   | timestamp with time zone | not null default now()                                          | plain    |              |
Triggers:
    sales_part_trig BEFORE INSERT ON test.sales FOR EACH ROW EXECUTE PROCEDURE test.sales_part_trig_func()
Child tables: test.sales_p2017_04,
              test.sales_p2017_05,
              test.sales_p2017_06,
              test.sales_p2017_07,
              test.sales_p2017_08,
              test.sales_p2017_09,
              test.sales_p2017_10,
              test.sales_p2017_11,
              test.sales_p2017_12,
              test.sales_p2018_01,
              test.sales_p2018_02,
              test.sales_p2018_03,
              test.sales_p2018_04

For upgrade, do a pg_dump of the 9.x database. Before performing the pg_restore on pg10, ensure the below postgresql.conf parameters are set. A restart of the pg10 server is needed for these parameters to take effect.

--- postgresql.conf settings in pg10

shared_preload_libraries = 'pg_partman_bgw' 
pg_partman_bgw.interval = 3600 
pg_partman_bgw.role = 'postgres' 
pg_partman_bgw.dbname = 'postgres'

Now pg_restore would install the partman extensions and respective tables with their metadata as well.

postgres=# \d+ test.sales
                                                                Table "test.sales"
   Column   |           Type           | Collation | Nullable |                  Default                  | Storage  | Stats target | Description
------------+--------------------------+-----------+----------+-------------------------------------------+----------+--------------+-------------
 inv_number | integer                  |           | not null | nextval('sales_inv_number_seq'::regclass) | plain    |              |
 product    | text                     |           |          |                                           | extended |              |
 inv_date   | timestamp with time zone |           | not null | now()                                     | plain    |              |
Triggers:
    sales_part_trig BEFORE INSERT ON sales FOR EACH ROW EXECUTE PROCEDURE sales_part_trig_func()
Child tables: sales_p2017_04,
              sales_p2017_05,
              sales_p2017_06,
              sales_p2017_07,
              sales_p2017_08,
              sales_p2017_09,
              sales_p2017_10,
              sales_p2017_11,
              sales_p2017_12,
              sales_p2018_01,
              sales_p2018_02,
              sales_p2018_03,
              sales_p2018_04

postgres=# select * from partman.part_config;
 parent_table | control  | partition_type | partition_interval | constraint_cols | premake | optimize_trigger | optimize_constraint | epoch | inherit_fk | r
etention | retention_schema | retention_keep_table | retention_keep_index | infinite_time_partitions | datetime_string | automatic_maintenance | jobmon | su
b_partition_set_full | undo_in_progress | trigger_exception_handling | upsert | trigger_return_null | template_table
--------------+----------+----------------+--------------------+-----------------+---------+------------------+---------------------+-------+------------+--
---------+------------------+----------------------+----------------------+--------------------------+-----------------+-----------------------+--------+---
---------------------+------------------+----------------------------+--------+---------------------+----------------
 test.sales   | inv_date | partman        | 1 mon              |                 |       4 |                4 |                  30 | none  | t          |
         |                  | t                    | t                    | f                        | YYYY_MM         | on                    | t      | f
                     | f                | f                          |        | t                   |
(1 row)

As observed and understood, in 9.x data is loaded into child tables using TRIGGERS. When restored as is, the newly created child tables in pg10 get data loaded using triggers. We can still continue to manage these tables using pg_partman, however, the native partitioning feature will not be utilized.

Conversely, to use the NATIVE PARTITIONING feature of pg10 and to continue to use pg_partman to manage the partitioned tables, first off identify the partitioned tables in 9.x. Create parent tables manually on the pg 10 environment. Using pg_partman, then create the child tables using pg10’s native RANGE or LIST partition feature. Of note, pg_partman 3.1.0 was used for this demonstration.

postgres=# CREATE TABLE test.sales (inv_number serial, product text, inv_date timestamptz NOT NULL DEFAULT now()) partition by RANGE (inv_date);
CREATE TABLE

postgres=# SELECT partman.create_parent('test.sales', 'inv_date', 'native', 'monthly');
 create_parent
---------------
 t
(1 row)

postgres=# \d+ test.sales
                                                                  Table "test.sales"
   Column   |           Type           | Collation | Nullable |                    Default                     | Storage  | Stats target | Description
------------+--------------------------+-----------+----------+------------------------------------------------+----------+--------------+-------------
 inv_number | integer                  |           | not null | nextval('test.sales_inv_number_seq'::regclass) | plain    |              |
 product    | text                     |           |          |                                                | extended |              |
 inv_date   | timestamp with time zone |           | not null | now()                                          | plain    |              |
Partition key: RANGE (inv_date)
Partitions: test.sales_p2017_04 FOR VALUES FROM ('2017-04-01 00:00:00+05:30') TO ('2017-05-01 00:00:00+05:30'),
            test.sales_p2017_05 FOR VALUES FROM ('2017-05-01 00:00:00+05:30') TO ('2017-06-01 00:00:00+05:30'),
            test.sales_p2017_06 FOR VALUES FROM ('2017-06-01 00:00:00+05:30') TO ('2017-07-01 00:00:00+05:30'),
            test.sales_p2017_07 FOR VALUES FROM ('2017-07-01 00:00:00+05:30') TO ('2017-08-01 00:00:00+05:30'),
            test.sales_p2017_08 FOR VALUES FROM ('2017-08-01 00:00:00+05:30') TO ('2017-09-01 00:00:00+05:30'),
            test.sales_p2017_09 FOR VALUES FROM ('2017-09-01 00:00:00+05:30') TO ('2017-10-01 00:00:00+05:30'),
            test.sales_p2017_10 FOR VALUES FROM ('2017-10-01 00:00:00+05:30') TO ('2017-11-01 00:00:00+05:30'),
            test.sales_p2017_11 FOR VALUES FROM ('2017-11-01 00:00:00+05:30') TO ('2017-12-01 00:00:00+05:30'),
            test.sales_p2017_12 FOR VALUES FROM ('2017-12-01 00:00:00+05:30') TO ('2018-01-01 00:00:00+05:30'),
            test.sales_p2018_01 FOR VALUES FROM ('2018-01-01 00:00:00+05:30') TO ('2018-02-01 00:00:00+05:30'),
            test.sales_p2018_02 FOR VALUES FROM ('2018-02-01 00:00:00+05:30') TO ('2018-03-01 00:00:00+05:30'),
            test.sales_p2018_03 FOR VALUES FROM ('2018-03-01 00:00:00+05:30') TO ('2018-04-01 00:00:00+05:30'),
            test.sales_p2018_04 FOR VALUES FROM ('2018-04-01 00:00:00+05:30') TO ('2018-05-01 00:00:00+05:30')

A pg_dump of these tables using “only data” on the 9.x server and then a pg_restore in pg10 would complete the upgrade.

-bash-4.2$ /usr/pgsql-10/bin/pg_dump -d postgres -Fc -t test.sales -t 'test.sales_p*' -a -f /tmp/pg96_data.dump
-bash-4.2$ /usr/pgsql-10/bin/pg_restore -d postgres  /tmp/pg96_data.dump
-bash-4.2$ /usr/pgsql-10/bin/psql
psql (10.0)
Type "help" for help.

postgres=# select count(*) from test.sales;
 count
--------
 999999
(1 row)

postgres=# select count(*) from only test.sales;
 count
-------
     0
(1 row)

postgres=# select count(*) from test.sales_p2017_04;
 count
-------
 75960
(1 row)

Conclusion: Pg_partman has introduced in version 3.1.0 “native” way of managing the partitioned tables. Since pg_partman 3.1.0 can be installed in PostgreSQL 9.4 and above, upgrade can be done from 9.4 onwards. For environments not managed by pg_partman, this condion does not apply.

Summarizing the above, harbouring the thought of upgrading the current version of your server to pg10, consider the huge tables benefit from all the groovy features of PostgreSQL 10.