Postgres-XL数据库集群在RedHat/Fedora/Oracle/CentOS平台上的搭建

最新推荐文章于 2024-05-28 15:58:24 发布
最新推荐文章于 2024-05-28 15:58:24 发布
阅读量197 收藏
点赞数
分类专栏： DataBase数据库 文章标签： Postgres-XL集群
原文链接：www.csdn.net
版权

转载来源：https://blog.csdn.net/pg_hgdb/article/details/78911440

 

Postgresql-XL简单搭建

 

一、安装部署

 

  1. 1、集群规划

共三个节点，GTM放在了一节点上，建议是为GTM单独放一个服务器

pgxl1       192.168.102.98     coordinator1    datanode1       gtm 
pgxl2       192.168.102.99     coordinator2    datanode2
pgxl3       192.168.102.100    coordinator3    datanode3

 

  1. 2、关闭防火墙和selinux
[root@pgxl1 ~]# systemctl stop firewalld.service
[root@pgxl1 ~]# systemctl disable firewalld.service
Removed symlink/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink/etc/systemd/system/basic.target.wants/firewalld.service.
  •          关闭SELINUX
[root@pgxl1 ~]# sed -i"s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
[root@pgxl1 ~]# setenforce 0
[root@pgxl1 ~]# cat /etc/selinux/config
# This file controls the state of SELinux on thesystem.
# SELINUX= can take one of these three values:
#    enforcing - SELinux security policy is enforced.
#    permissive - SELinux prints warnings instead of enforcing.
#    disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
#    targeted - Targeted processes are protected,
#     minimum- Modification of targeted policy. Only selected processes are protected.
#     mls -Multi Level Security protection.
SELINUXTYPE=targeted
[root@pgxl1 ~]# setenforce 0
[root@pgxl1 ~]# getenforce
Permissive

 

  1. 3、关闭NetworkManager
[root@pgxl1 ~]# systemctl stop  NetworkManager.service
[root@pgxl1 ~]# systemctl disableNetworkManager.service
Removed symlink /etc/systemd/system/multi-user.target.wants/NetworkManager.service.
Removed symlink/etc/systemd/system/dbus-org.freedesktop.NetworkManager.service.
Removed symlink/etc/systemd/system/dbus-org.freedesktop.nm-dispatcher.service.

 

  1. 4、修改host文件
sed -i '2 a HOSTNAME=pgxl1' /etc/sysconfig/network
hostname pgxl1
[root@pgxl1 ~]# cat /etc/hosts
127.0.0.1  localhost localhost.localdomain localhost4 localhost4.localdomain4
::1        localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.102.98  pgxl1
192.168.102.99  pgxl2
192.168.102.100 pgxl3

 

  1. 5、检查时间差
[root@pgxl1 ~]# timedatectl
      Localtime: Tue 2017-11-14 13:29:34 CST
  Universaltime: Tue 2017-11-14 05:29:34 UTC
        RTCtime: Tue 2017-11-14 05:29:51
       Timezone: Asia/Shanghai (CST, +0800)
     NTPenabled: no
NTP synchronized: no
 RTC in localTZ: no
      DSTactive: n/a

 

  1. 6、安装依赖包
yum install wget readline readline-staticreadline-devel openjade zlib zlib-devel docbook-style-dsssl bzip2 gcc* zlib-staticopenssl openssl-devel pam-devel libxml2-devel libxslt-devel python-develtcl-devel flex bison perl-ExtUtils-Embed openldap-devel cmake -y

 

  1. 7、创建用户postgres
检测UID是否被占用
cat /etc/passwd | grep 600

创建用户
groupadd -g 600 postgres; useradd -u 600 -gpostgres postgres; echo postgres | passwd -f --stdin postgres

ssh免密码登录

每个节点都要做：
su - postgres
mkdir ~/.ssh
chmod 700 ~/.ssh

仅在GTM节点做此操作：
ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys


将刚生成的认证文件拷贝到pgxl1到pgxl3中，使得gtm节点可以免密码登录pgxl1~pgxl3的任意一个节点：
scp ~/.ssh/authorized_keys postgres@pgxl1:~/.ssh/
scp ~/.ssh/authorized_keys postgres@pgxl2:~/.ssh/
scp ~/.ssh/authorized_keys postgres@pgxl3:~/.ssh/
验证是否成功：
[postgres@pgxl1 ~]$ ssh pgxl2 date
Tue Dec 19 15:27:53 UTC 2017
[postgres@pgxl1 ~]$
[postgres@pgxl1 ~]$ ssh pgxl3 date
Tue Dec 19 15:28:09 UTC 2017
[postgres@pgxl1 ~]$ ssh pgxl1 date
Tue Dec 19 15:28:21 UTC 2017

 

  1. 8、安装postgres-xl
上传安装包 postgres-xl-9.5r1.6.tar.bz2

创建目录：
mkdir /home/postgres/pgxl/ -p
chown postgres:postgres /home/postgres/pgxl/ -R


tar -jxvf postgres-xl-9.5r1.6.tar.bz2
./configure --prefix=/home/postgres/pgxl/
make                            
结束标志：All of Postgres-XL successfully made. Ready toinstall.

make install                   
结束标志：Postgres-XL installation complete.


9、配置环境变量

[postgres@pgxl1 ~]$ cat .bashrc
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
exportPGHOME=/home/postgres/pgxl
exportPGUSER=postgres
exportLD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
exportPATH=$PGHOME/bin:$PATH

注意: 请不要使用.bash_profile文件来修改环境变量，后期初始化不生效。具体原因请搜索.bash_profile和.bashrc的区别

 

二、配置集群

 

  1. 1、修改配置参数
#!/bin/bash
#!/usr/bin/env bash

#user and path
pgxcInstallDir=$HOME/pgxl
pgxcOwner=postgres
pgxcUser=$pgxcOwner

tmpDir=/tmp                                     # temporary dir used in XC servers
localTmpDir=$tmpDir

#gtm
gtmName=gtm
gtmMasterServer=pgxl1
gtmMasterPort=6866
gtmMasterDir=$HOME/pgxl/gtm

#---- Configuration ---
gtmExtraConfig=none                     # Will be added gtm.conffor both Master and Slave (done at initilization only)
gtmMasterSpecificExtraConfig=none       # Will be added to Master's gtm.conf(done at initialization only)

gtmSlave=n

#gtm proxy
gtmProxy=n

#coordinator
coordMasterDir=$HOME/pgxl/nodes/coord
coordNames=(coord1 coord2 coord3)
coordPorts=(20004 20004 20004)
poolerPorts=(20010 20010 20010)
coordPgHbaEntries=(0.0.0.0/0)
coordMasterServers=(pgxl1 pgxl2 pgxl3)
coordMasterDirs=($coordMasterDir $coordMasterDir$coordMasterDir)
coordMaxWALsernder=0
coordMaxWALSenders=($coordMaxWALsernder$coordMaxWALsernder $coordMaxWALsernder)
coordSlave=n
coordSpecificExtraConfig=(none none none)
coordSpecificExtraPgHba=(none none none)

#datanode
datanodeNames=(datanode1 datanode2 datanode3)
datanodePorts=(20008 20008 20008)
datanodePoolerPorts=(20012 20012 20012)
datanodePgHbaEntries=(0.0.0.0/0)
datanodeMasterServers=(pgxl1 pgxl2 pgxl3)
datanodeMasterDir=$HOME/pgxl/nodes/dn_master
datanodeMasterDirs=($datanodeMasterDir$datanodeMasterDir $datanodeMasterDir)
datanodeMaxWalSender=0
datanodeMaxWALSenders=($datanodeMaxWalSender$datanodeMaxWalSender $datanodeMaxWalSender)
datanodeSlave=n
primaryDatanode=datanode1

配置过程一定要注意路径的正确性，提前创建好各个目录，由于是在pgxl1上做的gtm，所以$HOME/pgxl/gtm只在pgxl1节点创建即可，$HOME/pgxl/nodes/coord和$HOME/pgxl/nodes/dn_master则必须在三个节点都要创建。

 

  1. 2、传送配置文件
[postgres@pgxl1 pgxc_ctl]$ scp pgxc_ctl.confpgxl2:~/pgxc_ctl/pgxc_ctl.conf     
100% 1519     1.5KB/s   00:00   
[postgres@pgxl1 pgxc_ctl]$ scp pgxc_ctl.confpgxl3:~/pgxc_ctl/pgxc_ctl.conf    
100% 1519     1.5KB/s   00:00

 

  1. 3、初始化
