最近公司業(yè)務(wù)需求，需要使用Postgres-XL 集群，關(guān)于這部分知識，網(wǎng)絡(luò)資料不多。經(jīng)過一段時間的查詢，和各種彎路之后，終于完成安裝。將詳細步驟完整記錄，以備查閱。也希望能幫到需要的人。
創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),交口企業(yè)網(wǎng)站建設(shè),交口品牌網(wǎng)站建設(shè),網(wǎng)站定制,交口網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,交口網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學習、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。
下面就開始吧：
主機列表和集群安裝的角色分配
10.21.13.109 GTM 10.21.13.67 coordinator&datanode 10.21.13.60 datanode
2.創(chuàng)建postgres用戶，這部分我使用ansible完成的用戶創(chuàng)建，以及相關(guān)軟件包的應用，節(jié)省勞動力（yum其實可以使用ansible自帶的方式，所以我ansible寫的比較業(yè)余）
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. 在每個節(jié)點上下載軟件，并解壓縮
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. 全部節(jié)點都創(chuàng)建pgxc路徑，用來存放postgres文件
ansible all -m shell -a "su - postgres -c 'mkdir -p /home/postgres/pgxc/nodes/'"
5. 修改每個節(jié)點的.bashrc文件，注意：修改/etc/profile是不生效的。
su - postgres vi .bashrc
添加如下內(nèi)容：
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. 關(guān)閉防火墻或者放開相應的端口，這里我直接關(guān)閉了每個虛擬機的防火墻，并且重啟它們：
service iptables stop chkconfig iptables off --重啟后生效
7. 關(guān)閉SELinux：
查看SELinux狀態(tài)：
/usr/sbin/sestatus -v ##如果SELinux status參數(shù)為enabled即為開啟狀態(tài) SELinux status: enabled
臨時關(guān)閉（不用重啟機器）：
setenforce 0 ##設(shè)置SELinux 成為permissive模式 ##setenforce 1 設(shè)置SELinux 成為enforcing模式
永久關(guān)閉：
修改/etc/selinux/config 文件 將SELINUX=enforcing改為SELINUX=disabled
8. 到每一個節(jié)點執(zhí)行下面的軟件安裝步驟，這個過程注意，如果中間有報錯，需要解決到。例如確實gcc包之類的，不然后面的編譯也不會成功的。
cd postgres-xl ./configure --prefix=/home/postgres/pgxl/ make make install cd contrib/ make make instal
9. 執(zhí)行pgxc_ctl命令來生成配置集群的模板文件：
./pgxc_ctl ---會提示Error說沒有配置文件，忽略即可
PGXC prepare ---執(zhí)行該命令將會生成一份配置文件模板
10. 根據(jù)模板修改對應的主機名，端口等信息，內(nèi)容如下：
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 <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節(jié)點上，本地連接方式
psql -p 20004遠程用戶連接方式
psql -h 10.21.13.67 -p 20004 -U postgrespostgres=# 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 | 188569664313. 建表測試
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');
查詢結(jié)果
postgres=# select * from test; id | name ----+------ 1 | test 2 | test (2 rows)問題匯總：
1. 如果登陸到數(shù)據(jù)庫中，執(zhí)行下面語句，發(fā)現(xiàn)只有coord的信息，說明并沒有創(chuàng)建成功，
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)的訪問權(quán)限控制上，一定要和實際的子網(wǎng)掩碼匹配,不然各個datanode節(jié)點和coord節(jié)點間是無法正常通訊的。
由于沒有注意各個細節(jié)，讓這個問題困擾我一周時間。一直在找為什么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:1602. 如果遇到建表，建庫失敗，也是遇到上面的訪問控制權(quán)限問題。在部署完成之后，這個參數(shù)是在/home/postgres/pgxc/nodes/dn_master/pg_hba.conf和/home/postgres/pgxc/nodes/coord/pg_hba.conf中設(shè)置的。但是重新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 parameters3. 有很多人的安裝方式，是最后通過手動執(zhí)行下面命令，添加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軟件過程中，一定要仔細查看輸出，有錯誤一定要糾正。不然后續(xù)會發(fā)現(xiàn)，有很多命令軟件，都沒有安裝成功。
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中的訪問控制權(quán)允許對應的IP有訪問權(quán)限，就可以登陸了。注意，要修改全部的coordinator和datanode的pg_hba.conf配置文件才行。然后通過postgres用戶執(zhí)行reload命令，使配置文件生效?；蛘咧貑⑹古渲蒙А?/p>
[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.下面三篇是我主要參考的blog，幫助很大，但是每一個都有“坑”。所以，只有自己動手實驗過，才會記住，你掉過和跳過的“坑”
http://blog.csdn.net/yeruby/article/details/48996027
https://www.cnblogs.com/songyuejie/archive/2015/08/23/4752124.html
http://blog.csdn.net/freeland1/article/details/52346669?ref=myread
附件是第10和11步的文本文件，可以下載修改并使用。
附件：http://down.51cto.com/data/2368732
本文題目：Postgres-XL集群詳細創(chuàng)建步驟
網(wǎng)站URL：http://weahome.cn/article/jijgeo.html
重慶分公司
重慶分公司
