pg_pathman 之一 setup

os: centos 7.4
db: postgresql 10.10
pg_pathman: 1.5

pg_pathman 是 postgresql 管理分区插件，postgresql 9.6、10 的内置分区管理也一直都在完善。使用哪种方式来管理，用户自己决定。不过pg_pathman 确实很方便。
由于 pg_pathman 使用了custom scan provider api，所以只支持PostgreSQL 9.5以及以上的版本

The pg_pathman module provides optimized partitioning mechanism and functions to manage partitions.

The extension is compatible with:

PostgreSQL 9.5, 9.6, 10;
Postgres Pro Standard 9.5, 9.6;
Postgres Pro Enterprise;

PostgreSQL <= 10 supports partitioning via table inheritance: each partition must be created as a child table with CHECK CONSTRAINT:

CREATE TABLE test (id SERIAL PRIMARY KEY, title TEXT);
CREATE TABLE test_1 (CHECK ( id >= 100 AND id < 200 )) INHERITS (test);
CREATE TABLE test_2 (CHECK ( id >= 200 AND id < 300 )) INHERITS (test);

PostgreSQL 10 provides native partitioning:

CREATE TABLE test(id int4, value text) PARTITION BY RANGE(id);
CREATE TABLE test_1 PARTITION OF test FOR VALUES FROM (1) TO (10);
CREATE TABLE test_2 PARTITION OF test FOR VALUES FROM (10) TO (20);

下载

$ git clone https://github.com/postgrespro/pg_pathman.git
$ git branch -a
$ git checkout PGPRO

安装

这里是编译，也可以使用 yum、apt 方式安装，简单快捷

$ export PGHOME=/usr/pgsql-10;
export PGDATA=/var/lib/pgsql/10/data;
export MANPATH=$PGHOME/share/man:$MANPATH;
export LD_LIBRARY_PATH=$PGHOME/lib;
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib;
export PATH=$PGHOME/bin:$PATH;

$ make USE_PGXS=1
$ make install USE_PGXS=1
$ ls -l /usr/pgsql-10/lib/ |grep -i path
-rw-r--r-- 2 root     root      226408 Jul  3  2019 pg_pathman.so

$ ls -l /usr/pgsql-10/share/extension/ |grep -i path
-rw-r--r-- 1 root root 55882 Jul  3  2019 pg_pathman--1.0--1.1.sql
-rw-r--r-- 1 root root 36973 Jul  3  2019 pg_pathman--1.1--1.2.sql
-rw-r--r-- 1 root root 29154 Jul  3  2019 pg_pathman--1.2--1.3.sql
-rw-r--r-- 1 root root 43021 Jul  3  2019 pg_pathman--1.3--1.4.sql
-rw-r--r-- 1 root root 27195 Jul  3  2019 pg_pathman--1.4--1.5.sql
-rw-r--r-- 1 root root 52593 Jul  3  2019 pg_pathman--1.5.sql
-rw-r--r-- 1 root root   131 Jul  3  2019 pg_pathman.control

需要修改 shared_preload_libraries 参数，启动时就加载 pg_pathman 插件

# vi /var/lib/pgsql/10/data/postgresql.conf

shared_preload_libraries = 'pg_pathman, pg_stat_statements'

# systemctl restart postgresql-10.service

$ psql
postgres=# \c peiybdb
You are now connected to database "peiybdb" as user "postgres".

peiybdb=# show shared_preload_libraries;
    shared_preload_libraries    
--------------------------------
 pg_pathman, pg_stat_statements
(1 row)

peiybdb=# select * from pg_available_extensions where name like '%path%';
    name    | default_version | installed_version |             comment              
------------+-----------------+-------------------+----------------------------------
 pg_pathman | 1.5             | 1.5               | Partitioning tool for PostgreSQL
(1 row)

peiybdb=# CREATE EXTENSION pg_pathman;
CREATE EXTENSION

peiybdb=# select * from pg_extension where 1=1 and extname like '%path%';

  extname   | extowner | extnamespace | extrelocatable | extversion |    extconfig    | extcondition 
------------+----------+--------------+----------------+------------+-----------------+--------------
 pg_pathman |       10 |         2200 | f              | 1.5        | {148457,148468} | {"",""}
(1 row)

peiybdb=# select name,setting from pg_settings where name like 'pg_pathman%' order by name;
                 name                 | setting  
--------------------------------------+----------
 pg_pathman.enable                    | on
 pg_pathman.enable_auto_partition     | on
 pg_pathman.enable_bounds_cache       | on
 pg_pathman.enable_partitionfilter    | on
 pg_pathman.enable_partitionrouter    | off
 pg_pathman.enable_runtimeappend      | on
 pg_pathman.enable_runtimemergeappend | on
 pg_pathman.insert_into_fdw           | postgres
 pg_pathman.override_copy             | on
(9 rows)

更新

peiybdb=# ALTER EXTENSION pg_pathman UPDATE TO "X.Y";

peiybdb=# SET pg_pathman.enable = t;

查看一些相关信息

peiybdb=# \dx+ pg_pathman

 table pathman_config
 table pathman_config_params
 view pathman_cache_stats
 view pathman_concurrent_part_tasks
 view pathman_partition_list
(83 rows)

peiybdb=# \d+
                                   List of relations
 Schema |             Name              |   Type   |  Owner   |    Size    | Description 
--------+-------------------------------+----------+----------+------------+-------------
 public | pathman_cache_stats           | view     | postgres | 0 bytes    | 
 public | pathman_concurrent_part_tasks | view     | postgres | 0 bytes    | 
 public | pathman_config                | table    | postgres | 16 kB      | 
 public | pathman_config_params         | table    | postgres | 16 kB      | 
 public | pathman_partition_list        | view     | postgres | 0 bytes    |

安装完毕，可以使用了。

参考：
https://github.com/postgrespro/pg_pathman
https://github.com/postgrespro/pg_pathman/wiki
https://github.com/postgrespro/pg_pathman_build

https://gitee.com/mirrors/pg_pathman/