β

Postgres-XL测试

连顺科的个人页面 309 阅读


GTM-Proxy的作用:

GTM-Proxy is not a mandatory component of Postgres-XL cluster but it can be used to group messages between GTM and cluster nodes, reducing workload and the number of packages exchanged through network.

项目由来:

In 2010, NTT's Open Source Software Center approached EnterpriseDB to build off of NTT OSSC's experience with a project called RitaDB and EnterpriseDB's experience with a project called GridSQL, and the result was a new project, Postgres-XC.

In 2012, a company called StormDB was formed with some of the original key Postgres-XC developers. StormDB added enhancements, including MPP parallelism for performance and multi-tenant security.

In 2013, TransLattice acquired StormDB, and in 2014, open sourced it as Postgres-XL.

PostgreSQL、Postgres-XC、Postgres-XL之间的区别:


启动集群的顺序:

gtm

gtm_standby

gtm_proxy

datanode

coordinator

关闭集群的顺序:

coordinator

datanode

gtm_proxy

gtm_standby

gtm

IP、HostName设置:

# vi /etc/hosts
.168.100.160 gtm
.168.100.161 node1
.168.100.162 node2

编译安装

# yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl
# wget http://jaist.dl.sourceforge.net/project/postgres-xl/Releases/Version_9.2rc/postgres-xl-v9.2-src.tar.gz
# tar -zxvf postgres-xl-v9.2-src.tar.gz
# cd postgres-xl
# ./configure --prefix=/opt/pgxl
# make
# make install

创建用户:

# useradd postgres

设置环境变量:

# su - postgres
$ vi .bash_profile
export PGHOME=/opt/pgxl
export PGUSER=postgres
export LD_LIBRARY_PATH=$PGHOME/lib
export PATH=$PGHOME/bin:$PATH

初始化

在gtm节点上执行:

[root@gtm ~]# mkdir /opt/pgxl/data_gtm
[root@gtm ~]# chown postgres:postgres /opt/pgxl/data_gtm/
[root@gtm ~]# su - postgres
[postgres@gtm ~]$ initgtm -D /opt/pgxl/data_gtm/ -Z gtm

在node1上执行:

[root@node1 ~]# mkdir -p /opt/pgxl/data/data_gtm_proxy
[root@node1 ~]# mkdir -p /opt/pgxl/data/data_coord1
[root@node1 ~]# mkdir -p /opt/pgxl/data/data_datanode1
[root@node1 ~]# chown -R postgres:postgres /opt/pgxl/data/
[root@node1 ~]# su - postgres
[postgres@node1 ~]$ initdb -D /opt/pgxl/data/data_coord1/ --nodename coord1
[postgres@node1 ~]$ initdb -D /opt/pgxl/data/data_datanode1/ --nodename datanode1
[postgres@node1 ~]$ initgtm -D /opt/pgxl/data/data_gtm_proxy/ -Z gtm_proxy

在node2上执行:

[root@node2 ~]# mkdir -p /opt/pgxl/data/data_gtm_proxy
[root@node2 ~]# mkdir -p /opt/pgxl/data/data_coord2
[root@node2 ~]# mkdir -p /opt/pgxl/data/data_datanode2
[root@node2 ~]# chown -R postgres:postgres /opt/pgxl/data/
[root@node2 ~]# su - postgres
[postgres@node2 ~]$ initdb -D /opt/pgxl/data/data_coord2/ --nodename coord2
[postgres@node2 ~]$ initdb -D /opt/pgxl/data/data_datanode2/ --nodename datanode2
[postgres@node2 ~]$ initgtm -D /opt/pgxl/data/data_gtm_proxy/ -Z gtm_proxy

配置

gtm配置

gtm:

[postgres@gtm ~]$ vi /opt/pgxl/data_gtm/gtm.conf
nodename = 'gtm'
listen_addresses = '*'
port = 6666
startup = ACT
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm.log'
log_min_messages = WARNING

gtm_proxy配置

node1:

[postgres@node1 ~]$ vi /opt/pgxl/data/data_gtm_proxy/gtm_proxy.conf
nodename = 'gtm_proxy1'
listen_addresses = '*'
port = 6661
worker_threads = 1
gtm_host = 'gtm'
gtm_port = 6666
gtm_connect_retry_interval = 5
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm_proxy1.log'
log_min_messages = WARNING

node2:

[postgres@node2 ~]$ vi /opt/pgxl/data/data_gtm_proxy/gtm_proxy.conf
nodename = 'gtm_proxy2'
listen_addresses = '*'
port = 6662
worker_threads = 1
gtm_host = 'gtm'
gtm_port = 6666
gtm_connect_retry_interval = 5
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm_proxy2.log'
log_min_messages = WARNING

coordinator配置

node1:

