PostgreSQL学习随笔15 Postgres-XL搭建实验

CP的PostgreSQL厨房 2022-05-05
2835
Postgres-XL安装部署参考
相关概念
Global Transaction Monitor (GTM) 
全局事务管理器，确保群集范围内的事务一致性。GTM负责发放事务ID和快照作为其多版本并发控制的一部分。集群可选地配置一个备用GTM，以改进可用性。此外，可以在协调器间配置代理GTM，可用于改善可扩展性，减少GTM的通信量。 

GTM Standby 
GTM的备节点，在pgxl中，GTM控制所有的全局事务分配，如果出现问题，就会导致整个集群不可用，为了增加可用性，增加该备用节点。当GTM出现问题时，GTM Standby可以升级为GTM，保证集群正常工作。 

GTM-Proxy 
GTM需要与所有的Coordinators通信，为了降低压力，可以在每个Coordinator机器上部署一个GTM-Proxy。 

Coordinator 
协调员管理用户会话，并与GTM和数据节点进行交互。协调员解析，并计划查询，并给语句中的每一个组件发送下一个序列化的全局性计划。为节省机器，通常此服务和数据节点部署在一起。 

Data Node 
数据节点是数据实际存储的地方。数据的分布可以由DBA来配置。为了提高可用性，可以配置数据节点的热备以便进行故障转移准备。

gtm负责ACID，保证分布式数据库全局事务一致性。虽然数据节点是分布的，但在主节点操作增删改查事务时，就如同只操作一个数据库一样简单。 
Coordinator是调度功能，将操作指令发送到各个数据节点。 
datanodes是数据节点，分布式存储数据。
节点规划
节点  IP  作用
pgxl1  10.110.9.35  gtm
pgxl2  10.110.9.29  gtm-proxy,coordinator,datanode
pgxl3  10.110.9.31  gtm-proxy,coordinator,datanode
pgxl4  10.110.9.36  gtm-proxy,coordinator,datanode
安装步骤
4个节点每个节点都安装和配置
hostnamectl set-hostname pgxl1
hostnamectl set-hostname pgxl2
hostnamectl set-hostname pgxl3
hostnamectl set-hostname pgxl4
vi etc/hosts
10.110.9.35 pgxl1
10.110.9.29 pgxl2
10.110.9.31 pgxl3
10.110.9.36 pgxl4
---------------------------------------------------------------------------
关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
SELINUX
vi etc/selinux/config
将SELINUX=enforcing 改为SELINUX=disabled
安装依赖包
# yum clean all
# yum makecache
# yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl gcc 

创建用户，设置环境变量，创建安装目录 
# useradd postgres
# passwd postgres
添加以下内容到环境变量，".bash_profile"及".bashrc"两个文件都要添加(每个节点都执行)
export PGHOME=/postgresxl
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
export PGUSER=postgres
export PGXC_CTL_HOME=/postgresxl/bin
export PATH
source .bash_profile
source .bashrc
配置环境变量(每个节点都执行)
su - root
vi etc/environment
PATH="/postgresxl/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games"
source etc/environment

(每个节点都执行)
# mkdir -p postgresxl
# chown -R postgres:postgres postgresxl
注：环境变量设置不对会报以下错误
Initialize GTM master
ERROR: target directory (/postgresxl/data/gtm) exists and not empty. Skip GTM initilialization
bash: gtm: command not found
bash: gtm_ctl: command not found
Done.
Start GTM master
bash: gtm_ctl: command not found
Initialize GTM slave
bash: initgtm: command not found
上传XL安装包并解压（四台机器都需要安装XL）
编译安装pgxl 
su – root
cd opt/soft_file
tar -zxf postgres-xl-10r1.1.tar.gz
cd postgres-xl-10r1.1
./configure --prefix=/postgresxl
make&&make install

