【Postgres-XL-10】安装实战

卸载PGXL

停止PGXL

pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf stop all

然后删除PGXL所有文件

rm -rf /postgres

删除PGXL用户

[root@hadoop1 ~]# userdel -r postgres
userdel: user postgres is currently used by process 24261

没有删除成功，原因是用户root-> su - postgres-> su - root切换导致的，需要退出登录
使用ctrl+d退出，直至断开连接，重新连接后再删除用户

ctrl+d

查看linux的用户、看用户postgres删除成功没有

cat /etc/passwd

安装PGXL

集群分配

CentOS	Hostname	节点类型
7.5	hadoop1、gtm	gtm
7.5	hadoop2、xl1	coordinator1,datanode1,datanode3
7.5	hadoop3、xl2	coordinator2,datanode2,datanode4

节点分配

节点名称	所在机器	节点类型	端口号	连接池端口号
gtm	gtm@hadoop2	gtm	6666
gtm_pxy	xl1@hadoop1	gtm_proxy	6666
coord1	xl1@hadoop1	coordinator	5432	6667
gtm_pxy	gtm@hadoop2	gtm_proxy	6666
coord2	xl2@hadoop3	coordinator	5432	6667
node1	xl1@hadoop1	datanode	5433	6668
node2	xl2@hadoop3	datanode	5433	6668
node3	xl1@hadoop1	datanode	5434	6669
node4	xl2@hadoop3	datanode	5434	6669

安装文件与环境准备

查看操作系统版本

[root@hadoop1 ~]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 

下载Postgres-XL

https://www.postgres-xl.org/download/

每个节点解压

tar -zxvf postgres-xl-10r1.1.tar.gz -C /home/postgres/

修改host

vi /etc/hosts
ip1 gtm
ip2 xl1
ip3 xl2

关闭防火墙

systemctl stop firewalld.service
firewall-cmd --state

永久关闭selinux

vi /etc/sysconfig/selinux
修改selinux内容 selinux=enforcing 替换为 selinux=disabled
getenforce

创建用户(所用节点都要做)

useradd postgres
passwd postgres
切换用户 su - postgres
ssh-keygen -t rsa
按4下enter生成密钥
将公钥复制到其他节点
ssh-copy-id postgres@xl1
ssh-copy-id postgres@xl2
ssh-copy-id postgres@gtm

修改内核参数

 vi /etc/sysctl.conf
添加以下参数
kernel.sem = 50100 128256000 50100 2560
生效
sysctl -p

安装依赖（每个节点）

yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl gcc

编译安装PGXL（每个节点）

cd /home/postgres/postgres-xl-10r1.1/

./configure --prefix=/home/postgres/pgxl/

make

make install

安装必要工具

cd /contrib

make

make install

修改pgxl权限：

chown -R postgres:postgres /home/postgres/pgxl

配置PG-XL集群环境变量

vi ~/.bashrc

export PGHOME=/home/postgres/pgxl
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH

生成pgxc_ctl.conf配置文件
pgxc_ctl prepare指令执行生成pgxc.conf配置文件

pgxc_ctl prepare

/bin/bash
Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash.
ERROR: File "/home/postgres/pgxc_ctl/pgxc_ctl.conf" not found or not a regular file. No such file or directory
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
执行完成后，在postgres用户根目录下，会生成一个pgxc_ctl目录，其中存在pgxc_ctl.conf文件

修改pgxc_ctl.conf文件，改完后所有节点复制过去保持一致

