postgres-xl实现postgres数据库的分布式部署

服务器规划

主机    操作系统配置IP服务
gtmcentos 74CPU   2G10.18.96.37gtm
node1centos 74CPU   2G10.18.96.20coord1、datanode1、gtm_proxy1
node2centos 74CPU   2G10.18.96.39coord2、datanode2、gtm_proxy

修改主机名

在每台机器的 /etc/hosts中加入以下内容:
gtm10.18.96.37 gtm localhost
10.18.96.20 node1
10.18.96.39 node2
 
node110.18.96.37 gtm 
10.18.96.20 node1 localhost
10.18.96.39 node2
 
node210.18.96.37 gtm
10.18.96.20 node1
10.18.96.39 node2  localhost
 
在每台机器的 /etc/sysconfig/network修改HOSTNAME,并重启服务器
gtm主机:HOSTNAME=gtm
node1主机:HOSTNAME=node1
node1主机:HOSTNAME=node2

安装准备

依赖包

[root@localhost ~]# yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl 

新建用户

每个节点都建立用户postgres,并且建立.ssh目录,并配置相应的权限:

[root@localhost ~]# useradd postgres
[root@localhost ~]# passwd postgres
[root@localhost ~]# su - postgres
[root@localhost ~]# mkdir ~/.ssh
[root@localhost ~]# chmod 700 ~/.ssh

ssh免密码登录

在gtm节点配置如下操作:

[root@localhost ~]# su - postgres
[postgres@localhost ~]# ssh-keygen -t rsa
[postgres@localhost ~]# cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
[postgres@localhost ~]# chmod 600 ~/.ssh/authorized_keys

将刚生成的认证文件拷贝到datanode1和datanode2中

[postgres@localhost ~]# scp ~/.ssh/authorized_keys postgres@datanode1:~/.ssh/
[postgres@localhost ~]# scp ~/.ssh/authorized_keys postgres@datanode2:~/.ssh/

对所有提示都不要输入,直接enter下一步。直到最后,因为第一次要求输入目标机器的用户密码,输入即可。

postgres-xl安装

3个服务器都需安装配置。切换回root用户下,执行如下步骤安装

# 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

设置环境变量

# 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配置

[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 0.0.0.0/0 trust 

node2

[postgres@node2 ~]$ vi /opt/pgxl/data/data_coord2/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_coord2/pg_hba.conf
[添加]
host all all 0.0.0.0/0 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_datanode1/pg_hba.conf
[添加]
host all all 0.0.0.0/0 trust

node2

[postgres@node1 ~]$ vi /opt/pgxl/data/data_datanode2/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_datanode2/pg_hba.conf
[添加]host all all 0.0.0.0/0 trust 

启动

启动gtm

[postgres@gtm ~]$ gtm_ctl start -Z gtm -D /opt/pgxl/data_gtm/
启动gtm_proxy
node1
[postgres@node1 ~]$ gtm_ctl start -Z gtm_proxy -D /opt/pgxl/data/data_gtm_proxy/

node2

[postgres@node2 ~]$ gtm_ctl start -Z gtm_proxy -D /opt/pgxl/data/data_gtm_proxy/

启动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@node1 ~]$ pg_ctl start -Z coordinator -D /opt/pgxl/data/data_coord2/


注册

node1

su - postgres
psql -p 15432 -c "CREATE NODE datanode2 WITH (TYPE='datanode',HOST='node2',PORT=15432)"
psql -p 15432 -c "CREATE NODE coord1 WITH (TYPE='coordinator',HOST='node1',PORT=5432)"
psql -p 15432 -c "CREATE NODE coord2 WITH (TYPE='coordinator',HOST='node2',PORT=5432)"
psql -p 15432 -c "ALTER NODE datanode1 WITH (TYPE='datanode',HOST='node1',PORT=15432)" 

node2

su - postgres
psql -p 15432 -c "CREATE NODE datanode1 WITH (TYPE='datanode',HOST='node1',PORT=15432)"
psql -p 15432 -c "CREATE NODE coord1 WITH (TYPE='coordinator',HOST='node1',PORT=5432)"
psql -p 15432 -c "CREATE NODE coord2 WITH (TYPE='coordinator',HOST='node2',PORT=5432)"
psql -p 15432 -c "ALTER NODE datanode2 WITH (TYPE='datanode',HOST='node2',PORT=15432)" 

coord1

su - postgres
psql -p 5432 -c "CREATE NODE coord2 WITH (TYPE='coordinator',HOST='node2',PORT=5432)"
psql -p 5432 -c "CREATE NODE datanode1 WITH (TYPE='datanode',HOST='node1',PORT=15432)"
psql -p 5432 -c "CREATE NODE datanode2 WITH (TYPE='datanode',HOST='node2',PORT=15432)"
psql -p 5432 -c "ALTER NODE coord1 WITH (TYPE='coordinator',HOST='node1',PORT=5432)"

coord2

su - postgres
psql -p 5432 -c "CREATE NODE coord1 WITH (TYPE='coordinator',HOST='node1',PORT=5432)"
psql -p 5432 -c "CREATE NODE datanode1 WITH (TYPE='datanode',HOST='node1',PORT=15432)"
psql -p 5432 -c "CREATE NODE datanode2 WITH (TYPE='datanode',HOST='node2',PORT=15432)"
psql -p 5432 -c "ALTER NODE coord2 WITH (TYPE='coordinator',HOST='node2',PORT=5432)"

重新加载

在node1和node2两台主机分别执行

[postgres@node1 ~]$ psql -p 5432 -c "select pgxc_pool_reload()"
[postgres@node1 ~]$ psql -p 15432 -c "select pgxc_pool_reload()"
查看
在node1和node2两台主机分别查看节点数
[postgres@node1 ~]$ psql -p 5432 -c "select * from pgxc_node"
[postgres@node1 ~]$ psql -p 15432 -c "select * from pgxc_node"


测试

在任意一个节点创建表

postgres=# CREATE TABLE xlTest(col1 int, col2 int, col3 text) DISTRIBUTE BY HASH(col1);

批量插入数据

postgres=# INSERT INTO xlTest SELECT generate_series(1,10), generate_series(1, 10), 'test';

查看全量数据

[postgres@node2 ~]$ psql
psql (PGXL 9.2.0, based on PG 9.2.4 (Postgres-XL 9.2.0))
Type "help" for help.

postgres=# select * from xlTest;
LOG:  Will fall back to local snapshot for XID = 15841, source = 0, gxmin = 0, autovac launch = 0, autovac = 0, normProcMode = 0, postEnv = 1
 col1 | col2 | col3 
------+------+------
    1 |    1 | test
    2 |    2 | test
    5 |    5 | test
    6 |    6 | test
    8 |    8 | test
    9 |    9 | test
    3 |    3 | test
    4 |    4 | test
    7 |    7 | test
   10 |   10 | test
(10 rows)
-bash: psl: command not found
[postgres@node2 ~]$ psql -p 15432
LOG:  Will fall back to local snapshot for XID = 15846, source = 0, gxmin = 0, autovac launch = 0, autovac = 0, normProcMode = 0, postEnv = 1
psql (PGXL 9.2.0, based on PG 9.2.4 (Postgres-XL 9.2.0))
Type "help" for help.

postgres=# select * from xlTest;
 col1 | col2 | col3 
------+------+------
    3 |    3 | test
    4 |    4 | test
    7 |    7 | test
   10 |   10 | test
(4 rows)

参考:

https://my.oschina.net/lianshunke/blog/308708

http://blog.csdn.net/chenshijie2011/article/details/78261496


                
  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值