Postgres-XL叢集搭建詳細建立步驟

Postgres-XL叢集搭建詳細建立步驟

最近公司業務需求，需要使用Postgres-XL 叢集，關於這部分知識，網路資料不多。經過一段時間的查詢，和各種彎路之後，終於完成安裝。將詳細步驟完整記錄，以備查閱。也希望能幫到需要的人。

下面就開始吧：

主機列表和叢集安裝的角色指派

10.21.13.109  GTM
10.21.13.67  coordinator&datanode
10.21.13.60  datanode

2.建立postgres使用者，這部分我使用ansible完成的使用者建立，以及相關軟體包的應用，節省勞動力（yum其實可以使用ansible內建的方式，所以我ansible寫的比較業餘）

ansible all -m shell -a  "useradd postgres"
ansible all -m shell -a 'echo "postgres:postgres" | chpasswd'
ansible all -m shell -a "yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl gcc"

3. 在每個節點上下載軟體，並解壓縮

ansible all -m shell -a "wget https://www.postgres-xl.org/downloads/postgres-xl-9.5r1.6.tar.bz2"
ansible all -m shell -a "mv /root/postgres-xl-9.5r1.6.tar.bz2 /tmp; chown postgres:postgres /tmp/postgres-xl-9.5r1.6.tar.bz2"
ansible all -m shell -a "tar  -jxvf /tmp/postgres-xl-9.5r1.6.tar.bz2 -C /home/postgres"

4. 全部節點都建立pgxc路徑，用來存放postgres檔案

1 ansible all -m shell -a "su - postgres -c 'mkdir -p /home/postgres/pgxc/nodes/'"

5. 修改每個節點的.bashrc檔案，注意：修改/etc/profile是不生效的。

su - postgres
vi .bashrc

添加如下內容：

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

使之生效：

source ~/.bashrc

6. 關閉防火牆或者放開相應的連接埠，這裡我直接關閉了每個虛擬機器的防火牆，並且重啟它們：

service iptables stop
chkconfig iptables off  --重啟後生效

7. 關閉SELinux：

查看SELinux狀態：

/usr/sbin/sestatus -v    ##如果SELinux status參數為enabled即為開啟狀態
SELinux status:                enabled

臨時關閉（不用重啟機器）：

setenforce 0      ##設定SELinux 成為permissive模式
                          ##setenforce 1 設定SELinux 成為enforcing模式

永久關閉：

修改/etc/selinux/config 檔案
將SELINUX=enforcing改為SELINUX=disabled

8. 到每一個節點執行下面的軟體安裝步驟，這個過程注意，如果中間有報錯，需要解決到。例如確實gcc包之類的，不然後面的編譯也不會成功的。

cd postgres-xl
./configure --prefix=/home/postgres/pgxl/
make
make install
cd contrib/
make
make instal

9. 執行pgxc_ctl命令來組建組態叢集的模板檔案：

./pgxc_ctl              ---會提示Error說沒有設定檔，忽略即可

PGXC prepare            ---執行該命令將會產生一份設定檔範本

10. 根據模板修改對應的主機名稱，連接埠等資訊，內容如下：