#!/usr/bin/env bash
#
# Postgres-XC Configuration file for pgxc_ctl utility. 
#
# Configuration file can be specified as -c option from pgxc_ctl command.   Default is
# $PGXC_CTL_HOME/pgxc_ctl.org.
#
# This is bash script so you can make any addition for your convenience to configure
# your Postgres-XC cluster.
#
# Please understand that pgxc_ctl provides only a subset of configuration which pgxc_ctl
# provide.  Here's several several assumptions/restrictions pgxc_ctl depends on.
#
# 1) All the resources of pgxc nodes has to be owned by the same user.   Same user means
#    user with the same user name.  User ID may be different from server to server.
#    This must be specified as a variable $pgxcOwner.
#
# 2) All the servers must be reacheable via ssh without password.   It is highly recommended
#    to setup key-based authentication among all the servers.
#
# 3) All the databases in coordinator/datanode has at least one same superuser.  Pgxc_ctl
#    uses this user to connect to coordinators and datanodes.   Again, no password should
#    be used to connect.  You have many options to do this, pg_hba.conf, pg_ident.conf and
#    others.  Pgxc_ctl provides a way to configure pg_hba.conf but not pg_ident.conf.   This
#    will be implemented in the later releases.
#
# 4) Gtm master and slave can have different port to listen, while coordinator and datanode
#    slave should be assigned the same port number as master.
#
# 5) Port nuber of a coordinator slave must be the same as its master.
#
# 6) Master and slave are connected using synchronous replication.  Asynchronous replication
#    have slight (almost none) chance to bring total cluster into inconsistent state.
#    This chance is very low and may be negligible.  Support of asynchronous replication
#    may be supported in the later release.
#
# 7) Each coordinator and datanode can have only one slave each.  Cascaded replication and
#    multiple slave are not supported in the current pgxc_ctl.
#
# 8) Killing nodes may end up with IPC resource leak, such as semafor and shared memory.
#    Only listening port (socket) will be cleaned with clean command.
#
# 9) Backup and restore are not supported in pgxc_ctl at present.   This is a big task and
#    may need considerable resource.
#
#========================================================================================
#
#
# pgxcInstallDir variable is needed if you invoke "deploy" command from pgxc_ctl utility.
# If don't you don't need this variable.
pgxcInstallDir=$HOME/pgxc
#---- OVERALL -----------------------------------------------------------------------------
#
pgxcInstallDir=$PGHOME

pgxlDATA=$PGHOME/data

pgxcOwner=postgres			# owner of the Postgres-XC databaseo cluster.  Here, we use this
						# both as linus user and database user.  This must be
						# the super user of each coordinator and datanode.
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 ------------------------------------------------------------------------------------

# GTM is mandatory.  You must have at least (and only) one GTM master in your Postgres-XC cluster.
# If GTM crashes and you need to reconfigure it, you can do it by pgxc_update_gtm command to update
# GTM master with others.   Of course, we provide pgxc_remove_gtm command to remove it.  This command
# will not stop the current GTM.  It is up to the operator.


#---- GTM Master -----------------------------------------------

#---- Overall ----
gtmName=gtm
gtmMasterServer=gtm
gtmMasterPort=6666
gtmMasterDir=$pgxlDATA/pgxc/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 -----------------------------------------------

# Because GTM is a key component to maintain database consistency, you may want to configure GTM slave
# for backup.

#---- Overall ------
gtmSlave=n					# Specify y if you configure GTM Slave.   Otherwise, GTM slave will not be configured and
							# all the following variables will be reset.
#gtmSlaveName=gtmSlave
#gtmSlaveServer=node12		# 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=$HOME/pgxc/nodes/gtm	# Not used if you don't configure GTM slave.
# Please note that when you have GTM failover, then there will be no slave available until you configure the slave
# again. (pgxc_add_gtm_slave function will handle it)

#---- Configuration ----
gtmSlaveSpecificExtraConfig=none # Will be added to Slave's gtm.conf (done at initialization only)

#---- GTM Proxy -------------------------------------------------------------------------------------------------------
# GTM proxy will be selected based upon which server each component runs on.
# When fails over to the slave, the slave inherits its master's gtm proxy.  It should be
# reconfigured based upon the new location.
#
# To do so, slave should be restarted.   So pg_ctl promote -> (edit postgresql.conf and recovery.conf) -> pg_ctl restart
#
# You don't have to configure GTM Proxy if you dont' configure GTM slave or you are happy if every component connects
# to GTM Master directly.  If you configure GTL slave, you must configure GTM proxy too.


gtmProxyDir=$pgxlDATA/nodes/gtm_proxy
#---- 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=(xl1 xl2) 		 # 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.


#---- Shortcuts ------
#gtmProxyDir=$pgxlDATA/pgxc/nodes/gtm_pxy

#---- Overall -------
#gtmProxy=y				# Specify y if you conifugre at least one GTM proxy.   You may not configure gtm proxies
						# only when you dont' configure GTM slaves.
						# If you specify this value not to y, the following parameters will be set to default empty values.
						# If we find there're no valid Proxy server names (means, every servers are specified
						# as none), then gtmProxy value will be set to "n" and all the entries will be set to
						# empty values.
