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
192.168.100.160 gtm
192.168.100.161 node1
192.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
1: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
1:140169430058752:2014-06-12 07:51:17.652 CST -LOG: Restoring last GXID to 10000
LOCATION: GTM_RestoreTxnInfo, gtm_txn.c:2673
1: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
1: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
1: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