一. 前言
PostgreSQL使用比较广泛,但Postgres-XL相对少很多,网络上的很多文章都是学习&实验性质,仅从对pg_hba.conf的配置来看,大多一笔带过,不适合生产部署;官网的Tutorial(https://www.postgres-xl.org/documentation/index.html)写的太敷衍,实用性非常差!为了方便熟悉Postgres-XL的原理,本文参考官网文档,使用最原始的方法安装(建议使用官方推荐的pgxc_ctl工具,更方便快捷)
二. 系统架构
三. 编译安装配置
3.1 源码下载 #all nodes
https://www.postgres-xl.org/
3.2 OS #all nodes
最小化安装OS,关防火墙&selinux,vi /etc/hosts(比较简单不再赘述)。
3.3 Packages #all nodes
yum install -y gcc zlib zlib-devel readline readline-devel flex
yum install -y lrzsz net-tools #可选,建议安装。
3.4 Install #all nodes
mkdir -p /u01/app/pgxl10r1
./configure --enable-debug --enable-cassert --enable-thread-safety CFLAGS=’-O0’ --prefix=/u01/app/pgxl10r1 #’-O0’不对源码做任何优化,可以解决gdb无法print&让进程运行逻辑看起来更符合源码,不用加’-g’已默认配置,如果不需要Debug Postgres-XL进程,仅保留–prefix即可。
make world #比单纯的make多了文档&工具类模块。
…
PostgreSQL, contrib, and documentation successfully made. Ready to install.
make install-world
make clean
3.5 Config profile #all nodes
vi /etc/profile
export LD_LIBRARY_PATH=/u01/app/pgxl10r1/lib
export PATH=/u01/app/pgxl10r1/bin:$PATH
export MANPATH=/u01/app/pgxl10r1/share/man:$MANPATH
/sbin/ldconfig /u01/app/pgxl10r1/lib
3.6 Create user #all nodes
useradd postgres
passwd postgres
3.7 Config DB Cluster
3.7.1 DB
============
#running at all nodes
mkdir -p /data/pgxl10r1/datanode
mkdir -p /data/pgxl10r1/coord
chown -R postgres:postgres /data
su - postgres
initdb -D /data/pgxl10r1/datanode -d --nodename datanode1 #注意此值datanode1不同节点不一样。
initdb -D /data/pgxl10r1/coord -d --nodename coord1 #注意此值coord1不同节点不一样。
vi /data/pgxl10r1/datanode/pg_hba.conf
vi /data/pgxl10r1/coord/pg_hba.conf
host all postgres 192.168.100.0/24 trust
host all all 192.168.100.0/24 scram-sha-256
注意:如果使用pgxc_ctl工具,第1行会自动生成,由于postgres是超级管理账户,如果192.168.100.0是集群内网址,这种配置没问题;
第2行网络上很多配置成trust,这将导致配置IP范围内无密码登录,存在安全隐患,所以我们配成scram-sha-256,此IP在生产环境应该配成所有客户端IP范围,实际上这个看起来逻辑合理的配置也是存在问题的,后面我们会通过Debug来解析问题根源。
vi /data/pgxl10r1/datanode/postgresql.conf
vi /data/pgxl10r1/coord/postgresql.conf
password_encryption = scram-sha-256
3.7.2 GTM
============
#running at k8s01
initgtm -Z gtm -D /data/pgxl10r1/gtm
注意:如果GTM>M_Proxy在一台服务器,需修改自己的端口:
vi /data/pgxl10r1/gtm/gtm.conf
port = 6665
nohup gtm -D /data/pgxl10r1/gtm &
3.7.3 GTM-Proxy
============
#running at all nodes
initgtm -Z gtm_proxy -D /data/pgxl10r1/gtm_proxy
vi /data/pgxl10r1/gtm_proxy/gtm_proxy.conf
gtm_host='k8s01'
gtm_port = 6665
nohup gtm_proxy -D /data/pgxl10r1/gtm_proxy &
3.7.4 DataNode
============
#running at all nodes
vi /data/pgxl10r1/datanode/postgresql.conf
listen_addresses = '*'
其它两参数修改算法:
max_connections= coornode数量*coonode’s max_con
max_prepared_transactions=max_connections
pg_ctl -D /data/pgxl10r1/datanode -l logfile start -Z datanode
3.7.5 CoorNode
============
#running at all nodes
vi /data/pgxl10r1/coord/postgresql.conf
listen_addresses = '*'
port = 5433
pooler_port = 6668
其它参数修改算法:
max_connections:=con数量
pg_ctl -D /data/pgxl10r1/coord -l logfile start -Z coordinator
3.8 Create Nodes
上面的所有步骤配置完成后,集群可以连接,但还不能正常使用:
[postgres@k8s01 ~]$ psql -p5433 #coordinator
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type “help” for help.
postgres=# create database tdb;
ERROR: No Datanode defined in cluster
HINT: You need to define at least 1 Datanode with CREATE NODE.
3.8.1 k8s01
=========
[postgres@k8s01 ~]$ psql #datanode
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type “help” for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-----------
datanode1 | C | 5432 | localhost | f | f | 888802358
(1 row)
postgres=# create node coord1 with(type=‘coordinator’,host=‘k8s01’,port=5433);
CREATE NODE
postgres=# create node coord2 with(type=‘coordinator’,host=‘k8s02’,port=5433);
CREATE NODE
postgres=# alter node datanode1 WITH (TYPE = ‘datanode’, HOST = ‘k8s01’,PORT = 5432,PRIMARY, PREFERRED);
ALTER NODE
postgres=# create node datanode2 WITH (TYPE = ‘datanode’, HOST = ‘k8s02’,PORT = 5432,PREFERRED);
CREATE NODE
postgres=# select pgxc_pool_reload();
pgxc_pool_reload
------------------
t
(1 row)
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-------------
coord1 | C | 5433 | k8s01 | f | f | 1885696643
coord2 | C | 5433 | k8s02 | f | f | -1197102633
datanode1 | D | 5432 | k8s01 | t | t | 888802358
datanode2 | D | 5432 | k8s02 | f | t | -905831925
(4 rows)
postgres=# \q
[postgres@k8s01 ~]$ psql -p5433 #coordinator
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type “help” for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+------------
coord1 | C | 5432 | localhost | f | f | 1885696643
(1 row)
postgres=# alter node coord1 with(type=‘coordinator’,host=‘k8s01’,port=5433);
ALTER NODE
postgres=# create node coord2 with(type=‘coordinator’,host=‘k8s02’,port=5433);
CREATE NODE
postgres=# create node datanode1 WITH (TYPE = ‘datanode’, HOST = ‘k8s01’,PORT = 5432,PRIMARY, PREFERRED);
CREATE NODE
postgres=# create node datanode2 WITH (TYPE = ‘datanode’, HOST = ‘k8s02’,PORT = 5432);
CREATE NODE
postgres=# select pgxc_pool_reload();
pgxc_pool_reload
------------------
t
(1 row)
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-------------
coord1 | C | 5433 | k8s01 | f | f | 1885696643
coord2 | C | 5433 | k8s02 | f | f | -1197102633
datanode1 | D | 5432 | k8s01 | t | t | 888802358
datanode2 | D | 5432 | k8s02 | f | f | -905831925
(4 rows)
postgres=#
3.8.2 k8s02
=========
psql #datanode
[postgres@k8s02 ~]$ psql #datanode
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type “help” for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+------------
datanode2 | C | 5432 | localhost | f | f | -905831925
(1 row)
postgres=# create node coord2 with(type=‘coordinator’,host=‘k8s02’,port=5433);
CREATE NODE
postgres=# create node coord1 with(type=‘coordinator’,host=‘k8s01’,port=5433);
CREATE NODE
postgres=# create node datanode1 WITH (TYPE = ‘datanode’, HOST = ‘k8s01’,PORT = 5432,PRIMARY, PREFERRED);
CREATE NODE
postgres=# alter node datanode2 WITH (TYPE = ‘datanode’, HOST = ‘k8s02’,PORT = 5432,PREFERRED);
ALTER NODE
postgres=# select pgxc_pool_reload();
pgxc_pool_reload
------------------
t
(1 row)
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-------------
coord2 | C | 5433 | k8s02 | f | f | -1197102633
coord1 | C | 5433 | k8s01 | f | f | 1885696643
datanode1 | D | 5432 | k8s01 | t | t | 888802358
datanode2 | D | 5432 | k8s02 | f | t | -905831925
(4 rows)
postgres=# \q
[postgres@k8s02 ~]$ psql -p5433 #coordinator
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type “help” for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-------------
coord2 | C | 5432 | localhost | f | f | -1197102633
(1 row)
postgres=# create node coord1 with(type=‘coordinator’,host=‘k8s01’,port=5433);
CREATE NODE
postgres=# alter node coord2 with(type=‘coordinator’,host=‘k8s02’,port=5433);
ALTER NODE
postgres=# create node datanode1 WITH (TYPE = ‘datanode’, HOST = ‘k8s01’,PORT = 5432,PRIMARY);
CREATE NODE
postgres=# create node datanode2 WITH (TYPE = ‘datanode’, HOST = ‘k8s02’,PORT = 5432,PREFERRED);
CREATE NODE
postgres=# select pgxc_pool_reload();
pgxc_pool_reload
------------------
t
(1 row)
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-------------
coord1 | C | 5433 | k8s01 | f | f | 1885696643
coord2 | C | 5433 | k8s02 | f | f | -1197102633
datanode1 | D | 5432 | k8s01 | t | f | 888802358
datanode2 | D | 5432 | k8s02 | f | t | -905831925
(4 rows)
postgres=# \q
3.9 测试验证
[postgres@k8s01 ~]$ psql -p5433 #coordinator
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type “help” for help.
postgres=# create database testdb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# \c testdb
You are now connected to database “testdb” as user “postgres”.
testdb=# create table tdba_test(sn varchar(40));
CREATE TABLE
testdb=# insert into tdba_test values(‘sn000000001111111111222222222201’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222202’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222203’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222204’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222205’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222206’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222207’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222208’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222209’);
INSERT 0 1
testdb=# insert into tdba_test values(‘sn000000001111111111222222222210’);
INSERT 0 1
testdb=# select datname,oid from pg_catalog.pg_database where datname like ‘testdb’;
datname | oid
---------+-------
testdb | 16388
(1 row)
testdb=# select relname,oid from pg_class where relname like ‘tdba_test’;
relname | oid
-----------+-------
tdba_test | 16389
(1 row)
testdb=# \q
[postgres@k8s01 ~]$ psql -dtestdb #datanode
psql (PGXL 10r1, based on PG 10.5 (Postgres-XL 10r1))
Type “help” for help.
testdb=# select datname,oid from pg_catalog.pg_database where datname like ‘testdb’;
datname | oid
---------+-------
testdb | 16387
(1 row)
testdb=# select relname,oid from pg_class where relname like ‘tdba_test’;
relname | oid
-----------+-------
tdba_test | 16388
(1 row)
[postgres@k8s01 ~]$ ls -lt /data/pgxl10r1/coord/base/16388/1638*
-rw------- 1 postgres postgres 0 9:49 /data/pgxl10r1/coord/base/16388/16389
[postgres@k8s01 ~]$ ls -lt /data/pgxl10r1/datanode/base/16387/1638*
-rw------- 1 postgres postgres 8192 9:52 /data/pgxl10r1/datanode/base/16387/16388
通过表文件大小可以看出,虽然coord&datanode都有数据库及表的元数据,但是表数据实际存储在datanode上。