POSTGRES-XL Cluster Construction

POSTGRES-XL is a fully acid-compliant, open-source, easy to scale, multi-tenant security. Support share-nothing; Support massive data parallel processing-mpp (massively Parallel processing). is not very similar to Greenplum.

Host Assignment

3 host, install centos6.2. Of course, you can prepare more than one server; This depends on the scenario (the GTM is a separate host; The exception is that each machine is best equipped with one coordinator and one datanode, so you don't have to worry about load balancing and reduce network traffic);

192.168.1.201 (GTM)192.168.1.202 (Coordinator,datanode)192.168.1.203 (Coordinator,datanode)

POSTGRES-XL Download and install

--http://www.postgres-xl.org/download/Download package:--postgres-xl-v9.2-src.tar.gzinstall the dependent packages on each node;Yum install-y Flex Bison readline-devel zlib-devel openjade docbook-style-dsssl gccInstalling POSTGRES-XLexecute at each node# TAR-ZXVF postgres-xl-v9.2-src.tar.gz# CD POSTGRES-XL1#./configure--prefix=/usr/local/pgxl-9.2--installation directory, can not carry. # make# make installin the GTM host, i.e. 192.168.1.201 executes the installation tool Pgxc_ctl# CD Contrib/pgxc_ctl# make# make install

Create user

# useradd Postgres# passwd Postgres 

Configuring Environment variables

Export Pguser=postgresExport pghome=/usr/local/pgxl-9.2Export ld_library_path= $PGHOME/lib: $LD _library_pathExport path= $HOME/bin: $PGHOME/bin: $PATHAdd the above content to the. bashrc file in the Postgres home directory, and source. BASHRC to make it work!

Configure SSH password-free login for pgxc_ctl;

$ ssh-keygen-t RSA$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys$ chmod ~/.ssh/authorized_keys$ SCP ~/.ssh/authorized_keys [email protected]:~/.ssh/$ SCP ~/.ssh/authorized_keys [email protected]:~/.ssh/if the other server does not have a. SSH directory, you can manually create the following mkdir. Ssh;chmod. SSH

Configure the cluster and initialize the cluster with Pgxc_ctl;

in the GTM node server operation; Create a new/home/postgres/pgxc_ctl/pgxc_ctl.conf file for the 192.168.1.201 server; add the following

#user and Path Pgxcowner=Postgrespgxcuser=$pgxcOwnerpgxcInstallDir=/usr/local/pgxl-9.2#gtm and Gtmproxygtmmasterdir=$HOME/pgxc/nodes/gtmgtmmasterport=6666Gtmmasterserver=192.168.1.201Gtmslave=N#gtm Proxygtmproxy=N#coordinatorcoordmasterdir=$HOME/pgxc/nodes/coordcoordnames=(coord1 coord2) coordports= (5432 5432) Poolerports= (6667 6668) Coordpghbaentries= (192.168.1.0/32) Coordmasterservers= (192.168.1.202 192.168.1.203) Coordmasterdirs=($coordMasterDir/coord1 $coordMasterDir/coord2) Coordmaxwalsernder=0coordmaxwalsenders=($coordMaxWALsernder $coordMaxWALsernder) Coordslave=Ncoordspecificextraconfig=(none None) Coordspecificextrapghba=(none None) #datanodedatanodeNames=(datanode1 datanode2) datanodeports= (15432 15432) Datanodepoolerports= (6669 6670) Datanodepghbaentries= (192.168.1.0/32) Datanodemasterservers= (192.168.1.202 192.168.1.203) Datanodemasterdir=$HOME/pgxc/nodes/datanodedatanodemasterdirs=($datanodeMasterDir/datanode1 $datanodeMasterDir/datanode2) Datanodemaxwalsender=0datanodemaxwalsenders=($datanodeMaxWalSender $datanodeMaxWalSender) Datanodeslave=Nprimarydatanode=datanode1

initializing a clusterpgxc_ctl-c/home/postgres/pgxc_ctl/pgxc_ctl.conf Init AllStart/close clusterpgxc_ctl-c/home/postgres/pgxc_ctl/pgxc_ctl.conf Start Allpgxc_ctl-c/home/postgres/pgxc_ctl/pgxc_ctl.conf Stop AllConfiguring cluster Informationoperation on Coordinator,datanode nodein the server 192.168.1.202,192.168.1.203 operation into the Psql directory psql-p 5432-d postgres, psql-p 15432-d postgres other node do the corresponding operation; Local node can only be modified, and all the others will create node. select * from Pgxc_node;Create node Coord1 with (type=coordinator,host= ' 192.168.1.202 ', port=5432);Create node Coord2 with (type=coordinator,host= ' 192.168.1.203 ', port=5432);alter node coord1 with (type=coordinator,host= ' 192.168.1.202 ', port=5432);alter node COORD2 with (type=coordinator,host= ' 192.168.1.203 ', port=5432);Create node Datanode1 with (Type=datanode, host= ' 192.168.1.202 ', port=15432,primary=true);Create node Datanode2 with (Type=datanode, host= ' 192.168.1.203 ', port=15432);alter node datanode1 with (Type=datanode, host= ' 192.168.1.202 ', port=15432,primary=true);alter node Datanode2 with (Type=datanode, host= ' 192.168.1.203 ', port=15432);select Pgxc_pool_reload ();test environment OK? the above steps are done; start a test Operation

[[email protected] datanode1]$ psql-P5432Postgrespsql (PGXL9.2.0, based onPg9.2.4(Postgres-Xl9.2.0) ) Type "Help" forHelp.postgres=#CreateRole Lottu Nosuperuser Login encrypted password'li0924'; CREATERolepostgres=#CreateTablespace Tbs_lottu owner Postgres Location'/home/postgres/data/lottu'; Error:directory "/Home/Postgres/Data/Lottu "does notExistpostgres=#CreateTablespace Tbs_lottu owner Postgres Location'/home/postgres/data/lottu'; CREATETablespacepostgres=#Create DatabaseLottu owner Lottu Template TEMPLATE0 encoding'UTF8'tablespace Tbs_lottu;CREATE DATABASEPostgres=# \c Lottu Lottulottu= Create TableLOTTU01 (IDint Primary Key, namevarchar(Ten)); NOTICE:CREATE TABLE / PRIMARY KEYWouldCreateImplicitIndex"Lottu01_pkey" for Table"Lottu01"CREATE TABLELottu= Insert  intoLottu01SelectGenerate_series (1, +),'Lottu'||Generate_series (1, +);INSERT 0  +[[email protected] datanode1]$ psql-P5432Lottu lottupsql (PGXL9.2.0, based onPg9.2.4(Postgres-Xl9.2.0) ) Type "Help" forHelp.lottu= Select Count(*) fromlottu01;Count -------   +(1row) Lottu=\q[[email protected] datanode1]$ psql-P15432Lottu lottupsql (PGXL9.2.0, based onPg9.2.4(Postgres-Xl9.2.0) ) Type "Help" forHelp.lottu= Select Count(*) fromlottu01;Count -------   492(1row)[[email protected] ~]$ psql-P15432Lottu lottupsql (PGXL9.2.0, based onPg9.2.4(Postgres-Xl9.2.0) ) Type "Help" forHelp.lottu= Select Count(*) fromlottu01;Count -------   508(1Row

"FAQ"1. When configuring cluster information appears"Warning:can not connect to gtm:no the route to hostWarning:xid is invalid.Error:gtm ERROR, could not obtain snapshot XID = 0 "Analysis Reason: see the hint "No route to host" is related to the network routing, or can be related to the firewallSolution: Configure the firewall or turn off the firewall2. "Error:failed to get pooled connections" appears when creating roleAnalysis Reason:1. First I see if the pool process exists;2. Check whether the configuration of Pgxc_pool is OK;3. Perform psql-h 192.168.1.203-p 5432 discovery Timeout; This is related to the pg_hba.conf file. Solution:Add host all 192.168.1.0/32 Trust32 of these are related to the netmask of the host settingsReference--https://ruihaijiang.wordpress.com/2015/09/17/postgres-xl-installation-example-on-linux/

POSTGRES-XL Cluster Construction