PGXC
熟悉pg的人对pgxc都不陌生，pgxc最初由stromdb公司开发，应用于商业，后来被TransLattice收购并将其开源，也就是现在的pgxl。Pgxc是基于pg的非常成熟的分布式架构，是一款混合负载的htap数据库。国内也有很多基于pgxc来做的分布式数据库，例如华为GaussDB-A，腾讯Tbase，苏宁易购，亚信antdb等或多或少都借鉴了pgxc的架构理念。pgxc的总体架构大家都很清晰了，不再赘述。
pgxc的架构特点如下：
①gtm保证全局读一致性，两阶段提交保证全局写一致性。
②gtm是整个系统的瓶颈点，在超过150并发的情况下，gtm的瓶颈就会显现，每一个事务开启都会去gtm取事务号和快照信息，造成gtm在网络压力和分配事务号速度上存在瓶颈。
③多个协调节点间需要同步元数据信息，如果协调节点失败，不仅会造成ddl hang住，也可能造成两阶段事务的阻塞。
④pgxc的出现主要是在pg在oltp应用场景上的优化，不管是新增gtm，还是数据一致性的保证上面都做得更加精细化。
⑤和citus类似，数据表也可以分为分布表和复制表，复制表在每一个数据节点都有一份全量数据。
PGXC 的基本结构如下：
Postgres-XC的组件
Coordinators: 协调器，应用程序连接Postgres-XC,实际上连接的就是Coordinators。它对SQL进行分析，并生成全局的执行计划。通常和数据节点 部署在一起也可以单独部署
Datanodes ：数据节点（Nodes）。这是实际数据存储节点，执行本地的SQL。
GTM： 全局事务管理器（Global Transaction Manage），它管理全局的事务ID(GXID即 Global Transaction ID)的分配和管理，GTM控制着全局多版本的可见性，也提供一些全局值如 SEQUENCE的管理。为了保证数据的全局读一致性。这里有个误区，可能有人认为如果没有gtm就会造成节点间数据不一致，这种说法是错误的，gtm是为了保证某一时刻读到一致的数据，而写一致性是通过两阶段提交保证的。
GTM Standby：GTM的备份节点。
GTM-Proxy：GTM要与所有的Coordinators通信，为了降低压力，可以在每台Coordinators机器上部署一个GTM-Proxy。
环境如下：
开启防火墙
firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --zone=public --add-port=5439/tcp --permanent
firewall-cmd --zone=public --add-port=6666/tcp --permanent
firewall-cmd --reload
每台机器编辑 hosts
vim /etc/hosts
10.113.52.1 gtmprimary vlnx113052001.firstshare.cn
10.113.53.1 gtmslave vlnx113053001.firstshare.cn
172.31.107.1 dnode1 gtm-porxy1 vlnx107001.firstshare.cn cdtor1
172.31.107.2 dnode2 gtm-porxy2 vlnx107002.firstshare.cn cdtor2
172.31.107.3 dnode3 gtm-porxy3 vlnx107003.firstshare.cn cdtor3
[zhaowzh@vlnx107001 ~]$ tar zxvf pgxc-v1.2.1.tar.gz
[zhaowzh@vlnx107001 ~]$ cd postgres-xc-1.2.1/3
所有服务器上 添加用户组及用户及其密码
[root@vlnx107001 postgres-xc-1.2.1]# groupadd pgxc
[root@vlnx107001 postgres-xc-1.2.1]# useradd pgxc -g pgxc
[root@vlnx107001 postgres-xc-1.2.1]# passwd pgxc #简单起见设置了 密码为 pgxc
进行源码编译安装
一、
编译
[root@vlnx107001 postgres-xc-1.2.1]# ./configure --prefix=/usr/local/pgxc1.2 --with-perl --with-python
或
[root@vlnx107003 postgres-xc-1.2.1]# ./configure --prefix=/usr/local/pgxc1.2 --with-perl --with-python --enable-debug --enable-depend
# 与本次安装无关，扩展学习需要
./configure --prefix=/home/gpdb --with-libedit-preferred --with-perl --with-python --with-openssl --with-libxml --with-libxslt --enable-profiling --enable-thread-safety --enable-nls=zh_CN --enable-debug --enable-depend
# 与本次安装无关，扩展学习需要
如果遇到
1、
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.
则
[root@vlnx113052001 postgres-xc-1.2.1]# yum install perl-ExtUtils-Embed -y
2、
checking for dtrace... no
configure: error: dtrace not found
则
[root@vlnx113052001 postgres-xc-1.2.1]# yum search dtrace
[root@vlnx113052001 postgres-xc-1.2.1]# yum install systemtap-sdt-devel.x86_64 -y
3、
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support
则
[root@vlnx112052001 postgres-xc-1.2.1]# yum install readline readline-devel -y
4、
configure: error: zlib library not found
If you have zlib already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-zlib to disable zlib support.
则
[root@vlnx112053001 postgres-xc-1.2.1]# yum install zlib zlib-devel -y
5、
configure: error: header file <Python.h> is required for Python
则
[root@vlnx112053001 postgres-xc-1.2.1]# yum install python python-devel -y
6、
configure: error: no acceptable C compiler found in $PATH
则
yum install gcc -y
其他问题 参考
[root@vlnx107001 postgres-xc-1.2.1]# make
当最后出现 All of PostgreSQL successfully made. Ready to install. 则 说明编译成功
如果出现错误请用yum安装flex依赖包然后重新./configure
则需要安装依赖包
依赖包安装
yum install -y bison flex perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake
如果还不可以
则
[root@vlnx107001 postgres-xc-1.2.1]# ./configure --prefix=/usr/local/pgxc1.2 --with-perl --with-python --enable-debug --enable-depend
二、
安装
[root@vlnx107001 postgres-xc-1.2.1]# make install
最后出现 PostgreSQL installation complete. 则 pgxc安装成功
默认安装到 /usr/local/pgxc1.2 目录下三、
创建链接
[root@vlnx107001 pgxc1.2]# ln -sf /usr/local/pgxc1.2 /usr/pgsql-9.6/四、
建环境变量
[root@vlnx107001 pgxc1.2]# export PATH=/usr/local/pgxc1.2/bin:$PATH
[root@vlnx107001 pgxc1.2]# export LD_LIBRARY_PATH=/usr/local/pgxc1.2/lib:$LD_LIBRARY_PATH
echo $PATH
echo $LD_LIBRARY_PATH五、
创建数据目录
[root@vlnx107001 ~]# mkdir -p /opt/pgxc/gtm_proxy
[root@vlnx107002 ~]# mkdir -p /opt/pgxc/gtm_proxy
[root@vlnx107003 ~]# mkdir -p /opt/pgxc/gtm_proxy
chown -R pgxc:pgxc /opt/pgxc
[root@vlnx107001 ~]# mkdir -p /var/lib/pgsql/9.6/dnode
[root@vlnx107002 ~]# mkdir -p /var/lib/pgsql/9.6/dnode
[root@vlnx107003 ~]# mkdir -p /var/lib/pgsql/9.6/dnode
[root@vlnx107001 ~]# mkdir -p /opt/pgxc/coordinator
[root@vlnx107002 ~]# mkdir -p /opt/pgxc/coordinator
[root@vlnx107003 ~]# mkdir -p /opt/pgxc/coordinator
chown -R pgxc:pgxc /var/lib/pgsql/9.6/dnode
[root@vlnx113052001 postgres-xc-1.2.1]# mkdir -p /opt/pgxc/gtm
[root@vlnx113053001 postgres-xc-1.2.1]# mkdir -p /opt/pgxc/gtm_standby
chown -R pgxc:pgxc /opt/pgxc
所有节点
mkdir /var/run/postgresql
chmod 0777 /var/run/postgresql -R六、
修改各种配置文件并初始化
初始化GTM
[pgxc@vlnx113052001 ~]$ /usr/local/pgxc1.2/bin/initgtm -Z gtm -D /opt/pgxc/gtm
修改gtm配置文件
[pgxc@vlnx113052001 ~]$ vim /opt/pgxc/gtm/gtm.conf
nodename = 'gtmprimary'
listen_addresses = '*'
port = 6666
startup = ACT初始化GTM备库
[pgxc@vlnx113053001 ~]$ /usr/local/pgxc1.2/bin/initgtm -Z gtm -D /opt/pgxc/gtm_standby
修改gtm备库配置文件
[pgxc@vlnx113053001 ~]$ vim /opt/pgxc/gtm_standby/gtm.conf
nodename = 'gtmslve'
listen_addresses = '*'
port = 6666
startup = STANDBY
active_host = 'gtmprimary'
active_port = 6666初始化 GTM-Proxy
[pgxc@vlnx107001 ~]$ /usr/local/pgxc1.2/bin/initgtm -Z gtm_proxy -D /opt/pgxc/gtm_proxy
修改 gtm_proxy 配置文件
[pgxc@vlnx107001 ~]$ vim /opt/pgxc/gtm_proxy/gtm_proxy.conf
nodename = 'gtm-porxy1'
port = 6666
gtm_host = 'gtmprimary'
gtm_port = 6666
[pgxc@vlnx107002 ~]$ /usr/local/pgxc1.2/bin/initgtm -Z gtm_proxy -D /opt/pgxc/gtm_proxy
[pgxc@vlnx107002 ~]$ vim /opt/pgxc/gtm_proxy/gtm_proxy.conf
nodename = 'gtm-porxy2'
port = 6666
gtm_host = 'gtmprimary'
gtm_port = 6666
[pgxc@vlnx107003 ~]$ /usr/local/pgxc1.2/bin/initgtm -Z gtm_proxy -D /opt/pgxc/gtm_proxy
[pgxc@vlnx107003 ~]$ vim /opt/pgxc/gtm_proxy/gtm_proxy.conf
nodename = 'gtm-porxy3'
port = 6666
gtm_host = 'gtmprimary'
gtm_port = 6666初始化 Coordinators
[pgxc
@vlnx107001 ~]
$ /usr/local/pgxc1.2/bin/initdb --nodename cdtor1 -D /opt/pgxc/coordinator/ -E UTF8 --locale=C -U postgres -W
密码 pgxc
修改 coordinator配置文件
注意
默认会对每个db创建200个空闲连接
[pgxc@vlnx107001 ~]$ vim /opt/pgxc/coordinator/postgresql.conf
listen_addresses = '*'
port = 5432
logging_collector = on
gtm_host = 'gtmprimary'
gtm_port = 6666
pgxc_node_name = 'cdtor1'
unix_socket_directories = '/var/run/postgresql'
max_pool_size = 1000
min_pool_size = 100
pooler_port = 6667
max_prepared_transactions = 1000
enforce_two_phase_commit = on
vim pg_hba.conf
host all all 172.31.107.1/24 trust
host all all 0.0.0.0/0 trust
[pgxc@vlnx107002 ~]$ /usr/local/pgxc1.2/bin/initdb --nodename cdtor2 -D /opt/pgxc/coordinator/ -E UTF8 --locale=C -U postgres -W
[pgxc@vlnx107002 ~]$ vim /opt/pgxc/coordinator/postgresql.conf
listen_addresses = '*'
port = 5432
logging_collector = on
gtm_host = 'gtmprimary'
gtm_port = 6666
pgxc_node_name = 'cdtor2'
unix_socket_directories = '/var/run/postgresql'
max_pool_size = 1000
min_pool_size = 100
pooler_port = 6667
max_prepared_transactions = 1000 #应该为 max_connection*datanode数
enforce_two_phase_commit = on
vim pg_hba.conf
host all all 172.31.107.1/24 trust
host all all 0.0.0.0/0 trust
[pgxc@vlnx107003 ~]$ /usr/local/pgxc1.2/bin/initdb --nodename cdtor3 -D /opt/pgxc/coordinator/ -E UTF8 --locale=C -U postgres -W
[pgxc@vlnx107003 ~]$ vim /opt/pgxc/coordinator/postgresql.conf
listen_addresses = '*'
port = 5432
logging_collector = on
gtm_host = 'gtmprimary'
gtm_port = 6666
pgxc_node_name = 'cdtor3'
unix_socket_directories = '/var/run/postgresql'
max_pool_size = 1000
min_pool_size = 100
pooler_port = 6667
max_prepared_transactions = 1000 #应该为 max_connection*datanode数
enforce_two_phase_commit = on
vim pg_hba.conf
host all all 172.31.107.1/24 trust
host all all 0.0.0.0/0 trust
初始化 Datanodes
[pgxc@vlnx107001 ~]$ /usr/local/pgxc1.2/bin/initdb --nodename dnode1 -E UTF8 -D /var/lib/pgsql/9.6/dnode --locale=C -U postgres -W
密码 pgxc
修改 node配置文件
[root@vlnx107001 dnode]# vim postgresql.conf
listen_addresses = '*'
port = 5439
logging_collector = on
gtm_host ='gtmprimary'
gtm_port = 6666
pgxc_node_name = 'dnode1'
unix_socket_directories = '/var/run/postgresql'
max_prepared_transactions = 1000
max_connection = 1000
vim pg_hba.conf
host all all 172.31.107.1/24 trust
host all all 0.0.0.0/0 trust
[pgxc@vlnx107002 dnode]$ /usr/local/pgxc1.2/bin/initdb --nodename dnode2 -E UTF8 -D /var/lib/pgsql/9.6/dnode --locale=C -U postgres -W
[root@vlnx107002 dnode]# vim postgresql.conf
listen_addresses = '*'
port = 5439
logging_collector = on
gtm_host =gtmprimary'
gtm_port = 6666
pgxc_node_name = 'dnode2'
unix_socket_directories = '/var/run/postgresql'
max_prepared_transactions = 1000
max_connection = 1000
vim pg_hba.conf
host all all 172.31.107.1/24 trust
host all all 0.0.0.0/0 trust
[pgxc@vlnx107003 dnode]$ /usr/local/pgxc1.2/bin/initdb --nodename dnode3 -E UTF8 -D /var/lib/pgsql/9.6/dnode --locale=C -U postgres -W
[root@vlnx107003 dnode]# vim postgresql.conf
listen_addresses = '*'
port = 5439
logging_collector = on
gtm_host ='gtmprimary'
gtm_port = 6666
pgxc_node_name = 'dnode3'
unix_socket_directories = '/var/run/postgresql'
max_prepared_transactions = 1000
max_connection = 1000
vim pg_hba.conf
host all all 172.31.107.1/24 trust
host all all 0.0.0.0/0 trust七、
启动集群：
注意：
启动集群的顺序为： GTM -> GTM Standby
-> GTM-Proxy
-> Datanodes
-> Coordinators
停止集群的顺序为：Coordinators
-> Datanodes
-> GTM-Proxy
-> GTM Standby
-> GTM
启动：
[pgxc@vlnx113052001 ~]$ /usr/local/pgxc1.2/bin/gtm_ctl -Z gtm -D /opt/pgxc/gtm -l logfile start
[pgxc@vlnx113052001 ~]$ /usr/local/pgxc1.2/bin/gtm_ctl -Z gtm status -D /opt/pgxc/gtm/
[pgxc@vlnx113053001 ~]$ /usr/local/pgxc1.2/bin/gtm_ctl -Z gtm_standby -D /opt/pgxc/gtm_standby -l logfile start
[pgxc@vlnx113053001 ~]$ /usr/local/pgxc1.2/bin/gtm_ctl -Z gtm_standby status -D /opt/pgxc/gtm_standby/
/usr/local/pgxc1.2/bin/gtm_ctl -Z gtm_proxy -D /opt/pgxc/gtm_proxy -l /opt/pgxc/gtm_proxy/logfile start
/usr/local/pgxc1.2/bin/pg_ctl start -D /var/lib/pgsql/9.6/dnode -Z datanode -l /var/lib/pgsql/9.6/dnode/logfile
/usr/local/pgxc1.2/bin/pg_ctl start -D /opt/pgxc/coordinator/ -Z coordinator -l /opt/pgxc/coordinator/logfile停止：
/usr/local/pgxc1.2/bin/pg_ctl stop -D /opt/pgxc/coordinator/ -Z coordinator
/usr/local/pgxc1.2/bin/pg_ctl stop -D /var/lib/pgsql/9.6/dnode -Z datanode
/usr/local/pgxc1.2/bin/gtm_ctl stop -Z gtm_proxy -D /opt/pgxc/gtm_proxy
/usr/local/pgxc1.2/bin/gtm_ctl stop -Z gtm_standby -D /opt/pgxc/gtm_standby
/usr/local/pgxc1.2/bin/gtm_ctl stop -Z gtm -D /opt/pgxc/gtm八、
配置集群节点信息
启动集群后，还需要在各个 Coordinators 中配置集群节点信息，然后集群才可以正常使用
登录都所有 Coordinator上，执行下列SQL命令
pgxc@vlnx113052001 coordinator]$ psql -p 5435 postgres
create node cdtor1 with (type = 'coordinator', host = 'vlnx107001.firstshare.cn',port=5432);
create node cdtor2 with (type = 'coordinator', host = 'vlnx107002.firstshare.cn',port=5432);
create node cdtor3 with (type = 'coordinator', host = 'vlnx107003.firstshare.cn',port=5432);
create node dnode1 with (type = 'datanode', host = 'vlnx107001.firstshare.cn',port=5439);
create node dnode2 with (type = 'datanode', host = 'vlnx107002.firstshare.cn',port=5439);
create node dnode3 with (type = 'datanode', host = 'vlnx107003.firstshare.cn',port=5439);
注册数据节点时可以指定 primary preferred选项
比如
postgres=# create node dnode1 with (type = 'datanode', host = 'vlnx107001.firstshare.cn',port=5439,primary,preferred);
postgres=# select pgxc_pool_reload();
postgres-XC如何使用及维护 见其 《postgres-XC 使用详解及维护》
官网 建议 Gtm_proxy,Coordinator 和 DataNode 部署在同一台服务器上
max_prepared_transactions要和max_connection 保持一样 参考：
691
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
