PostgreSQL從繼承到分區（三）

三、Pg_partman

3.1 介紹

pg_partman是基于PostgreSQL分區開發的一個分區表管理工具，通過多個引入函數實現了對分區表的管理，相比手工創建分區表、觸發器函數、觸發器顯得更加快捷方便，同時提供了對分區表的日常維護與管理功能。

其實現原理是將約束、函數、觸發器、分區表的創建以及基礎管理命令均寫入函數，通過函數的調用即可方便創建與維護，并且避免了手工創建引入錯誤。

3.2 安裝

下載地址： https://github.com/keithf4/pg_partman

編譯安裝：

[postgres@localhost ~]$ cd pg_partman-master
[postgres@localhost pg_partman-master]$ make
[postgres@localhost pg_partman-master]$ make install

創建partman使用的空間：

postgres=# create schema partman;
CREATE SCHEMA

引入擴展模塊pg_partman：

postgres=# create extension pg_partman with schema partman ;
CREATE EXTENSION
postgres=# \dx
                               List of installed extensions
    Name    | Version |   Schema   |                     Description                      
------------+---------+------------+------------------------------------------------------
 pg_partman | 1.5.1   | partman    | Extension to manage partitioned tables by time or ID
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

查看自動生成的對象：

postgres=# set search_path to partman;
SET
postgres=# \d
            List of relations
 Schema  |    Name     | Type  |  Owner   
---------+-------------+-------+----------
 partman | part_config | table | postgres
(1 row)
{擴展模塊pg_partman引入后在partman模式中生成一個配置記錄表part_config}

postgres=# \d part_config
               Table "partman.part_config"
        Column        |  Type   |       Modifiers        
----------------------+---------+------------------------
 parent_table         | text    | not null
 type                 | text    | not null
 part_interval        | text    | not null
 control              | text    | not null
 constraint_cols      | text[]  | 
 premake              | integer | not null default 4
 retention            | text    | 
 retention_schema     | text    | 
 retention_keep_table | boolean | not null default true
 retention_keep_index | boolean | not null default true
 datetime_string      | text    | 
 last_partition       | text    | 
 undo_in_progress     | boolean | not null default false
Indexes:
    "part_config_parent_table_pkey" PRIMARY KEY, btree (parent_table)
    "part_config_type_idx" btree (type)
Check constraints:
    "part_config_type_check" CHECK (check_partition_type(type))
    "positive_premake_check" CHECK (premake > 0)

3.3 創建管理分區表

新建一個用于測試的schema：

postgres=# create schema test;
CREATE SCHEMA

創建主表：

postgres=# create table test.part_test(col1 serial, col2 text, col3 timestamptz DEFAULT now() NOT NUll);
CREATE TABLE
postgres=# \d test.part_test
                                      Table "test.part_test"
 Column |           Type           |                           Modifiers                           
--------+--------------------------+---------------------------------------------------------------
 col1   | integer                  | not null default nextval('test.part_test_col1_seq'::regclass)
 col2   | text                     | 
 col3   | timestamp with time zone | not null default now()

調用pg_partman提供的create_parent函數生成分區表以及約束、觸發器函數和觸發器：

postgres=# select partman.create_parent('test.part_test', 'col3', 'time-static', 'half-hour');
 create_parent 
---------------
 
(1 row)
postgres=# \d+ test.part_test
                                                          Table "test.part_test"
 Column |           Type           |                           Modifiers                           | Storage  | Stats target | Description 
--------+--------------------------+---------------------------------------------------------------+----------+--------------+-------------
 col1   | integer                  | not null default nextval('test.part_test_col1_seq'::regclass) | plain    |              | 
 col2   | text                     |                                                               | extended |              | 
 col3   | timestamp with time zone | not null default now()                                        | plain    |              | 
Triggers:
    part_test_part_trig BEFORE INSERT ON test.part_test FOR EACH ROW EXECUTE PROCEDURE test.part_test_part_trig_func()
Child tables: test.part_test_p2014_02_21_0330,
              test.part_test_p2014_02_21_0400,
              test.part_test_p2014_02_21_0430,
              test.part_test_p2014_02_21_0500,
              test.part_test_p2014_02_21_0530,
              test.part_test_p2014_02_21_0600,
              test.part_test_p2014_02_21_0630,
              test.part_test_p2014_02_21_0700,
              test.part_test_p2014_02_21_0730
Has OIDs: no
{在主表上創建了trigger并建立了繼承關系}


postgres=# select now();
              now              
-------------------------------
 2014-02-21 05:37:35.764547+08
