Postgres-XL测试

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了Postgres-XL测试，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。



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.160gtm
192.168.100.161node1
192.168.100.162node2

编译安装

#yuminstall-yflexbisonreadline-develzlib-developenjadedocbook-style-dsssl
#wgethttp://jaist.dl.sourceforge.net/project/postgres-xl/Releases/Version_9.2rc/postgres-xl-v9.2-src.tar.gz
#tar-zxvfpostgres-xl-v9.2-src.tar.gz
#cdpostgres-xl
#./configure--prefix=/opt/pgxl
#make
#makeinstall

创建用户：

#useraddpostgres

设置环境变量：

#su-postgres
$vi.bash_profile
exportPGHOME=/opt/pgxl
exportPGUSER=postgres
exportLD_LIBRARY_PATH=$PGHOME/lib
exportPATH=$PGHOME/bin:$PATH

初始化

在gtm节点上执行：

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

在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-Rpostgres:postgres/opt/pgxl/data/
[root@node1~]#su-postgres
[postgres@node1~]$initdb-D/opt/pgxl/data/data_coord1/--nodenamecoord1
[postgres@node1~]$initdb-D/opt/pgxl/data/data_datanode1/--nodenamedatanode1
[postgres@node1~]$initgtm-D/opt/pgxl/data/data_gtm_proxy/-Zgtm_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-Rpostgres:postgres/opt/pgxl/data/
[root@node2~]#su-postgres
[postgres@node2~]$initdb-D/opt/pgxl/data/data_coord2/--nodenamecoord2
[postgres@node2~]$initdb-D/opt/pgxl/data/data_datanode2/--nodenamedatanode2
[postgres@node2~]$initgtm-D/opt/pgxl/data/data_gtm_proxy/-Zgtm_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
[添加]
hostallall192.168.100.0/24trust

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
[添加]
hostallall192.168.100.0/24trust

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
[添加]
hostallall192.168.100.0/24trust

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
[添加]
hostallall192.168.100.0/24trust

启动

启动gtm

[postgres@gtm~]$gtm_ctlstart-Zgtm-D/opt/pgxl/data_gtm/
serverstarting

[postgres@gtm~]$tail/opt/pgxl/data_gtm/gtm.log
1:140169430058752:2014-06-1207:51:17.652CST-LOG:StartingGTMserverat(*:6666)--controlfile/opt/pgxl/data_gtm/gtm.control
LOCATION:main,main.c:601
1:140169430058752:2014-06-1207:51:17.652CST-LOG:RestoringlastGXIDto10000

LOCATION:GTM_RestoreTxnInfo,gtm_txn.c:2673
1:140169430058752:2014-06-1207:51:17.652CST-LOG:StartedtorunasGTM-Active.
LOCATION:main,main.c:682

启动gtm_proxy

node1：

[postgres@node1~]$gtm_ctlstart-Zgtm_proxy-D/opt/pgxl/data/data_gtm_proxy/
serverstarting
[postgres@node1~]$tail/opt/pgxl/data/data_gtm_proxy/gtm_proxy1.log
1:139860198487808:2014-06-1207:53:59.665CST-LOG:StartingGTMproxyat(*:6661)
LOCATION:main,proxy_main.c:805

node2：

[postgres@node2~]$gtm_ctlstart-Zgtm_proxy-D/opt/pgxl/data/data_gtm_proxy/
serverstarting
[postgres@node2~]$tail/opt/pgxl/data/data_gtm_proxy/gtm_proxy2.log
1:140266037184256:2014-06-1207:54:16.440CST-LOG:StartingGTMproxyat(*:6662)
LOCATION:main,proxy_main.c:805

启动datanode

node1：

[postgres@node1~]$pg_ctlstart-Zdatanode-D/opt/pgxl/data/data_datanode1/

node2：

[postgres@node2~]$pg_ctlstart-Zdatanode-D/opt/pgxl/data/data_datanode2/

启动coordinator

node1：