[postgres@node1 ~]$ vi /opt/pgxl/data/data_coord1/postgresql.conf
listen_addresses = '*'
port = 5432
pooler_port = 6667
max_pool_size = 100
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_coordinators = 16
max_datanodes = 16
gtm_host = 'localhost'
gtm_port = 6661
pgxc_node_name = 'coord1'
 
[postgres@node1 ~]$ vi /opt/pgxl/data/data_coord1/pg_hba.conf
[添加]
host    all             all             192.168.100.0/24        trust

node2:

[postgres@node2 ~]$ vi /opt/pgxl/data/data_coord1/postgresql.conf
listen_addresses = '*'
port = 5432
pooler_port = 6667
max_pool_size = 100
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_coordinators = 16
max_datanodes = 16
gtm_host = 'localhost'
gtm_port = 6662
pgxc_node_name = 'coord2'
 
[postgres@node2 ~]$ vi /opt/pgxl/data/data_coord1/pg_hba.conf
[添加]
host    all             all             192.168.100.0/24        trust

datanode配置

node1:

[postgres@node1 ~]$ vi /opt/pgxl/data/data_datanode1/postgresql.conf
listen_addresses = '*'
port = 15432
pooler_port = 6668
max_pool_size = 100
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_coordinators = 16
max_datanodes = 16
gtm_host = 'localhost'
gtm_port = 6661
pgxc_node_name = 'datanode1'
 
[postgres@node1 ~]$ vi /opt/pgxl/data/data_coord1/pg_hba.conf
[添加]
host    all             all             192.168.100.0/24        trust

node2:

[postgres@node1 ~]$ vi /opt/pgxl/data/data_datanode1/postgresql.conf
listen_addresses = '*'
port = 15432
pooler_port = 6668
max_pool_size = 100
pool_conn_keepalive = 600
pool_maintenance_timeout = 30
max_coordinators = 16
max_datanodes = 16
gtm_host = 'localhost'
gtm_port = 6662
pgxc_node_name = 'datanode2'
 
[postgres@node1 ~]$ vi /opt/pgxl/data/data_coord1/pg_hba.conf
[添加]
host    all             all             192.168.100.0/24        trust

启动

启动gtm

[postgres@gtm ~]$ gtm_ctl start -Z gtm -D /opt/pgxl/data_gtm/
server starting
 
[postgres@gtm ~]$ tail /opt/pgxl/data_gtm/gtm.log 
:140169430058752:2014-06-12 07:51:17.652 CST -LOG:  Starting GTM server at (*:6666) -- control file /opt/pgxl/data_gtm/gtm.control
LOCATION:  main, main.c:601
:140169430058752:2014-06-12 07:51:17.652 CST -LOG:  Restoring last GXID to 10000
       
LOCATION:  GTM_RestoreTxnInfo, gtm_txn.c:2673
:140169430058752:2014-06-12 07:51:17.652 CST -LOG:  Started to run as GTM-Active.
LOCATION:  main, main.c:682

启动gtm_proxy

node1:

[postgres@node1 ~]$ gtm_ctl start -Z gtm_proxy -D /opt/pgxl/data/data_gtm_proxy/
server starting
[postgres@node1 ~]$ tail /opt/pgxl/data/data_gtm_proxy/gtm_proxy1.log 
:139860198487808:2014-06-12 07:53:59.665 CST -LOG:  Starting GTM proxy at (*:6661)
LOCATION:  main, proxy_main.c:805

node2:

[postgres@node2 ~]$ gtm_ctl start -Z gtm_proxy -D /opt/pgxl/data/data_gtm_proxy/
server starting
[postgres@node2 ~]$ tail /opt/pgxl/data/data_gtm_proxy/gtm_proxy2.log 
:140266037184256:2014-06-12 07:54:16.440 CST -LOG:  Starting GTM proxy at (*:6662)
LOCATION:  main, proxy_main.c:805

启动datanode

node1:

[postgres@node1 ~]$ pg_ctl start -Z datanode -D /opt/pgxl/data/data_datanode1/

node2:

[postgres@node2 ~]$ pg_ctl start -Z datanode -D /opt/pgxl/data/data_datanode2/

启动coordinator

node1:

[postgres@node1 ~]$ pg_ctl start -Z coordinator -D /opt/pgxl/data/data_coord1/

node2:

[postgres@node2 ~]$ pg_ctl start -Z coordinator -D /opt/pgxl/data/data_coord2/

注册节点信息

查看当前节点信息:

[postgres@node1 ~]$ psql -p 5432 -c "select * from pgxc_node"
 node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |  node_id   
-----------+-----------+-----------+-----------+----------------+------------------+------------
 coord1    | C         |      5432 | localhost | f              | f                | 1885696643
(1 row)
 
[postgres@node2 ~]$ psql -p 5432 -c "select * from pgxc_node"
 node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id   
-----------+-----------+-----------+-----------+----------------+------------------+-------------
 coord2    | C         |      5432 | localhost | f              | f                | -1197102633
