Postgres-XC

Postgres-XC 是读写可伸缩的同步多主 PostgreSQL 集群。
特性：
读/写 可伸缩
对称 (多主，同步) 集群
事物管理和一致性元组可视化
集群节点之间的并行事物执行

集群原理初级介绍
1、GTM (Global Transaction Manager)

2、Coordinator

XC VS HS

个人理解，有点类似redis cluster

这个有点类似mysql的主从

XL 它与XC很相近，架构几乎是一样的，只是比XC多了一个MPP功能；
什么是MPP架构呢？
单条SQL提供了多数据节点的并行执行，它更适合商务智能领域；

文档参考：https://www.cnblogs.com/lottu/p/5646455.html

地址：https://www.postgres-xl.org/

XC安装（XL与XC安装差不多）

一，安装规划

GTM 节点: gtm 6666
协调节点一: coord1 1921
协调节点二: coord1 1925
数据节点一: db_1 15431
数据节点二: db_2 15432

二，糸统配置
1、创建 Postgresql 系统用户
[root@redhatB bin]# groupadd Postgresql
[root@redhatB bin]# useradd Postgresql -g Postgresql
[root@redhatB bin]# passwd Postgresql
Changing password for user Postgresql.
New password:
BAD PASSWORD: it is based on a dictionary word
Retype new password:
passwd: all authentication tokens updated successfully.

2、授权
[root@redhatB Postgresql]#mkdir PostgresqlCluster
[root@redhatB Postgresql]# chown -R Postgresql:Postgresql ./PostgresqlCluster

三，配置数据节点
1、配置环境变量
export PGPORT=15431
export PGDATA=./PostgresqlCluster/db_1/data
export LANG=en_US.utf8
export PGHOME=/home/Postgresql/PostgresqlCluster
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexportPATH=$PGHOME/bin:$PATH:.exportMANPATH=$PGHOME/share/man:$MANPATH

2、初始化节点
initdb -D PostgresqlCluster/db_1/data --nodename db_1
initdb -D PostgresqlCluster/db_2/data --nodename db_2

3、修改Postgresql.conf参数
port = 15431
gtm_host = ‘192.168.1.35’
gtm_port = 6666

4、修改PG_ hba.conf
host all all 192.168.1.35/32 trust
host all all 0.0.0.0/0 md5

四，配置GTM节点
1、配置环境变量
export PGPORT=1921
export PGDATA=./PostgresqlCluster /coord1/ data
export LANG=en_US.utf8
export PGHOME=/home/Postgresql/PostgresqlCluster
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexportPATH=$PGHOME/bin:$PATH:.exportMANPATH=$PGHOME/share/man:$MANPATH

2、初始化节点
Initgtm –Z gtm –D PostgresqlCluster/gtm/data

五，配置 coordinator 节点
1、初始化节点
initdb -D PostgresqlCluster/coor_1/data --nodename coor_1
initdb -D PostgresqlCluster/coor_2/data --nodename coor_2

2、修改Postgresql.conf参数
port = 1921
gtm_host = ‘192.168.1.35’
gtm_port = 6666
PGxc_node_name = ‘coor_1’
pooler_port = 6667
（第2个节点配置为pooler_port=6668）

3、修改PG_ hba.conf
host all all 192.168.1.35/32 trust
host all all 0.0.0.0/0 md5

六，启动(因为所有的节点都依懒全局事务，所以要优先启动GTM；一切按下面的顺序来，如果关闭的话则顺序相反；)
1、启动GTM
start gtm
gtm -D ./ gtm/ data
查看 gtm 是否启动
gtm_ctl status -S gtm -D./ gtm/ data &
ps -ef | grep gtm

2、启动数据节点（ 192.168.1.35 ）
Postgres –X -D PostgresqlCluster /db_1/data &
Postgres –X -D PostgresqlCluster /db_2/data &

3、启动coor节点（ 192.168.1.35 ）
Postgres –C -D PostgresqlCluster /coor_1/data &
Postgres –C -D PostgresqlCluster /coor_2/data &
备注：-C 表示 coordinator 节点。

