Postgresql12 分区表实例以及自动添加分区

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$;