Shulou(Shulou.com)06/01 Report--

III. Introduction to Pg_partman3.1

Pg_partman is a partition table management tool based on PostgreSQL partition, which realizes the management of partition table through several functions, which is faster and more convenient than manual creation of partition table, trigger function and trigger. At the same time, it provides the daily maintenance and management functions of partition table.

Its implementation principle is to write constraints, functions, triggers, the creation of partition tables and basic management commands into functions, which can be easily created and maintained through function calls, and avoid manual creation errors.

3.2 installation

Download address: https://github.com/keithf4/pg_partman

Compile and install:

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

Create the space used by partman:

Postgres=# create schema partman;CREATE SCHEMA

Introduce the expansion module pg_partman:

Postgres=# create extension pg_partman with schema partman CREATE EXTENSIONpostgres=#\ 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)

View automatically generated objects:

Postgres=# set search_path to partman;SETpostgres=#\ d List of relations Schema | Name | Type | Owner-+-partman | part_config | table | postgres (1 row) {generate a configuration record table part_config in partman mode after the extension module pg_partman is introduced

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 falseIndexes: "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 create an administrative partition table

Create a new schema for testing:

Postgres=# create schema test;CREATE SCHEMA

Create the master table:

Postgres=# create table test.part_test (col1 serial, col2 text, col3 timestamptz DEFAULT now () NOT NUll) CREATE TABLEpostgres=#\ 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 ()

Call the create_parent function provided by pg_partman to generate partition tables as well as constraints, trigger functions, and triggers:

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_0730Has OIDs: no {created a trigger on the main table and established an inheritance relationship} postgres=# select now () Now-- 2014-02-21 05VG 37pur35.764547mm 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_testHas 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 05VOG 30V 00008' 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 05VOG 00000008' 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 06VOG 0012 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 04VOG 30V 00008' 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 VLV 30V 00008' 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 04VOG 0012 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 07VOG 00000008' 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 virtual 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 0730 virtual 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=# \xExpanded display is on.postgres=# select * from partman.part_config ;-[ RECORD 1 ]--------+--------------------------------parent_table | test.part_testtype | time-staticpart_interval | 00:30:00control | col3constraint_cols | premake | 4retention | retention_schema | retention_keep_table | tretention_keep_index | tdatetime_string | YYYY_MM_DD_HH24MIlast_partition | test.part_test_p2014_02_21_0730undo_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:00Fri Feb 21 03:45:00 CST 2014postgres=# insert into part_test(col2) values ('lian1');INSERT 0 0[root@localhost ~]# date -s 04:15:00Fri Feb 21 04:15:00 CST 2014postgres=# insert into part_test(col2) values ('lian2');INSERT 0 0[root@localhost ~]# date -s 04:45:00Fri Feb 21 04:45:00 CST 2014postgres=# insert into part_test(col2) values ('lian3');INSERT 0 0[root@localhost ~]# date -s 05:15:00Fri Feb 21 05:15:00 CST 2014postgres=# insert into part_test(col2) values ('lian4');INSERT 0 0[root@localhost ~]# date -s 05:45:00Fri Feb 21 05:45:00 CST 2014postgres=# insert into part_test(col2) values ('lian5');INSERT 0 0[root@localhost ~]# date -s 06:15:00Fri Feb 21 06:15:00 CST 2014postgres=# insert into part_test(col2) values ('lian6');INSERT 0 0[root@localhost ~]# date -s 06:45:00Fri Feb 21 06:45:00 CST 2014postgres=# insert into part_test(col2) values ('lian7');INSERT 0 0[root@localhost ~]# date -s 07:15:00Fri Feb 21 07:15:00 CST 2014postgres=# insert into part_test(col2) values ('lian8');INSERT 0 0[root@localhost ~]# date -s 07:45:00Fri Feb 21 07:45:00 CST 2014postgres=# insert into part_test(col2) values ('lian9');INSERT 0 0[root@localhost ~]# date -s 08:15:00Fri Feb 21 08:15:00 CST 2014postgres=# insert into part_test(col2) values ('lian10');INSERT 0 1postgres=# 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: based on dynamic sequence ID, the usage is similar to time-dynamic and only supports id > = 0

P_interval is the partition interval, including 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)

[redistribute the data that already exists before the parent table based on the time period partition to the corresponding partition. If the partition table does not exist, it will be created, and then the data will be automatically migrated]