4、查看期待状态，查看GTM、POOl连接
netstat -anp | grep gtm
ps -ef | grep pool

七，注册节点
drop node coor_1;
drop node coor_2;
create node coor_1 with(TYPE=coordinator,HOST=‘192.168.1.35’,PORT=1921);
create node coor_2 with(TYPE=coordinator,HOST=‘192.168.1.35’,PORT=1925);
drop node db_1;
drop node db_2;
create node db_1 with(TYPE=datanode,HOST=‘192.168.1.35’,PORT=15431,primary=false);
create node db_2 with(TYPE=datanode,HOST=‘192.168.1.35’,PORT=15432,primary=false);
alter node coor_1 with(TYPE=‘coordinator’,HOST=‘192.168.1.35’,PORT=1921);
alter node coor_2 with(TYPE=‘coordinator’,HOST=‘192.168.1.35’,PORT=1925);
alter node db_1 with(TYPE=datanode,HOST=‘192.168.1.35’,PORT=15431,primary=true);
alter node db_2 with(TYPE=datanode,HOST=‘192.168.1.35’,PORT=15432,primary=false);
select pgxc_pool_reload();

select * from pgxc_node;

安装

下面全部在一台测试机上部署
一，
糸统配置
1）创建Postgresql糸统用户与安装
[root@node160 ~]# groupadd Postgresql
[root@node160 ~]# useradd Postgresql -g Postgresql
[root@node160 ~]# passwd Postgresql
Changing password for user Postgresql.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.

[root@node160 ~]# tar xzvf pgxc-v1.0.4.tar.gz
[root@node160 ~]# cd postgres-xc-1.0.4/
[root@node160 postgres-xc-1.0.4]# ls
aclocal.m4 config configure configure.in contrib COPYRIGHT doc doc-xc GNUmakefile.in HISTORY Makefile README README.git src

[root@node160 postgres-xc-1.0.4]# yum install zlib-devel readline-devel

[root@node160 postgres-xc-1.0.4]# ./configure --prefix=/home/Postgresql
…
ERROR: flex' is missing on your system. It is needed to create the filegtm_opt_scanner.c’. You can either get flex from a GNU mirror site
or download an official distribution of PostgreSQL, which contains
pre-packaged flex output.
…

解决：
[root@node160 postgres-xc-1.0.4]# yum install flex dlex-devel
[root@node160 postgres-xc-1.0.4]# make clean
[root@node160 postgres-xc-1.0.4]# ./configure --prefix=/home/Postgresql

[root@node160 postgres-xc-1.0.4]# make
[root@node160 postgres-xc-1.0.4]# make install

2） 授权
[root@node160 postgres-xc-1.0.4]# cd /home/ Postgresql/
[root@node160 Postgresql]# mkdir PostgresqlCluster
[root@node160 Postgresql]# chown -R Postgresql:Postgresql *
[root@node160 Postgresql]# ll
total 12
drwxr-xr-x 2 Postgresql Postgresql 4096 Apr 5 10:50 bin
drwxr-xr-x 4 Postgresql Postgresql 4096 Apr 5 10:50 include
drwxr-xr-x 3 Postgresql Postgresql 4096 Apr 5 10:50 lib
drwxrwxr-x 2 Postgresql Postgresql 6 Apr 5 10:51 PostgresqlCluster
drwxr-xr-x 3 Postgresql Postgresql 24 Apr 5 10:50 share

二， 配置数据节点
1）配置环境变量
[Postgresql@node160 ~]$ vim .bash_profile
export PGPORT=15431
export PGDATA=./PostgresqlCluster/db_1/data
export LANG=en_US.utf8
export PGHOME=/home/Postgresql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexportMANPATH=$PGHOME/share/man:$MANPATH
重新加载一下环境变量，退出再登录 即可！