(1 row)
postgres=# \d+ test.part_test_p2014_02_21_0330
                                                  Table "test.part_test_p2014_02_21_0330"
 Column |           Type           |                           Modifiers                           | Storage  | Stats target | Description 
--------+--------------------------+---------------------------------------------------------------+----------+--------------+-------------
 col1   | integer                  | not null default nextval('test.part_test_col1_seq'::regclass) | plain    |              | 
 col2   | text                     |                                                               | extended |              | 
 col3   | timestamp with time zone | not null default now()                                        | plain    |              | 
Check constraints:
    "part_test_p2014_02_21_0330_partition_check" CHECK (col3 >= '2014-02-21 03:30:00+08'::timestamp with time zone AND col3 < '2014-02-21 04:00:00+08'::timestamp with time zone)
Inherits: test.part_test
Has OIDs: no
{在分區表上創建了check約束}

創建了觸發器函數：

postgres=# \df
                                  List of functions
 Schema |           Name           | Result data type | Argument data types |  Type   
--------+--------------------------+------------------+---------------------+---------
 test   | part_test_part_trig_func | trigger          |                     | trigger
(1 row)
postgres=# select prosrc from pg_proc where proname='part_test_part_trig_func';
                                                  prosrc                                                  
----------------------------------------------------------------------------------------------------------
                                                                                                         +
         BEGIN                                                                                           +
         IF TG_OP = 'INSERT' THEN                                                                        +
             IF NEW.col3 >= '2014-02-21 05:30:00+08' AND NEW.col3 < '2014-02-21 06:00:00+08' THEN        +
                 INSERT INTO test.part_test_p2014_02_21_0530 VALUES (NEW.*);                             +
                 ELSIF NEW.col3 >= '2014-02-21 05:00:00+08' AND NEW.col3 < '2014-02-21 05:30:00+08' THEN +
                     INSERT INTO test.part_test_p2014_02_21_0500 VALUES (NEW.*);                         +
                 ELSIF NEW.col3 >= '2014-02-21 06:00:00+08' AND NEW.col3 < '2014-02-21 06:30:00+08' THEN +
                 INSERT INTO test.part_test_p2014_02_21_0600 VALUES (NEW.*);                             +
                 ELSIF NEW.col3 >= '2014-02-21 04:30:00+08' AND NEW.col3 < '2014-02-21 05:00:00+08' THEN +
                     INSERT INTO test.part_test_p2014_02_21_0430 VALUES (NEW.*);                         +
                 ELSIF NEW.col3 >= '2014-02-21 06:30:00+08' AND NEW.col3 < '2014-02-21 07:00:00+08' THEN +
                 INSERT INTO test.part_test_p2014_02_21_0630 VALUES (NEW.*);                             +
                 ELSIF NEW.col3 >= '2014-02-21 04:00:00+08' AND NEW.col3 < '2014-02-21 04:30:00+08' THEN +
                     INSERT INTO test.part_test_p2014_02_21_0400 VALUES (NEW.*);                         +
                 ELSIF NEW.col3 >= '2014-02-21 07:00:00+08' AND NEW.col3 < '2014-02-21 07:30:00+08' THEN +
                 INSERT INTO test.part_test_p2014_02_21_0700 VALUES (NEW.*);                             +
                 ELSIF NEW.col3 >= '2014-02-21 03:30:00+08' AND NEW.col3 < '2014-02-21 04:00:00+08' THEN +
                     INSERT INTO test.part_test_p2014_02_21_0330 VALUES (NEW.*);                         +
                 ELSIF NEW.col3 >= '2014-02-21 07:30:00+08' AND NEW.col3 < '2014-02-21 08:00:00+08' THEN +
                 INSERT INTO test.part_test_p2014_02_21_0730 VALUES (NEW.*);                             +
             ELSE                                                                                        +
                 RETURN NEW;                                                                             +
             END IF;                                                                                     +
         END IF;                                                                                         +
         RETURN NULL;                                                                                    +
         END 
(1 row)

并在配置表part_config中添加一條記錄：

postgres=# \x
Expanded display is on.
postgres=# select * from partman.part_config ;
-[ RECORD 1 ]--------+--------------------------------
parent_table         | test.part_test
type                 | time-static
part_interval        | 00:30:00
control              | col3
constraint_cols      | 
premake              | 4
retention            | 
retention_schema     | 
retention_keep_table | t
retention_keep_index | t
datetime_string      | YYYY_MM_DD_HH24MI
last_partition       | test.part_test_p2014_02_21_0730
undo_in_progress     | f