mkdir /home/postgres/pgxl/gtm -p           （仅在gtm所在节点创建即可）
mkdir /home/postgres/pgxl/nodes/coord -p
mkdir /home/postgres/pgxl/nodes/dn_master -p
chown postgres:postgres /home/postgres/pgxl -R
[postgres@pgxl1 dn_master]$ pgxc_ctl -cpgxc_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
The files belonging to this GTM system will beowned by user "postgres".
This user must also own the server process.
fixing permissions on existing directory/home/postgres/pgxl/gtm ... ok
creating configuration files ... ok
creating control file ... ok
Success.
waiting for server to shut down.... done
server stopped
Done.

Start GTM master
server starting
Initialize all the coordinator masters.
Initialize coordinator master coord1.
Initialize coordinator master coord2.
Initialize coordinator master coord3.
The files belonging to this database system will beowned by user "postgres".
This user must also own the server process.
The database cluster will be initialized withlocale "en_US.UTF-8".
The default database encoding has accordingly beenset to "UTF8".
The default text search configuration will be setto "english".
Data page checksums are disabled.
fixing permissions on existing directory/home/postgres/pgxl/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/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" authenticationfor local connections
You can change this by editing pg_hba.conf or usingthe option -A, or
--auth-local and --auth-host, the next time you runinitdb.
Success.
The files belonging to this database system will beowned by user "postgres".
This user must also own the server process.
The database cluster will be initialized withlocale "en_US.UTF-8".
The default database encoding has accordingly beenset to "UTF8".
The default text search configuration will be setto "english".
Data page checksums are disabled.
fixing permissions on existing directory/home/postgres/pgxl/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/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" authenticationfor local connections
You can change this by editing pg_hba.conf or usingthe option -A, or
--auth-local and --auth-host, the next time you runinitdb.
Success.

The files belonging to this database system will beowned by user "postgres".
This user must also own the server process.
The database cluster will be initialized withlocale "en_US.UTF-8".
The default database encoding has accordingly beenset to "UTF8".
The default text search configuration will be setto "english".
Data page checksums are disabled.
fixing permissions on existing directory/home/postgres/pgxl/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/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" authenticationfor local connections
You can change this by editing pg_hba.conf or usingthe option -A, or
--auth-local and --auth-host, the next time you runinitdb.
Success.
Done.
Starting coordinator master.
Starting coordinator master coord1
Starting coordinator master coord2
Starting coordinator master coord3
LOG: database system was shut down at 2017-12-26 03:19:48 UTC
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG:  clustermonitor started
LOG: autovacuum launcher started
LOG: database system was shut down at 2017-12-26 03:19:47 UTC
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG:  clustermonitor started
LOG: database system was shut down at 2017-12-26 03:19:47 UTC
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG:  autovacuumlauncher started
LOG:  clustermonitor started
Done.

