三、Pg_partman

3.1 介绍

pg_partman是基于Postgresql分区开发的一个分区表管理工具，通过多个引入函数实现了对分区表的管理，相比手工创建分区表、触发器函数、触发器显得更加快捷方便，同时提供了对分区表的日常维护与管理功能。

其实现原理是将约束、函数、触发器、分区表的创建以及基础管理命令均写入函数，通过函数的调用即可方便创建与维护，并且避免了手工创建引入错误。

3.2 安装

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

编译安装：

[postgres@localhost~]$cdpg_partman-master
[postgres@localhostpg_partman-master]$make
[postgres@localhostpg_partman-master]$makeinstall

创建partman使用的空间：

postgres=#createschemapartman;
CREATESCHEMA

引入扩展模块pg_partman：

postgres=#createextensionpg_partmanwithschemapartman;
CREATEEXTENSION
postgres=#\dx
Listofinstalledextensions
Name|Version|Schema|Description
------------+---------+------------+------------------------------------------------------
pg_partman|1.5.1|partman|ExtensiontomanagepartitionedtablesbytimeorID
plpgsql|1.0|pg_catalog|PL/pgsqlprocedurallanguage
(2rows)

查看自动生成的对象：

postgres=#setsearch_pathtopartman;
SET
postgres=#\d
Listofrelations
Schema|Name|Type|Owner
---------+-------------+-------+----------
partman|part_config|table|postgres
(1row)
{扩展模块pg_partman引入后在partman模式中生成一个配置记录表part_config}

postgres=#\dpart_config
Table"partman.part_config"
Column|Type|Modifiers
----------------------+---------+------------------------
parent_table|text|notnull
type|text|notnull
part_interval|text|notnull
control|text|notnull
constraint_cols|text[]|
premake|integer|notnulldefault4
retention|text|
retention_schema|text|
retention_keep_table|boolean|notnulldefaulttrue
retention_keep_index|boolean|notnulldefaulttrue
datetime_string|text|
last_partition|text|
undo_in_progress|boolean|notnulldefaultfalse
Indexes:
"part_config_parent_table_pkey"PRIMARYKEY,btree(parent_table)
"part_config_type_idx"btree(type)
Checkconstraints:
"part_config_type_check"CHECK(check_partition_type(type))
"positive_premake_check"CHECK(premake>0)

3.3 创建管理分区表

新建一个用于测试的schema：

postgres=#createschematest;
CREATESCHEMA

创建主表：

postgres=#createtabletest.part_test(col1serial,col2text,col3timestamptzDEFAULTnow()NOTNUll);
CREATETABLE
postgres=#\dtest.part_test
Table"test.part_test"
Column|Type|Modifiers
--------+--------------------------+---------------------------------------------------------------
col1|integer|notnulldefaultnextval('test.part_test_col1_seq'::regclass)
col2|text|
col3|timestampwithtimezone|notnulldefaultnow()

调用pg_partman提供的create_parent函数生成分区表以及约束、触发器函数和触发器：

postgres=#selectpartman.create_parent('test.part_test','col3','time-static','half-hour');
create_parent
---------------

(1row)
postgres=#\d+test.part_test
Table"test.part_test"
Column|Type|Modifiers|Storage|Statstarget|Description
--------+--------------------------+---------------------------------------------------------------+----------+--------------+-------------
col1|integer|notnulldefaultnextval('test.part_test_col1_seq'::regclass)|plain||
col2|text||extended||
col3|timestampwithtimezone|notnulldefaultnow()|plain||
Triggers:
part_test_part_trigBEFOREINSERTONtest.part_testFOREACHROWEXECUTEPROCEDUREtest.part_test_part_trig_func()
Childtables: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
HasOIDs:no
{在主表上创建了trigger并建立了继承关系}


postgres=#selectnow();
now
-------------------------------
2014-02-2105:37:35.764547+08
(1row)
postgres=#\d+test.part_test_p2014_02_21_0330
Table"test.part_test_p2014_02_21_0330"
Column|Type|Modifiers|Storage|Statstarget|Description
--------+--------------------------+---------------------------------------------------------------+----------+--------------+-------------
col1|integer|notnulldefaultnextval('test.part_test_col1_seq'::regclass)|plain||
col2|text||extended||
col3|timestampwithtimezone|notnulldefaultnow()|plain||
Checkconstraints:
"part_test_p2014_02_21_0330_partition_check"CHECK(col3>='2014-02-2103:30:00+08'::timestampwithtimezoneANDcol3<'2014-02-2104:00:00+08'::timestampwithtimezone)
Inherits:test.part_test
HasOIDs:no
{在分区表上创建了check约束}

