三、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、。
]
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: t1apply_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 &nbs
文章标题：PostgreSQL从继承到分区（三）
本文地址：http://cdkjz.cn/article/gepcdd.html