$ cd contrib
$ make
$ make install
配置主节点可以无密码访问备节点 
su - postgres
ssh-keygen
ssh-copy-id postgres@pgxl1           
ssh-copy-id postgres@pgxl2          
ssh-copy-id postgres@pgxl3  
ssh-copy-id postgres@pgxl4
$ ssh pgxl1 date
$ ssh pgxl2 date
$ ssh pgxl3 date
$ ssh pgxl2 date
$ ssh pgxl4 date
生成配置文件，配置文件生成的位置在$PGXC_CTL_HOME目录下。 
su – root
chown -R postgres:postgres postgresxl
[postgres@pgxl1 bin]$ pgxc_ctl prepare
/bin/bash
Installing pgxc_ctl_bash script as postgresxl/bin/pgxc_ctl_bash.
Installing pgxc_ctl_bash script as postgresxl/bin/pgxc_ctl_bash.
Reading configuration using postgresxl/bin/pgxc_ctl_bash --home postgresxl/bin --configuration postgresxl/bin/pgxc_ctl.conf
Finished reading configuration.
   ******** PGXC_CTL START ***************
Current directory: postgresxl/bin

------------------------参数文件内容-------------------------------------
修改配置文件内容，如下： 
vi postgresxl/bin/pgxc_ctl.conf
#环境变量
pgxcInstallDir=/postgresxl
pgxlDATA=$PGHOME/data
pgxcOwner=postgres
pgxcUser=postgres
tmpDir=/tmp
localTmpDir=$tmpDir

#---- GTM配置信息
gtmName=pgxl1_gtm
gtmMasterServer=pgxl1
gtmMasterPort=6666
gtmMasterDir=$pgxlDATA/gtm
#---- Configuration
gtmExtraConfig=none
gtmMasterSpecificExtraConfig=none

#---- GTM Slave配置信息
gtmSlave=y
gtmSlaveName=pgxl1_gtm_slave
gtmSlaveServer=pgxl1
gtmSlavePort=6667
gtmSlaveDir=$pgxlDATA/gtm_slave
#---- Configuration
gtmSlaveSpecificExtraConfig=none

#---- GTM Proxy配置信息，最好每个数据节点配置一个，gtmProxyNames及后面部分要写三个
gtmProxyDir=$pgxlDATA/gtm_proxy
gtmProxy=y
gtmProxyNames=(gtm_proxy1 gtm_proxy2 gtm_proxy3)
gtmProxyServers=(pgxl2 pgxl3 pgxl4)
gtmProxyPorts=(6668 6668 6668)
gtmProxyDirs=($gtmProxyDir $gtmProxyDir $gtmProxyDir)
#---- Configuration
gtmPxyExtraConfig=none
gtmPxySpecificExtraConfig=(none none)

#---- Coordinators配置信息，coordPorts端口为之后访问数据库的端口
coordMasterDir=$pgxlDATA/coord
coordSlaveDir=$pgxlDATA/coord_slave
coordArchLogDir=$pgxlDATA/coord_archlog
#---- Overall
coordNames=(coord2 coord3 coord4)
coordPorts=(20014 20015 20016)
poolerPorts=(20022 20023 20024)
#配置数据库的访问控制
coordPgHbaEntries=(0.0.0.0/0)

#----主数据节点信息
coordMasterServers=(pgxl2 pgxl3 pgxl4)
coordMasterDirs=($coordMasterDir $coordMasterDir $coordMasterDir)
coordMaxWALsernder=10
coordMaxWALSenders=($coordMaxWALsernder $coordMaxWALsernder $coordMaxWALsernder)
#---- 备数据节点信息，此处配置为相互备份，主备节点的数据同步采用流复制
coordSlave=y
coordSlaveSync=y
coordSlaveServers=(pgxl4 pgxl3 pgxl2)   
coordSlavePorts=(20006 20005 20004)            # Master ports
coordSlavePoolerPorts=(20012 20011 20010)            # Master pooler ports
coordSlaveDirs=($coordSlaveDir $coordSlaveDir $coordSlaveDir)
coordArchLogDirs=($coordArchLogDir $coordArchLogDir $coordArchLogDir)
#---- Configuration files
coordExtraConfig=coordExtraConfig
#配置pg的参数
cat > $coordExtraConfig <<EOF
#================================================
# Added to all the coordinator postgresql.conf
# Original: $coordExtraConfig
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
listen_addresses = '*'
max_connections = 100
EOF

coordSpecificExtraConfig=(none none none)
coordExtraPgHba=none
coordSpecificExtraPgHba=(none none none)