创建了触发器函数：

postgres=#\df
Listoffunctions
Schema|Name|Resultdatatype|Argumentdatatypes|Type
--------+--------------------------+------------------+---------------------+---------
test|part_test_part_trig_func|trigger||trigger
(1row)
postgres=#selectprosrcfrompg_procwhereproname='part_test_part_trig_func';
prosrc
----------------------------------------------------------------------------------------------------------
+
BEGIN+
IFTG_OP='INSERT'THEN+
IFNEW.col3>='2014-02-2105:30:00+08'ANDNEW.col3<'2014-02-2106:00:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0530VALUES(NEW.*);+
ELSIFNEW.col3>='2014-02-2105:00:00+08'ANDNEW.col3<'2014-02-2105:30:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0500VALUES(NEW.*);+
ELSIFNEW.col3>='2014-02-2106:00:00+08'ANDNEW.col3<'2014-02-2106:30:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0600VALUES(NEW.*);+
ELSIFNEW.col3>='2014-02-2104:30:00+08'ANDNEW.col3<'2014-02-2105:00:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0430VALUES(NEW.*);+
ELSIFNEW.col3>='2014-02-2106:30:00+08'ANDNEW.col3<'2014-02-2107:00:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0630VALUES(NEW.*);+
ELSIFNEW.col3>='2014-02-2104:00:00+08'ANDNEW.col3<'2014-02-2104:30:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0400VALUES(NEW.*);+
ELSIFNEW.col3>='2014-02-2107:00:00+08'ANDNEW.col3<'2014-02-2107:30:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0700VALUES(NEW.*);+
ELSIFNEW.col3>='2014-02-2103:30:00+08'ANDNEW.col3<'2014-02-2104:00:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0330VALUES(NEW.*);+
ELSIFNEW.col3>='2014-02-2107:30:00+08'ANDNEW.col3<'2014-02-2108:00:00+08'THEN+
INSERTINTOtest.part_test_p2014_02_21_0730VALUES(NEW.*);+
ELSE+
RETURNNEW;+
ENDIF;+
ENDIF;+
RETURNNULL;+
END
(1row)

并在配置表part_config中添加一条记录：

postgres=#\x
Expandeddisplayison.
postgres=#select*frompartman.part_config;
-[RECORD1]--------+--------------------------------
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-s03:45:00
FriFeb2103:45:00CST2014
postgres=#insertintopart_test(col2)values('lian1');
INSERT00
[root@localhost~]#date-s04:15:00
FriFeb2104:15:00CST2014
postgres=#insertintopart_test(col2)values('lian2');
INSERT00
[root@localhost~]#date-s04:45:00
FriFeb2104:45:00CST2014
postgres=#insertintopart_test(col2)values('lian3');
INSERT00
[root@localhost~]#date-s05:15:00
FriFeb2105:15:00CST2014
postgres=#insertintopart_test(col2)values('lian4');
INSERT00
[root@localhost~]#date-s05:45:00
FriFeb2105:45:00CST2014
postgres=#insertintopart_test(col2)values('lian5');
INSERT00
[root@localhost~]#date-s06:15:00
FriFeb2106:15:00CST2014
postgres=#insertintopart_test(col2)values('lian6');
INSERT00
[root@localhost~]#date-s06:45:00
FriFeb2106:45:00CST2014
postgres=#insertintopart_test(col2)values('lian7');
INSERT00
[root@localhost~]#date-s07:15:00
FriFeb2107:15:00CST2014
postgres=#insertintopart_test(col2)values('lian8');
INSERT00
[root@localhost~]#date-s07:45:00
FriFeb2107:45:00CST2014
postgres=#insertintopart_test(col2)values('lian9');
INSERT00
[root@localhost~]#date-s08:15:00
FriFeb2108:15:00CST2014
postgres=#insertintopart_test(col2)values('lian10');
INSERT01
postgres=#SELECTp.relname,c.*FROMpart_testc,pg_classpWHEREc.tableoid=p.oidorderbycol1;
relname|col1|col2|col3
----------------------------+------+--------+-------------------------------
part_test_p2014_02_21_0330|1|lian1|2014-02-2103:45:01.862785+08
part_test_p2014_02_21_0400|2|lian2|2014-02-2104:15:06.863605+08
part_test_p2014_02_21_0430|3|lian3|2014-02-2104:45:07.144351+08
part_test_p2014_02_21_0500|4|lian4|2014-02-2105:15:05.446265+08
part_test_p2014_02_21_0530|5|lian5|2014-02-2105:45:02.607934+08
part_test_p2014_02_21_0600|6|lian6|2014-02-2106:15:06.643714+08
part_test_p2014_02_21_0630|7|lian7|2014-02-2106:45:03.646074+08
part_test_p2014_02_21_0700|8|lian8|2014-02-2107:15:04.595398+08
part_test_p2014_02_21_0730|9|lian9|2014-02-2107:45:03.498948+08
part_test|10|lian10|2014-02-2108:15:03.737789+08
(10rows)
postgres=#select*fromonlypart_test;
col1|col2|col3
------+--------+-------------------------------
10|lian10|2014-02-2108:15:03.737789+08
(1row)
{不符合条件的数据直接放入了主表中}

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=#selectpartman.partition_data_time('test.part_test');
partition_data_time
---------------------
1
(1row)
{移动了一条数据}