[

Parent_table:主表名稱

Type:分區類型，包括time-static/time-dynamic/id-static/id-dynamic四種類型

Part_interval:分區間隔

Control:鍵字字段

Constraint_cols:

Premake:生成分區表時分別向當前時間段分區的前后各再生成的分區表個數

Retention:

Retention_schema:

Retention_keep_table:是否在刪除分區表時只取消繼承關系

Retention_keep_index:未繼承的分區表的索引是否被刪除

Datetime_string:時間格式

Last_partition:最后的分區表

Undo_in_progress:

]

3.4 測試

插入測試數據：

[root@localhost ~]# date -s 03:45:00
Fri Feb 21 03:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian1');
INSERT 0 0
[root@localhost ~]# date -s 04:15:00
Fri Feb 21 04:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian2');
INSERT 0 0
[root@localhost ~]# date -s 04:45:00
Fri Feb 21 04:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian3');
INSERT 0 0
[root@localhost ~]# date -s 05:15:00
Fri Feb 21 05:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian4');
INSERT 0 0
[root@localhost ~]# date -s 05:45:00
Fri Feb 21 05:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian5');
INSERT 0 0
[root@localhost ~]# date -s 06:15:00
Fri Feb 21 06:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian6');
INSERT 0 0
[root@localhost ~]# date -s 06:45:00
Fri Feb 21 06:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian7');
INSERT 0 0
[root@localhost ~]# date -s 07:15:00
Fri Feb 21 07:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian8');
INSERT 0 0
[root@localhost ~]# date -s 07:45:00
Fri Feb 21 07:45:00 CST 2014
postgres=# insert into part_test(col2) values ('lian9');
INSERT 0 0
[root@localhost ~]# date -s 08:15:00
Fri Feb 21 08:15:00 CST 2014
postgres=# insert into part_test(col2) values ('lian10');
INSERT 0 1
postgres=# SELECT p.relname,c.*  FROM part_test c, pg_class p WHERE c.tableoid = p.oid order by col1;
          relname           | col1 |  col2  |             col3              
----------------------------+------+--------+-------------------------------
 part_test_p2014_02_21_0330 |    1 | lian1  | 2014-02-21 03:45:01.862785+08
 part_test_p2014_02_21_0400 |    2 | lian2  | 2014-02-21 04:15:06.863605+08
 part_test_p2014_02_21_0430 |    3 | lian3  | 2014-02-21 04:45:07.144351+08
 part_test_p2014_02_21_0500 |    4 | lian4  | 2014-02-21 05:15:05.446265+08
 part_test_p2014_02_21_0530 |    5 | lian5  | 2014-02-21 05:45:02.607934+08
 part_test_p2014_02_21_0600 |    6 | lian6  | 2014-02-21 06:15:06.643714+08
 part_test_p2014_02_21_0630 |    7 | lian7  | 2014-02-21 06:45:03.646074+08
 part_test_p2014_02_21_0700 |    8 | lian8  | 2014-02-21 07:15:04.595398+08
 part_test_p2014_02_21_0730 |    9 | lian9  | 2014-02-21 07:45:03.498948+08
 part_test                  |   10 | lian10 | 2014-02-21 08:15:03.737789+08
(10 rows)
postgres=# select * from only part_test;
 col1 |  col2  |             col3              
------+--------+-------------------------------
   10 | lian10 | 2014-02-21 08:15:03.737789+08
(1 row)
{不符合條件的數據直接放入了主表中}

3.5 函數說明

包括的函數如下所列：

apply_constraints

drop_constraints

check_name_length

check_parent

check_partition_type

check_unique_column

create_id_function

create_id_partition

create_next_time_partition

create_parent

create_time_function

create_time_partition

create_trigger

drop_partition_id

drop_partition_time

partition_data_id

partition_data_time

reapply_privileges

run_maintenance

show_partitions

undo_partition

undo_partition_id

undo_partition_time

主要函數用法例舉：

Creation Functions

create_parent(p_parent_table text, p_control text, p_type text, p_interval text, p_constraint_cols text[] DEFAULT NULL, p_premake int DEFAULT 4, p_debug boolean DEFAULT false)

[

創建分區表函數，父表必須存在。

p_type分為兩大類：基于時間、基于序列號，再可細分為四種類型：time-static/time-dynamic/id-static/id-dynamic

Time-static：基于靜態時間段，即在生成分區表時分別向當前時間段分區的前后各再生成premake個分區表

Time-dynamic：基于動態時間段，即當需要某個時間段分區時動態生成

Id-static：基于靜態序列ID，當id超出了分區最大id的50%時下一個分區如果不存在將自動會被創建，不需要使用run_maintenance()函數創建，其它用法類似于time-static，僅支持id>=0

Id-dynamic：基于動態序列ID，用法類似于time-dynamic，僅支持id>=0

p_interval為分區間隔，包括yearly、quarterly、monthly、weekly、daily、hourly、half-hour、quarter-hour、<integer>。

]