Postgres=# select partman.partition_data_time ('test.part_test'); partition_data_time-1 (1 row) {moved a piece of data} postgres=# SELECT p.relameJournal 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 005 lian4 part_test_p2014_02_21_0500 | 4 | lian4 | 2014-02-21 005 part_test_p2014_02_21_0600 | 6 | lian6 | 2014-02-21 0614 058 part_test_p2014_02_21_0430 | 3 | lian3 | 2014-02-21 0445 45 lian4 | 7 | lian7 | 2014 | 2014 | -02-21 006 part_test_p2014_02_21_0400 45 part_test_p2014_02_21_0700 03.646074 008 part_test_p2014_02_21_0730 | 2 | lian2 | 2014-02-21 004 15 part_test_p2014_02_21_0700 06.863605 December 08 part_test_p2014_02_21_0730 | 8 | lian8 | 2014-02-21 007 15 part_test_p2014_02_21_0330 | 1 | lian1 | 2014-02-21 0314 4V 01.862785 part_test_p2014_02_21_0730 | 9 | lian9 | 2014-02-21 07 rows 45 rows 03.498948 partitioned 08 part_test_p2014_02_21_0800 | 10 | lian10 | 2014-02-21 008 VLV 15purl 03.73778908 (10 partitions) {automatically create a partitioned table that matches the range of data in the parent table and move the data to the new partition}

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)

[migrate data that exists in parent tables based on id partitions]

Postgres=# create table test.part_students (id serial, name text not null, success int not null); CREATE TABLEpostgres=# 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_p40Has OIDs: nopostgres=# insert into part_students (name,success) values ('lian1',92) INSERT 0 1postgres=# insert into part_students (name,success) values ('lian2',88); INSERT 0 1postgres=# insert into part_students (name,success) values (' lian3',70); INSERT 0 1postgres=# insert into part_students (name,success) values ('lian4',51); INSERT 0 1postgres=# SELECT p.relamethec. * 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) {because there is no qualified partition So all records are inserted into the main table} postgres=# select partman.partition_data_id ('test.part_students') Partition_data_id-1 (1 row) {moved a piece of data} postgres=# SELECT p.relame WHERE c.tableoid 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) {the partition was created correctly and Migrate data} postgres=# select partman.partition_data_id ('test.part_students') Partition_data_id-1 (1 row) postgres=# SELECT p.relameMagnec. * 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)

Move the remaining two pieces of data in batches at one time:

Postgres=# select partman.partition_data_id ('test.part_students',2); partition_data_id-2 (1 row) postgres=# SELECT p.relame. 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 ()

[as a function used in scheduled jobs and as a scheduled task of the system, partitions are maintained regularly, such as automatically generating newly needed partitions, but the data in the main table is not migrated]

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 | postgres test | 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:00Fri Feb 21 10:05 : 00 CST 2014postgres=# 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 _ p20142.022100500 | 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')

[list all partition tables of the main table, arranged in ascending order by default]

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 ()

[check the entries inserted into the parent table when no matching partition is found, and list the parent table and the number of entries]

Postgres=# SELECT p. Relamec. * FROM part_test c, pg_class p WHERE c.tableoid = p.oid Relname | col1 | col2 | col3-- +-- part_test | 10 | | lian10 | 2014-02-21 008 lian5 | 5 | lian5 | 2014-02-21 005 part_test_p2014_02_21_0500 | 4 | lian4 | 2014-02-21 005 lian5 | 6 | lian6 | 2014-02-21 0666 14008 part_test_p2014_02_21_0430 | 3 | lian3 | 2014 | -02-21 004 part_test_p2014_02_21_0400 45part_test_p2014_02_21_0400 07.144351room08 part_test_p2014_02_21_0630 | 7 | lian7 | 2014-02-21 0666 part_test_p2014_02_21_0400 03.646074room08 part_test_p2014_02_21_0330 | 2 | lian2 | 2014-02-21 0414605 008 part_test_p2014_02_21_0330 | 8 | lian8 | 2014-02-21 07qi 15Switzerland 04.59539858 part_test_p2014_02_21_0330 | 1 | lian1 | 2014-02-21 03 rows 45 part_test_p2014_02_21_0730 01.862785 8 postgres=# select partman.check_parent | 9 | lian9 | 2014-02-21 07 rows 14) 03.498948 December 08 (10) Check_parent-(test.part_test,1) (1 row) {indicates that a record was detected in the main table}

Check_unique_column (p_parent_table text, p_column text)

[check the uniqueness of the specified field data]

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 row 2) (1 row) {indicates that there are two 88} on this field.

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 nullCheck constraints: "part_students_p0_partition_check" CHECK (success > = 0 AND success)