2）初始化节点：
[Postgresql@node160 ~]$ bin/initdb -D PostgresqlCluster/db_1/data --nodename db_1

[Postgresql@node160 ~]$ bin/initdb -D PostgresqlCluster/db_2/data --nodename db_2
The files belonging to this database system will be owned by user “Postgresql”.
This user must also own the server process.
…

WARNING: enabling “trust” authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success.
You can now start the database server of the Postgres-XC coordinator using:

bin/postgres -C -D PostgresqlCluster/db_2/data

or
bin/pg_ctl start -D PostgresqlCluster/db_2/data -Z coordinator -l logfile

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

bin/postgres -X -D PostgresqlCluster/db_2/data

or
bin/pg_ctl start -D PostgresqlCluster/db_2/data -Z datanode -l logfile

3）修改配置
[Postgresql@node160 ~]$ vim PostgresqlCluster/db_1/data/postgresql.conf
port = 15431
listen_addresses = ‘*’
gtm_host = ‘192.168.8.160’
gtm_port = 6666

[Postgresql@node160 ~]$ vim PostgresqlCluster/db_1/data/pg_hba.conf
host all all 192.168.8.160/32 trust
host all all 0.0.0.0/32 md5

[Postgresql@node160 ~]$ vim PostgresqlCluster/db_2/data/postgresql.conf
port = 15432
listen_addresses = ‘*’
gtm_host = ‘192.168.8.160’
gtm_port = 6666

[Postgresql@node160 ~]$ vim PostgresqlCluster/db_2/data/pg_hba.conf
host all all 192.168.8.160/32 trust
host all all 0.0.0.0/32 md5

三，配置GTM节点
1、配置环境变量
export PGPORT=1921
export PGDATA=./PostgresqlCluster /coord1/ data
export LANG=en_US.utf8
export PGHOME=/home/Postgresql/PostgresqlCluster
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexportPATH=$PGHOME/bin:$PATH:.exportMANPATH=$PGHOME/share/man:$MANPATH

初始化节点：
[Postgresql@node160 ~]$ bin/initgtm -Z gtm -D PostgresqlCluster/gtm/data
The files belonging to this GTM system will be owned by user “Postgresql”.
This user must also own the server process.

creating directory PostgresqlCluster/gtm/data … ok
creating configuration files … ok

Success. You can now start the GTM server using:

bin/gtm -D PostgresqlCluster/gtm/data

or
bin/gtm_ctl -Z gtm -D PostgresqlCluster/gtm/data -l logfile start

四，配置 coordinator 节点
1、初始化节点
[Postgresql@node160 ~]$ bin/initdb -D PostgresqlCluster/coor_1/data --nodename coor_1
[Postgresql@node160 ~]$ bin/initdb -D PostgresqlCluster/coor_2/data --nodename coor_2
…
copying template1 to template0 … ok
copying template1 to postgres … ok

WARNING: enabling “trust” authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success.
You can now start the database server of the Postgres-XC coordinator using:

bin/postgres -C -D PostgresqlCluster/coor_2/data

or
bin/pg_ctl start -D PostgresqlCluster/coor_2/data -Z coordinator -l logfile

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

bin/postgres -X -D PostgresqlCluster/coor_2/data

or
bin/pg_ctl start -D PostgresqlCluster/coor_2/data -Z datanode -l logfile

2、修改Postgresql.conf参数
[Postgresql@node160 ~]$ vim PostgresqlCluster/coor_1/data/postgresql.conf
listen_addresses = ‘192.168.8.160’
port = 1921
gtm_host = ‘192.168.8.160’
gtm_port = 6666
pooler_port = 6667 #其他几个配置文件分别改为6668、6667、(同一台机器要使用不同的端口)

[Postgresql@node160 ~]$ vim PostgresqlCluster/coor_1/data/pg_hba.conf
host all all 192.168.8.160/32 trust
host all all 0.0.0.0/32 md5

