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

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,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 '"'