Initialize all the datanode masters.
Initialize the datanode master datanode1.
Initialize the datanode master datanode2.
Initialize the datanode master datanode3.
The files belonging to this database system will beowned by user "postgres".
This user must also own the server process.
The database cluster will be initialized withlocale "en_US.UTF-8".
The default database encoding has accordingly beenset to "UTF8".
The default text search configuration will be setto "english".
Data page checksums are disabled.
fixing permissions on existing directory/home/postgres/pgxl/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/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" authenticationfor local connections
You can change this by editing pg_hba.conf or usingthe option -A, or
--auth-local and --auth-host, the next time you runinitdb.
Success.
The files belonging to this database system will beowned by user "postgres".
This user must also own the server process.
The database cluster will be initialized withlocale "en_US.UTF-8".
The default database encoding has accordingly beenset to "UTF8".
The default text search configuration will be setto "english".
Data page checksums are disabled.
fixing permissions on existing directory/home/postgres/pgxl/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/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" authenticationfor local connections
You can change this by editing pg_hba.conf or usingthe option -A, or
--auth-local and --auth-host, the next time you runinitdb.
Success.
The files belonging to this database system will beowned by user "postgres".
This user must also own the server process.
The database cluster will be initialized withlocale "en_US.UTF-8".
The default database encoding has accordingly beenset to "UTF8".
The default text search configuration will be setto "english".
Data page checksums are disabled.
fixing permissions on existing directory/home/postgres/pgxl/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/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" authenticationfor local connections
You can change this by editing pg_hba.conf or usingthe option -A, or
--auth-local and --auth-host, the next time you runinitdb.
Success.
Done.
Starting all the datanode masters.
Starting datanode master datanode1.
Starting datanode master datanode2.
Starting datanode master datanode3.
LOG: redirecting log output to logging collector process
HINT:  Futurelog output will appear in directory "pg_log".
LOG: redirecting log output to logging collector process
HINT:  Futurelog output will appear in directory "pg_log".
LOG: redirecting log output to logging collector process
HINT:  Futurelog output will appear in directory "pg_log".
Done.
ALTER NODE coord1 WITH (HOST='pgxl1', PORT=20004);
ALTER NODE
CREATE NODE coord2 WITH (TYPE='coordinator',HOST='pgxl2', PORT=20004);
CREATE NODE
CREATE NODE coord3 WITH (TYPE='coordinator',HOST='pgxl3', PORT=20004);
CREATE NODE
CREATE NODE datanode1 WITH (TYPE='datanode',HOST='pgxl1', PORT=20008, PRIMARY, PREFERRED);
CREATE NODE
CREATE NODE datanode2 WITH (TYPE='datanode',HOST='pgxl2', PORT=20008);
CREATE NODE
CREATE NODE datanode3 WITH (TYPE='datanode',HOST='pgxl3', PORT=20008);
CREATE NODE
SELECT pgxc_pool_reload();
pgxc_pool_reload
------------------
 t
(1 row)

CREATE NODE coord1 WITH (TYPE='coordinator',HOST='pgxl1', PORT=20004);
CREATE NODE
ALTER NODE coord2 WITH (HOST='pgxl2', PORT=20004);
ALTER NODE
CREATE NODE coord3 WITH (TYPE='coordinator',HOST='pgxl3', PORT=20004);
CREATE NODE
CREATE NODE datanode1 WITH (TYPE='datanode',HOST='pgxl1', PORT=20008, PRIMARY);
CREATE NODE
CREATE NODE datanode2 WITH (TYPE='datanode',HOST='pgxl2', PORT=20008, PREFERRED);
CREATE NODE
CREATE NODE datanode3 WITH (TYPE='datanode',HOST='pgxl3', PORT=20008);
CREATE NODE
SELECT pgxc_pool_reload();
pgxc_pool_reload
------------------
 t
(1 row)

CREATE NODE coord1 WITH (TYPE='coordinator',HOST='pgxl1', PORT=20004);
CREATE NODE
CREATE NODE coord2 WITH (TYPE='coordinator',HOST='pgxl2', PORT=20004);
CREATE NODE
ALTER NODE coord3 WITH (HOST='pgxl3', PORT=20004);
ALTER NODE
CREATE NODE datanode1 WITH (TYPE='datanode',HOST='pgxl1', PORT=20008, PRIMARY);
CREATE NODE
CREATE NODE datanode2 WITH (TYPE='datanode',HOST='pgxl2', PORT=20008);
CREATE NODE
CREATE NODE datanode3 WITH (TYPE='datanode',HOST='pgxl3', PORT=20008, PREFERRED);
CREATE NODE
SELECT pgxc_pool_reload();
pgxc_pool_reload
------------------
 t
(1 row)
Done.