#---- Datanodes
datanodeMasterDir=$pgxlDATA/dn_master
datanodeSlaveDir=$pgxlDATA/dn_slave
datanodeArchLogDir=$pgxlDATA/dn_archlog
#---- Overall
primaryDatanode=pgxl2
datanodeNames=(datapgxl1 datapgxl2 datapgxl3)
datanodePorts=(20008 20009 20010)   
datanodePoolerPorts=(20012 20013 20014)
datanodePgHbaEntries=(0.0.0.0/0)
#---- Master
datanodeMasterServers=(pgxl2 pgxl3 pgxl4)       
datanodeMasterDirs=($datanodeMasterDir $datanodeMasterDir $datanodeMasterDir)
datanodeMaxWalSender=10
datanodeMaxWALSenders=($datanodeMaxWalSender $datanodeMaxWalSender $datanodeMaxWalSender)
#---- 是否使用数据备节点，并配置节点信息
datanodeSlave=y
datanodeSlaveServers=(pgxl4 pgxl3 pgxl2)
datanodeSlavePorts=(20210 20209 20208)
datanodeSlavePoolerPorts=(20012 20013 20014)
datanodeSlaveSync=y   
datanodeSlaveDirs=($datanodeSlaveDir $datanodeSlaveDir $datanodeSlaveDir)
datanodeArchLogDirs=( $datanodeArchLogDir $datanodeArchLogDir $datanodeArchLogDir)
# ---- Configuration files
datanodeExtraConfig=none
datanodeSpecificExtraConfig=(none none none)
datanodeExtraPgHba=none
datanodeSpecificExtraPgHba=(none none none)
#----- Additional Slaves
datanodeAdditionalSlaves=n
#---- WAL archives
walArchive=y
------------------------参数文件内容-------------------------------------

配置文件修改完成后，放在$PGXC_CTL_HOME目录下，然后初始化集群，过程如下：
[postgres@pgxl1 postgresxl]$ pgxc_ctl
/bin/bash
Installing pgxc_ctl_bash script as postgresxl/bin/pgxc_ctl_bash.
Installing pgxc_ctl_bash script as postgresxl/bin/pgxc_ctl_bash.
Reading configuration using postgresxl/bin/pgxc_ctl_bash --home postgresxl/bin --configuration postgresxl/bin/pgxc_ctl.conf
Finished reading configuration.
   ******** PGXC_CTL START ***************
Current directory: postgresxl/bin

PGXC init all
Initialize GTM master
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 postgresxl/data/gtm ... ok
creating configuration files ... ok
creating control file ... ok
Success.
Done.
Start GTM master
server starting
Initialize GTM slave
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 postgresxl/data/gtm_slave ... 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_proxy1.
Initializing gtm proxy gtm_proxy2.
Initializing gtm proxy gtm_proxy3.
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 postgresxl/data/gtm_proxy ... 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 postgresxl/data/gtm_proxy ... 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 postgresxl/data/gtm_proxy ... ok
creating configuration files ... ok

Success.
Done.
Starting all the gtm proxies.
Starting gtm proxy gtm_proxy1.
Starting gtm proxy gtm_proxy2.
Starting gtm proxy gtm_proxy3.
server starting
server starting
server starting
Done.
Initialize all the coordinator masters.
Initialize coordinator master coord2.
Initialize coordinator master coord3.
Initialize coordinator master coord4.
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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory postgresxl/data/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
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... 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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory postgresxl/data/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
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... 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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.