partition_data_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_lock_wait numeric DEFAULT 0)

[將設置為基于時間段分區的父表之前已經存在的數據重新分布到相應的分區上去，若分區表不存在將會被創建，之后自動將數據遷移過去]

postgres=# select partman.partition_data_time('test.part_test');
 partition_data_time 
---------------------
                   1
(1 row)
{移動了一條數據}

postgres=# SELECT p.relname,c.*  FROM part_test c, pg_class p WHERE c.tableoid = p.oid;
          relname           | col1 |  col2  |             col3              
----------------------------+------+--------+-------------------------------
 part_test_p2014_02_21_0530 |    5 | lian5  | 2014-02-21 05:45:02.607934+08
 part_test_p2014_02_21_0500 |    4 | lian4  | 2014-02-21 05:15:05.446265+08
 part_test_p2014_02_21_0600 |    6 | lian6  | 2014-02-21 06:15:06.643714+08
 part_test_p2014_02_21_0430 |    3 | lian3  | 2014-02-21 04:45:07.144351+08
 part_test_p2014_02_21_0630 |    7 | lian7  | 2014-02-21 06:45:03.646074+08
 part_test_p2014_02_21_0400 |    2 | lian2  | 2014-02-21 04:15:06.863605+08
 part_test_p2014_02_21_0700 |    8 | lian8  | 2014-02-21 07:15:04.595398+08
 part_test_p2014_02_21_0330 |    1 | lian1  | 2014-02-21 03:45:01.862785+08
 part_test_p2014_02_21_0730 |    9 | lian9  | 2014-02-21 07:45:03.498948+08
 part_test_p2014_02_21_0800 |   10 | lian10 | 2014-02-21 08:15:03.737789+08
(10 rows)
{自動創建了符合父表中數據范圍的分區表并將數據移動到新分區中}

partition_data_id(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval int DEFAULT NULL, p_lock_wait numeric DEFAULT 0)

[對基于id分區的父表中存在的數據進行遷移]

postgres=# create table test.part_students(id serial, name text not null, success int not null);
CREATE TABLE
postgres=# select partman.create_parent('test.part_students', 'success', 'id-static', '10');
 create_parent 
---------------
 
(1 row)
postgres=# \d+ part_students
                                               Table "test.part_students"
 Column  |  Type   |                         Modifiers                          | Storage  | Stats target | Description 
---------+---------+------------------------------------------------------------+----------+--------------+-------------
 id      | integer | not null default nextval('part_students_id_seq'::regclass) | plain    |              | 
 name    | text    | not null                                                   | extended |              | 
 success | integer | not null                                                   | plain    |              | 
Triggers:
    part_students_part_trig BEFORE INSERT ON part_students FOR EACH ROW EXECUTE PROCEDURE part_students_part_trig_func()
Child tables: part_students_p0,
              part_students_p10,
              part_students_p20,
              part_students_p30,
              part_students_p40
Has OIDs: no
postgres=# insert into part_students(name,success) values ('lian1',92);
INSERT 0 1
postgres=# insert into part_students(name,success) values ('lian2',88);
INSERT 0 1
postgres=# insert into part_students(name,success) values ('lian3',70);
INSERT 0 1
postgres=# insert into part_students(name,success) values ('lian4',51);
INSERT 0 1
postgres=# SELECT p.relname,c.*  FROM part_students c, pg_class p WHERE c.tableoid = p.oid;
    relname    | id | name  | success 
---------------+----+-------+---------
 part_students |  1 | lian1 |      92
 part_students |  4 | lian2 |      88
 part_students |  5 | lian3 |      70
 part_students |  6 | lian4 |      51
(4 rows)
{因為沒有符合條件的分區，所以所有記錄均插入了主表中}

postgres=# select partman.partition_data_id('test.part_students');
 partition_data_id 
-------------------
                 1
(1 row)
{移動了一條數據}

postgres=# SELECT p.relname,c.*  FROM part_students c, pg_class p WHERE c.tableoid = p.oid;
      relname      | id | name  | success 
-------------------+----+-------+---------
 part_students     |  1 | lian1 |      92
 part_students     |  4 | lian2 |      88
 part_students     |  5 | lian3 |      70
 part_students_p50 |  6 | lian4 |      51
(4 rows)
{正確的創建了分區并將數據遷移}

postgres=# select partman.partition_data_id('test.part_students');
 partition_data_id 
-------------------
                 1
(1 row)
postgres=# SELECT p.relname,c.*  FROM part_students c, pg_class p WHERE c.tableoid = p.oid;
      relname      | id | name  | success 
