Postgres-XL集群安装与配置

vi /pgxl_data/datanode/dn1/pg_hba.conf # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 md5

vi /pgxl_data/datanode/dn2/postgresql.conf #———————————— listen_addresses = ‘*’ port = 15432

max_connections = 100

# DATA NODES AND CONNECTION POOLING #———————————————- pooler_port = 6668 #min_pool_size = 1 max_pool_size = 100 # GTM CONNECTION #—————————– gtm_host = ‘192.168.8.106’ gtm_port = 6666

pgxc_node_name = ‘db2’

vi /pgxl_data/datanode/dn2/pg_hba.conf # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 md5 7）启动节点 启动顺序

gtm + (gtm_standby) + (gtmproxy) + datanode + coordinator ? 启动gtm

$gtm -D /pgxl_data/gtm & ? 启动datanode

$postgres –datanode -D /pgxl_data/datanode/dn1 & $postgres –datanode -D /pgxl_data/datanode/dn2 &

? 启动coordinator

$postgres –coordinator -D /pgxl_data/coordinator/cd1 & $postgres –coordinator -D /pgxl_data/coordinator/cd2 & 8）注册节点

? 在coord1上注册

$psql –p 1921 postgres Select * from pgxc_node;

create node coord2 with (TYPE=coordinator, HOSt=’192.168.8.106’, PORT=1925); create node db1 with (TYPE=datanode, HOST=’192.168.8.106’, PORT=15431, primary); create node db2 with (TYPE=datanode, HOST=’192.168.8.106’, PORT=15432); alter node coord1 with (TYPE=’coordinator’, HOST=’192.168.8.106’, PORT=1921); select pgxc_pool_reload(); ? 在coord2上注册

$psql –p 1925 postgres Select * from pgxc_node;

create node coord1 with (TYPE=coordinator, HOST=’192.168.8.106’, PORT=1921); create node db1 with (TYPE=datanode, HOST=’192.168.8.106’, PORT=15431, primary); create node db2 with (TYPE=datanode, HOST=’192.168.8.106’, PORT=15432); alter node coord2 with (TYPE=’coordinator’ HOST=’192.168.8.106’, PORT=15431, primary=true);

select pgxc_pool_reload(); ? 在db1上注册

$psql –p 15431 postgres Select * from pgxc_node;

create node coord1 with (TYPE=coordinator, HOST=’192.168.8.106’, PORT=1921); create node coord2 with (TYPE=coordinator, HOST=’192.168.8.106’, PORT=1925); create node db2 with (TYPE=datanode, HOST=’192.168.8.106’, PORT=15432); alter node db1 with (TYPE=datanode, HOST=’192.168.8.106’, PORT=15431, primary=true);

select pgxc_pool_reload(); ? 在db2上注册

$psql –p 15432 postgres

Select * from pgxc_node;

create node coord1 with (TYPE=coordinator, HOST=’192.168.8.106’, PORT=1921); create node coord2 with (TYPE=coordinator, HOST=’192.168.8.106’, PORT=1925); create node db1 with (TYPE=datanode, HOST=’192.168.8.106’, PORT=15431,primary); alter node db2 with (TYPE=datanode, HOST=’192.168.8.106’, PORT=15432, primary=false);

select pgxc_pool_reload();

注册节点后，每个节点上都能查询到以下结果：

9）停止节点 停止顺序

coordinator + datanode + (gtmproxy) + (gtm_standby) + gtm

停止节点指令：

$pg_ctl stop –D /pgxl_data/coordinator/cd1 –Z coordinator –m fast $pg_ctl stop –D /pgxl_data/coordinator/cd2 –Z coordinator –m fast $pg_ctl stop –D /pgxl_data/datanode/dn1 –Z datanode –m fast $pg_ctl stop –D /pgxl_data/datanode/dn2 –Z datanode –m fast $gtm_ctl stop –D /pgxl_data/gtm –Z gtm –m fast

3.验证测试

在coord1上创建一个数据库

$psql –p 1921 postgres

postgres=# create database pgxl_test;

postgres=#\\c pgxl_test #切换到pgxl_test数据库

? 创建分区表

create table test_xl (id integer,name varchar(32));

(1)测试插入100条记录

insert into test_xl select generate_series(1,100),’pgxl_test’;

到db1、db2查看pgxl_test中test_xl表中的数据量。 登录db1节点：

登录db2节点：

? 创建复制表

create table t2(id serial8 primary key, info text, crt_time timestamp) DISTRIBUTE BY REPLICATION; (1). 测试插入数据

insert into t2 (info, crt_time) select md5(random()::text), clock_timestamp() from generate_series(1,200);

可以登录db1,db2查看pgxl_test中t2表的数据量都是200：