fixing permissions on existing directory /postgresxl/data/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
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... 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 coord2
Starting coordinator master coord3
Starting coordinator master coord4
2019-03-21 03:37:37.689 EDT [3173] LOG:  listening on IPv4 address "0.0.0.0", port 20014
2019-03-21 03:37:37.689 EDT [3173] LOG:  listening on IPv6 address "::", port 20014
2019-03-21 03:37:37.696 EDT [3173] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20014"
2019-03-21 03:37:37.708 EDT [3173] LOG:  redirecting log output to logging collector process
2019-03-21 03:37:37.708 EDT [3173] HINT:  Future log output will appear in directory "pg_log".
2019-03-21 03:37:55.560 EDT [3145] LOG:  listening on IPv4 address "0.0.0.0", port 20015
2019-03-21 03:37:55.560 EDT [3145] LOG:  listening on IPv6 address "::", port 20015
2019-03-21 03:37:55.564 EDT [3145] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20015"
2019-03-21 03:37:55.572 EDT [3145] LOG:  redirecting log output to logging collector process
2019-03-21 03:37:55.572 EDT [3145] HINT:  Future log output will appear in directory "pg_log".
2019-03-21 03:37:38.208 EDT [3162] LOG:  listening on IPv4 address "0.0.0.0", port 20016
2019-03-21 03:37:38.208 EDT [3162] LOG:  listening on IPv6 address "::", port 20016
2019-03-21 03:37:38.211 EDT [3162] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20016"
2019-03-21 03:37:38.219 EDT [3162] LOG:  redirecting log output to logging collector process
2019-03-21 03:37:38.219 EDT [3162] HINT:  Future log output will appear in directory "pg_log".
Done.
Initialize all the coordinator slaves.
Initialize the coordinator slave coord2.
Initialize the coordinator slave coord3.
Initialize the coordinator slave coord4.
Done.
Starting all the coordinator slaves.
Starting coordinator slave coord2.
Starting coordinator slave coord3.
Starting coordinator slave coord4.
2019-03-21 03:37:46.567 EDT [3437] LOG:  listening on IPv4 address "0.0.0.0", port 20006
2019-03-21 03:37:46.567 EDT [3437] LOG:  listening on IPv6 address "::", port 20006
2019-03-21 03:37:46.576 EDT [3437] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20006"
2019-03-21 03:37:46.586 EDT [3437] LOG:  redirecting log output to logging collector process
2019-03-21 03:37:46.586 EDT [3437] HINT:  Future log output will appear in directory "pg_log".
2019-03-21 03:38:03.893 EDT [3420] LOG:  listening on IPv4 address "0.0.0.0", port 20005
2019-03-21 03:38:03.893 EDT [3420] LOG:  listening on IPv6 address "::", port 20005
2019-03-21 03:38:03.902 EDT [3420] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20005"
2019-03-21 03:38:03.912 EDT [3420] LOG:  redirecting log output to logging collector process
2019-03-21 03:38:03.912 EDT [3420] HINT:  Future log output will appear in directory "pg_log".
2019-03-21 03:37:46.039 EDT [3448] LOG:  listening on IPv4 address "0.0.0.0", port 20004
2019-03-21 03:37:46.039 EDT [3448] LOG:  listening on IPv6 address "::", port 20004
2019-03-21 03:37:46.048 EDT [3448] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20004"
2019-03-21 03:37:46.057 EDT [3448] LOG:  redirecting log output to logging collector process
2019-03-21 03:37:46.057 EDT [3448] HINT:  Future log output will appear in directory "pg_log".
Done
Initialize all the datanode masters.
Initialize the datanode master datapgxl1.
Initialize the datanode master datapgxl2.
Initialize the datanode master datapgxl3.
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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".


Data page checksums are disabled.


fixing permissions on existing directory /postgresxl/data/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
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... 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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".


Data page checksums are disabled.


fixing permissions on existing directory /postgresxl/data/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
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... 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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".


Data page checksums are disabled.