postgres=#SELECTp.relname,pg_classpWHEREc.tableoid=p.oid;
relname|col1|col2|col3
----------------------------+------+--------+-------------------------------
part_test_p2014_02_21_0530|5|lian5|2014-02-2105:45:02.607934+08
part_test_p2014_02_21_0500|4|lian4|2014-02-2105:15:05.446265+08
part_test_p2014_02_21_0600|6|lian6|2014-02-2106:15:06.643714+08
part_test_p2014_02_21_0430|3|lian3|2014-02-2104:45:07.144351+08
part_test_p2014_02_21_0630|7|lian7|2014-02-2106:45:03.646074+08
part_test_p2014_02_21_0400|2|lian2|2014-02-2104:15:06.863605+08
part_test_p2014_02_21_0700|8|lian8|2014-02-2107:15:04.595398+08
part_test_p2014_02_21_0330|1|lian1|2014-02-2103:45:01.862785+08
part_test_p2014_02_21_0730|9|lian9|2014-02-2107:45:03.498948+08
part_test_p2014_02_21_0800|10|lian10|2014-02-2108:15:03.737789+08
(10rows)
{自动创建了符合父表中数据范围的分区表并将数据移动到新分区中}

partition_data_id(p_parent_table text,p_batch_interval int DEFAULT NULL,'宋体';font-size:13px;white-space:normal;">[对基于id分区的父表中存在的数据进行迁移]

postgres=#createtabletest.part_students(idserial,nametextnotnull,successintnotnull);
CREATETABLE
postgres=#selectpartman.create_parent('test.part_students','success','id-static','10');
create_parent
---------------

(1row)
postgres=#\d+part_students
Table"test.part_students"
Column|Type|Modifiers|Storage|Statstarget|Description
---------+---------+------------------------------------------------------------+----------+--------------+-------------
id|integer|notnulldefaultnextval('part_students_id_seq'::regclass)|plain||
name|text|notnull|extended||
success|integer|notnull|plain||
Triggers:
part_students_part_trigBEFOREINSERTONpart_studentsFOREACHROWEXECUTEPROCEDUREpart_students_part_trig_func()
Childtables:part_students_p0,part_students_p10,part_students_p20,part_students_p30,part_students_p40
HasOIDs:no
postgres=#insertintopart_students(name,success)values('lian1',92);
INSERT01
postgres=#insertintopart_students(name,success)values('lian2',88);
INSERT01
postgres=#insertintopart_students(name,success)values('lian3',70);
INSERT01
postgres=#insertintopart_students(name,success)values('lian4',51);
INSERT01
postgres=#SELECTp.relname,c.*FROMpart_studentsc,pg_classpWHEREc.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
(4rows)
{因为没有符合条件的分区，所以所有记录均插入了主表中}

postgres=#selectpartman.partition_data_id('test.part_students');
partition_data_id
-------------------
1
(1row)
{移动了一条数据}

postgres=#SELECTp.relname,pg_classpWHEREc.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
(4rows)
{正确的创建了分区并将数据迁移}

postgres=#selectpartman.partition_data_id('test.part_students');
partition_data_id
-------------------
1
(1row)
postgres=#SELECTp.relname,pg_classpWHEREc.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
(4rows)

一次性将剩下的两条数据一次性批量移动：

postgres=#selectpartman.partition_data_id('test.part_students',2);
partition_data_id
-------------------
2
(1row)
postgres=#SELECTp.relname,pg_classpWHEREc.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
(4rows)

Maintenance Functions

run_maintenance()

[作为计划作业中使用的函数，作为系统的一个定时任务，定时对分区进行维护，例如自动生成新需要的分区，但不会对主表中的数据进行迁移]

postgres=#\d
Listofrelations
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
(30rows)
[root@localhost~]#date-s10:05:00
FriFeb2110:05:00CST2014
postgres=#selectpartman.run_maintenance();
run_maintenance
-----------------

(1row)
postgres=#\d
Listofrelations
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
(32rows)

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

[罗列主表的所有分区表，默认按照升序排列]

postgres=#selectpartman.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
(9rows)

check_parent()

[检查未找到符合的分区而插入到父表中的条目，并列出父表及条目数]

postgres=#SELECTp.relname,pg_classpWHEREc.tableoid=p.oid;
relname|col1|col2|col3
----------------------------+------+--------+-------------------------------
part_test|10|lian10|2014-02-2108:15:03.737789+08
part_test_p2014_02_21_0530|5|lian5|2014-02-2105:45:02.607934+08
part_test_p2014_02_21_0500|4|lian4|2014-02-2105:15:05.446265+08
part_test_p2014_02_21_0600|6|lian6|2014-02-2106:15:06.643714+08
part_test_p2014_02_21_0430|3|lian3|2014-02-2104:45:07.144351+08
part_test_p2014_02_21_0630|7|lian7|2014-02-2106:45:03.646074+08
part_test_p2014_02_21_0400|2|lian2|2014-02-2104:15:06.863605+08
part_test_p2014_02_21_0700|8|lian8|2014-02-2107:15:04.595398+08
part_test_p2014_02_21_0330|1|lian1|2014-02-2103:45:01.862785+08
part_test_p2014_02_21_0730|9|lian9|2014-02-2107:45:03.498948+08
(10rows)

postgres=#selectpartman.check_parent();
check_parent
--------------------
(test.part_test,1)
(1row)
{说明检查到主表中存在一条记录}

check_unique_column(p_parent_table text,p_column text)

[检查指定字段数据的唯一性]

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

postgres=#selectpartman.check_unique_column('test.part_students','success');
NOTICE:v_sql:SELECTsuccess::textAScolumn_value,count(success)AScount
FROMtest.part_studentsGROUPBYsuccessHAVING(count(success)>1)ORDERBYsuccess
check_unique_column
---------------------
(88,2)
(1row)
{表明该字段上存在两个88}

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

postgres=#\dpart_students_p0
Table"test.part_students_p0"
Column|Type|Modifiers
---------+---------+------------------------------------------------------------
id|integer|notnulldefaultnextval('part_students_id_seq'::regclass)
name|text|notnull
success|integer|notnull
Checkconstraints:
"part_students_p0_partition_check"CHECK(success>=0ANDsuccess<10)
Inherits:part_students
postgres=#selectpartman.drop_constraints('test.part_students','test.part_students_p0');
ERROR:Givenparenttable(test.part_students)notsetupforconstraintmanagement(constraint_colsisNULL)
STATEMENT:selectpartman.drop_constraints('test.part_students','test.part_students_p0');
ERROR:Givenparenttable(test.part_students)notsetupforconstraintmanagement(constraint_colsisNULL)
{提示指定的主表中未指定约束字段，这是因为在创建分区的时候没有指定约束字段}

postgres=#createtabletest.t1(idserial,nametext,ageint); CREATETABLE postgres=#selectpartman.create_parent('test.t1','id','5',array['age']); create_parent --------------- (1row) postgres=#selectpartman.drop_constraints('test.t1','test.t1_p20'); drop_constraints ------------------ (1row) postgres=#\dt1_p20 Table"test.t1_p20" Column|Type|Modifiers --------+---------+------------------------------------------------- id|integer|notnulldefaultnextval('t1_id_seq'::regclass) name|text| age|integer| Checkconstraints: "t1_p20_partition_check"CHECK(id>=20ANDid<25) Inherits:t1 postgres=#selectpartman.apply_constraints('test.t1','test.t1_p20'); apply_constraints ------------------- (1row) postgres=#\dt1_p20 Table"test.t1_p20" Column|Type|Modifiers --------+---------+------------------------------------------------- id|integer|notnulldefaultnextval('t1_id_seq'::regclass) name|text| age|integer| Checkconstraints: "t1_p20_partition_check"CHECK(id>=20ANDid<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=#createuserlian;
CREATEROLE
postgres=#altertablet1_p0ownertolian;
ALTERTABLE
postgres=#\d
Listofrelations
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=#selectpartman.reapply_privileges('test.t1');
reapply_privileges
--------------------

(1row)
postgres=#\d
Listofrelations
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_keep_table boolean DEFAULT true) RETURNS bigint

