Postgresql集群搭建
Postgres-XL 一款开源的PG集群软件，XL代表eXtensible Lattice，即可扩展的PG“格子”之意。它是一个完全满足ACID的、开源的、可方便进行水平扩展的、多租户安全的、基于PostgreSQL的数据库解决方案。与Pgpool不同的是，Postgres-XL是在PG源代码的基础上增加新功能实现的。它将PG的SQL解析层的工作和数据存取层的工作分离到不同的两种节点上，分别称为Coordinator节点和Datanode节点，而且每种节点可以配置多个，共同协调完成原本单个PG实例完成的工作。此外，为了保证分布模式下事务能够正确执行，增加了一个GTM节点。为了避免单点故障，可以为所有节点配置对应的slave节点。
应用场景
Postgres-XL是一个水平可扩展的开源SQL数据库集群，足够灵活，可以处理不同的数据库工作负载：
-
OLTP 写频繁的业务
-
需要MPP并行性商业智能/大数据分析
-
操作数据存储
-
Key-value 存储
-
GIS的地理空间
-
混合业务工作环境
-
多租户服务提供商托管环境
-
Web 2.0
组件简介
-
Global Transaction Monitor (GTM) 全局事务管理器，确保群集范围内的事务一致性。 GTM负责发放事务ID和快照作为其多版本并发控制的一部分。 集群可选地配置一个备用GTM，以改进可用性。此外，可以在协调器间配置代理GTM， 可用于改善可扩展性，减少GTM的通信量。
-
GTM Standby GTM的备节点，在pgxc,pgxl中，GTM控制所有的全局事务分配，如果出现问题，就会导致整个集群不可用，为了增加可用性，增加该备用节点。当GTM出现问题时，GTM Standby可以升级为GTM，保证集群正常工作。
-
GTM-Proxy GTM需要与所有的Coordinators通信，为了降低压力，可以在每个Coordinator机器上部署一个GTM-Proxy。
-
Coordinator 协调员管理用户会话，并与GTM和数据节点进行交互。协调员解析，并计划查询，并给语句中的每一个组件发送下一个序列化的全局性计划。 为节省机器，通常此服务和数据节点部署在一起。
-
Data Node 数据节点是数据实际存储的地方。数据的分布可以由DBA来配置。为了提高可用性，可以配置数据节点的热备以便进行故障转移准备。
总结：gtm是负责ACID的，保证分布式数据库全局事务一致性。得益于此，就算数据节点是分布的，但是你在主节点操作增删改查事务时，就如同只操作一个数据库一样简单。Coordinator是调度的，将操作指令发送到各个数据节点。datanodes是数据节点，分布式存储数据。
集群搭建
集群规划
| 主机名 | IP | 角色 | 端口 | nodename | 数据目录 |
|---|---|---|---|---|---|
| gtm | 10.4.7.100 | GTM | 6666 | gtm | /nodes/gtm |
| GTM Slave | 20001 | gtmSlave | /nodes/gtmSlave | ||
| datanode1 | 10.4.7.101 | Coordinator | 5432 | coord1 | /nodes/coord |
| Datanode | 5433 | node1 | /nodes/dn_master | ||
| Datanode Slave | 15433 | node1_slave | /nodes/dn_slave | ||
| GTM Proxy | 6666 | gtm_pxy1 | /nodes/gtm_pxy | ||
| datanode2 | 10.4.7.102 | Coordinator | 5432 | coord2 | /nodes/coord |
| Datanode | 5433 | node2 | nodes/dn_master | ||
| Datanode Slave | 15433 | node2_slave | /nodes/dn_slave | ||
| GTM Proxy | 6666 | gtm_pxy2 | /nodes/gtm_pxy |
三台服务器都添加以下hosts内容
[root@gtm ~]# cat /etc/hosts
10.4.7.100 gtm
10.4.7.101 datanode1
10.4.7.102 datanode2
开始搭建
# 三台服务器都操作
yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl gcc
# 关闭防火墙（可选）
systemctl stop firewalld.service && systemctl disable firewalld.service
# 关闭selinux
setenforce 0
sed -i 's#SELINUX=.*#SELINUX=disabled#' /etc/selinux/config
# 同步系统时间
ntpdate ntp1.aliyun.com
创建用户
# 三台服务器都操作
useradd postgres
passwd postgres
su - postgres
mkdir ~/.ssh
chmod 700 ~/.ssh
gtm节点免密登陆配置
su - postgres
ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
将刚生成的认证文件拷贝到node2到node3中，使得gtm节点可以免密码登录node2~node3的任意一个节点：
scp ~/.ssh/authorized_keys postgres@node2:~/.ssh/
scp ~/.ssh/authorized_keys postgres@node3:~/.ssh/
Postgres-XL安装
下载安装包https://www.postgres-xl.org/downloads/postgres-xl-9.5r1.6.tar.bz2
# 三台服务器都操作
# 安装解压缩工具
yum install lbzip2 bzip2 -y
tar xvf postgres-xl-9.5r1.6.tar.bz2
cd postgres-xl-9.5r1.6
./configure --prefix=/home/postgres/pgxl/
make && make install
cd contrib/
make && make install
cortrib中有很多postgres很牛的工具，一般要装上。如ltree,uuid,postgres_fdw等等。
配置环境变量
# 三台服务器都操作
[postgres@gtm ~]$ su - postgres
[postgres@gtm ~]$ cat >>.bashrc<<"EOF"
export PGHOME=/home/postgres/pgxl
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
EOF
[postgres@gtm ~]$ source .bashrc
集群配置
生成pgxc_ctl配置文件
[postgres@gtm ~]# pgxc_ctl
PGXC prepare ---执行该命令将会生成一份配置文件模板
PGXC ---按ctrl c退出。
配置pgxc_ctl.conf
在pgxc_ctl文件夹中存在一个pgxc_ctl.conf文件，编辑如下：
#!/usr/bin/env bash
pgxcInstallDir=$PGHOME
pgxlDATA=$PGHOME/data
pgxcOwner=postgres # owner of the Postgres-XC databaseo cluster. Here, we use this
pgxcUser=$pgxcOwner # OS user of Postgres-XC owner
tmpDir=/tmp # temporary dir used in XC servers
localTmpDir=$tmpDir # temporary dir used here locally
configBackup=n # If you want config file backup, specify y to this value.
configBackupHost=pgxc-linker # host to backup config file
configBackupDir=$HOME/pgxc # Backup directory
configBackupFile=pgxc_ctl.bak # Backup file name --> Need to synchronize when original changed.
#---- GTM Master -----------------------------------------------
gtmName=gtm
gtmMasterServer=gtm
gtmMasterPort=6666
gtmMasterDir=$pgxlDATA/nodes/gtm
#---- Configuration ---
gtmExtraConfig=none # Will be added gtm.conf for both Master and Slave (done at initilization only)
gtmMasterSpecificExtraConfig=none # Will be added to Master's gtm.conf (done at initialization only)
#---- GTM Slave -----------------------------------------------
gtmSlave=y # Specify y if you configure GTM Slave. Otherwise, GTM slave will not be configured and
gtmSlaveName=gtmSlave
gtmSlaveServer=gtm # value none means GTM slave is not available. Give none if you don't configure GTM Slave.
gtmSlavePort=20001 # Not used if you don't configure GTM slave.
gtmSlaveDir=$pgxlDATA/nodes/gtmSlave # Not used if you don't configure GTM slave.
#---- Configuration ----
gtmSlaveSpecificExtraConfig=none # Will be added to Slave's gtm.conf (done at initialization only)
#---- GTM Proxy -------------------------------------------------------------------------------------------------------
gtmProxyDir=$pgxlDATA/nodes/gtm_pxy
#---- Overall -------
gtmProxy=y # Specify y if you conifugre at least one GTM proxy. You may not configure gtm proxies
gtmProxyNames=(gtm_pxy1 gtm_pxy2) # No used if it is not configured
gtmProxyServers=(datanode1 datanode2) # Specify none if you dont' configure it.
gtmProxyPorts=(6666 6666) # Not used if it is not configured.
gtmProxyDirs=($gtmProxyDir $gtmProxyDir) # Not used if it is not configured.
#---- Configuration ----
gtmPxyExtraConfig=none # Extra configuration parameter for gtm_proxy. Coordinator section has an example.
gtmPxySpecificExtraConfig=(none none none none)
#---- Coordinators ----------------------------------------------------------------------------------------------------
coordMasterDir=$pgxlDATA/nodes/coord
coordSlaveDir=$HOME/pgxc/nodes/coord_slave
coordArchLogDir=$HOME/pgxc/nodes/coord_archlog
#---- Overall ------------
coordNames=(coord1 coord2) # Master and slave use the same name
coordPorts=(5432 5432) # Master ports
poolerPorts=(6667 6667) # Master pooler ports
coordPgHbaEntries=(0.0.0.0/0) # Assumes that all the coordinator (master/slave) accepts
#---- Master -------------
coordMasterServers=(datanode1 datanode2) # none means this master is not available
coordMasterDirs=($coordMasterDir $coordMasterDir)
coordMaxWALsernder=0 # max_wal_senders: needed to configure slave. If zero value is specified,
coordMaxWALSenders=($coordMaxWALsernder $coordMaxWALsernder)
#---- Slave -------------
coordSlave=n # Specify y if you configure at least one coordiantor slave. Otherwise, the following
coordSlaveSync=y # Specify to connect with synchronized mode.
coordSlaveServers=(node07 node08 node09 node06) # none means this slave is not available
coordSlavePorts=(20004 20005 20004 20005) # Master ports
coordSlavePoolerPorts=(20010 20011 20010 20011) # Master pooler ports
coordSlaveDirs=($coordSlaveDir $coordSlaveDir $coordSlaveDir $coordSlaveDir)
coordArchLogDirs=($coordArchLogDir $coordArchLogDir $coordArchLogDir $coordArchLogDir)
#---- Configuration files---
coordExtraConfig=coordExtraConfig # Extra configuration file for coordinators.
cat > $coordExtraConfig <<EOF
#================================================
# Added to all the coordinator postgresql.conf
# Original: $coordExtraConfig
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
listen_addresses = '*'
max_connections = 100
EOF
coordSpecificExtraConfig=(none none none none)
coordExtraPgHba=none # Extra entry for pg_hba.conf. This file will be added to all the coordinators' pg_hba.conf
coordSpecificExtraPgHba=(none none none none)
coordAdditionalSlaves=n # Additional slave can be specified as follows: where you
coordAdditionalSlaveSet=(cad1) # Each specifies set of slaves. This case, two set of slaves are
# configured
cad1_Sync=n # All the slaves at "cad1" are connected with asynchronous mode.
# If not, specify "y"
# The following lines specifies detailed configuration for each
# slave tag, cad1. You can define cad2 similarly.
cad1_Servers=(node08 node09 node06 node07) # Hosts
cad1_dir=$HOME/pgxc/nodes/coord_slave_cad1
cad1_Dirs=($cad1_dir $cad1_dir $cad1_dir $cad1_dir)
cad1_ArchLogDir=$HOME/pgxc/nodes/coord_archlog_cad1
cad1_ArchLogDirs=($cad1_ArchLogDir $cad1_ArchLogDir $cad1_ArchLogDir $cad1_ArchLogDir)
#---- Datanodes -------------------------------------------------------------------------------------------------------
#---- Shortcuts --------------
datanodeMasterDir=$pgxlDATA/nodes/dn_master
datanodeSlaveDir=$HOME/pgxc/nodes/dn_slave
datanodeArchLogDir=$HOME/pgxc/nodes/datanode_archlog
primaryDatanode=node1 # Primary Node.
datanodeNames=(node1 node2)
datanodePorts=(5433 5433) # Master ports
datanodePoolerPorts=(6668 6668) # Master pooler ports
datanodePgHbaEntries=(0.0.0.0/0) # Assumes that all the coordinator (master/slave) accepts
#---- Master ----------------
datanodeMasterServers=(datanode1 datanode2) # none means this master is not available.
datanodeMasterDirs=($datanodeMasterDir $datanodeMasterDir)
datanodeMaxWalSender=4 # max_wal_senders: needed to configure slave. If zero value is
datanodeMaxWALSenders=($datanodeMaxWalSender $datanodeMaxWalSender)
#---- Slave -----------------
datanodeSlave=n # Specify y if you configure at least one coordiantor slave. Otherwise, the following
datanodeSlaveServers=(datanode2 datanode1) # value none means this slave is not available
datanodeSlavePorts=(15433 15433) # value none means this slave is not available
datanodeSlavePoolerPorts=(20012 20012) # value none means this slave is not available
datanodeSlaveSync=y # If datanode slave is connected in synchronized mode
datanodeSlaveDirs=($datanodeSlaveDir $datanodeSlaveDir)
datanodeArchLogDirs=($datanodeArchLogDir $datanodeArchLogDir )
# ---- Configuration files ---
datanodeExtraConfig=none # Extra configuration file for datanodes. This file will be added to all the
datanodeSpecificExtraConfig=(none none none none)
datanodeExtraPgHba=none # Extra entry for pg_hba.conf. This file will be added to all the datanodes' postgresql.conf
datanodeSpecificExtraPgHba=(none none none none)
#----- Additional Slaves -----
datanodeAdditionalSlaves=n # Additional slave can be specified as follows: where you
#---- WAL archives -------------------------------------------------------------------------------------------------
walArchive=n # If you'd like to configure WAL archive, edit this section.
walArchiveSet=(war1 war2)
war1_source=(master) # you can specify master, slave or ano other additional slaves as a source of WAL archive.
# Default is the master
wal1_source=(slave)
wal1_source=(additiona_coordinator_slave_set additional_datanode_slave_set)
war1_host=node10 # All the nodes are backed up at the same host for a given archive set
war1_backupdir=$HOME/pgxc/backup_war1
wal2_source=(master)
war2_host=node11
war2_backupdir=$HOME/pgxc/backup_war2
集群初始化
第一次启动集群，需要初始化，初始化如下：
# 三台服务器赋权
[root@gtm pgxc_ctl]$ chown -R postgres:postgres /home/postgres/
# gtm主机操作
[postgres@gtm pgxc_ctl]$ pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf init all
初始化后会直接启动集群
/bin/bash
Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash.
Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash.
Reading configuration using /home/postgres/pgxc_ctl/pgxc_ctl_bash --home /home/postgres/pgxc_ctl --configuration /home/postgres/pgxc_ctl/pgxc_ctl.conf
Finished reading configuration.
******** PGXC_CTL START ***************
Current directory: /home/postgres/pgxc_ctl
Initialize GTM master
ERROR: target directory (/home/postgres/pgxl/data/nodes/gtm) exists and not empty. Skip GTM initilialization
1:1456899904:2022-04-09 17:07:10.378 CST -FATAL: lock file "gtm.pid" already exists
2:1456899904:2022-04-09 17:07:10.378 CST -HINT: Is another GTM (PID 21960) running in data directory "/home/postgres/pgxl/data/nodes/gtm"?
LOCATION: CreateLockFile, main.c:2099
waiting for server to shut down.... done
server stopped
Done.
Start GTM master
server starting
Initialize GTM slave
waiting for server to shut down.... done
server stopped
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.
fixing permissions on existing directory /home/postgres/pgxl/data/nodes/gtmSlave ... ok
creating configuration files ... ok
creating control file ... ok
Success.
Done.
Start GTM slaveserver starting
Done.
Initialize all the gtm proxies.
Initializing gtm proxy gtm_pxy1.
Initializing gtm proxy gtm_pxy2.
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.
fixing permissions on existing directory /home/postgres/pgxl/data/nodes/gtm_pxy ... ok
creating configuration files ... ok
Success.
The files belonging to this GTM system will be owned by user "postgres".
This user must also own the server process.
fixing permissions on existing directory /home/postgres/pgxl/data/nodes/gtm_pxy ... ok
creating configuration files ... ok
Success.
Done.
Starting all the gtm proxies.
Starting gtm proxy gtm_pxy1.
Starting gtm proxy gtm_pxy2.
server starting
server starting
Done.
Initialize all the coordinator masters.
Initialize coordinator master coord1.
Initialize coordinator master coord2.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory /home/postgres/pgxl/data/nodes/coord ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /home/postgres/pgxl/data/nodes/coord/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
creating cluster information ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory /home/postgres/pgxl/data/nodes/coord ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /home/postgres/pgxl/data/nodes/coord/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
creating cluster information ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success.
Done.
Starting coordinator master.
Starting coordinator master coord1
Starting coordinator master coord2
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
Done.
Initialize all the datanode masters.
Initialize the datanode master node1.
Initialize the datanode master node2.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory /home/postgres/pgxl/data/nodes/dn_master ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /home/postgres/pgxl/data/nodes/dn_master/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
creating cluster information ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory /home/postgres/pgxl/data/nodes/dn_master ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /home/postgres/pgxl/data/nodes/dn_master/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok