PostgreSQL XL Installation Guide

Host1	Host2
全局配置 vi /etc/profile export PGHOME=/appl/postgres-xl-9.5r1.4 export PGUSER=pgxl export LD_LIBRARY_PATH=$PGHOME/lib export PATH=$PATH:$PGHOME/bin source /etc/profile	(same)
建用户 groupadd pgxl useradd -d /home/pgxl -m pgxl -g pgxl -p pgxl passwd pgxl （password: pgxl）	(same)
打通ssh su - pgxl ssh-keygen -t rsa cat ~/.ssh/id_rsa.pub>> ~/.ssh/authorized_keys chmod 600 ~/.ssh/authorized_keys scp ~/.ssh/authorized_keys pgxl@192.168.1.102:~/.ssh/ ssh pgxl@centos1 （测试）	(nil)
前置包 #For “./configure --prefix=/appl/postgres-xl-9.5r1.4” yum install gcc yum install readline readline-devel yum install zlib zlib-devel #For “make” yum install flex	(nil)
解压编译，同步编译后软件包 tar -xvzf xxx.tar.gz ./configure --prefix=/opt/pgxl make make install chown -R pgxl:pgxl /appl/postgres-xl-9.5r1.4 zip -q -r postgres-xl-9.5r1.4.zip /appl/postgres-xl-9.5r1.4 scp postgres-xl-9.5r1.4.zip pgxl@192.168.1.102:/	cd /appl unzip -q postgres-xl-9.5r1.4.zip cd /appl mv postgres-xl-9.5r1.4 /appl/ rm postgres-xl-9.5r1.4.zip
初始化 cd /appl/postgres-xl-9.5r1.4/bin ./initgtm -Z gtm -D /appl/postgres-xl-9.5r1.4/DATA/gtm ./initgtm -Z gtm_proxy -D /appl/postgres-xl-9.5r1.4/DATA/gtm_proxy ./initdb -D /appl/postgres-xl-9.5r1.4/DATA/coord2 --nodename coord2 -E UTF8 --locale=C -U pgxl -W（设密码：pgxl） ./initdb -D /appl/postgres-xl-9.5r1.4/DATA/dn2 --nodename dn2 -E UTF8 --locale=C -U pgxl -W（设密码：pgxl）	cd /appl/postgres-xl-9.5r1.4/bin ./initdb -D /appl/postgres-xl-9.5r1.4/DATA/coord1 --nodename coord1 -E UTF8 --locale=C -U pgxl -W（设密码：pgxl） ./initdb -D /appl/postgres-xl-9.5r1.4/DATA/dn1 --nodename dn1 -E UTF8 --locale=C -U pgxl -W（设密码：pgxl）
配置gtm、gtm_proxy cd /appl/postgres-xl-9.5r1.4/DATA/gtm cp -p gtm.conf gtm.conf.bk vi gtm.conf ----- nodename = 'gtm' #listen_addresses = '*' port = 6666 #startup = ACT #keepalives_idle = 60 #keepalives_interval = 10 #keepalives_count = 10 ----- cd /appl/postgres-xl-9.5r1.4/DATA/gtm_proxy cp -p gtm_proxy.conf gtm_proxy.conf.bk vi gtm_proxy.conf ----- nodename = 'gtm_proxy' #listen_addresses = '*' port = 6667 gtm_host = 'centos2' gtm_port = 6666 #keepalives_idle = 60 #keepalives_interval = 10 #keepalives_count = 10	(nil)
配置coord里的postgresql cd /appl/postgres-xl-9.5r1.4/DATA/coord2 cp -p postgresql.conf postgresql.conf.bk vi postgresql.conf ----- listen_addresses = '*' port = 5432 pooler_port = 6668 max_pool_size = 100 gtm_host = 'centos2' gtm_port = 6667 pgxc_node_name = 'coord2' ----- scp /appl/postgres-xl-9.5r1.4/DATA/coord2/postgresql.conf pgxl@centos1:/appl/postgres-xl-9.5r1.4/DATA/coord1	cd /appl/postgres-xl-9.5r1.4/DATA/coord1 vi postgresql.conf ----- pgxc_node_name = 'coord1'
配置coord里的pg_hba cp -p pg_hba.conf pg_hba.conf.bk vi pg_hba.conf ----- # IPv4（增加两行） host   all            all            0.0.0.0/0       trust（允许无密登录） host   all            all            0.0.0.0/0               md5（允许远程连接） ----- scp /appl/postgres-xl-9.5r1.4/DATA/coord2/pg_hba.conf pgxl@centos1:/appl/postgres-xl-9.5r1.4/DATA/coord1	(nil)
配置datanode里的postgresql scp /appl/postgres-xl-9.5r1.4/DATA/coord2/postgresql.conf pgxl@centos2:/appl/postgres-xl-9.5r1.4/DATA/dn2   cd /appl/postgres-xl-9.5r1.4/DATA/dn2 vi postgresql.conf ----- port = 5442 pgxc_node_name = 'dn2' pooler_port = 6669 ----- scp /appl/postgres-xl-9.5r1.4/DATA/dn2/postgresql.conf pgxl@centos1:/appl/postgres-xl-9.5r1.4/DATA/dn1	cd /appl/postgres-xl-9.5r1.4/DATA/dn1 vi postgresql.conf ----- pgxc_node_name = 'dn1'
配置datanode里的pg_hba scp /appl/postgres-xl-9.5r1.4/DATA/coord2/pg_hba.conf pgxl@centos2:/appl/postgres-xl-9.5r1.4/DATA/dn2 scp /appl/postgres-xl-9.5r1.4/DATA/coord2/pg_hba.conf pgxl@centos1:/appl/postgres-xl-9.5r1.4/DATA/dn1	(nil)
启动 /appl/postgres-xl-9.5r1.4/bin/gtm_ctl start -Z gtm -D /appl/postgres-xl-9.5r1.4/DATA/gtm   /appl/postgres-xl-9.5r1.4/bin/gtm_ctl start -Z gtm_proxy -D /appl/postgres-xl-9.5r1.4/DATA/gtm_proxy   /appl/postgres-xl-9.5r1.4/bin/pg_ctl start -Z datanode -D /appl/postgres-xl-9.5r1.4/DATA/dn2   /appl/postgres-xl-9.5r1.4/bin/pg_ctl start -Z coordinator -D /appl/postgres-xl-9.5r1.4/DATA/coord2     5、配置集群信息…	/appl/postgres-xl-9.5r1.4/bin/pg_ctl start -Z datanode -D /appl/postgres-xl-9.5r1.4/DATA/dn1   /appl/postgres-xl-9.5r1.4/bin/pg_ctl start -Z coordinator -D /appl/postgres-xl-9.5r1.4/DATA/coord1

 