[将基于时间分区的分区表里的数据移动到父表中，并解除继承关系，同时可以指定迁移完成后是否删除分区表（默认保留）]

sql;toolbar: true; auto-links: false;" style="margin-top:5px;margin-bottom:10px;margin-left:10px;padding:5px;background-color:rgb(246,c.*FROMtest.part_testc,pg_classpWHEREc.tableoid=p.oid;
relname|col1|col2|col3
----------------------------+------+--------+-------------------------------
part_test_p2014_02_21_0530|5|lian5|2014-02-2105:45:02.607934+08
part_test_p2014_02_21_0500|4|lian4|2014-02-2105:15:05.446265+08
part_test_p2014_02_21_0600|6|lian6|2014-02-2106:15:06.643714+08
part_test_p2014_02_21_0430|3|lian3|2014-02-2104:45:07.144351+08
part_test_p2014_02_21_0630|7|lian7|2014-02-2106:45:03.646074+08
part_test_p2014_02_21_0400|2|lian2|2014-02-2104:15:06.863605+08
part_test_p2014_02_21_0700|8|lian8|2014-02-2107:15:04.595398+08
part_test_p2014_02_21_0330|1|lian1|2014-02-2103:45:01.862785+08
part_test_p2014_02_21_0730|9|lian9|2014-02-2107:45:03.498948+08
part_test_p2014_02_21_0800|10|lian10|2014-02-2108:15:03.737789+08
part_test_p2014_02_21_0830|11|lian11|2014-02-2108:45:39.154074+08
(11rows)
postgres=#selectpartman.undo_partition_time('test.part_test',20);
NOTICE:Copied11row(s)totheparent.Removed18partitions.
undo_partition_time
---------------------
11
(1row)
postgres=#SELECTp.relname,pg_classpWHEREc.tableoid=p.oid;
relname|col1|col2|col3
-----------+------+--------+-------------------------------
part_test|5|lian5|2014-02-2105:45:02.607934+08
part_test|4|lian4|2014-02-2105:15:05.446265+08
part_test|6|lian6|2014-02-2106:15:06.643714+08
part_test|3|lian3|2014-02-2104:45:07.144351+08
part_test|7|lian7|2014-02-2106:45:03.646074+08
part_test|2|lian2|2014-02-2104:15:06.863605+08
part_test|8|lian8|2014-02-2107:15:04.595398+08
part_test|1|lian1|2014-02-2103:45:01.862785+08
part_test|9|lian9|2014-02-2107:45:03.498948+08
part_test|10|lian10|2014-02-2108:15:03.737789+08
part_test|11|lian11|2014-02-2108:45:39.154074+08
(11rows)
postgres=#\d+part_test
Table"test.part_test"
Column|Type|Modifiers|Storage|Statstarget|Description
--------+--------------------------+----------------------------------------------------------+----------+--------------+-------------
col1|integer|notnulldefaultnextval('part_test_col1_seq'::regclass)|plain||
col2|text||extended||
col3|timestampwithtimezone|notnulldefaultnow()|plain||
HasOIDs:no

此时如果想再次恢复分区，那么需要使用create_parent函数重新生成。

undo_partition_id(p_parent_table text,p_batch_interval bigint DEFAULT NULL,'宋体';font-size:13px;white-space:normal;">[将基于id分区的分区表里的数据移动到父表中，并解除继承关系，同时可以指定迁移完成后是否删除分区表（默认保留）]

undo_partition(p_parent_table text,'宋体';font-size:13px;white-space:normal;">[拷贝分区表中的数据到父表中，解除继承关系，但是分区表中的数据依然存在]

sql;toolbar: true; auto-links: false;" style="margin-top:5px;margin-bottom:10px;margin-left:10px;padding:5px;background-color:rgb(246,c.*FROMtest.part_studentsc,pg_classpWHEREc.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
(5rows)
postgres=#selectpartman.undo_partition('test.part_students',5);
NOTICE:Copied3row(s)from7childtable(s)totheparent:test.part_students
undo_partition
----------------
3
(1row)
postgres=#SELECTp.relname,pg_classpWHEREc.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
(5rows)
postgres=#\d+test.part_students
Table"test.part_students"
Column|Type|Modifiers|Storage|Statstarget|Description
---------+---------+------------------------------------------------------------+----------+--------------+-------------
id|integer|notnulldefaultnextval('part_students_id_seq'::regclass)|plain||
name|text|notnull|extended||
success|integer|notnull|plain||
HasOIDs:no
postgres=#select*frompart_students_p70;
id|name|success
----+-------+---------
5|lian3|70
(1row)

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,'宋体';font-size:13px;white-space:normal;">[删除基于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

原文链接：https://www.f2er.com/postgresql/195670.html

PostgreSQL从继承到分区（三）

三、Pg_partman

3.1 介绍

3.2 安装

3.3 创建管理分区表

总结

4.1 pg_partman优缺点

猜你在找的Postgre SQL相关文章