Postgres-XC

2024-02-05 09:21:52

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= P G H O M E / l i b : / l i b 64 : / u s r / l i b 64 : / u s r / l o c a l / l i b 64 : / l i b : / u s r / l i b : / u s r / l o c a l / l i b e x p o r t P A T H = PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib export PATH= PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexportPATH=PGHOME/bin: P A T H : . e x p o r t M A N P A T H = PATH:. export MANPATH= 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= P G H O M E / l i b : / l i b 64 : / u s r / l i b 64 : / u s r / l o c a l / l i b 64 : / l i b : / u s r / l i b : / u s r / l o c a l / l i b e x p o r t P A T H = PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib export PATH= PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexportPATH=PGHOME/bin: P A T H : . e x p o r t M A N P A T H = PATH:. export MANPATH= 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= P G H O M E / l i b : / l i b 64 : / u s r / l i b 64 : / u s r / l o c a l / l i b 64 : / l i b : / u s r / l i b : / u s r / l o c a l / l i b e x p o r t M A N P A T H = PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib export MANPATH= 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= P G H O M E / l i b : / l i b 64 : / u s r / l i b 64 : / u s r / l o c a l / l i b 64 : / l i b : / u s r / l i b : / u s r / l o c a l / l i b e x p o r t P A T H = PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib export PATH= PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexportPATH=PGHOME/bin: P A T H : . e x p o r t M A N P A T H = PATH:. export MANPATH= 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 [::]

上一篇：题解 [ABC213E]Stronger Takahashi


下一篇：流复制浅析 —— 物理流复制部署