fixing permissions on existing directory /postgresxl/data/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
running bootstrap script ... ok
performing post-bootstrap initialization ... creating cluster information ... 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 all the datanode masters.
Starting datanode master datapgxl1.
Starting datanode master datapgxl2.
Starting datanode master datapgxl3.
2019-03-21 03:37:54.407 EDT [3816] LOG:  listening on IPv4 address "0.0.0.0", port 20008
2019-03-21 03:37:54.407 EDT [3816] LOG:  listening on IPv6 address "::", port 20008
2019-03-21 03:37:54.411 EDT [3816] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20008"
2019-03-21 03:37:54.421 EDT [3816] LOG:  redirecting log output to logging collector process
2019-03-21 03:37:54.421 EDT [3816] HINT:  Future log output will appear in directory "pg_log".
2019-03-21 03:38:12.234 EDT [3788] LOG:  listening on IPv4 address "0.0.0.0", port 20009
2019-03-21 03:38:12.234 EDT [3788] LOG:  listening on IPv6 address "::", port 20009
2019-03-21 03:38:12.238 EDT [3788] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20009"
2019-03-21 03:38:12.248 EDT [3788] LOG:  redirecting log output to logging collector process
2019-03-21 03:38:12.248 EDT [3788] HINT:  Future log output will appear in directory "pg_log".
2019-03-21 03:37:54.915 EDT [3805] LOG:  listening on IPv4 address "0.0.0.0", port 20010
2019-03-21 03:37:54.915 EDT [3805] LOG:  listening on IPv6 address "::", port 20010
2019-03-21 03:37:54.920 EDT [3805] LOG:  listening on Unix socket "/tmp/.s.PGSQL.20010"
2019-03-21 03:37:54.929 EDT [3805] LOG:  redirecting log output to logging collector process
2019-03-21 03:37:54.929 EDT [3805] HINT:  Future log output will appear in directory "pg_log".
Done.
ALTER NODE coord2 WITH (HOST='pgxl2', PORT=20014);
ALTER NODE
CREATE NODE coord3 WITH (TYPE='coordinator', HOST='pgxl3', PORT=20015);
CREATE NODE
CREATE NODE coord4 WITH (TYPE='coordinator', HOST='pgxl4', PORT=20016);
CREATE NODE
CREATE NODE datapgxl1 WITH (TYPE='datanode', HOST='pgxl2', PORT=20008, PREFERRED);
CREATE NODE
CREATE NODE datapgxl2 WITH (TYPE='datanode', HOST='pgxl3', PORT=20009);
CREATE NODE
CREATE NODE datapgxl3 WITH (TYPE='datanode', HOST='pgxl4', PORT=20010);
CREATE NODE
SELECT pgxc_pool_reload();
 pgxc_pool_reload
------------------
 t
(1 row)


CREATE NODE coord2 WITH (TYPE='coordinator', HOST='pgxl2', PORT=20014);
CREATE NODE
ALTER NODE coord3 WITH (HOST='pgxl3', PORT=20015);
ALTER NODE
CREATE NODE coord4 WITH (TYPE='coordinator', HOST='pgxl4', PORT=20016);
CREATE NODE
CREATE NODE datapgxl1 WITH (TYPE='datanode', HOST='pgxl2', PORT=20008);
CREATE NODE
CREATE NODE datapgxl2 WITH (TYPE='datanode', HOST='pgxl3', PORT=20009, PREFERRED);
CREATE NODE
CREATE NODE datapgxl3 WITH (TYPE='datanode', HOST='pgxl4', PORT=20010);
CREATE NODE
SELECT pgxc_pool_reload();
 pgxc_pool_reload
------------------
 t
(1 row)


CREATE NODE coord2 WITH (TYPE='coordinator', HOST='pgxl2', PORT=20014);
CREATE NODE
CREATE NODE coord3 WITH (TYPE='coordinator', HOST='pgxl3', PORT=20015);
CREATE NODE
ALTER NODE coord4 WITH (HOST='pgxl4', PORT=20016);
ALTER NODE
CREATE NODE datapgxl1 WITH (TYPE='datanode', HOST='pgxl2', PORT=20008);
CREATE NODE
CREATE NODE datapgxl2 WITH (TYPE='datanode', HOST='pgxl3', PORT=20009);
CREATE NODE
CREATE NODE datapgxl3 WITH (TYPE='datanode', HOST='pgxl4', PORT=20010, PREFERRED);
CREATE NODE
SELECT pgxc_pool_reload();
 pgxc_pool_reload
------------------
 t
(1 row)