#gtmProxyNames=(gtm_pxy1 gtm_pxy2 gtm_pxy3 gtm_pxy4)	# No used if it is not configured
#gtmProxyServers=(node06 node07 node08 node09)			# Specify none if you dont' configure it.
#gtmProxyPorts=(20001 20001 20001 20001)				# Not used if it is not configured.
#gtmProxyDirs=($gtmProxyDir $gtmProxyDir $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)

#---- Coordinators ----------------------------------------------------------------------------------------------------

#---- shortcuts ----------
coordMasterDir=$pgxlDATA/pgxc/nodes/coord
coordSlaveDir=$pgxlDATA/pgxc/nodes/coord_slave
coordArchLogDir=$pgxlDATA/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=(172.16.1.0/22)				# Assumes that all the coordinator (master/slave) accepts
												# the same connection
												# This entry allows only $pgxcOwner to connect.
												# If you'd like to setup another connection, you should
												# supply these entries through files specified below.
# Note: The above parameter is extracted as "host all all 0.0.0.0/0 trust".   If you don't want
# such setups, specify the value () to this variable and suplly what you want using coordExtraPgHba
# and/or coordSpecificExtraPgHba variables.
#coordPgHbaEntries=(::1/128)	# Same as above but for IPv6 addresses

#---- Master -------------
coordMasterServers=(xl1 xl2)		# none means this master is not available
coordMasterDirs=($coordMasterDir $coordMasterDir)
coordMaxWALsernder=5	# max_wal_senders: needed to configure slave. If zero value is specified,
						# it is expected to supply this parameter explicitly by external files
						# specified in the following.	If you don't configure slaves, leave this value to zero.
coordMaxWALSenders=($coordMaxWALsernder $coordMaxWALsernder)
						# max_wal_senders configuration for each coordinator.

#---- Slave -------------
coordSlave=n			# Specify y if you configure at least one coordiantor slave.  Otherwise, the following
						# configuration parameters will be set to empty values.
						# If no effective server names are found (that is, every servers are specified as none),
						# then coordSlave value will be set to n and all the following values will be set to
						# empty values.

coordUserDefinedBackupSettings=n	# Specify whether to update backup/recovery
									# settings during standby addition/removal.

#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---
# Need these when you'd like setup specific non-default configuration 
# These files will go to corresponding files for the master.
# You may supply your bash script to setup extra config lines and extra pg_hba.conf entries 
# Or you may supply these files manually.
coordExtraConfig=coordExtraConfig	# Extra configuration file for coordinators.  
						# This file will be added to all the coordinators'
						# postgresql.conf
# Pleae note that the following sets up minimum parameters which you may want to change.
# You can put your postgresql.conf lines here.
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 = 1024
EOF

# Additional Configuration file for specific coordinator master.
# You can define each setting by similar means as above.
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)

#----- Additional Slaves -----
#
# Please note that this section is just a suggestion how we extend the configuration for
# multiple and cascaded replication.   They're not used in the current version.
#
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/pgxc/nodes/dn_master
datanodeSlaveDir=$pgxlDATA/pgxc/nodes/dn_slave
datanodeArchLogDir=$pgxlDATA/pgxc/nodes/datanode_archlog

#---- Overall ---------------
#primaryDatanode=datanode1				# Primary Node.
# At present, xc has a priblem to issue ALTER NODE against the primay node.  Until it is fixed, the test will be done
# without this feature.
primaryDatanode=datanode1				# Primary Node.
datanodeNames=(datanode1 datanode2 datanode3 datanode4)
datanodePorts=(5433 5433 5434 5434)	# Master ports
datanodePoolerPorts=(6668 6668 6669 6669)	# Master pooler ports
datanodePgHbaEntries=(172.16.1.0/22)	# Assumes that all the coordinator (master/slave) accepts
										# the same connection
										# This list sets up pg_hba.conf for $pgxcOwner user.
										# If you'd like to setup other entries, supply them
										# through extra configuration files specified below.
# Note: The above parameter is extracted as "host all all 0.0.0.0/0 trust".   If you don't want
# such setups, specify the value () to this variable and suplly what you want using datanodeExtraPgHba
# and/or datanodeSpecificExtraPgHba variables.
#datanodePgHbaEntries=(::1/128)	# Same as above but for IPv6 addresses

#---- Master ----------------
datanodeMasterServers=(xl1 xl2 xl1 xl2)	# none means this master is not available.
													# This means that there should be the master but is down.
													# The cluster is not operational until the master is
													# recovered and ready to run.	