-------------------+----+-------+---------
 part_students     |  1 | lian1 |      92
 part_students     |  4 | lian2 |      88
 part_students_p50 |  6 | lian4 |      51
 part_students_p70 |  5 | lian3 |      70
(4 rows)

一次性將剩下的兩條數據一次性批量移動：

postgres=# select partman.partition_data_id('test.part_students',2);
 partition_data_id 
-------------------
                 2
(1 row)
postgres=# SELECT p.relname,c.*  FROM part_students c, pg_class p WHERE c.tableoid = p.oid;
      relname      | id | name  | success 
-------------------+----+-------+---------
 part_students_p50 |  6 | lian4 |      51
 part_students_p70 |  5 | lian3 |      70
 part_students_p80 |  4 | lian2 |      88
 part_students_p90 |  1 | lian1 |      92
(4 rows)

Maintenance Functions

run_maintenance()

[作為計劃作業中使用的函數，作為系統的一個定時任務，定時對分區進行維護，例如自動生成新需要的分區，但不會對主表中的數據進行遷移]

postgres=# \d
                     List of relations
 Schema  |            Name            |   Type   |  Owner   
---------+----------------------------+----------+----------
 partman | part_config                | table    | postgres
 test    | part_test                  | table    | postgres
 test    | part_test_col1_seq         | sequence | postgres
 test    | part_test_p2014_02_21_0330 | table    | postgres
 test    | part_test_p2014_02_21_0400 | table    | postgres
 test    | part_test_p2014_02_21_0430 | table    | postgres
 test    | part_test_p2014_02_21_0500 | table    | postgres
 test    | part_test_p2014_02_21_0530 | table    | postgres
 test    | part_test_p2014_02_21_0600 | table    | postgres
 test    | part_test_p2014_02_21_0630 | table    | postgres
 test    | part_test_p2014_02_21_0700 | table    | postgres
 test    | part_test_p2014_02_21_0730 | table    | postgres
 test    | part_test_p2014_02_21_0800 | table    | postgres
 test    | part_test_p2014_02_21_0830 | table    | postgres
 test    | part_test_p2014_02_21_0900 | table    | postgres
 test    | part_test_p2014_02_21_0930 | table    | postgres
 test    | part_test_p2014_02_21_1000 | table    | postgres
 test    | part_test_p2014_02_21_1030 | table    | postgres
 test    | part_test_p2014_02_21_1100 | table    | postgres
(30 rows)
[root@localhost ~]# date -s 10:05:00
Fri Feb 21 10:05:00 CST 2014
postgres=# select partman.run_maintenance();
 run_maintenance 
-----------------
 
(1 row)
postgres=# \d
                     List of relations
 Schema  |            Name            |   Type   |  Owner   
---------+----------------------------+----------+----------
 partman | part_config                | table    | postgres
 test    | part_test                  | table    | postgres
 test    | part_test_col1_seq         | sequence | postgres
 test    | part_test_p2014_02_21_0330 | table    | postgres
 test    | part_test_p2014_02_21_0400 | table    | postgres
 test    | part_test_p2014_02_21_0430 | table    | postgres
 test    | part_test_p2014_02_21_0500 | table    | postgres
 test    | part_test_p2014_02_21_0530 | table    | postgres
 test    | part_test_p2014_02_21_0600 | table    | postgres
 test    | part_test_p2014_02_21_0630 | table    | postgres
 test    | part_test_p2014_02_21_0700 | table    | postgres
 test    | part_test_p2014_02_21_0730 | table    | postgres
 test    | part_test_p2014_02_21_0800 | table    | postgres
 test    | part_test_p2014_02_21_0830 | table    | postgres
 test    | part_test_p2014_02_21_0900 | table    | postgres
 test    | part_test_p2014_02_21_0930 | table    | postgres
 test    | part_test_p2014_02_21_1000 | table    | postgres
 test    | part_test_p2014_02_21_1030 | table    | postgres
 test    | part_test_p2014_02_21_1100 | table    | postgres
 test    | part_test_p2014_02_21_1130 | table    | postgres
 test    | part_test_p2014_02_21_1200 | table    | postgres
(32 rows)

show_partitions (p_parent_table text, p_order text DEFAULT 'ASC')

[羅列主表的所有分區表，默認按照升序排列]

postgres=# select partman.show_partitions ('test.part_students');
    show_partitions     
------------------------
 test.part_students_p0
 test.part_students_p10
 test.part_students_p20
 test.part_students_p30
 test.part_students_p40
 test.part_students_p50
 test.part_students_p70
 test.part_students_p80
 test.part_students_p90
(9 rows)

check_parent()