Done.
EXECUTE DIRECT ON (datapgxl1) 'CREATE NODE coord2 WITH (TYPE=''coordinator'', HOST=''pgxl2'', PORT=20014)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl1) 'CREATE NODE coord3 WITH (TYPE=''coordinator'', HOST=''pgxl3'', PORT=20015)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl1) 'CREATE NODE coord4 WITH (TYPE=''coordinator'', HOST=''pgxl4'', PORT=20016)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl1) 'ALTER NODE datapgxl1 WITH (TYPE=''datanode'', HOST=''pgxl2'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl1) 'CREATE NODE datapgxl2 WITH (TYPE=''datanode'', HOST=''pgxl3'', PORT=20009, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl1) 'CREATE NODE datapgxl3 WITH (TYPE=''datanode'', HOST=''pgxl4'', PORT=20010, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl1) 'SELECT pgxc_pool_reload()';
 pgxc_pool_reload
------------------
 t
(1 row)


EXECUTE DIRECT ON (datapgxl2) 'CREATE NODE coord2 WITH (TYPE=''coordinator'', HOST=''pgxl2'', PORT=20014)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl2) 'CREATE NODE coord3 WITH (TYPE=''coordinator'', HOST=''pgxl3'', PORT=20015)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl2) 'CREATE NODE coord4 WITH (TYPE=''coordinator'', HOST=''pgxl4'', PORT=20016)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl2) 'CREATE NODE datapgxl1 WITH (TYPE=''datanode'', HOST=''pgxl2'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl2) 'ALTER NODE datapgxl2 WITH (TYPE=''datanode'', HOST=''pgxl3'', PORT=20009, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl2) 'CREATE NODE datapgxl3 WITH (TYPE=''datanode'', HOST=''pgxl4'', PORT=20010, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl2) 'SELECT pgxc_pool_reload()';
 pgxc_pool_reload
------------------
 t
(1 row)


EXECUTE DIRECT ON (datapgxl3) 'CREATE NODE coord2 WITH (TYPE=''coordinator'', HOST=''pgxl2'', PORT=20014)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl3) 'CREATE NODE coord3 WITH (TYPE=''coordinator'', HOST=''pgxl3'', PORT=20015)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl3) 'CREATE NODE coord4 WITH (TYPE=''coordinator'', HOST=''pgxl4'', PORT=20016)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl3) 'CREATE NODE datapgxl1 WITH (TYPE=''datanode'', HOST=''pgxl2'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl3) 'CREATE NODE datapgxl2 WITH (TYPE=''datanode'', HOST=''pgxl3'', PORT=20009, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl3) 'ALTER NODE datapgxl3 WITH (TYPE=''datanode'', HOST=''pgxl4'', PORT=20010, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datapgxl3) 'SELECT pgxc_pool_reload()';
 pgxc_pool_reload
------------------
 t
(1 row)
Done.
---------------------------------初始化集群结束----------------------------
查看集群信息
 [root@pgxl3 ~]# su - postgres
Last login: Thu Mar 21 03:15:20 EDT 2019 on pts/0
[postgres@pgxl3 ~]$ psql -p 20015
psql (PGXL 10r1.1, based on PG 10.6 (Postgres-XL 10r1.1))
Type "help" for help.


postgres=# select * from pgxc_node;
 node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id  
-----------+-----------+-----------+-----------+----------------+------------------+-------------
 coord2    | C         |     20014 | pgxl2     | f              | f                | -1197102633
 coord3    | C         |     20015 | pgxl3     | f              | f                |  1638403545
 coord4    | C         |     20016 | pgxl4     | f              | f                |  -890157977
 datapgxl1 | D         |     20008 | pgxl2     | f              | f                |  -345433902
 datapgxl2 | D         |     20009 | pgxl3     | f              | t                |  -573655949
 datapgxl3 | D         |     20010 | pgxl4     | f              | f                | -1070702387
(6 rows)
coord2、coord3、coord4表示coordinator，分别在pgxl2~pgxl4上，需要通过其连接数据库，连接端口为对应的node_port端口。


创建表，连接任意一个coordinator，创建一个表，可以看到数据均匀分布在各个节点
 
[postgres@pgxl3 ~]$ psql -p 20015
psql (PGXL 10r1.1, based on PG 10.6 (Postgres-XL 10r1.1))
Type "help" for help.


--以下创建表默认分片到全部数据节点，可参考以下链接DISTRIBUTE BY关键词
--https://www.postgres-xl.org/documentation/sql-createtable.html
postgres=# create table test(c0 varchar(100),c1 varchar(100));
CREATE TABLE
postgres=# insert into test(c0,c1) SELECT id::varchar,md5(id::varchar) FROM generate_series(1,10000) as id;
INSERT 0 10000
postgres=# select xc_node_id,count(1) from test group by xc_node_id;
 xc_node_id  | count
-------------+-------
 -1070702387 |  3220
  -573655949 |  3429
  -345433902 |  3351
(3 rows)


复制
postgresql
文章转载自CP的PostgreSQL厨房，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
PostgreSQL学习随笔15 Postgres-XL搭建实验

评论
