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 其他问题 参考 https://www.linuxidc.com/Linux/2017-12/149769.htm https://blog.csdn.net/ma141982/article/details/72875294 [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 保持一样参考：
https://wiki.postgresql.org/wiki/Postgres-XC https://sourceforge.net/projects/postgres-xc/files/latest/download https://excellmedia.dl.sourceforge.net/project/postgres-xc/Version_1.2/pgxc-v1.2.1.tar.gz