postgresql中分区表相关操作

目录

一、前言

二、版本详情

二、创建分区表

三、新增分区

        3.1、新增分区

        3.2、批量新增分区

四、删除分区

        4.1、删除分区

        4.2、批量删除分区

五、查看分区表详情

六、其他

---

一、前言

        本文记录一些postgresql中分区表的一些操作，包括但不限于创建分区表、删除分区、新增分区、清除分区数据等。关于postgresql的安装操作详见：linux中单节点安装postgresql数据库 博客文章内容。

二、版本详情

        postgresql version：6.13.0

        python version：2.7.13

二、创建分区表

-- 范围性创建分区示例
-- 指定创建分区表名称为t_partition_table 指定分区字段为data_bsn_dt
-- 并创建一个名称为p_20210701的分区，此分区数据范围为2021-07-01
create table tfdd.t_partition_table(
    id varchar(32)
    ,name varchar(32)
    ,data_bsn_dt date
)
with (appendonly=true, compresslevel=5)
distributed by (id)
partition by range(data_bsn_dt)
(
  partition p_20210701 start ('2021-07-01'::date) inclusive end ('2021-07-02'::date) exclusive every ('1 day'::interval)
);

三、新增分区

        对于一个分区表，我们后续可能会频繁需要新增分区来满足业务需要。所以在通常项目会使用shell脚本执行psql命令，并配合crontab定时执行，用以满足每日按天分区的业务需求（本文案例业务背景是这样的）。

        3.1、新增分区

-- 新增分区，分区名称为p_20210702
-- inclusive 包含 20210702
-- exclusive 不包含 20210703
alter table tfdd.t_partition_table add partition p_20210702 start ('2021-07-02') inclusive end ('2021-07-03') exclusive;

        3.2、批量新增分区

                假如项目刚上线，我们需要初始化近半年或近一年的数据，如果按照上面一个个复制修改sql又会很费时费力，这时我们可以使用循环来达到批量执行新增分区语句。

-- 定义一个循环语句
-- stdt、endt分别为按天创建分区的开始时间和结束时间
do $$
declare
  stdt date := '2021-07-02';  
  endt date := '2021-07-31';
  curr_date date := stdt;
begin
  while curr_date <= endt loop    -- 包含开始结束
    execute 'alter table tfdd.t_partition_table add partition p_' || to_char(curr_date,'YYYYMMDD') || ' start (date''' || to_char(curr_date,'YYYY-MM-DD') || ''') inclusive end (date ''' || to_char(curr_date + interval '1 day','YYYY-MM-DD') || ''') exclusive';
    curr_date := curr_date + interval '1 day';
  end loop;
end $$
;

四、删除分区

        当然，有增就有减。当项目上线到一段时间后，分区数量会累加变得很多，当业务上我们不再需要这么多的数据的时候，我们就可以把不需要的数据删除掉。

        4.1、删除分区

-- partition_name 为新增分区时指定的分区名称
alter table tfdd.t_partition_table drop partition partition_name;

        4.2、批量删除分区

                批量删除参考3.2节批量新增分区逻辑，换汤不换药。

-- 循环删除分区
do $$
declare
  stdt date := '2021-07-01';  
  endt date := '2023-11-30';
  curr_date date := stdt;
  p_date char(20);
begin
  while curr_date <= endt loop    -- 包含开始结束
    p_date := 'p_' || to_char(curr_date,'YYYYMMDD');
    execute 'alter table tfdd.t_partition_table drop partition ' || p_date;
	raise notice 'drop successful of %',p_date;
    curr_date := curr_date + interval '1 day';
  end loop;
end $$
;

        这里记录一个批量删除近n天分区的存储过程

-- 在schema下创建存储过程
-- 删除n天外的分区，简单理解为维护分区表中分区范围近n天。
create or replace function schema.drop_table_days(table_name character varying,table_number interval day)
returns void
language plpgsql
as $function$
	declare
	v_date char(20);
	partition_name varchar(64);
	begin
		for v_date in select substring(inhrelid::regclass::text from length(table_name)+10 for 8) from pg_inherits
			where inhparent::regclass::text=table_name
			and substring(inhrelid::regclass::text from length(table_name)+10 for 8)::date < current_date - table_number order by 1
			loop
				partition_name := 'p_' || v_date;
				raise notice '% will be droped partition！',partition_name;
				execute 'alter table ' || table_name || ' drop partition ' || partition_name;
				raise notice 'partition % have been droped successful！',partition_name;
		end loop;
	end;
$function$;


-- 调用存储过程
select drop_table_days('schema.table_name',interval '760 day')

五、查看分区表详情

-- 用于查找某个表的分区信息
select
	c.relname
from pg_class c
inner join pg_inherits i on i.inhrelid = c. oid
inner join pg_class d on d.oid = i.inhparent
where d.relname = 't_partition_table';


-- 返回如下
              relname
------------------------------------
 t_partition_table_1_prt_p_20210701
(1 row)

六、其他

        最后记录一些在linux后台使用postgresql的命令

-- 切换linux用户
su gpadmin

-- 启动postgresql服务
gpstart

-- 停止postgresql服务
gpstop

-- 进入postgresql数据库
psql/psql -u user

-- 切换数据库
\c xxx