PostgreSQL源码系列一:Postgres-XL编译&安装&配置

一. 前言

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 datanode
1 #注意此值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&GTM_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上。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值