< 10)Inherits: part_studentspostgres=# 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 TABLEpostgres=# 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: t1postgres=# 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)

[reapply the permissions set by the parent table to the partition table]

Postgres=# create user lian;CREATE ROLEpostgres=# alter table t1_p0 owner to lian ALTER TABLEpostgres=#\ 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 | postgrespostgres=# 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

[move the data in the partition table based on time partition to the parent table and uninherit it, and you can specify whether to delete the partition table after the migration is complete (reserved by default)]

Postgres=# SELECT p. Relamec. * 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 005 lian4 part_test_p2014_02_21_0500 | 4 | lian4 | 2014-02-21 005 part_test_p2014_02_21_0600 | 6 | lian6 | 2014-02-21 0614 058 part_test_p2014_02_21_0430 | 3 | lian3 | 2014-02-21 0445 45 lian4 | 7 | lian7 | 2014 | 2014 | -02-21 006 part_test_p2014_02_21_0400 45 part_test_p2014_02_21_0700 03.646074 008 part_test_p2014_02_21_0730 | 2 | lian2 | 2014-02-21 004 15 part_test_p2014_02_21_0700 06.863605 December 08 part_test_p2014_02_21_0730 | 8 | lian8 | 2014-02-21 007 15 part_test_p2014_02_21_0330 | 1 | lian1 | 2014-02-21 0314 4V 01.862785 part_test_p2014_02_21_0730 | 9 | lian9 | 2014-02-21 07lian11 | 2014-02-21 008 part_test_p2014_02_21_0800 part_test_p2014_02_21_0800 | 10 | lian10 | 2014-02-21 08 part_test_p2014_02_21_0830 15 part_test_p2014_02_21_0830 03.737789 008 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.relameMagnec. * 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 col2 45 relname 02.607934-08 part_test | 4 | lian4 | 2014-02-21 05:15:05 .446265 + 08 part_test | 6 | lian6 | 2014-02-21 0615 part_test 06.643714008 part_test | 3 | lian3 | 2014-02-21 0474 part_test 07.144351room08 part_test | 7 | lian7 | 2014-02-21 06Vera 453.646074lane 08 part_test | 2 | lian2 | 2014-02-21 0415 Switzerland 06.8636051408 part_test | 8 | lian8 | 2014-02221 07Freight 04.5953981908 part_test | 1 | lian1 | 2014-02-2014 21 003 part_test 45 part_test 01.862785 rows 08 part_test | 9 | lian9 | 2014-02-21 077 part_test 03.498948 part_test | 10 | lian10 | 2014-02-21 008 part_test | 11 | lian11 | 2014-02-21 08 part_test postgres=# + 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

At this point, if you want to restore the partition again, you need to regenerate it using the create_parent function.

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

[move the data from the partition table based on the id partition to the parent table and uninherit it, and you can specify whether to delete the partition table after the migration is complete (reserved by default)]

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

[copy the data from the partitioned table to the parent table and uninherit it, but the data in the partitioned table still exists]

Postgres=# SELECT p. Relamec. * 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.relameLab 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: nopostgres=# 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

[delete partition table based on time partition]

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

[delete partition table based on id partition]

Summarize the advantages and disadvantages of 4.1pg_partman

Compared to the original manual creation of maintenance partition tables, the pros and cons of using the pg_partman tool:

Creating a partition is simple and convenient.

It is more convenient to manage and maintain partition tables.

The ability to migrate data easily and quickly is useful for production businesses that need to partition a current large table and migrate data

Tasks can be set for regular maintenance of partitions.

Only range partitioning is supported, not list partitioning

Flexibility is less than manual use.

4.2 comparison of different database partitions

Compare with Oracle:

The logically complete table is physically divided into several pieces and stored on different physical files, and the partitioned table is transparent to external users.

The purpose is to improve the performance of the database

Both support partitioning current large tables and migrating data, but PostgreSQL requires the help of pg_partman tools.

PostgreSQL implements the partition table function based on inheritance, and each partition is a real data table. There is no concept of inheritance in Oracle, and the partition table is implemented through its own storage mechanism.

It is easy to create and manage Oracle partition tables.

PostgreSQL needs to turn on the exclude constraint function to perform constraint filtering checks on operations, while Oracle always does filtering checks.

PostgreSQL currently supports only range and list partitions; Oracle and MySQL also support hash partitions.

The creation and management of MySQL partition tables is very similar to Oracle. )

PostgreSQL inherits to Partition (1)

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

PostgreSQL from inheritance to Partition (2)

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

PostgreSQL from inheritance to Partition (3)

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.