参考：

http://blog.csdn.net/liuzongxi/article/details/46380985

http://www.linuxidc.com/Linux/2015-11/125624.htm

http://blog.csdn.net/jacktonny1/article/details/50779568

http://files.postgres-xl.org/documentation/server-start.html

http://www.jianshu.com/p/82aaf352b772

Q&A

HINT:  Is another postmaster already running on port 5442? If not, wait a few seconds and retry. WARNING:  could not create listen socket for "centos2" FATAL:  could not create any TCP/IP sockets A: (1) lsof -i:5442; (2) 修改postgresql.conf中的port=xxx和listen_addresses = '*'

WARNING:  can not connect to GTM: No route to host ERROR:  Could not obtain a transaction ID from GTM. The GTM might have failed or lost connectivity A: (1) postgresql.conf 中的gtm_*配置；(2)关防火墙chkconfig iptables off, service iptables stop

 

分布式同步配置

Host1	Host2
psql -p5432 postgres（协调节点，其它参数：-Upgxl -h<hostname>）   select * from pgxc_node; create node coord1 with(TYPE=coordinator,HOST='centos1',PORT=5432); create node coord2 with (type=coordinator,host='centos2', port=5432); create node dn1 with (type=datanode, host='centos1',port=5442,primary,preferred); create node dn2 with (type=datanode, host='centos2',port=5442); select pgxc_pool_reload(); select * from pgxc_node; (alter node coord1)	psql -p5432 postgres (same except alter node coord1)
psql -p5442 postgres（数据节点） (same except alter node dn2)	psql -p5442 postgres (same except alter node dn1)

 

测试

Host1	Host2
psql -p5432 postgres select * from test1;	只能在协调节点操作，数据节点都是只读的 psql -p5432 postgres ALTER USER pgxl WITH PASSWORD 'pgxl'; create table test1(id integer,name varchar(20)); insert into test1(id,name) values(1,'xk'); commit; select * from test1;

 

远程连接

关闭防火墙 chkconfig iptables off（重启后生效） service iptables stop（即时生效，但重启后防火墙会再次启动） 开通权限 /appl/postgres-xl-9.5r1.4/DATA/coord1(2)/pg_hba.conf /appl/postgres-xl-9.5r1.4/DATA/dn1(2)/pg_hba.conf Java代码 String url = "jdbc:postgresql://centos2:5432/postgres"; Class.forName("org.postgresql.Driver")

 

基本命令

\l        查看数据库 \c huarun 切换数据库 \d+       查看所有表 \d <table>查看表结构 \q        退出

 

分布式策略

CREATE TABLE disttab(col1 int, col2 text) DISTRIBUTE BY HASH(col1); -- Default Hash CREATE TABLE repltab (col1 int, col2 int) DISTRIBUTE BY REPLICATION; -- 数据在单一节点 SELECT xc_node_id, count(*) FROM disttab GROUP BY xc_node_id; -- 查看数据在哪个节点 select * from pgxc_node; -- 查看节点ID对应hostname \d+ disttab -- 查看表分布式策略（“Distribute By”和“Location Nodes”） ALTER TABLE disttab ADD NODE (dn3); -- 增加存储数据节点（会redistribute tables） 参考： http://files.postgres-xl.org/documentation/tutorial-createcluster.html

 

数据导入导出

导入 psql -p5432 postgres COPY vender(sid,vender_id,vender_name,connector,created_by,created_dt,version,del_flg)  from 'E:\Vendor.csv' WITH CSV HEADER; copy testdata from 'd:/test/testdata.csv' delimiter as',' csv quote as '"'

导出 copy testdata to 'd:/test/testdata.csv' delimiteras ',' csv quote as '"'