[檢查未找到符合的分區而插入到父表中的條目，并列出父表及條目數]

postgres=# SELECT p.relname,c.*  FROM part_test c, pg_class p WHERE c.tableoid = p.oid;
          relname           | col1 |  col2  |             col3              
----------------------------+------+--------+-------------------------------
 part_test                  |   10 | lian10 | 2014-02-21 08:15:03.737789+08
 part_test_p2014_02_21_0530 |    5 | lian5  | 2014-02-21 05:45:02.607934+08
 part_test_p2014_02_21_0500 |    4 | lian4  | 2014-02-21 05:15:05.446265+08
 part_test_p2014_02_21_0600 |    6 | lian6  | 2014-02-21 06:15:06.643714+08
 part_test_p2014_02_21_0430 |    3 | lian3  | 2014-02-21 04:45:07.144351+08
 part_test_p2014_02_21_0630 |    7 | lian7  | 2014-02-21 06:45:03.646074+08
 part_test_p2014_02_21_0400 |    2 | lian2  | 2014-02-21 04:15:06.863605+08
 part_test_p2014_02_21_0700 |    8 | lian8  | 2014-02-21 07:15:04.595398+08
 part_test_p2014_02_21_0330 |    1 | lian1  | 2014-02-21 03:45:01.862785+08
 part_test_p2014_02_21_0730 |    9 | lian9  | 2014-02-21 07:45:03.498948+08
(10 rows)

postgres=# select partman.check_parent();
    check_parent    
--------------------
 (test.part_test,1)
(1 row)
{說明檢查到主表中存在一條記錄}

check_unique_column(p_parent_table text, p_column text)

[檢查指定字段數據的唯一性]

postgres=# select * from part_students;
 id | name  | success 
----+-------+---------
  7 | lian5 |      64
  8 | lian4 |      88
  5 | lian3 |      70
  4 | lian2 |      88
  1 | lian1 |      92
(5 rows)

postgres=# select partman.check_unique_column('test.part_students','success');
NOTICE:  v_sql: SELECT success::text AS column_value, count(success) AS count
        FROM test.part_students GROUP BY success HAVING (count(success) > 1) ORDER BY success
 check_unique_column 
---------------------
 (88,2)
(1 row)
{表明該字段上存在兩個88}

drop_constraints(p_parent_table text, p_child_table text, p_debug boolean DEFAULT false)

postgres=# \d part_students_p0
                         Table "test.part_students_p0"
 Column  |  Type   |                         Modifiers                          
---------+---------+------------------------------------------------------------
 id      | integer | not null default nextval('part_students_id_seq'::regclass)
 name    | text    | not null
 success | integer | not null
Check constraints:
    "part_students_p0_partition_check" CHECK (success >= 0 AND success < 10)
Inherits: part_students
postgres=# select partman.drop_constraints('test.part_students','test.part_students_p0');
ERROR:  Given parent table (test.part_students) not set up for constraint management (constraint_cols is NULL)
STATEMENT:  select partman.drop_constraints('test.part_students','test.part_students_p0');
ERROR:  Given parent table (test.part_students) not set up for constraint management (constraint_cols is NULL)
{提示指定的主表中未指定約束字段，這是因為在創建分區的時候沒有指定約束字段}

postgres=# create table test.t1(id serial,name text,age int);
CREATE TABLE
postgres=# select partman.create_parent('test.t1', 'id', 'id-static', '5',array['age']);
 create_parent 
---------------
 
(1 row)
postgres=# select partman.drop_constraints('test.t1','test.t1_p20');
 drop_constraints 
------------------
 
(1 row)
postgres=# \d t1_p20
                        Table "test.t1_p20"
 Column |  Type   |                    Modifiers                    
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t1_id_seq'::regclass)
 name   | text    | 
 age    | integer | 
Check constraints:
    "t1_p20_partition_check" CHECK (id >= 20 AND id < 25)
Inherits: t1
postgres=# select partman.apply_constraints('test.t1','test.t1_p20');
 apply_constraints 
-------------------
 
(1 row)
postgres=# \d t1_p20
                        Table "test.t1_p20"
 Column |  Type   |                    Modifiers                    
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t1_id_seq'::regclass)
 name   | text    | 
 age    | integer | 
Check constraints:
    "t1_p20_partition_check" CHECK (id >= 20 AND id < 25)
Inherits: t1

apply_constraints(p_parent_table text, p_child_table text DEFAULT NULL, p_debug BOOLEAN DEFAULT FALSE)

reapply_privileges(p_parent_table text)

[將父表設置的權限重新應用到分區表]

