12版本内置分区表性能提升，可以选择使用替换掉之前的继承方式，支持三种分区策略类型，list,hash,range,这里以range为例，如下：
#直接设置a字段为主键，在分区表报错，缺少分区列，需要把分区键也加进去
hank=> create table tbl_test_partition (a bigint primary key,b text,c timestamp with time zone) partition by range(c);
ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: PRIMARY KEY constraint on table "tbl_test_partition" lacks column "c" which is part of the partition key.
#以下语句执行成功
hank=> create table tbl_test_partition (a bigint,b text,c timestamp with time zone, constraint pk_a_c primary key(a,c)) partition by range(c);
CREATE TABLE
#添加一个分区
hank=> CREATE TABLE tbl_test_partition_202105 PARTITION OF tbl_test_partition FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE
#查看总表可见分区表
hank=> \d+ tbl_test_partition
Partitioned table "hank.tbl_test_partition"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
a | bigint | | not null | | plain | |
b | text | | | | extended | |
c | timestamp with time zone | | not null | | plain | |
Partition key: RANGE (c)
Indexes:
"pk_a_c" PRIMARY KEY, btree (a, c)
Partitions: tbl_test_partition_202105 FOR VALUES FROM ('2021-05-01 00:00:00+08') TO ('2021-06-01 00:00:00+08')
#可见主键可以继承下来，也就是说是索引也可以继承下来
hank=> \d tbl_test_partition_202105
Table "hank.tbl_test_partition_202105"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
a | bigint | | not null |
b | text | | |
c | timestamp with time zone | | not null |
Partition of: tbl_test_partition FOR VALUES FROM ('2021-05-01 00:00:00+08') TO ('2021-06-01 00:00:00+08')
Indexes:
"tbl_test_partition_202105_pkey" PRIMARY KEY, btree (a, c)
#如下可见，权限无法继承，需要单独授权
hank=> grant select on tbl_test_partition to logic ;
GRANT
hank=> \z tbl_test_partition
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------------+-------------------+-------------------+-------------------+----------
hank | tbl_test_partition | partitioned table | hank=arwdDxt/hank+| |
| | | logic=r/hank | |
(1 row)
hank=> \z tbl_test_partition_202105
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------------------------+-------+-------------------+-------------------+----------
hank | tbl_test_partition_202105 | table | | |
(1 row)
#一般生产不会保留所有数据，清理数据，直接drop或者truncate分区即可,如下
hank=> drop table tbl_test_partition_202106;
DROP TABLE
hank=> truncate tbl_test_partition_202105;
TRUNCATE TABLE
#也可以使分区分离，如下，分离后，不在显示分区
hank=> alter table tbl_test_partition detach partition tbl_test_partition_202105;
ALTER TABLE
hank=> \d+ tbl_test_partition
Partitioned table "hank.tbl_test_partition"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
a | bigint | | not null | | plain | |
b | text | | | | extended | |
c | timestamp with time zone | | not null | | plain | |
Partition key: RANGE (c)
Indexes:
"pk_a_c" PRIMARY KEY, btree (a, c)
Number of partitions: 0
#子分区的约束在分离后消失了，所以tbl_test_partition_202105现在就是一张普通表
hank=> \d+ tbl_test_partition_202105
Table "hank.tbl_test_partition_202105"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
a | bigint | | not null | | plain | |
b | text | | | | extended | |
c | timestamp with time zone | | not null | | plain | |
Partition of: tbl_test_partition FOR VALUES FROM ('2021-05-01 00:00:00+08') TO ('2021-06-01 00:00:00+08')
Partition constraint: ((c IS NOT NULL) AND (c >= '2021-05-01 00:00:00+08'::timestamp with time zone) AND (c < '2021-06-01 00:00:00+08'::timestamp with time zone))
Indexes:
"tbl_test_partition_202105_pkey" PRIMARY KEY, btree (a, c)
Access method: heap
hank=> alter table tbl_test_partition detach partition tbl_test_partition_202105;
ALTER TABLE
hank=> \d+ tbl_test_partition_202105
Table "hank.tbl_test_partition_202105"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
a | bigint | | not null | | plain | |
b | text | | | | extended | |
c | timestamp with time zone | | not null | | plain | |
Indexes:
"tbl_test_partition_202105_pkey" PRIMARY KEY, btree (a, c)
Access method: heap
#再次加进来，语法注意和create table的时候一致
hank=> alter table tbl_test_partition attach partition tbl_test_partition_202105 for values from ('2021-05-01') to ('2021-06-01');
ALTER TABLE
#可见与原来一致，插入数据也正常落入tbl_test_partition_202105分区中
hank=> \d+ tbl_test_partition
Partitioned table "hank.tbl_test_partition"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
a | bigint | | not null | | plain | |
b | text | | | | extended | |
c | timestamp with time zone | | not null | | plain | |
Partition key: RANGE (c)
Indexes:
"pk_a_c" PRIMARY KEY, btree (a, c)
Partitions: tbl_test_partition_202105 FOR VALUES FROM ('2021-05-01 00:00:00+08') TO ('2021-06-01 00:00:00+08')
hank=> \d+ tbl_test_partition_202105
Table "hank.tbl_test_partition_202105"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
a | bigint | | not null | | plain | |
b | text | | | | extended | |
c | timestamp with time zone | | not null | | plain | |
Partition of: tbl_test_partition FOR VALUES FROM ('2021-05-01 00:00:00+08') TO ('2021-06-01 00:00:00+08')
Partition constraint: ((c IS NOT NULL) AND (c >= '2021-05-01 00:00:00+08'::timestamp with time zone) AND (c < '2021-06-01 00:00:00+08'::timestamp with time zone))
Indexes:
"tbl_test_partition_202105_pkey" PRIMARY KEY, btree (a, c)
Access method: heap
hank=> insert into tbl_test_partition values (1,'hank',now());
INSERT 0 1
hank=> select * from tbl_test_partition;
a | b | c
---+------+-------------------------------
1 | hank | 2021-05-12 15:42:34.769521+08
(1 row)
hank=> select * from tbl_test_partition_202105;
a | b | c
---+------+-------------------------------
1 | hank | 2021-05-12 15:42:34.769521+08
(1 row)
#分区修剪
打开enable_partition_pruning即可，篇幅原因，不再实验，见官网 Partition Pruning章节
https://www.postgresql.org/docs/12/ddl-partitioning.html最后写一个自动加分区的脚本
#注意自己的环境，简单修改一下应该可用，这里还是以_yyyymm结尾为例
cat add_partitons.sh
#!/bin/bash
source ~/.bash_profile
#截止时间变量ptdate
ptdate=202212
psql -h 127.0.0.1 -Upostgres postgres << EOF
\t
\o dbname
select datname from pg_database where datname not in('postgres','repmgr','template1','template0');
EOF
for i in `cat dbname`
do
echo ${i}
psql -h 127.0.0.1 -U ${i} ${i} << EOF
CREATE OR REPLACE FUNCTION f_add_partition(mydate text)
RETURNS void
LANGUAGE plpgsql
AS \$function\$
declare
v_max_childname text; --最大子表名称
v_parentname text; --子表对应的父表名称
v_suffix text; --子表日期后缀
v_begin_date text; --起始时间
v_end_date text;
sql text; --要执行的SQL语句
GRA_TO text; --赋予权限的语句
begin
--取出分区表最大分区表以及父表名称
for v_max_childname, v_parentname in select max(inhrelid::regclass::text),inhparent::regclass from pg_inherits where inhparent::regclass::text not like '%.%' group by inhparent
loop
raise notice '最大子表:%,父表:%',v_max_childname,v_parentname;
sql= 'select split_part('''|| v_max_childname || ''',''_'',(length(''' || v_max_childname || ''')-length(replace(''' || v_max_childname || ''',''_'',''''))+1))'; --取出日期是按月还是按日
execute sql into v_suffix; --将取出的日期存入v_suffix
while(length(v_suffix)=6 and v_suffix<mydate) --判断如果是按月，那么循环执行建表语句并且赋予权限
loop
v_suffix=to_char(to_timestamp(v_suffix,'yyyymm')+interval '1 month','yyyymm') ; --在取出的分区表日期按月+1
v_begin_date=to_char(to_timestamp(v_suffix,'yyyymm'),'yyyy-mm-dd'); --取出起始时间戳
v_end_date=to_char(to_timestamp(v_begin_date,'yyyy-mm-dd')+interval '1 month','yyyy-mm-dd'); --取出结束时间戳
sql= 'CREATE TABLE '||v_parentname ||'_'||v_suffix || ' PARTITION OF ' || v_parentname ||' FOR VALUES FROM ('''||v_begin_date||''') TO ('''||v_end_date||''')';
execute sql;
for GRA_TO in execute 'select ''grant ''||privilege_type||'' on '||v_parentname||'_'||v_suffix ||' to ''||grantee from information_schema.table_privileges where table_name='''||v_max_childname||'''' loop
execute GRA_TO;
end loop;
end loop;
end loop;
RAISE NOTICE 'Partition tables have been added successfully！';
end;
\$function\$;
select f_add_partition('$ptdate');
EOF
done;#如果不是用原来的owner执行，是其他可操作的用户，比如postgres,可替换为以下函数
CREATE OR REPLACE FUNCTION hank.f_add_partition(mydate text)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
v_max_childname text; --最大子表名称
v_parentname text; --子表对应的父表名称
v_suffix text; --子表日期后缀
v_begin_date text; --起始时间
v_end_date text;
v_owner text;
v_parentname_without_schema text;
v_parentname_with_point boolean;
sql text; --要执行的SQL语句
GRA_TO text; --赋予权限的语句
begin
--取出分区表最大分区表以及父表名称
for v_max_childname, v_parentname in select max(inhrelid::regclass::text),inhparent::regclass from pg_inherits where inhrelid::regclass::text ~ '[0-9]{6}$' group by inhparent
loop
raise notice '最大子表:%,父表:%',v_max_childname,v_parentname;
sql= 'select split_part('''|| v_max_childname || ''',''_'',(length(''' || v_max_childname || ''')-length(replace(''' || v_max_childname || ''',''_'',''''))+1))'; --取出日期是按月还是按日
execute sql into v_suffix; --将取出的日期存入v_suffix
execute ('select '''||v_parentname||''' like ''%.%''') into v_parentname_with_point;
if v_parentname_with_point='t' then
v_parentname_without_schema=split_part(v_parentname,'.',2);
else
v_parentname_without_schema=v_parentname;
end if;
execute format('select tableowner from pg_tables where tablename=$1') into v_owner using v_parentname_without_schema;
raise notice 'owner is:%',v_owner;
while(length(v_suffix)=6 and v_suffix<mydate) --判断如果是按月，那么循环执行建表语句并且赋予权限
loop
v_suffix=to_char(to_timestamp(v_suffix,'yyyymm')+interval '1 month','yyyymm') ; --在取出的分区表日期按月+1
v_begin_date=to_char(to_timestamp(v_suffix,'yyyymm'),'yyyy-mm-dd'); --取出起始时间戳
v_end_date=to_char(to_timestamp(v_begin_date,'yyyy-mm-dd')+interval '1 month','yyyy-mm-dd'); --取出结束时间戳
sql= 'CREATE TABLE '||v_parentname ||'_'||v_suffix || ' PARTITION OF ' || v_parentname ||' FOR VALUES FROM ('''||v_begin_date||''') TO ('''||v_end_date||''')';
execute sql;
execute ('alter table '||v_parentname ||'_'||v_suffix||' owner to '||v_owner||'');
for GRA_TO in execute 'select ''grant ''||privilege_type||'' on '||v_parentname||'_'||v_suffix ||' to ''||grantee from information_schema.table_privileges where table_name='''||v_max_childname||'''' loop
execute GRA_TO;
end loop;
end loop;
end loop;
RAISE NOTICE 'Partition tables have been added successfully！';
end;
$function$;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