EXECUTE DIRECT ON (datanode1) 'CREATE NODE coord1WITH (TYPE=''coordinator'', HOST=''pgxl1'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'CREATE NODE coord2WITH (TYPE=''coordinator'', HOST=''pgxl2'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'CREATE NODE coord3WITH (TYPE=''coordinator'', HOST=''pgxl3'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'ALTER NODE datanode1WITH (TYPE=''datanode'', HOST=''pgxl1'', PORT=20008, PRIMARY, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'CREATE NODEdatanode2 WITH (TYPE=''datanode'', HOST=''pgxl2'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'CREATE NODEdatanode3 WITH (TYPE=''datanode'', HOST=''pgxl3'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'SELECTpgxc_pool_reload()';
pgxc_pool_reload
------------------
 t
(1 row)

EXECUTE DIRECT ON (datanode2) 'CREATE NODE coord1WITH (TYPE=''coordinator'', HOST=''pgxl1'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'CREATE NODE coord2WITH (TYPE=''coordinator'', HOST=''pgxl2'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'CREATE NODE coord3WITH (TYPE=''coordinator'', HOST=''pgxl3'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'CREATE NODEdatanode1 WITH (TYPE=''datanode'', HOST=''pgxl1'', PORT=20008, PRIMARY,PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'ALTER NODE datanode2WITH (TYPE=''datanode'', HOST=''pgxl2'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'CREATE NODEdatanode3 WITH (TYPE=''datanode'', HOST=''pgxl3'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'SELECTpgxc_pool_reload()';
pgxc_pool_reload
------------------
 t
(1 row)

EXECUTE DIRECT ON (datanode3) 'CREATE NODE coord1WITH (TYPE=''coordinator'', HOST=''pgxl1'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode3) 'CREATE NODE coord2WITH (TYPE=''coordinator'', HOST=''pgxl2'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode3) 'CREATE NODE coord3WITH (TYPE=''coordinator'', HOST=''pgxl3'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode3) 'CREATE NODEdatanode1 WITH (TYPE=''datanode'', HOST=''pgxl1'', PORT=20008, PRIMARY,PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode3) 'CREATE NODEdatanode2 WITH (TYPE=''datanode'', HOST=''pgxl2'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode3) 'ALTER NODE datanode3WITH (TYPE=''datanode'', HOST=''pgxl3'', PORT=20008, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode3) 'SELECTpgxc_pool_reload()';
 pgxc_pool_reload
------------------
 t
(1 row)
Done.

初始化完毕后集群自动启动

 

三、状态检查和数据检测


（1）

[postgres@pgxl1 pgxc_ctl]$ ps -ef | grep gtm

postgres  4171    1  0 03:19 ?        00:00:02 gtm -D /home/postgres/pgxl/gtm

postgres 7867  5829  0 08:05 pts/3    00:00:00 grep --color=auto gtm

[postgres@pgxl1 pgxc_ctl]$ ps -ef | grep postgres

postgres 4171     1  0 03:19 ?        00:00:02 gtm -D /home/postgres/pgxl/gtm

postgres 4629     1  0 03:19 ?        00:00:00 /home/postgres/pgxl/bin/postgres--coordinator -D /home/postgres/pgxl/nodes/coord -i

postgres 4631  4629  0 03:19 ?        00:00:00 postgres: pooler process  

postgres 4632  4629  0 03:19 ?        00:00:00 postgres: checkpointer process  

postgres  4633 4629  0 03:19 ?        00:00:00 postgres: writer process  

postgres 4634  4629  0 03:19 ?        00:00:00 postgres: wal writer process  

postgres 4635  4629  0 03:19 ?        00:00:00 postgres: autovacuum launcherprocess  

postgres 4636  4629  0 03:19 ?        00:00:00 postgres: stats collectorprocess  

postgres 4637  4629  0 03:19 ?        00:00:00 postgres: cluster monitorprocess  

postgres 5220     1  0 03:20 ?        00:00:00/home/postgres/pgxl/bin/postgres --datanode -D /home/postgres/pgxl/nodes/dn_master-i

postgres 5221  5220  0 03:20 ?        00:00:00 postgres: logger process  

postgres 5223  5220  0 03:20 ?        00:00:00 postgres: pooler process  

postgres 5224  5220  0 03:20 ?        00:00:00 postgres: checkpointer process  

postgres 5225  5220  0 03:20 ?        00:00:00 postgres: writer process  

postgres 5226  5220  0 03:20 ?        00:00:00 postgres: wal writer process  

postgres 5227  5220  0 03:20 ?        00:00:00 postgres: autovacuum launcherprocess  

postgres 5228  5220  0 03:20 ?        00:00:00 postgres: stats collectorprocess  

postgres 5229  5220  0 03:20 ?        00:00:00 postgres: cluster monitorprocess  

root     5828  5807  0 04:09 pts/3    00:00:00 su - postgres

postgres 5829  5828  0 04:09 pts/3    00:00:00 -bash

postgres 7868  5829  0 08:05 pts/3    00:00:00 ps -ef

postgres 7869  5829  0 08:05 pts/3    00:00:00 grep --color=auto postgres

 

（2）

[postgres@pgxl1 pgxc_ctl]$  gtm_ctl status -Z gtm -D/home/postgres/pgxl/gtm/

gtm_ctl: server is running (PID: 4171)

 "-D""/home/postgres/pgxl/gtm"

[postgres@pgxl1 pgxc_ctl]$ pg_ctl status -D/home/postgres/pgxl/nodes/dn_master

pg_ctl: server is running (PID: 5220)

/home/postgres/pgxl/bin/postgres"--datanode" "-D""/home/postgres/pgxl/nodes/dn_master" "-i"

[postgres@pgxl1 pgxc_ctl]$ pg_ctl status -D/home/postgres/pgxl/nodes/coord/

pg_ctl: server is running (PID: 4629)

/home/postgres/pgxl/bin/postgres"--coordinator" "-D" "/home/postgres/pgxl/nodes/coord""-i"

 

数据测试：

一节点：

[postgres@pgxl1 ~]$ psql -p 20004 -U postgres
psql (PGXL 9.5r1.6, based on PG 9.5.8 (Postgres-XL9.5r1.6))
Type "help" for help.

postgres=# select * from pgxc_node;
 node_name |node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id  
-----------+-----------+-----------+-----------+----------------+------------------+-------------
 coord1    | C        |     20004 | pgxl1     | f              | f                |  1885696643
 coord2    | C        |     20004 | pgxl2     | f              | f                | -1197102633
 coord3    | C        |     20004 | pgxl3     | f              | f                |  1638403545
 datanode1 |D         |     20008 | pgxl1     | t              | t                |   888802358
 datanode2 |D         |     20008 | pgxl2     | f              | f                |  -905831925
 datanode3 |D         |     20008 | pgxl3     | f              | f                | -1894792127
(6 rows)

postgres=# create table test (id int,name text);
CREATE TABLE
postgres=# insert into test values (1,'Tom');
INSERT 0 1
postgres=# select * from test;
 id | name
----+------
  1 | Tom
(1 row)

postgres=# l
postgres-# \l
                                 List ofdatabases
   Name    | Owner   | Encoding |   Collate  |    Ctype    |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8     |en_US.UTF-8 | en_US.UTF-8 |
 template0 |postgres | UTF8     | en_US.UTF-8 |en_US.UTF-8 | =c/postgres          +
          |          |          |             |             | postgres=CTc/postgres
 template1 |postgres | UTF8     | en_US.UTF-8 |en_US.UTF-8 | =c/postgres          +
          |          |          |             |             | postgres=CTc/postgres

(3 rows)

 

二节点：

postgres=# select * from test;
 id | name
----+------
  1 | Tom
(1 row)

postgres=# show port;
 port 
-------
 20004
(1 row)
postgres=# select * from pgxc_node;
 node_name |node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id  
-----------+-----------+-----------+-----------+----------------+------------------+-------------
 coord1    | C        |     20004 | pgxl1     | f              | f                |  1885696643
 coord2    | C        |     20004 | pgxl2     | f              | f                | -1197102633
 coord3    | C        |     20004 | pgxl3     | f              | f                |  1638403545
 datanode1 |D         |     20008 | pgxl1     | t              | f                |   888802358
 datanode2 |D         |     20008 | pgxl2     | f              | t                |  -905831925
 datanode3 |D         |     20008 | pgxl3     | f              | f                | -1894792127
(6 rows)

三节点：

postgres=# select * from pgxc_node;
 node_name |node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id  
-----------+-----------+-----------+-----------+----------------+------------------+-------------
 coord1    | C        |     20004 | pgxl1     | f              | f                |  1885696643
 coord2    | C        |     20004 | pgxl2     | f              | f                | -1197102633
 coord3    | C        |     20004 | pgxl3     | f              | f                |  1638403545
 datanode1 |D         |     20008 | pgxl1     | t              | f                |   888802358
 datanode2 |D         |     20008 | pgxl2     | f              | f                |  -905831925
 datanode3 |D         |     20008 | pgxl3     | f              | t                | -1894792127
(6 rows)
postgres=# select * from test;
 id | name
----+------
  1 | Tom
(1 row)

 

四、常用命令管理


一节点运行：

启动命令：
pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.confstart all

#关闭命令：
pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.confstop all


作者：PostgreSQL_HighGoDB   转载来源：https://blog.csdn.net/pg_hgdb/article/details/78911440 
 

星河_赵梓宇
关注
专栏目录
Postgresql-xl 调研
oblesslyy的专栏
12-27 2909
Postgresql-xl 调研 来历 这个项目的背后是一家叫做stormDB的公司。整个代买基于postgres-xc。开源版本应该是stormdb的一个分支。 In 2010, NTT's Open Source Software Center approached EnterpriseDB to build off of NTT OSSC's experience wit
linux安装pgsql11的教程,在CentOS 8/RHEL 8系统上安装PostgreSQL 11的方法
weixin_39953578的博客
05-07 499
本文介绍在CentOS 8/RHEL 8操作系统上安装PostgreSQL 11的方法，CentOS 8/RHEL 8上正式维护的模块化存储库的PostgreSQL版本为10和9.6，我们将添加PostgreSQL Yum Repository，它包含所有最新版本。其实PostgreSQL项目为最常见的发行版提供了所有支持版本的软件包存储库，支持所有Red Hat系列，如CentOS、Fedora...
Postgresql-XL简单搭建
HighGO
12-27 4592
一、安装部署 1、集群规划 共三个节点，GTM放在了一节点上，建议是为GTM单独放一个服务器 pgxl1 192.168.102.98 coordinator1 datanode1 gtm pgxl2 192.168.102.99 coordinator2 datanode2 pgxl3 192.168.1
Postgres-XL介绍
热门推荐
在路上的学习者
10-09 1万+
“沉默即赞同” —— 无名 一、什么是Postgres-XLXL的意思是：eXtensible Lattice，可以扩展的格子，即将PostgreSQL应用在多机器上的分布式数据库的形象化表达。Postgres-XL 是一个完全满足ACID的、开源的、可方便进行水平扩展的、多租户安全的、基于PostgreSQL的数据库解决方案。
POSTGRESQL Postgres-XL 了解一下
sql server的专栏
08-12 708
上次分析的POSTGRES -XC 的结构,实际上POSTGRES-X 系列一直在发展,POSTGRES除了 XC 还有XL 的高可用的结构.Postgres-XL 是一款Postgr...
Postgres-XL简介
HighGO
12-27 4738
Postgres-XL是什么？类似于”ORACLE RAC”的集群解决方案，对于Postgres-XL来说，一个实例实际上是由多个独立的PostgreSQL实例组成，它们分布在不同的物理（或虚拟）主机上协同工作，呈现给用户的是一个数据库实例的效果。每一个Coordinator都可以作为Postgres-XL系统的访问入口，负责处理客户端的连接及SQL命令、协调系统中的其他Coordinator和Da
centos7-postgresql12安装全过程
eryou_979的博客
06-07 337
一、准备工作 换源 wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo 清空yum&生成cache&update yum clean all && yum makecache && yum update -y 必要工具（看着加） yum install vim -y 一些环境 yum install centos-release-s
CentOS7.9+Postfix+Dovecot+PostgreSQL+Postfixadmin搭建一个邮件服务器
h2402108258的博客
04-01 4577
1.准备工作 介绍 这是配置邮件服务器的多部分教程。在最后，你将拥有一个能够为多个域提供标准电子邮件服务的邮件服务器。我将使用 Postfix、Dovecot、PostgreSQL 和 Postfixadmin。 在此之前，我将假设你拥有自己的服务器或 CentOS 7 最小的虚拟专用服务器 (VPS)。我还假设你有自己的域名。 开始使用 在进行设置postfix之前，我们需要一些做一些事情。更新你的yum。 yum update -y 设置主机名 重新设置你的主机名，你可以用"hostn
如何将 Langfuse 链接到自有 PostgreSQL 数据库并升级 PostgreSQL 版本
最新发布
_midnight的博客
05-28 1467
在本文中，我们将介绍如何将 Langfuse 应用程序链接到自有的 PostgreSQL 数据库，并升级 PostgreSQL 以支持jsonb类型。
podman加速器配置，harbor镜像仓库部署
-
12-16 3303
Docker加速器 registries加速器 [root@localhost ~]# cat /etc/redhat-release CentOS Stream release 8 [root@localhost ~]# cd /etc/containers/ [root@localhost containers]# ls certs.d policy.json registries.conf.d storage.conf oci registries.conf registr
Postgres-XL:-开源
05-03
Postgres-XL是基于PostgreSQL横向扩展群集，由于MPP并行性，它既可以处理OLTP密集型工作负载，也可以处理OLAP / BI类型的工作负载。 Postgres-XL是完全ACID，并支持整个集群范围的多版本并发控制（MVCC），以实现全局一致性和性能。 有诸如2ndQuadrant之类的商业公司，它们围绕Postgres-XL提供支持和咨询。 请访问http://2ndquadrant.com/en/resources/postgres-xl/了解更多详情。
Postgres-XL安装使用说明
01-23
Postgres-XL安装使用说明，在Centos 6.4下进行Postgres-XL安装
Postgresql-XL介绍
zg_hover的专栏
04-04 3551
概述 postgresql-xl类似于greenplum，都是MPP数据库，postgresql-xl一直都是开源的，但开源社区活跃度不是很高，gp在前两年也开源了，两者各有优缺点，在性能上有些方面不相上下，但gp要相对稳定一些。据称都支持海量数据，但这两种数据在实际使用过程中的数据量，都有一定的上限。本文介绍postgresql-xl的基本概念，在几年前对该数据库进行过调研，这里做个整理，分享出...
Postgres-XL 集群详细创建步骤
weixin_33701564的博客
11-08 294
最近公司业务需求，需要使用Postgres-XL 集群，关于这部分知识，网络资料不多。经过一段时间的查询，和各种弯路之后，终于完成安装。将详细步骤完整记录，以备查阅。也希望能帮到需要的人。 下面就开始吧： 主机列表和集群安装的角色分配 1 2 3 10.21.13.109GTM 10.21.13.67coordinator&a...
Postgres-XL 数据库
无锋剑
11-26 389
Postgres-XL是一个大规模并行数据库，建立在PostgreSQL 9.5之上，与PostgreSQL 9.5高度兼容。它的不同之处在于它同时支持商业智能工作负载和高容量事务写入和读取工作负载。 Postgres-XL是一款开源的PG集群软件，XL代表eXtensible Lattice，即可扩展的PG“格子”之意，以下简称PGXL。官方称其既适合写操作压力较大的OLTP应用，又适合读操作...
Postgres-XL集群的搭建
weixin_33762130的博客
10-30 127
一、集群规划建立5个虚拟机构成的集群，虚拟机的os均为centos6.5，依次命名为cnode1，cnode2，cnode3，cnode4，cnode5，其中cnode1为gtm，其余4个节点均为coordinator（程序直接用）和datanode（数据库9.2，一开始还以为是管理数据库的啥玩意儿呢害我又删掉原来部署好的9.4坑爹呀！）。hostnamei...
​Postgres-XL：基于PostgreSQL的开源可扩展数据库集群
jj_tyro的专栏
04-23 7221
​Postgres-XL：基于PostgreSQL的开源可扩展数据库集群                   最近这一年业界去“IOE”越叫越响，很多传统企业也把去“IOE”计划摆上了桌面。我老是想不明白这些非互联网企业（比如：银行）做这种事的动力何在？ 高大上的“自主可控”、“振兴民族科技”等空洞口号先不去管，真正的动力在哪里？ “安全”、“成本”、“互联网架构”..
【Postgres-XL】并行查询之谜
BIG BOSS的博客
11-17 582
单机的postgresql并发查询 开启了并发查询，查看查询计划 Gather (cost=1.65..1419.84 rows=325 width=452) (actual time=50.680..218.201 rows=36004 loops=1) Workers Planned: 2 Workers Launched: 2 -> Append (cost=1.65..1419.84 rows=137 width=452) (actual time=6.576..155.12
Postgres-XL 2022：灵活的SQL集群部署指南
Postgres-XL是一款基于PostgreSQL的高性能分布式SQL数据库集群，它旨在提供横向扩展能力和多模式的数据库应用场景支持，包括高并发OLTP（在线事务处理）和大规模并行处理（OLAP）的商业智能分析。XL的名称来源于其可...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值