Postgre2015大象会，大家都很关注PostgreSQL的集群，目前，开发人员已经转向Postgres-X2，近期根据自己和同事部署xl的过程部署了一下Postgres-X2。本次部署试验是利用pgxc_ctl部署的，更加灵活的部署集群。
1、整体概括：
一共四个节点，一个gtm，一个coordinator，两个datanode。
a. GTM节点 IP:192.168.238.129 nodename:gtm port:6666 b.coordinator IP:192.168.238.130 nodename:coord1 port:5432 pooler_port:6668 c.datanode1 IP:192.168.238.131 nodename:datanode1 port:15432 pooler_port:6669 d.datanode2 IP:192.168.238.132 nodename:datanode2 port:15432 pooler_port:6669
2、准备工作(不特别指明，四个节点做相同的操作)：
a.编译安装pgx2，同时编译contrib。
./configure --prefix=/opt/pgx2make; make installcd contribmake; make install
b.建立用户postgres，将安装目录属主赋给postgres。
chown -R postgres:postgres pgx2
c.配置ssh连接
[postgres@localhost~]# ssh-keygenGenerating public/private rsa key pair.Enter file in which to save the key (/root/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /root/.ssh/id_rsa.Your public key has been saved in /root/.ssh/id_rsa.pub.The key fingerprint is:ea:c9:48:2d:dc:0d:ab:9b:3d:99:cb:bd:db:3b:ba:fa root@localhost.localdomainThe key's randomart image is:+--[ RSA 2048]----+| || || || || . S || . o = || + =o. || . X+o .. || =.O=E=oo |+-----------------+[postgres@localhost~]#
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keysvi /etc/hosts192.168.238.129 localhost.localdomain--分发密钥，gtm节点向其他节点分发scp ~/.ssh/authorized_keys postgres@192.168.238.130scp ~/.ssh/authorized_keys postgres@192.168.238.131scp ~/.ssh/authorized_keys postgres@192.168.238.132
d.配置环境变量
[postgres@localhost ~]$ cat .bashrc # .bashrc# Source global definitionsif [ -f /etc/bashrc ]; then . /etc/bashrcfiexport PGHOME=/opt/pgx2/export PGUSER=postgresexport LD_LIBRARY_PATH=$PGHOME/libexport PATH=$PGHOME/bin:$PATH# User specific aliases and functions[postgres@localhost ~]$
建议：
在ssh连接时效率很慢，可以用ssh -v进行检测，这里就不做说明了。修改/etc/ssh/sshd_config中的GSSAPIAuthentication和UseDNS为no，然后/etc/init.d/sshd restart就可提高ssh连接速度。
为了方便起见我将所有节点的iptables关闭，大家可自行配置。
3、部署节点
a.配置pgxc_ctl.conf
--在/home/postgres/pgxc_ctl下--conf内容cat pgxc_ctl.conf#user and pathpgxcOwner=postgrespgxcUser=$pgxcOwnerpgxcInstallDir=/opt/pgx2#gtm and gtmproxygtmMasterDir=$HOME/pgxc/nodes/gtmgtmMasterPort=6666gtmMasterServer=192.168.238.129gtmSlave=n#gtmproxygtmProxy=ngtmProxyDir=$HOME/pgxc/nodes/coordgtmProxyNames=(gtm_pxy1)gtmProxyServers=(192.168.238.130)gtmProxyPorts=(20001)gtmProxyDirs=($gtmProxyDir/gtm_pxy1)gtmPxyExtraConfig=(none)gtmPxySpecificExtraConfig=(none)#coordinatorcoordMasterDir=$HOME/pgxc/nodes/coordcoordNames=(coord1)coordPorts=(5432)poolerPorts=(6668)coordPgHbaEntries=(192.168.238.0/24)coordMasterServers=(192.168.238.130)coordMasterDirs=($coordMasterDir/coord1)coordMaxWALsernder=0coordMaxWALSenders=($coordMaxWALsernder)coordSlave=ncoordSpecificExtraConfig=(none)coordSpecificExtraPgHba=(none)#datanodedatanodeNames=(datanode1 datanode2)datanodePorts=(15432 15432)datanodePoolerPorts=(6669 6669)datanodePgHbaEntries=(192.168.238.0/24)datanodeMasterServers=(192.168.238.131 192.168.238.132)datanodeMasterDir=$HOME/pgxc/nodes/dn_masterdatanodeMasterDirs=($datanodeMasterDir/datanode1 $datanodeMasterDir/datanode2)datanodeMaxWALsernder=0datanodeMaxWALSenders=($datanodeMaxWALsernder $datanodeMaxWALsernder)datanodeSlave=nprimaryDatanode=datanode1datanodeSpecificExtraConfig=(none none)datanodeSpecificExtraPgHba=(none none)
b.利用pgxc_ctl部署节点
pgxc_ctl init all[postgres@localhost ~]$ pgxc_ctl init allInstalling 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.confFinished to read configuration. ******** PGXC_CTL START ***************Current directory: /home/postgres/pgxc_ctlInitialize GTM masterThe 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 ... okcreating configuration files ... okSuccess. You can now start the GTM server using: gtm -D /home/postgres/pgxc/nodes/gtmor gtm_ctl -Z gtm -D /home/postgres/pgxc/nodes/gtm -l logfile startwaiting for server to shut down... doneserver stoppedDone.Start GTM mastergtm_ctl: PID file "/home/postgres/pgxc/nodes/gtm/gtm.pid" does not existIs server running?server startingInitialize all the coordinator masters.Initialize coordinator master coord1.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/coord/coord1 ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBcreating configuration files ... okcreating template1 database in /home/postgres/pgxc/nodes/coord/coord1/base/1 ... okinitializing pg_authid ... okinitializing dependencies ... okcreating system views ... okcreating cluster information ... okloading system objects' descriptions ... okcreating collations ... okcreating conversions ... okcreating dictionaries ... oksetting privileges on built-in objects ... okcreating information schema ... okloading PL/pgSQL server-side language ... okvacuuming database template1 ... okcopying template1 to template0 ... okcopying template1 to postgres ... oksyncing data to disk ... okfreezing database template0 ... okfreezing database template1 ... okfreezing database postgres ... okWARNING: enabling "trust" authentication for local connectionsYou 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. You can now start the database server of the Postgres-XC coordinator using: postgres --coordinator -D /home/postgres/pgxc/nodes/coord/coord1or pg_ctl start -D /home/postgres/pgxc/nodes/coord/coord1 -Z coordinator -l logfile You can now start the database server of the Postgres-XC datanode using: postgres --datanode -D /home/postgres/pgxc/nodes/coord/coord1or pg_ctl start -D /home/postgres/pgxc/nodes/coord/coord1 -Z datanode -l logfileDone.Starting coordinator master.Starting coordinator master coord1Done.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/datanode1 ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBcreating configuration files ... okcreating template1 database in /home/postgres/pgxc/nodes/dn_master/datanode1/base/1 ... okinitializing pg_authid ... okinitializing dependencies ... okcreating system views ... okcreating cluster information ... okloading system objects' descriptions ... okcreating collations ... okcreating conversions ... okcreating dictionaries ... oksetting privileges on built-in objects ... okcreating information schema ... okloading PL/pgSQL server-side language ... okvacuuming database template1 ... okcopying template1 to template0 ... okcopying template1 to postgres ... oksyncing data to disk ... okfreezing database template0 ... okfreezing database template1 ... okfreezing database postgres ... okWARNING: enabling "trust" authentication for local connectionsYou 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. You can now start the database server of the Postgres-XC coordinator using: postgres --coordinator -D /home/postgres/pgxc/nodes/dn_master/datanode1or pg_ctl start -D /home/postgres/pgxc/nodes/dn_master/datanode1 -Z coordinator -l logfile You can now start the database server of the Postgres-XC datanode using: postgres --datanode -D /home/postgres/pgxc/nodes/dn_master/datanode1or pg_ctl start -D /home/postgres/pgxc/nodes/dn_master/datanode1 -Z datanode -l logfileThe 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/datanode2 ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBcreating configuration files ... okcreating template1 database in /home/postgres/pgxc/nodes/dn_master/datanode2/base/1 ... okinitializing pg_authid ... okinitializing dependencies ... okcreating system views ... okcreating cluster information ... okloading system objects' descriptions ... okcreating collations ... okcreating conversions ... okcreating dictionaries ... oksetting privileges on built-in objects ... okcreating information schema ... okloading PL/pgSQL server-side language ... okvacuuming database template1 ... okcopying template1 to template0 ... okcopying template1 to postgres ... oksyncing data to disk ... okfreezing database template0 ... okfreezing database template1 ... okfreezing database postgres ... okWARNING: enabling "trust" authentication for local connectionsYou 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. You can now start the database server of the Postgres-XC coordinator using: postgres --coordinator -D /home/postgres/pgxc/nodes/dn_master/datanode2or pg_ctl start -D /home/postgres/pgxc/nodes/dn_master/datanode2 -Z coordinator -l logfile You can now start the database server of the Postgres-XC datanode using: postgres --datanode -D /home/postgres/pgxc/nodes/dn_master/datanode2or pg_ctl start -D /home/postgres/pgxc/nodes/dn_master/datanode2 -Z datanode -l logfileDone.Starting all the datanode masters.Starting datanode master datanode1.Starting datanode master datanode2.Done.ALTER NODE coord1 WITH (HOST='192.168.238.130', PORT=5432);ALTER NODECREATE NODE datanode1 WITH (TYPE='datanode', HOST='192.168.238.131', PORT=15432, PRIMARY);CREATE NODECREATE NODE datanode2 WITH (TYPE='datanode', HOST='192.168.238.132', PORT=15432);CREATE NODEDone.
4、运行演示
[postgres@localhost ~]$ psql -h 192.168.238.130 -p 5432 -d postgres -U postgrespsql (PGXC 1.3devel, based on PG 9.4beta1)Type "help" for help.postgres=# create table test(id int, name text) distribute by replication;CREATE TABLEpostgres=# insert into test values (1,'wang'),(2,'shuo');INSERT 0 2postgres=# select * from test; id | name ----+------ 1 | wang 2 | shuo(2 rows)postgres=# \q[postgres@localhost ~]$ psql -h 192.168.238.131 -p 15432 -d postgres -U postgres psql (PGXC 1.3devel, based on PG 9.4beta1)Type "help" for help.postgres=# select * from test; id | name ----+------ 1 | wang 2 | shuo(2 rows)postgres=# \q[postgres@localhost ~]$ psql -h 192.168.238.132 -p 15432 -d postgres -U postgres psql (PGXC 1.3devel, based on PG 9.4beta1)Type "help" for help.postgres=# select * from test; id | name ----+------ 1 | wang 2 | shuo(2 rows)postgres=#
总结：
相较于手动部署，利用pgxc_ctl部署效率以及正确率是非常高的，欢迎大家尝试。