vi ~/pgxc_ctl/pgxc_ctl.conf
pgxcInstallDir=$HOME/pgxc
pgxcOwner=$USER
pgxcUser=$pgxcOwner
tmpDir=/tmp
localTmpDir=$tmpDir
configBackup=n
configBackupHost=pgxc-linker
configBackupDir=$HOME/pgxc
configBackupFile=pgxc_ctl.bak
gtmName=gtm
gtmMasterServer=10.21.13.109
gtmMasterPort=20001
gtmMasterDir=$HOME/pgxc/nodes/gtm
gtmExtraConfig=none
gtmMasterSpecificExtraConfig=none
gtmSlave=n
gtmProxyDir=$HOME/pgxc/nodes/gtm_pxy
gtmProxy=y
gtmProxyNames=(gtm_pxy1 gtm_pxy2)
gtmProxyServers=(10.21.13.67 10.21.13.60)
gtmProxyPorts=(20001 20001)
gtmProxyDirs=($gtmProxyDir $gtmProxyDir)
gtmPxyExtraConfig=none
gtmPxySpecificExtraConfig=(none none)
coordMasterDir=$HOME/pgxc/nodes/coord
coordSlaveDir=$HOME/pgxc/nodes/coord_slave
coordArchLogDir=$HOME/pgxc/nodes/coord_archlog
coordNames=(coord1)
coordPorts=(20004)
poolerPorts=(20010)
coordPgHbaEntries=(10.21.13.0/25)  <<<<<<<<<<<<<<<<<<<<<<<<<非常重要
coordMasterServers=(10.21.13.67)
coordMasterDirs=($coordMasterDir)
coordMaxWALsernder=5
coordMaxWALSenders=($coordMaxWALsernder)
coordSlave=n              <<<<<<<<<<<<<<<<沒使用slave
coordSlaveSync=y
coordSlaveServers=(10.21.13.60 10.21.13.67)
coordSlavePorts=(20004 20005 20004 20005)
coordSlavePoolerPorts=(20010 20011 20010 20011)
coordSlaveDirs=($coordSlaveDir $coordSlaveDir)
coordArchLogDirs=($coordArchLogDir $coordArchLogDir)
coordExtraConfig=coordExtraConfig
cat > $coordExtraConfig <<EOF
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
listen_addresses = '*'
max_connections = 100
EOF
coordSpecificExtraConfig=(none none)
coordExtraPgHba=none
coordSpecificExtraPgHba=(none none)
coordAdditionalSlaves=n
coordAdditionalSlaveSet=(cad1)
cad1_Sync=n
cad1_Servers=(10.21.13.67 10.21.13.60)
cad1_dir=$HOME/pgxc/nodes/coord_slave_cad1
cad1_Dirs=($cad1_dir $cad1_dir)
cad1_ArchLogDir=$HOME/pgxc/nodes/coord_archlog_cad1
cad1_ArchLogDirs=($cad1_ArchLogDir $cad1_ArchLogDir)
datanodeMasterDir=$HOME/pgxc/nodes/dn_master
datanodeSlaveDir=$HOME/pgxc/nodes/dn_slave
datanodeArchLogDir=$HOME/pgxc/nodes/datanode_archlog
primaryDatanode=datanode1
datanodeNames=(datanode1 datanode2)
datanodePorts=(20008 20008) # Master ports
datanodePoolerPorts=(20012 20012)
datanodePgHbaEntries=(10.21.13.0/25)  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<非常重要
datanodeMasterServers=(10.21.13.67 10.21.13.60)
datanodeMasterDirs=($datanodeMasterDir $datanodeMasterDir)
datanodeMaxWalSender=5
datanodeMaxWALSenders=($datanodeMaxWalSender $datanodeMaxWalSender )
datanodeSlave=n                <<<<<<<<<<<<<<<<沒使用slave
datanodeSlaveServers=(10.21.13.60 10.21.13.67)
datanodeSlavePorts=(20008 20008)
datanodeSlavePoolerPorts=(20012 20012)
datanodeSlaveSync=y
datanodeSlaveDirs=($datanodeSlaveDir $datanodeSlaveDir)
datanodeArchLogDirs=( $datanodeArchLogDir $datanodeArchLogDir )
datanodeExtraConfig=none
datanodeSpecificExtraConfig=(none none )
datanodeExtraPgHba=none
datanodeSpecificExtraPgHba=(none none)
datanodeAdditionalSlaves=n
walArchive=n      <<<<<<<<<<<<<<<<不開wal歸檔
walArchiveSet=(war1 war2)
war1_source=(master)
wal1_source=(slave)
wal1_source=(additiona_coordinator_slave_set additional_datanode_slave_set)
war1_host=node10
war1_backupdir=$HOME/pgxc/backup_war1
wal2_source=(master)
war2_host=node11
war2_backupdir=$HOME/pgxc/backup_war2

11. 初始話命令，如果前面沒有任何錯誤，這裡就是最後一步了，PGXL會把全部的部署工作完成的。

pgxc_ctl -c pgxc_ctl.conf init all

完整輸出如下：