[Postgresql@node160 ~]$ vim PostgresqlCluster/coor_2/data/postgresql.conf
listen_addresses = ‘192.168.8.160’
port = 1922
gtm_host = ‘192.168.8.160’
gtm_port = 6666
pooler_port = 6668

[Postgresql@node160 ~]$ vim PostgresqlCluster/coor_2/data/pg_hba.conf
host all all 192.168.8.160/32 trust
host all all 0.0.0.0/32 md5

启动顺序为GTM->GTM-Proxy->Coordinators->Datanodes 关闭顺序为Coordinators->Datanodes-> GTM-Proxy->GTM

五 启动
1.启动GTM
[Postgresql@node160 ~]$ bin/gtm -D PostgresqlCluster/gtm/data &
[Postgresql@node160 ~]$ ps -ef|grep gtm
Postgre+ 40411 39710 0 15:50 pts/0 00:00:00 bin/gtm -D PostgresqlCluster/gtm/data

2.启动数据节点
[Postgresql@node160 ~]$ bin/postgres -X -D PostgresqlCluster/db_1/data &
[2] 40528
[Postgresql@node160 ~]$ LOG: database system was shut down at 2021-04-05 11:23:02 CST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started

[Postgresql@node160 ~]$ bin/postgres -X -D PostgresqlCluster/db_2/data &

3.启动coor节点：
[Postgresql@node160 ~]$ bin/postgres -C -D PostgresqlCluster/coor_1/data &
[4] 40569
[Postgresql@node160 ~]$ LOG: database system was shut down at 2021-04-05 15:13:03 CST
LOG: autovacuum launcher started
LOG: database system is ready to accept connections

[Postgresql@node160 ~]$ bin/postgres -C -D PostgresqlCluster/coor_2/data &

4、查看期待状态，查看GTM、POOl连接
[Postgresql@node160 ~]$ netstat -alp|grep gtm
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:ircu-2 0.0.0.0:* LISTEN 40411/bin/gtm
tcp6 0 0 [::]:ircu-2 [::]😗 LISTEN 40411/bin/gtm
[Postgresql@node160 ~]$ ps -ef|grep pool
Postgre+ 40571 40569 0 16:10 ? 00:00:00 postgres: pooler process
Postgre+ 40579 40577 0 16:10 ? 00:00:00 postgres: pooler process

注册节点
drop node coor_1;
drop node coor_2;
create node coor_1 with(TYPE=coordinator,HOST=‘192.168.8.160’,PORT=1921);
create node coor_2 with(TYPE=coordinator,HOST=‘192.168.8.160’,PORT=1922);
drop node db_1;
drop node db_2;
create node db_1 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15431,primary=false);
create node db_2 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15432,primary=false);
alter node coor_1 with(TYPE=‘coordinator’,HOST=‘192.168.8.160’,PORT=1921);
alter node coor_2 with(TYPE=‘coordinator’,HOST=‘192.168.8.160’,PORT=1922);
alter node db_1 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15431,primary=true);
alter node db_2 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15432,primary=false);
select pgxc_pool_reload();

select * from pgxc_node;

#分别连接端口1921和1922的coor节点，把上面注册节点的sql执行一遍

[Postgresql@node160 ~]$ bin/psql -h 192.168.8.160 -d postgres -p1922
psql (PGXC 1.0.4, based on PG 9.1.13)
Type “help” for help.

postgres=# drop node coor_1;
68.8.160’,PORT=1922);
alter node db_1 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15431,primary=true);
alter node db_2 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15432,primary=false);
select pgxc_pool_reload();

