postgres-xc-install
前言
考虑到华为的libra数据库是基于postgres-xc架构，刚好需要好好学习一下，就先安装一下postgres-xc数据库
集群规划
|
主机名 |
IP地址 |
角色 |
端口 |
nodename |
数据目录 |
|
gtm |
192.168.1.30 |
gtm |
6666 |
one |
/home/osdba/gtm |
|
gtm_standby |
192.168.1.31 |
gtm_standby |
6666 |
two |
/home/osdba/gtm_standby |
|
cd1 |
192.168.1.32 |
Coordinator |
5432 |
co1 |
/home/osdba/coordinator |
|
Datanode |
5433 |
dn1 |
/home/osdba/pgdata |
||
|
Gtm Proxy |
6666 |
gtmproxy01 |
/home/osdba/gtm_proxy |
||
|
cd2 |
192.168.1.33 |
Coordinator |
5432 |
co2 |
/home/osdba/coordinator |
|
Datanode |
5433 |
dn2 |
/home/osdba/pgdata |
||
|
Gtm Proxy |
6666 |
gtmproxy02 |
/home/osdba/gtm_proxy |
||
|
cd3 |
192.168.1.34 |
Coordinator |
5432 |
co3 |
/home/osdba/coordinator |
|
Datanode |
5433 |
dn3 |
/home/osdba/pgdata |
||
|
Gtm Proxy |
6666 |
gtmproxy03 |
/home/osdba/gtm_proxy |
集群启动(关闭)
启动
启动的顺序是GTM>GTM Standby>GTM-Proxy>Datanodes>Coordinators
[gtm]
$ gtm_ctl -Z gtm start -D /home/osdba/gtm
[gtm_standby]
$ gtm_ctl -Z gtm_standby start -D /home/osdba/gtm_standby
[cd1|cd2|cd3]
$ gtm_ctl -Z gtm_proxy start -D /home/osdba/gtm_proxy
$ pg_ctl start -D /home/osdba/pgdata -Z datanode
$ pg_ctl start -D /home/osdba/coordinator -Z coordinator
关闭
关闭的顺序是Coordinators>Datanodes>GTM-Proxy>GTM>GTM Standby
[cd1|cd2|cd3]
$ pg_ctl stop -D /home/osdba/coordinator -Z coordinator
$ pg_ctl stop -D /home/osdba/pgdata -Z datanode
$ gtm_ctl -Z gtm_proxy stop -D /home/osdba/gtm_proxy
[gtm]
$ gtm_ctl -Z gtm stop -D /home/osdba/gtm
[gtm_standby]
$ gtm_ctl -Z gtm_standby stop -D /home/osdba/gtm_standby
说明
在本次安装中,符号”#”代表root用户,”$”代表osdba用户,此次记录文档的信息来自于在虚拟机的部署，生产环境还没有测试过。
前期相关信息介绍
操作系统：centos_6.5_x64
内存：1g
磁盘空间:30G
postgres-xc:pgxc-v1.2.1.tar.gz
安装
所有主机
1、关闭防火墙 修改selinux安装设置
# chkconfig iptales off
# cat /etc/selinux/config
SELINUX=disabled
2、配置yum并且安装依赖包
# yum install -y bison flex perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake
3、创建用户
# groupadd osdba
# useradd -g osdba osdba
# passwd osdba
4、上传文件并且解压
# tar zxvf pgxc-v1.2.1.tar.gz
5、编译源代码
# ./configure --prefix=/home/osdba/pgsql --with-perl --with-python
# make
# make install
6、切换osdba用户并修改.bash_profile文件
# su - osdba
$ vim .bash_profile
export PGHOME=/home/osdba/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'
$ source .bash_profile
7、修改/etc/hosts文件
# vim /etc/hosts
192.168.1.30 gtm
192.168.1.31 gtm_standby
192.168.1.32 cd1
192.168.1.33 cd2
192.168.1.34 cd3
执行完上述步骤时，所有主机重启
初始化
[gtm]
# su - osdba
$ initgtm -Z gtm -D /home/osdba/gtm
$ vim /home/osdba/gtm/gtm.conf
nodename = 'one'
listen_addresses = '*'
port = 6666
startup = ACT
[gtm_standby]
# su - osdba
$ initgtm -Z gtm -D /home/osdba/gtm_standby
$ vim /home/osdba/gtm_standby/gtm.conf
nodename = 'two'
listen_addresses = '*'
port = 6666
startup = STANDBY
active_host = 'gtm'
active_port = 6666
[cd1|cd2|cd3]
[cd1|cd2|cd3] 命令都要执行
$ initgtm -Z gtm_proxy -D /home/osdba/gtm_proxy
[cd1]
$ vim /home/osdba/gtm_proxy/gtm_proxy.conf
nodename = 'gtmproxy01'
port = 6666
gtm_host = 'gtm'
gtm_port = 6666
[cd2]
$ vim /home/osdba/gtm_proxy/gtm_proxy.conf
nodename = 'gtmproxy02'
port = 6666
gtm_host = 'gtm'
gtm_port = 6666
[cd3]
$ vim /home/osdba/gtm_proxy/gtm_proxy.conf
nodename = 'gtmproxy03'
port = 6666
gtm_host = 'gtm'
gtm_port = 6666
[cd1]
$ initdb --nodename co1 -D /home/osdba/coordinator
$ initdb --nodename dn1 -D /home/osdba/pgdata
[cd2]
$ initdb --nodename co2 -D /home/osdba/coordinator
$ initdb --nodename dn2 -D /home/osdba/pgdata
[cd3]
$ initdb --nodename co3 -D /home/osdba/coordinator
$ initdb --nodename dn3 -D /home/osdba/pgdata
$ vim /home/osdba/coordinator/postgresql.conf
listen_addresses = '*'
port = 5432
logging_collector = on
gtm_host = 'gtm'
gtm_port = 6666
pgxc_node_name = 'coX'
对于cd1来说就是”co1”,对于cd2来说，就是”co2”,对于cd3来说就是”co3”
$ vim /home/osdba/pgdata/postgresql.conf
listen_addresses = '*'
port = 5433
logging_collector = on
gtm_host = 'gtm'
gtm_port = 6666
pgxc_node_name = 'dnX'
对于cd1来说就是”dn1”,对于cd2来说，就是”dn2”,对于cd3来说就是”dn3”
[cd1|cd2|cd3]的postgres添加访问权限
vim pg_hba.conf
host all all 192.168.1.1/24 trust
如果不添加访问权限，后面会报错，“CONTEXT: SQL statement "EXECUTE DIRECT ON (co2) 'SELECT pg_catalog.pg_try_advisory_xact_lock_shared(65535, 0)'"”
现在安装就成功了，下一步就是集群的启动。
集群启动
参考集群启动
配置集群节点信息
[cd1|cd2|cd3]
节点都要登录到Coordinator信息
$ psql -p 5432 postgres
=# create node dn1 with(type='datanode',host='cd1',port=5433);
=# create node dn2 with(type='datanode',host='cd2',port=5433);
=# create node dn3 with(type='datanode',host='cd3',port=5433);
=# create node co1 with(type='coordinator',host='cd1',port=5432);
=# create node co2 with(type='coordinator',host='cd2',port=5432);
=# create node co3 with(type='coordinator',host='cd3',port=5432);
=# select pgxc_pool_reload();
=# select pg_reload_conf();
插入后结果如下
=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+-----------+----------------+------------------+-------------
dn1 | D | 5433 | cd1 | f | f | -560021589
dn2 | D | 5433 | cd2 | f | f | 352366662
dn3 | D | 5433 | cd3 | f | f | -700122826
co2 | C | 5432 | cd2 | f | f | 474101254
co3 | C | 5432 | cd3 | f | f | -1046823559
co1 | C | 5432 | cd1 | f | f | 1344656819
可能中间有报错，在cd1上报”co1”存在，需要修改co1的nodehost参数，在cd2执行，报”co2”存在，就要修改”co2”的nodehost参数，在cd3上报”co3”存在，需要修改co3的nodehost参数
[cd1|cd2|cd3]
[cd1|cd2|cd3]
节点都要登录到Datanode信息
$ psql -p 5433 postgres
=# select pg_reload_conf();
语法
当前都在cd1上面操作
$ psql -p 5432 postgres
=# create database tutorial;
$ psql -p 5432 tutorial;
=# create table t_1(id int,info text);
=# insert into t_1 select generate_series(1,10000),’good night’;
=# select count(1) from t_1;
10000
$ psql -p 5433 tutorial;
=# select count(1) from t_1;
3235
说明在节点一上存了3000多条。
https://sourceforge.net/projects/postgres-xc/
https://www.2cto.com/database/201508/427109.html
https://www.2cto.com/database/201508/427113.html