[postgres@ip-10-21-13-109 pgxc_ctl]$ pgxc_ctl -c 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/pgxc/nodes/gtm) exists and not empty. Skip GTM initilialization
1:3768231680:2017-11-09 19:35:02.983 CST -FATAL:  lock file "gtm.pid" already exists
2:3768231680:2017-11-09 19:35:02.983 CST -HINT:  Is another GTM (PID 14635) running in data directory "/home/postgres/pgxc/nodes/gtm"?
LOCATION:  CreateLockFile, main.c:2099
waiting for server to shut down... done
server stopped
Done.
Start GTM master
server starting
Initialize all the gtm proxies.
Initializing gtm proxy gtm_pxy1.
Initializing gtm proxy gtm_pxy2.
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/pgxc/nodes/gtm_pxy ... ok
creating configuration files ... ok
Success.
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/pgxc/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.
ERROR: target coordinator master coord1 is running now.  Skip initilialization.
Done.
Starting coordinator master.
Starting coordinator master coord1
ERROR: target coordinator master coord1 is already running now.  Skip initialization.
Done.
Initialize all the datanode masters.
Initialize the datanode master datanode1.
Initialize the datanode master datanode2.
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 /home/postgres/pgxc/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/pgxc/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 "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 /home/postgres/pgxc/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/pgxc/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.
Done.
Starting all the datanode masters.
Starting datanode master datanode1.
Starting datanode master datanode2.
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.
ALTER NODE coord1 WITH (HOST='10.21.13.67', PORT=20004);
ALTER NODE
CREATE NODE datanode1 WITH (TYPE='datanode', HOST='10.21.13.67', PORT=20008, PRIMARY, PREFERRED);
ERROR:  PGXC Node datanode1: object already defined
CREATE NODE datanode2 WITH (TYPE='datanode', HOST='10.21.13.60', PORT=20008);
ERROR:  PGXC Node datanode2: object already defined
SELECT pgxc_pool_reload();
 pgxc_pool_reload
------------------
 t
(1 row)
Done.
EXECUTE DIRECT ON (datanode1) 'CREATE NODE coord1 WITH (TYPE=''coordinator'', HOST=''10.21.13.67'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'ALTER NODE datanode1 WITH (TYPE=''datanode'', HOST=''10.21.13.67'', PORT=20008, PRIMARY, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'CREATE NODE datanode2 WITH (TYPE=''datanode'', HOST=''10.21.13.60'', PORT=20008)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode1) 'SELECT pgxc_pool_reload()';
 pgxc_pool_reload
------------------
 t
(1 row)
EXECUTE DIRECT ON (datanode2) 'CREATE NODE coord1 WITH (TYPE=''coordinator'', HOST=''10.21.13.67'', PORT=20004)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'CREATE NODE datanode1 WITH (TYPE=''datanode'', HOST=''10.21.13.67'', PORT=20008, PRIMARY, PREFERRED)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'ALTER NODE datanode2 WITH (TYPE=''datanode'', HOST=''10.21.13.60'', PORT=20008)';
EXECUTE DIRECT
EXECUTE DIRECT ON (datanode2) 'SELECT pgxc_pool_reload()';
 pgxc_pool_reload
------------------
 t
(1 row)
Done.

12. 驗證可用性

在coordinator節點上，本地串連方式

psql -p 20004

遠端使用者串連方式

psql -h 10.21.13.67 -p 20004 -U postgres

postgres=# select * from pgxc_node;
node_name | node_type | node_port |  node_host  | nodeis_primary | nodeis_preferred |  node_id
-----------+-----------+-----------+-------------+----------------+------------------+------------
datanode1 | D        |    20008 | 10.21.13.67 | t              | t                |  888802358
datanode2 | D        |    20008 | 10.21.13.60 | f              | f                | -905831925
coord1    | C        |    20004 | 10.21.13.67 | f              | f                | 1885696643

13. 建表測試

postgres=#  create table test(id integer,name varchar(10));

postgres=#  insert into test(id,name) values(1,'test');

postgres=#  insert into test(id,name) values(2,'test');

查詢結果

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

問題匯總：

1. 如果登陸到資料庫中，執行下面語句，發現只有coord的資訊，說明並沒有建立成功，

postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |  node_id
-----------+-----------+-----------+-----------+----------------+------------------+------------
coord3    | C        |      20004 | localhost | f              | f          | 1638403545
(1 row)

而問題是出在coordPgHbaEntries=(10.21.13.0/25)和datanodePgHbaEntries=(10.21.13.0/25)的存取權限控制上，一定要和實際的子網路遮罩匹配,不然各個datanode節點和coord節點間是無法正常通訊的。

由於沒有注意各個細節，讓這個問題困擾我一周時間。一直在找為什麼datanode之間不能相互識別的原因，其實就是很小的一個點。

[postgres@ip-10-21-13-109 pgxc_ctl]$ ifconfig
eth0      Link encap:Ethernet  HWaddr 02:57:E7:56:4C:70
inet addr:10.21.13.109  Bcast:10.21.13.127  Mask:255.255.255.128<<<<<<10.21.13.0/25
inet6 addr: fe80::57:e7ff:fe56:4c70/64 Scope:Link
UP BROADCAST RUNNING MULTICAST  MTU:9001  Metric:1
RX packets:29500 errors:0 dropped:0 overruns:0 frame:0
TX packets:28538 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:3113614 (2.9 MiB)  TX bytes:4653853 (4.4 MiB)
Interrupt:160

2. 如果遇到建表，建庫失敗，也是遇到上面的存取控制許可權問題。在部署完成之後，這個參數是在/home/postgres/pgxc/nodes/dn_master/pg_hba.conf和/home/postgres/pgxc/nodes/coord/pg_hba.conf中設定的。但是重新init，會覆蓋掉這些檔案的。

postgres=# create database test;
ERROR:  Failed to get pooled connections
HINT:  This may happen because one or more nodes are currently unreachable, either because of node or network failure.
Its also possible that the target node may have hit the connection limit or the pooler is configured with low connections.
Please check if all nodes are running fine and also review max_connections and max_pool_size configuration parameters

3. 有很多人的安裝方式，是最後通過手動執行下面命令，添加coord和datanode，其實前面的pgxc_ctl.conf配置正確的話，指令碼都是自動幫你完成的，沒有必要人工幹預

create node coord1 with (type=coordinator,host='c6702', port=20004);
create node coord2 with (type=coordinator,host='c6703', port=20005);
alter node coord3 with (type=coordinator,host='c6704', port=20004);
create node datanode1 with (type=datanode, host='c6702',port=20008);
create node datanode2 with (type=datanode, host='c6703',port=20008);
alter  node datanode3 with (type=datanode, host='c6704',port=20008);

4. 第8步，make PGXL軟體過程中，一定要仔細查看輸出，有錯誤一定要糾正。不然後續會發現，有很多命令軟體，都沒有安裝成功。

5.如果你在遠程登陸postgres sql遇到如下問題

[root@c6703 ~]# psql -h 10.21.13.67 -p 20004 -U postgres
psql: FATAL:  no pg_hba.conf entry for host "172.16.32.116", user "postgres", database "postgres"

修改/home/postgres/pgxc/nodes/dn_master/pg_hba.conf和/home/postgres/pgxc/nodes/coord/pg_hba.conf中的存取控制權允許對應的IP有存取權限，就可以登陸了。注意，要修改全部的coordinator和datanode的pg_hba.conf設定檔才行。然後通過postgres使用者執行reload命令，使設定檔生效。或者重啟使配置生效。

[root@c6703 ~]# psql -h 10.21.13.67 -p 20004 -U test
postgres=# SELECT pg_reload_conf();
[root@c6703 ~]# psql -h 10.21.13.67 -p 20004 -U test
Password for user test:
psql (PGXL 9.5r1.6, based on PG 9.5.8 (Postgres-XL 9.5r1.6))
Type "help" for help.

下面提供的是第10和11步的文字檔，可以下載修改並使用。可以到幫客之家資來源站點下載：

------------------------------------------分割線------------------------------------------

免費在 http://linux.bkjia.com/

使用者名稱與密碼都是www.bkjia.com

具體下載目錄在 /2018年資料/3月/4日/Postgres-XL叢集搭建詳細建立步驟/

下載方法見

------------------------------------------分割線------------------------------------------

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-03/151184.htm