datanodeMasterDirs=($datanodeMasterDir/datanode1 $datanodeMasterDir/datanode2 $datanodeMasterDir/datanode3 $datanodeMasterDir/datanode4)
datanodeMaxWalSender=5								# max_wal_senders: needed to configure slave. If zero value is 
													# specified, it is expected this parameter is explicitly supplied
													# by external configuration files.
													# If you don't configure slaves, leave this value zero.
datanodeMaxWALSenders=($datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender)
						# max_wal_senders configuration for each datanode

#---- Slave -----------------
datanodeSlave=n			# Specify y if you configure at least one coordiantor slave.  Otherwise, the following
						# configuration parameters will be set to empty values.
						# If no effective server names are found (that is, every servers are specified as none),
						# then datanodeSlave value will be set to n and all the following values will be set to
						# empty values.

datanodeUserDefinedBackupSettings=n	# Specify whether to update backup/recovery
									# settings during standby addition/removal.

datanodeSlaveServers=(node07 node08 node09 node06)	# value none means this slave is not available
datanodeSlavePorts=(20008 20009 20008 20009)	# value none means this slave is not available
datanodeSlavePoolerPorts=(20012 20013 20012 20013)	# value none means this slave is not available
datanodeSlaveSync=y		# If datanode slave is connected in synchronized mode
datanodeSlaveDirs=($datanodeSlaveDir $datanodeSlaveDir $datanodeSlaveDir $datanodeSlaveDir)
datanodeArchLogDirs=( $datanodeArchLogDir $datanodeArchLogDir $datanodeArchLogDir $datanodeArchLogDir )

# ---- Configuration files ---
# You may supply your bash script to setup extra config lines and extra pg_hba.conf entries here.
# These files will go to corresponding files for the master.
# Or you may supply these files manually.
datanodeExtraConfig=none	# Extra configuration file for datanodes.  This file will be added to all the 
							# datanodes' postgresql.conf
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
# datanodeAdditionalSlaveSet=(dad1 dad2)		# Each specifies set of slaves.   This case, two set of slaves are
											# configured
# dad1_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.
# dad1_Servers=(node08 node09 node06 node07)	# Hosts
# dad1_dir=$HOME/pgxc/nodes/coord_slave_cad1
# dad1_Dirs=($cad1_dir $cad1_dir $cad1_dir $cad1_dir)
# dad1_ArchLogDir=$HOME/pgxc/nodes/coord_archlog_cad1
# dad1_ArchLogDirs=($cad1_ArchLogDir $cad1_ArchLogDir $cad1_ArchLogDir $cad1_ArchLogDir)

#---- WAL archives -------------------------------------------------------------------------------------------------
walArchive=n	# If you'd like to configure WAL archive, edit this section.
				# Pgxc_ctl assumes that if you configure WAL archive, you configure it
				# for all the coordinators and datanodes.
				# Default is "no".   Please specify "y" here to turn it on.
#
#		End of Configuration Section
#
#==========================================================================================================================

#========================================================================================================================
# The following is for extension.  Just demonstrate how to write such extension.  There's no code
# which takes care of them so please ignore the following lines.  They are simply ignored by pgxc_ctl.
# No side effects.
#=============<< Beginning of future extension demonistration >> ========================================================
# You can setup more than one backup set for various purposes, such as disaster recovery.
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
#=============<< End of future extension demonistration >> ========================================================

以postgres用户执行初始化，初始化后会直接启动集群。
杀死postgres所有的进程:

ps -ef|grep "postgres"|grep -v grep|awk '{print $2}'|xargs kill -9

初始化集群

pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf init all

查看集群状态

pgxc_ctl monitor 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
Running: gtm master
Running: gtm proxy gtm_pxy1
Running: gtm proxy gtm_pxy2
Running: coordinator master coord1
Running: coordinator master coord2
Running: datanode master datanode1
Running: datanode master datanode2
Running: datanode master datanode3
Running: datanode master datanode4

修改/home/postgres/pgxl/data/pgxc/nodes/里所有的coordinator和datanode的pg_hba.conf

# users
host    all             all             192.168.1.1xx/32            trust
host    all             all             192.168.1.1xx/32            trust
#host    all             all             192.168.1.0/32            md5
host    all             all             0.0.0.0/0            md5

32是子网掩码，可能要按自己的网络去计算，可能是16 22 24

重新启动

 pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf stop all

 pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf start all