pg-xl 的基本方式添加节点

os: centos 7.4
pgxl:pg.version ‘10.3 (Postgres-XL 10alpha2)

添加节点、删除节点在日常运维中是很常见的操作。
本次记录的是 pgxl 添加 datanode 类型的节点，典型的横向扩展。

node4节点准备

安装依赖包
关闭防火墙，selinux
创建用户，修改环境变量
创建目录
git获取pgxl
编译安装pgxl
配置ssh信任
ntp同步时间

这些步骤都可以参考上一篇博客。

node4节点上 datanode

$ initdb -D /var/lib/pgxl/data --nodename datanode3 -E UTF8 --locale=C -U postgres -W
Success. You can now start the database server of the Postgres-XL coordinator using:

    pg_ctl -D /var/lib/pgxl/data -l logfile start -Z coordinator

or
 You can now start the database server of the Postgres-XL datanode using:

    pg_ctl -D /var/lib/pgxl/data -l logfile start -Z datanode

$ vi /var/lib/pgxl/data/pg_hba.conf

host    all             all             192.168.56.101/32         trust
host    all             all             192.168.56.102/32         trust
host    all             all             192.168.56.103/32         trust
host    all             all             192.168.56.104/32         trust

$ vi /var/lib/pgxl/data/postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 100

pooler_port = 6667
max_pool_size = 100

gtm_host = 'node1'
gtm_port = 6668
pgxc_node_name = 'datanode3' 

启动 datanode3

$ pg_ctl -D /var/lib/pgxl/data -l logfile start -Z datanode

添加 coordinator，datanode信息

postgres=# alter node datanode3 with (type=datanode,host='node4', port=5432);
postgres=# create node coordinator1 with (type=coordinator,host='node1', port=5432);
postgres=# create node coordinator2 with (type=coordinator,host='node1', port=5433);

postgres=# create node datanode1 with (type=datanode, host='node2',port=5432,primary,preferred);
postgres=# create node datanode2 with (type=datanode, host='node3',port=5432);

postgres=# select pgxc_pool_reload();
postgres=# select * from pgxc_node;

coordinator，datanode添加 datanode3信息

coordinator1，coordinator2，datanode1，datanode2 都需要添加

postgres=# create node datanode3 with (type=datanode, host='node4',port=5432);
postgres=# select pgxc_pool_reload();
postgres=# select * from pgxc_node;

如果使用pgxc_ctl就会方便很多

表重新sharding

登录node1节点的 coordinator1, redistribute tables

$ psql -p 5432
psql (PGXL 10alpha2, based on PG 10.3 (Postgres-XL 10alpha2))
Type "help" for help.

postgres=# select * from pgxc_node;
  node_name   | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id   
--------------+-----------+-----------+-----------+----------------+------------------+-------------
 coordinator2 | C         |      5433 | node1     | f              | f                | -2089598990
 datanode1    | D         |      5432 | node2     | t              | t                |   888802358
 datanode2    | D         |      5432 | node3     | f              | f                |  -905831925
 coordinator1 | C         |      5432 | node1     | f              | f                |  1938253334
 datanode3    | D         |      5432 | node4     | f              | f                | -1894792127
(5 rows)

postgres=# EXECUTE DIRECT ON (datanode3) 'create database peiybdb;';
postgres=# c peiybdb
peiybdb=# EXECUTE DIRECT ON (datanode3) ' create table tmp_t0 (c0 varchar(100),c1 varchar(100));';

peiybdb=# ALTER TABLE tmp_t0 ADD NODE (datanode3);
peiybdb=# select xc_node_id,count(1) from tmp_t0 group by xc_node_id;
 xc_node_id  | count 
-------------+-------
 -1894792127 |  3220
  -905831925 |  3429
   888802358 |  3351
(3 rows)

控制表数据分布的节点

ALTER TABLE tmp_t0 ADD NODE (datanode3);
ALTER TABLE tmp_t0 DELETE NODE (datanode3);

ALTER TABLE tmp_t0 DISTRIBUTE BY HASH(c0);
ALTER TABLE tmp_t0 DISTRIBUTE BY REPLICATION;

下面是帮助文档的截取

To change the distribution type and the list of nodes where table data is located:

ALTER TABLE distributors TO NODE (dn1, dn7), DISTRIBUTE BY HASH(dist_id);

To add a node where data of table is distributed:

ALTER TABLE distributors ADD NODE (dn9, dn14);

To remove a node where data of table is distributed:

ALTER TABLE distributors DELETE NODE (dn4, dn0);

下面是简单的建表分析

CREATE TABLE table_name(...)
DISTRIBUTE BY 
HASH(col)|MODULO(col)|ROUNDROBIN|REPLICATION
TO NODE(nodename1,nodename2...)

可以看到，如果DISTRIBUTE BY 后面有如下选项：
REPLICATION，则是复制模式，其余则是分片模式，
HASH 指的是按照指定列的哈希值分布数据，
MODULO 指的是按照指定列的取摩运算分布数据，
ROUNDROBIN 指的是按照轮询的方式分布数据

TO NODE指定了数据分布的节点范围，如果没有指定则默认所有数据节点参与数据分布。如果没有指定分布模式，即使用普通的CREATE TABLE语句，PGXL会默认采用分片模式将数据分布到所有数据节点。

参考：
https://www.postgres-xl.org/documentation/tutorial-createcluster.html

https://www.postgres-xl.org/documentation/sql-createtable.html
https://www.postgres-xl.org/documentation/sql-altertable.html