Postgresql创建分区表---pg_pathman插件

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

https://yq.aliyun.com/articles/62314
https://github.com/postgrespro/pg_pathman

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
apt-get install postgresql-server-dev-9.5

cd pg_pathman
make USE_PGXS=1
make USE_PGXS=1 install

root@hostname:/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@hostname:/opt/pg_pathman# 

postgres用户：

$ cd $PGDATA
$ vi postgresql.conf
shared_preload_libraries = 'pg_pathman,pg_stat_statements' 

$ pg_ctl restart -m fast

$ 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

 
 
 

 
 
------分区键上必须要有非空约束
alter table tablename alter timestamp set  not null; 
 
------确定分区起始时间
select min(timestamp) from tablename;  

------创建分区表
select                                             
create_range_partitions('tablename'::regclass,            
                        'timestamp',                        
                        '2016-06-01 00:00:00'::timestampestamp, 
                        interval '1 month',               
                        24,                               
                        false) ;        ------不立即将数据从主表迁移到分区
                        
------迁移主表数据到分区表（非堵塞式）
select partition_table_concurrently('tablename'::regclass,
                             10000,
                             1.0);             
                                
                             
------查看迁移状态                             
select * from pathman_concurrent_part_tasks;

rocky=# select * from pathman_concurrent_part_tasks;
  userid  |  pid  | dbid  | relid | processed | status  
----------+-------+-------+-------+-----------+---------
 postgres | 26901 | 16384 | tablename   |    660000 | working
(1 row)

rocky=# select * from pathman_concurrent_part_tasks;
  userid  |  pid  | dbid  | relid | processed | status  
----------+-------+-------+-------+-----------+---------
 postgres | 26901 | 16384 | tablename   |    700000 | working
(1 row)

rocky=# select * from pathman_concurrent_part_tasks;
 userid | pid | dbid | relid | processed | status 
--------+-----+------+-------+-----------+--------
(0 rows)

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

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

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

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

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

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

---自动扩展分区(默认就是true)
select set_auto('tablename2'::regclass,true);  

只针对范围分区表，允许自动扩展分区。
如果新插入的数据不在已有的分区范围内，会自动创建分区。

rocky=# select * from pathman_config_params;
 partrel | enable_parent | auto | init_callback | spawn_using_bgw 
---------+---------------+------+---------------+-----------------
 tablename2    | f             | t    |               | f
(1 row)

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

------删除所有分区表，并将数据迁移到主表
select drop_partitions('tablename'::regclass, false);                                                        

转载于:https://my.oschina.net/rocky0202/blog/879828