[postgres@node1~]$pg_ctlstart-Zcoordinator-D/opt/pgxl/data/data_coord1/

node2：

[postgres@node2~]$pg_ctlstart-Zcoordinator-D/opt/pgxl/data/data_coord2/

注册节点信息

查看当前节点信息：

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

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

执行注册

node1：

[postgres@node1~]$psql-p5432-c"CREATENODEcoord2WITH(TYPE='coordinator',HOST='node2',PORT=5432)"
CREATENODE
[postgres@node1~]$psql-p5432-c"CREATENODEdatanode1WITH(TYPE='datanode',HOST='node1',PORT=15432)"
CREATENODE
[postgres@node1~]$psql-p5432-c"CREATENODEdatanode2WITH(TYPE='datanode',PORT=15432)"
CREATENODE

查看：

[postgres@node1~]$psql-p5432-c"select*frompgxc_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
(4rows)

重新加载：

[postgres@node1~]$psql-p5432-c"selectpgxc_pool_reload()"
pgxc_pool_reload
------------------
t
(1row)

node2：

[postgres@node2~]$psql-p5432-c"CREATENODEcoord1WITH(TYPE='coordinator',PORT=5432)"
CREATENODE
[postgres@node2~]$psql-p5432-c"CREATENODEdatanode1WITH(TYPE='datanode',PORT=15432)"
CREATENODE
[postgres@node2~]$psql-p5432-c"CREATENODEdatanode2WITH(TYPE='datanode',PORT=15432)"
CREATENODE

查看：

[postgres@node2~]$psql-p5432-c"select*frompgxc_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
(4rows)

重新加载：

[postgres@node2~]$psql-p5432-c"selectpgxc_pool_reload()"
pgxc_pool_reload
------------------
t
(1row)

BUG

createtablet1(idint,nametext)distributebyhash(id)togroupgroup1;


createtablet2(idintprimarykey,nametext)distributebyhash(id)togroupgroup1;



test=#createdatabasepgxc;
LOG:WillfallbacktolocalsnapshotforXID=19764,source=0,gxmin=0,autovaclaunch=0,autovac=0,normProcMode=0,postEnv=1
ERROR:node"coord1_19791"doesnotexist
STATEMENT:SETglobal_sessionTOcoord1_19791;
ERROR:node"coord1_19791"doesnotexist
STATEMENT:SETglobal_sessionTOcoord1_19791;
CREATEDATABASE

test=#createtablet1(idint,nametext)distributebyhash(id)togroup
group1;
ERROR:node"coord1_19791"doesnotexist
STATEMENT:SETglobal_sessionTOcoord1_19791;
ERROR:node"coord1_19791"doesnotexist
STATEMENT:SETglobal_sessionTOcoord1_19791;
CREATETABLE

test=#insertintot1values(1,'a');
LOG:WillfallbacktolocalsnapshotforXID=19544,postEnv=1
ERROR:node"coord1_19791"doesnotexist
STATEMENT:SETglobal_sessionTOcoord1_19791;
INSERT00

问题汇总

Q1

'/usr/bin/perl'/bin/collateindex.pl-f-g-i'bookindex'-obookindex.sgmlHTML.index
Can'topenperlscript"/bin/collateindex.pl":Nosuchfileordirectory
make[4]:***[bookindex.sgml]Error2
make[4]:Leavingdirectory`/root/postgres-xl/doc-xc/src/sgml'
make[3]:***[sql_help.h]Error2
make[3]:Leavingdirectory`/root/postgres-xl/src/bin/psql'
make[2]:***[all-psql-recurse]Error2
make[2]:Leavingdirectory`/root/postgres-xl/src/bin'
make[1]:***[all-bin-recurse]Error2
make[1]:Leavingdirectory`/root/postgres-xl/src'
make:***[all-src-recurse]Error2

解决方式：

#yuminstalldocbook-style-dsssl
#find/-namecollateindex.pl
/usr/bin/collateindex.pl

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1. 标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1. 数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1. 表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4. 从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1. 读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问