select * from pgxc_node;ERROR: PGXC Node coor_1: object not defined
STATEMENT: drop node coor_1;
ERROR: PGXC Node coor_1: object not defined
postgres=# drop node coor_2;
ERROR: PGXC Node coor_2: cannot drop local node
STATEMENT: drop node coor_2;
ERROR: PGXC Node coor_2: cannot drop local node
postgres=# create node coor_1 with(TYPE=coordinator,HOST=‘192.168.8.160’,PORT=1921);
CREATE NODE
postgres=# create node coor_2 with(TYPE=coordinator,HOST=‘192.168.8.160’,PORT=1922);
ERROR: PGXC Node coor_2: object already defined
STATEMENT: create node coor_2 with(TYPE=coordinator,HOST=‘192.168.8.160’,PORT=1922);
ERROR: PGXC Node coor_2: object already defined
postgres=# drop node db_1;
ERROR: PGXC Node db_1: object not defined
STATEMENT: drop node db_1;
ERROR: PGXC Node db_1: object not defined
postgres=# drop node db_2;
ERROR: PGXC Node db_2: object not defined
STATEMENT: drop node db_2;
ERROR: PGXC Node db_2: object not defined
postgres=# create node db_1 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15431,primary=false);
CREATE NODE
postgres=# create node db_2 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15432,primary=false);
CREATE NODE
postgres=# alter node coor_1 with(TYPE=‘coordinator’,HOST=‘192.168.8.160’,PORT=1921);
ALTER NODE
postgres=# alter node coor_2 with(TYPE=‘coordinator’,HOST=‘192.168.8.160’,PORT=1922);
ALTER NODE
postgres=# alter node db_1 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15431,primary=true);
ALTER NODE
postgres=# alter node db_2 with(TYPE=datanode,HOST=‘192.168.8.160’,PORT=15432,primary=false);
ALTER NODE
postgres=# select pgxc_pool_reload();
pgxc_pool_reload

t
(1 row)

postgres=#
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------±----------±----------±--------------±---------------±-----------------±-----------
coor_1 | C | 1921 | 192.168.8.160 | f | f | 1642251045
coor_2 | C | 1922 | 192.168.8.160 | f | f | -201747166
db_1 | D | 15431 | 192.168.8.160 | t | f | 1356996994
db_2 | D | 15432 | 192.168.8.160 | f | f | -822936791
(4 rows)
看到有两个C节点和两个D节点；

创建数据库和表
#在端口是1921下面创建测试数据SQl
[Postgresql@node160 ~]$ bin/psql -h 192.168.8.160 -d postgres -p1921
psql (PGXC 1.0.4, based on PG 9.1.13)
Type “help” for help.

postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database “test” as user “Postgresql”.
test=# create table test(id int);
CREATE TABLE
test=# insert into test select generate_series(1,10);
INSERT 0 10

test=# select * from test;
id

1
2
5
6
8
9
3
4
7
10
(10 rows)

在coor1和coor2可以看到全部的数据，连接15431和15432数据节点，可以看到数据分布在2个数据节点
[Postgresql@node160 ~]$ bin/psql -h 192.168.8.160 -d postgres -p1921
psql (PGXC 1.0.4, based on PG 9.1.13)
Type “help” for help.

postgres=# \c
You are now connected to database “postgres” as user “Postgresql”.
postgres=# select * from test;
id

1
2
5
6
8
9
3
4
7
10
(10 rows)

[Postgresql@node160 ~]$ bin/psql -h 192.168.8.160 -d postgres -p1922
psql (PGXC 1.0.4, based on PG 9.1.13)
Type “help” for help.

postgres=# \c test
You are now connected to database “test” as user “Postgresql”.
test=# select * from test;
id

3
4
7
10
1
2
5
6
8
9
(10 rows)

[Postgresql@node160 ~]$ bin/psql -h 192.168.8.160 -d postgres -p15431
psql (PGXC 1.0.4, based on PG 9.1.13)
Type “help” for help.

postgres=# \c test
You are now connected to database “test” as user “Postgresql”.
test=# select * from test;
id

1
2
5
6
8
9
(6 rows)

test=# \q
[Postgresql@node160 ~]$ bin/psql -h 192.168.8.160 -d postgres -p15432
psql (PGXC 1.0.4, based on PG 9.1.13)
Type “help” for help.

postgres=# \c test
You are now connected to database “test” as user “Postgresql”.
test=# select * from test;
id

3
4
7
10
(4 rows)