postgres=# create user lian;
CREATE ROLE
postgres=# alter table t1_p0 owner to lian;
ALTER TABLE
postgres=# \d
                     List of relations
 Schema  |            Name            |   Type   |  Owner   
---------+----------------------------+----------+----------
 partman | part_config                | table    | postgres
 test    | t1                         | table    | postgres
 test    | t1_id_seq                  | sequence | postgres
 test    | t1_p0                      | table    | lian
 test    | t1_p10                     | table    | postgres
 test    | t1_p15                     | table    | postgres
 test    | t1_p20                     | table    | postgres
 test    | t1_p5                      | table    | postgres
postgres=# select partman.reapply_privileges('test.t1');
 reapply_privileges 
--------------------
 
(1 row)
postgres=# \d
                     List of relations
 Schema  |            Name            |   Type   |  Owner   
---------+----------------------------+----------+----------
 partman | part_config                | table    | postgres
 test    | t1                         | table    | postgres
 test    | t1_id_seq                  | sequence | postgres
 test    | t1_p0                      | table    | postgres
 test    | t1_p10                     | table    | postgres
 test    | t1_p15                     | table    | postgres
 test    | t1_p20                     | table    | postgres
 test    | t1_p5                      | table    | postgres

Destruction Functions

undo_partition_time(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval interval DEFAULT NULL, p_keep_table boolean DEFAULT true) RETURNS bigint

[將基于時間分區的分區表里的數據移動到父表中，并解除繼承關系，同時可以指定遷移完成后是否刪除分區表（默認保留）]

postgres=# SELECT p.relname,c.*  FROM test.part_test c, pg_class p WHERE c.tableoid = p.oid;
          relname           | col1 |  col2  |             col3              
----------------------------+------+--------+-------------------------------
 part_test_p2014_02_21_0530 |    5 | lian5  | 2014-02-21 05:45:02.607934+08
 part_test_p2014_02_21_0500 |    4 | lian4  | 2014-02-21 05:15:05.446265+08
 part_test_p2014_02_21_0600 |    6 | lian6  | 2014-02-21 06:15:06.643714+08
 part_test_p2014_02_21_0430 |    3 | lian3  | 2014-02-21 04:45:07.144351+08
 part_test_p2014_02_21_0630 |    7 | lian7  | 2014-02-21 06:45:03.646074+08
 part_test_p2014_02_21_0400 |    2 | lian2  | 2014-02-21 04:15:06.863605+08
 part_test_p2014_02_21_0700 |    8 | lian8  | 2014-02-21 07:15:04.595398+08
 part_test_p2014_02_21_0330 |    1 | lian1  | 2014-02-21 03:45:01.862785+08
 part_test_p2014_02_21_0730 |    9 | lian9  | 2014-02-21 07:45:03.498948+08
 part_test_p2014_02_21_0800 |   10 | lian10 | 2014-02-21 08:15:03.737789+08
 part_test_p2014_02_21_0830 |   11 | lian11 | 2014-02-21 08:45:39.154074+08
(11 rows)
postgres=# select partman.undo_partition_time('test.part_test',20);
NOTICE:  Copied 11 row(s) to the parent. Removed 18 partitions.
 undo_partition_time 
---------------------
                  11
(1 row)
postgres=# SELECT p.relname,c.*  FROM test.part_test c, pg_class p WHERE c.tableoid = p.oid;
  relname  | col1 |  col2  |             col3              
-----------+------+--------+-------------------------------
 part_test |    5 | lian5  | 2014-02-21 05:45:02.607934+08
 part_test |    4 | lian4  | 2014-02-21 05:15:05.446265+08
 part_test |    6 | lian6  | 2014-02-21 06:15:06.643714+08
 part_test |    3 | lian3  | 2014-02-21 04:45:07.144351+08
 part_test |    7 | lian7  | 2014-02-21 06:45:03.646074+08
 part_test |    2 | lian2  | 2014-02-21 04:15:06.863605+08
 part_test |    8 | lian8  | 2014-02-21 07:15:04.595398+08
 part_test |    1 | lian1  | 2014-02-21 03:45:01.862785+08
 part_test |    9 | lian9  | 2014-02-21 07:45:03.498948+08
 part_test |   10 | lian10 | 2014-02-21 08:15:03.737789+08
 part_test |   11 | lian11 | 2014-02-21 08:45:39.154074+08
(11 rows)
postgres=# \d+ part_test
                                                        Table "test.part_test"
 Column |           Type           |                        Modifiers                         | Storage  | Stats target | Description 
--------+--------------------------+----------------------------------------------------------+----------+--------------+-------------
 col1   | integer                  | not null default nextval('part_test_col1_seq'::regclass) | plain    |              | 
 col2   | text                     |                                                          | extended |              | 
 col3   | timestamp with time zone | not null default now()                                   | plain    |              | 
