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;

-----------+-----------+-----------+-----------+----------------+------------------+-------------

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]

节点都要登录到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

postgres-xc install in linux

postgres-xc-install

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像