参考文档:
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
rocky=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+------------------------------
pg_pathman | 1.1 | public | Partitioning tool ver. 1.1
rocky=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------+----------
public | pathman_concurrent_part_tasks | view | postgres
public | pathman_config | table | postgres
public | pathman_config_params | table | postgres
public | pathman_partition_list | view | postgres
3、开始对表分区(先对该表做pg_dump备份,以备万一要回退。):
rocky=# \d tabname
Table "public.tabname"
Column | Type | Modifiers
--------+--------------------------+--------------------------------------------------
id | integer | not null default nextval('tabname_id_seq'::regclass)
loc | character varying(255) |
dat | jsonb |
timestamp | timestamp with time zone |
Indexes:
"tabname_pkey" PRIMARY KEY, btree (id)
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=# select * from pathman_concurrent_part_tasks;
userid | pid | dbid | relid | processed | status
----------+-------+-------+-------+-----------+---------
postgres | 12428 | 16393 | tabname | 3970000 | working
(1 row)
rocky=# select * from pathman_concurrent_part_tasks;
userid | pid | dbid | relid | processed | status
----------+-------+-------+-------+-----------+---------
postgres | 12428 | 16393 | tabname | 7250000 | working
(1 row)
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 * from pathman_config_params;
partrel | enable_parent | auto | init_callback | spawn_using_bgw
---------+---------------+------+---------------+-----------------
tabname2 | f | t | | f
(1 row)
rocky=# select auto from pathman_config_params;
auto
------
t
(1 row)
------删除所有分区表,并将数据迁移到主表。(即:回退分区操作)
select drop_partitions('tabname'::regclass, false);