Has OIDs: no

此時如果想再次恢復分區，那么需要使用create_parent函數重新生成。

undo_partition_id(p_parent_table text, p_batch_count int DEFAULT 1, p_batch_interval bigint DEFAULT NULL, p_keep_table boolean DEFAULT true) RETURNS bigint

[將基于id分區的分區表里的數據移動到父表中，并解除繼承關系，同時可以指定遷移完成后是否刪除分區表（默認保留）]

undo_partition(p_parent_table text, p_batch_count int DEFAULT 1, p_keep_table boolean DEFAULT true) RETURNS bigint

[拷貝分區表中的數據到父表中，解除繼承關系，但是分區表中的數據依然存在]

postgres=# SELECT p.relname,c.*  FROM test.part_students c, pg_class p WHERE c.tableoid = p.oid;
      relname      | id | name  | success 
-------------------+----+-------+---------
 part_students     |  7 | lian5 |      64
 part_students     |  8 | lian4 |      88
 part_students_p70 |  5 | lian3 |      70
 part_students_p80 |  4 | lian2 |      88
 part_students_p90 |  1 | lian1 |      92
(5 rows)
postgres=# select partman.undo_partition('test.part_students',5);
NOTICE:  Copied 3 row(s) from 7 child table(s) to the parent: test.part_students
 undo_partition 
----------------
              3
(1 row)
postgres=# SELECT p.relname,c.*  FROM test.part_students c, pg_class p WHERE c.tableoid = p.oid;
    relname    | id | name  | success 
---------------+----+-------+---------
 part_students |  7 | lian5 |      64
 part_students |  8 | lian4 |      88
 part_students |  5 | lian3 |      70
 part_students |  4 | lian2 |      88
 part_students |  1 | lian1 |      92
(5 rows)
postgres=# \d+ test.part_students
                                               Table "test.part_students"
 Column  |  Type   |                         Modifiers                          | Storage  | Stats target | Description 
---------+---------+------------------------------------------------------------+----------+--------------+-------------
 id      | integer | not null default nextval('part_students_id_seq'::regclass) | plain    |              | 
 name    | text    | not null                                                   | extended |              | 
 success | integer | not null                                                   | plain    |              | 
Has OIDs: no
postgres=# select * from part_students_p70;
 id | name  | success 
----+-------+---------
  5 | lian3 |      70
(1 row)

drop_partition_time(p_parent_table text, p_retention interval DEFAULT NULL, p_keep_table boolean DEFAULT NULL, p_keep_index boolean DEFAULT NULL, p_retention_schema text DEFAULT NULL) RETURNS int

[刪除基于時間分區的分區表]

drop_partition_id(p_parent_table text, p_retention bigint DEFAULT NULL, p_keep_table boolean DEFAULT NULL, p_keep_index boolean DEFAULT NULL, p_retention_schema text DEFAULT NULL) RETURNS int

[刪除基于id分區的分區表]

總結

4.1 pg_partman優缺點

相對原始手工創建維護分區表的方式，使用pg_partman工具的優缺點：

• 創建分區簡單、方便;

• 對分區表的管理與維護更加方便;

• 能夠方便快速地對數據進行遷移，這對于生產業務中需要對當前某個大表創建分區并遷移數據很有用;

• 可設置任務對分區進行定時維護。

• 只支持范圍分區，不支持列表分區;

• 靈活性遜于手工使用。

4.2 不同數據庫分區實現對比

與Oracle進行比較：

• 都是將邏輯上完整的表物理地分割成幾塊存儲在不同的物理文件上，分區表對外部用戶透明;

• 目的都是為了提高數據庫性能;

• 都支持對當前大表進行分區并遷移數據，不過PostgreSQL需要借助pg_partman工具。

• PostgreSQL基于繼承的特性來實現分區表功能，每個分區都是實實在在存在的數據表;  Oracle不存在繼承的概念，分區表的實現是通過自身存儲機制實現的;

• Oracle分區表創建與管理比較簡單;

• PostgreSQL需要打開排除約束功能才能對操作進行約束過濾檢查;  Oracle則始終進行過濾檢查;

• PostgreSQL目前僅支持范圍分區與列表分區;  Oracle與MySQL同時還支持散列分區。

（MySQL分區表的創建、管理與Oracle很相似。）

PostgreSQL從繼承到分區（一）

http://my.oschina.net/lianshunke/blog/205296

PostgreSQL從繼承到分區（二）

http://my.oschina.net/lianshunke/blog/205296

PostgreSQL從繼承到分區（三）

http://my.oschina.net/lianshunke/blog/205316