(1 row)

执行注册

node1:

[postgres@node1 ~]$ psql -p 5432 -c "CREATE NODE coord2 WITH (TYPE='coordinator',HOST='node2',PORT=5432)"
CREATE NODE
[postgres@node1 ~]$ psql -p 5432 -c "CREATE NODE datanode1 WITH (TYPE='datanode',HOST='node1',PORT=15432)"
CREATE NODE
[postgres@node1 ~]$ psql -p 5432 -c "CREATE NODE datanode2 WITH (TYPE='datanode',HOST='node2',PORT=15432)"
CREATE NODE

查看:

[postgres@node1 ~]$ psql -p 5432 -c "select * from pgxc_node"
 node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id   
-----------+-----------+-----------+-----------+----------------+------------------+-------------
 coord1    | C         |      5432 | localhost | f              | f                |  1885696643
 coord2    | C         |      5432 | node2     | f              | f                | -1197102633
 datanode1 | D         |     15432 | node1     | f              | f                |   888802358
 datanode2 | D         |     15432 | node2     | f              | f                |  -905831925
(4 rows)

重新加载:

[postgres@node1 ~]$ psql -p 5432 -c "select pgxc_pool_reload()"
 pgxc_pool_reload 
------------------
 t
(1 row)

node2:

[postgres@node2 ~]$ psql -p 5432 -c "CREATE NODE coord1 WITH (TYPE='coordinator',HOST='node1',PORT=5432)"
CREATE NODE
[postgres@node2 ~]$ psql -p 5432 -c "CREATE NODE datanode1 WITH (TYPE='datanode',HOST='node1',PORT=15432)"
CREATE NODE
[postgres@node2 ~]$ psql -p 5432 -c "CREATE NODE datanode2 WITH (TYPE='datanode',HOST='node2',PORT=15432)"
CREATE NODE

查看:

[postgres@node2 ~]$ psql -p 5432 -c "select * from pgxc_node"
 node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id   
-----------+-----------+-----------+-----------+----------------+------------------+-------------
 coord2    | C         |      5432 | localhost | f              | f                | -1197102633
 coord1    | C         |      5432 | node1     | f              | f                |  1885696643
 datanode1 | D         |     15432 | node1     | f              | f                |   888802358
 datanode2 | D         |     15432 | node2     | f              | f                |  -905831925
(4 rows)

重新加载:

[postgres@node2 ~]$ psql -p 5432 -c "select pgxc_pool_reload()"
 pgxc_pool_reload 
------------------
 t
(1 row)

BUG

create table t1(id int,name text) distribute by hash(id) to group group1;
 
 
create table t2(id int primary key,name text) distribute by hash(id) to group group1;
 
 
 
test=# create database pgxc;
LOG:  Will fall back to local snapshot for XID = 19764, source = 0,
gxmin = 0, autovac launch = 0, autovac = 0, normProcMode = 0, postEnv = 1
ERROR:  node "coord1_19791" does not exist
STATEMENT:  SET global_session TO coord1_19791;
ERROR:  node "coord1_19791" does not exist
STATEMENT:  SET global_session TO coord1_19791;
CREATE DATABASE
 
test=# create table t1(id int,name text) distribute by hash(id) to group
group1;
ERROR:  node "coord1_19791" does not exist
STATEMENT:  SET global_session TO coord1_19791;
ERROR:  node "coord1_19791" does not exist
STATEMENT:  SET global_session TO coord1_19791;
CREATE TABLE
 
test=# insert into t1 values (1,'a');
LOG:  Will fall back to local snapshot for XID = 19544, source = 0,
gxmin = 0, autovac launch = 0, autovac = 0, normProcMode = 0, postEnv = 1
ERROR:  node "coord1_19791" does not exist
STATEMENT:  SET global_session TO coord1_19791;
INSERT 0 0

问题汇总

Q1

'/usr/bin/perl' /bin/collateindex.pl -f -g -i 'bookindex' -o bookindex.sgml HTML.index
Can't open perl script "/bin/collateindex.pl": No such file or directory
make[4]: *** [bookindex.sgml] Error 2
make[4]: Leaving directory `/root/postgres-xl/doc-xc/src/sgml'
make[3]: *** [sql_help.h] Error 2
make[3]: Leaving directory `/root/postgres-xl/src/bin/psql'
make[2]: *** [all-psql-recurse] Error 2
make[2]: Leaving directory `/root/postgres-xl/src/bin'
make[1]: *** [all-bin-recurse] Error 2
make[1]: Leaving directory `/root/postgres-xl/src'
make: *** [all-src-recurse] Error 2

解决方式:

# yum install docbook-style-dsssl
# find / -name collateindex.pl
/usr/bin/collateindex.pl
作者:连顺科的个人页面
连顺科的博客
原文地址:Postgres-XL测试, 感谢原作者分享。