使用pg_pathman来管理PG库的分区表

最新推荐文章于 2024-07-25 18:15:54 发布

weixin_34107739

最新推荐文章于 2024-07-25 18:15:54 发布

阅读量794

点赞数 1

文章标签：数据库 python git

原文链接：https://my.oschina.net/rocky0202/blog/882123

版权

2019独角兽企业重金招聘Python工程师标准>>>

参考文档：
https://github.com/postgrespro/pg_pathman
https://yq.aliyun.com/articles/62314

1、root用户，安装相关包：
cd /opt
git clone https://github.com/postgrespro/pg_pathman
export PATH=/usr/lib/postgresql/9.5:$PATH
---需要postgresql-server-dev-*.*这个包（development files for PostgreSQL 9.5 server-side programming）
---http://packages.ubuntu.com/xenial/amd64/postgresql-server-dev-9.5/download
---如果数据库是9.6的，请下载9.6版本的。

apt-get install postgresql-server-dev-9.5
cd pg_pathman
make USE_PGXS=1
make USE_PGXS=1 install

例如：
root@rdcmp06-in:/opt/pg_pathman# make USE_PGXS=1 install
/bin/mkdir -p '/usr/lib/postgresql/9.5/lib'
/bin/mkdir -p '/usr/share/postgresql/9.5/extension'
/bin/mkdir -p '/usr/share/postgresql/9.5/extension'
/usr/bin/install -c -m 755 pg_pathman.so '/usr/lib/postgresql/9.5/lib/pg_pathman.so'
/usr/bin/install -c -m 644 .//pg_pathman.control '/usr/share/postgresql/9.5/extension/'
/usr/bin/install -c -m 644 .//pg_pathman--1.0--1.1.sql .//pg_pathman--1.1--1.2.sql .//pg_pathman--1.2--1.3.sql pg_pathman--1.3.sql '/usr/share/postgresql/9.5/extension/'
root@rdcmp06-in:/opt/pg_pathman#

2、postgres用户，修改参数、创建扩展：

$ cd $PGDATA
$ vi postgresql.conf
shared_preload_libraries = 'pg_pathman'
注意，如果还有其它的扩展参数，要把pg_pathman放到第一个。

重启pg库：
$ pg_ctl restart -m fast

连接进去所需要做分区的database里面，创建扩展：

$ psql
postgres=# \c rocky
rocky=# create extension pg_pathman;
CREATE EXTENSION

3、开始对表分区(先对该表做pg_dump备份，以备万一要回退。)：

rocky=#

------分区键上必须要有非空约束
alter table tabname alter timestamp set not null;

------确定分区起始时间
select min(timestamp) from tabname;

------创建分区表
select
create_range_partitions('tabname'::regclass,
'tim',
'2016-12-01 00:00:00'::timestamp,
interval '1 month',
24,
false) ; ------不立即将数据从主表迁移到分区

NOTICE: sequence "tabname_seq" does not exist, skipping
CONTEXT: SQL statement "DROP SEQUENCE IF EXISTS public.tabname_seq"
PL/pgSQL function create_or_replace_sequence(regclass) line 5 at EXECUTE
SQL statement "SELECT public.create_or_replace_sequence(parent_relid)
FROM public.get_plain_schema_and_relname(parent_relid)"
PL/pgSQL function create_range_partitions(regclass,text,anyelement,interval,integer,boolean) line 73 at PERFORM
create_range_partitions
-------------------------
24
(1 row)

rocky=#

4、迁移主表数据到分区表（非堵塞式）
select partition_table_concurrently('tabname'::regclass,
10000,
1.0);

NOTICE: worker started, you can stop it with the following command: select public.stop_concurrent_part_task('tabname');

5、查看迁移状态
select * from pathman_concurrent_part_tasks;

rocky=#

------迁移完毕后，查询pathman_concurrent_part_tasks表无数据返回。

6、迁移完毕后，查看主表only，数据量为0：
rocky=# select count(*) from only tabname ;
count
-------
0
(1 row)

7、数据迁移完成后，建议禁用主表，这样执行计划就不会出现主表了
postgres=# select set_enable_parent('tabname'::regclass, false);

8、数据迁移完成后，要对主表进行vacuum full，才会回收主表的空间：
VACUUM (FULL,VERBOSE, ANALYZE) tabname;

9、其它常见分区表的维护操作：

---向后添加新分区
select append_range_partition('tabname'::regclass);

---在前面添加新分区
select prepend_range_partition('tabname'::regclass);

---删除某个分区，不删数据，数据被移动到主表
select drop_range_partition('tabname_13',false);

---删除某个分区，同时删除数据
select drop_range_partition('tabname_13',true);

---设置为自动扩展分区(默认就是true)
select set_auto('tabname'::regclass,true);
只针对范围分区表，允许自动扩展分区。
如果新插入的数据不在已有的分区范围内，会自动创建分区。

rocky=# select auto from pathman_config_params;
auto
------
t
(1 row)

------删除所有分区表，并将数据迁移到主表。（即：回退分区操作）
select drop_partitions('tabname'::regclass, false);