centos6.5下postgres-XC集群安装与配置

来源：互联网发布：怎么理解站外优化编辑：程序博客网时间：2024/05/13 15:09

前言

最近在学习postgresql，工作中需要，买了一本《PostgreSQL修炼之道》按照上面操作，几次按照上面的都是失败。后面查看很多资料后，才成功。这是根据这本书第十九章内容修改的地方，希望给广大学习爱好者帮助。最后希望那些作者，把书写的详细一些，把自己做成功的案例写出来。

一、系统环境

系统平台:centos 6.5

postgres-XC版本: pgxc-v1.2.1.tar.gz

防火墙关闭 selinux设置SELINUX=disabled

二、集群规划

主机名

IP地址

角色

端口

nodename

数据目录

gtm

172.16.0.101

Gtm

6666

gtm

/home/pgxc/gtm

standby

172.16.0.102

Gtm的备库

6666

standby

/home/pgxc/gtm_standby

cd1

172.16.0.103

Coordinator

5432

co1

/home/pgxc/coordinator

Datanode

5433

dn1

/home/pgxc/pgdata

Gtm Proxy

6666

gtmproxy01

/home/pgxc/gtm_proxy

cd2

172.16.0.104

Coordinator

5432

co2

/home/pgxc/coordinator

Datanode

5433

dn2

/home/pgxc/pgdata

Gtm Proxy

6666

gtmproxy02

/home/pgxc/gtm_proxy

cd3

172.16.0.105

Coordinator

5432

co3

/home/pgxc/coordinator

Datanode

5433

dn3

/home/pgxc/pgdata

Gtm Proxy

6666

gtmproxy03

/home/pgxc/gtm_proxy

三、安装依赖包（五台操作）

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

四、增加用户（五台操作）

groupadd pgxc

useradd pgxc -g pgxc

passwd pgxc

五、源码安装（五台操作）

tar zxvf pgxc-v1.2.1.tar.gz

cd postgres-xc-1.2.1/

./configure --prefix=/opt/pgxc --with-perl --with-python

gmake

gmake install

六、配置环境变量（五台操作）

su - pgxc

vi .bash_profile

export PGHOME=/opt/pgxc

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

七、初始化GTM

在172.16.0.101这台机器上运行如下命令:

[root@gtm ~]# su - pgxc

[pgxc@gtm ~]$ initgtm -Z gtm -D /home/pgxc/gtm

编辑/home/pgxc/gtm/gtm.conf文件，如下：

nodename = 'gtm'

listen_addresses = '*'

port = 6666

startup = ACT

配置项的说明如下。

nodename:指定节点的名称，可能指定为任意的一个名称，不能与其他节点的名称重复。

listen_address:GTM监听的IP地址,“*”在所有的IP地址上监听

port:GTM监控的端口

startup:确定GTM启动后是主库还是standby。如果是主库，设置为“ACT”，如果是standby,则设置为“STANDBY”。

八、初始化GTM的备库

在172.16.0.102这台机器上运行如下命令:

[root@standby ~]# su - pgxc

[pgxc@standby ~]$ initgtm -Z gtm -D /home/pgxc/gtm_standby

编辑/home/pgxc/gtm_standby/gtm.conf文件，如下:

nodename = 'standby'

listen_addresses = '*'

port = 6666

startup = STANDBY

active_host = '172.16.0.101'

active_port =6666

配置项的说明如下。

startup:因为是GTM的备库，所以要设置为“STANDBY”。

active_host:指定连接GTM主库的IP地址

active_port:指定连接GTM备库的端口。

九、初始化GTM Proxy

在172.16.0.103，172.16.0.104，172.16.0.105这三台机器pgxc用户上，分别执行以下命令:

initgtm -Z gtm_proxy -D /home/pgxc/gtm_proxy

172.16.0.103中配置文件 /home/pgxc/gtm_proxy/gtm_proxy.conf的内容如下:

nodename = 'gtmproxy01'

port = 6666

gtm_host = '172.16.0.101'

gtm_port = 6666

172.16.0.104中配置文件 /home/pgxc/gtm_proxy/gtm_proxy.conf的内容如下:

nodename = 'gtmproxy02'

port = 6666

gtm_host = '172.16.0.101'

gtm_port = 6666

172.16.0.105中配置文件 /home/pgxc/gtm_proxy/gtm_proxy.conf的内容如下:

nodename = 'gtmproxy03'

port = 6666

gtm_host = '172.16.0.101'

gtm_port = 6666

十、初始化Coordinator 、数据节点

1.在172.16.0.103这台机器上运行如下命令：

initdb -D /home/pgxc/coordinator --nodename co1 -E UTF8 --local=C -U pgxc -W

initdb -D /home/pgxc/pgdata --nodename dn1 -E UTF8 --local=C -U pgxc -W

172.16.0.103这台机器上 coordinator配置文件/home/pgxc/coordinator/postgresql.conf的内容如下:

# - Connection Settings -

listen_addresses = '*'

port = 5432

max_connections = 100

# DATA NODES AND CONNECTION POOLING

#-------------------------------------------

pooler_port = 6667

min_pool_size = 1

max_pool_size = 100

# GTM CONNECTION

#----------------------

gtm_host = '172.16.0.101'

gtm_port = 6666

pgxc_node_name = 'co1'

172.16.0.103这台机器上 coordinator配置文件/home/pgxc/coordinator/pg_hba.conf的内容如下:

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all all 172.16.0.0/24 trust

host all all 0.0.0.0/0 md5

172.16.0.103这台机器上数据节点配置文件/home/pgxc/pgdata/postgresql.conf的内容如下:

# - Connection Settings -

listen_addresses = '*'

port = 5433

max_connections = 100

# DATA NODES AND CONNECTION POOLING

#----------------------------------------

pooler_port = 6668

#min_pool_size = 1

max_pool_size = 100

# GTM CONNECTION

#---------------------------

gtm_host = '172.16.0.101'

gtm_port = 6666

pgxc_node_name = 'dn1'

172.16.0.103这台机器上数据节点配置文件/home/pgxc/pgdata/pg_hba.conf的内容如下:

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all all 172.16.0.0/24 trust

host all all 0.0.0.0/0 md5

2.在172.16.0.104这台机器上运行如下命令：

initdb -D /home/pgxc/coordinator --nodename co2 -E UTF8 --local=C -U pgxc -W

initdb -D /home/pgxc/pgdata --nodename dn2 -E UTF8 --local=C -U pgxc -W

172.16.0.104这台机器上 coordinator配置文件/home/pgxc/coordinator/postgresql.conf的内容如下:

# - Connection Settings -

listen_addresses = '*'

port = 5432

max_connections = 100

# DATA NODES AND CONNECTION POOLING

#-------------------------------------------

pooler_port = 6667

min_pool_size = 1

max_pool_size = 100

# GTM CONNECTION

#----------------------

gtm_host = '172.16.0.101'

gtm_port = 6666

pgxc_node_name = 'co2'

172.16.0.104这台机器上 coordinator配置文件/home/pgxc/coordinator/pg_hba.conf的内容如下:

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all all 172.16.0.0/24 trust

host all all 0.0.0.0/0 md5

172.16.0.104这台机器上数据节点配置文件/home/pgxc/pgdata/postgresql.conf的内容如下:

# - Connection Settings -

listen_addresses = '*'

port = 5433

max_connections = 100

# DATA NODES AND CONNECTION POOLING

#----------------------------------------

pooler_port = 6668

#min_pool_size = 1

max_pool_size = 100

# GTM CONNECTION

#---------------------------

gtm_host = '172.16.0.101'

gtm_port = 6666

pgxc_node_name = 'dn2'

172.16.0.104这台机器上数据节点配置文件/home/pgxc/pgdata/pg_hba.conf的内容如下:

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all all 172.16.0.0/24 trust

host all all 0.0.0.0/0 md5

3. 在172.16.0.105这台机器上运行如下命令：

initdb -D /home/pgxc/coordinator --nodename co3 -E UTF8 --local=C -U pgxc -W

initdb -D /home/pgxc/pgdata --nodename dn3 -E UTF8 --local=C -U pgxc -W

172.16.0.105这台机器上 coordinator配置文件/home/pgxc/coordinator/postgresql.conf的内容如下:

# - Connection Settings -

listen_addresses = '*'

port = 5432

max_connections = 100

# DATA NODES AND CONNECTION POOLING

#-------------------------------------------

pooler_port = 6667

min_pool_size = 1

max_pool_size = 100

# GTM CONNECTION

#----------------------

gtm_host = '172.16.0.101'

gtm_port = 6666

pgxc_node_name = 'co3'

172.16.0.105这台机器上 coordinator配置文件/home/pgxc/coordinator/pg_hba.conf的内容如下:

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all all 172.16.0.0/24 trust

host all all 0.0.0.0/0 md5

172.16.0.105这台机器上数据节点配置文件/home/pgxc/pgdata/postgresql.conf的内容如下:

# - Connection Settings -

listen_addresses = '*'

port = 5433

max_connections = 100

# DATA NODES AND CONNECTION POOLING

#----------------------------------------

pooler_port = 6668

#min_pool_size = 1

max_pool_size = 100

# GTM CONNECTION

#---------------------------

gtm_host = '172.16.0.101'

gtm_port = 6666

pgxc_node_name = 'dn3'

172.16.0.105这台机器上数据节点配置文件/home/pgxc/pgdata/pg_hba.conf的内容如下:

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all all 172.16.0.0/24 trust

host all all 0.0.0.0/0 md5

十一、启动集群

启动集群的顺序为:

GTM

GTM Standby

GTP-Proxy

Datanodes

Coordinators

启动上面示例的集群的方法如下。

在172.16.0.101机器启动gtm，命令如下:

[pgxc@gtm ~]$ gtm_ctl -Z gtm start -D /home/pgxc/gtm

在172.16.0.102机器启动gtm standby,命令如下：

[pgxc@standby ~]$ gtm_ctl -Z gtm_standby start -D /home/pgxc/gtm_standby

可以用下面的命令查看gtm和gtm standby是否启动：

[pgxc@gtm ~]$ gtm_ctl -Z gtm status -D /home/pgxc/gtm

gtm_ctl: server is running (PID: 2091)

"-D" "/home/pgxc/gtm"

1 master

[pgxc@standby ~]$ gtm_ctl -Z gtm_standby status -D /home/pgxc/gtm_standby

gtm_ctl: server is running (PID: 2095)

"-D" "/home/pgxc/gtm_standby"

0 slave

以上说明启动成功。

启动完gtm和gtm_standby后，就可以启动gtm_proxy了。在172.17.0.103，172.16.0.104，172.16.0.105这三台机器上运行如下命令:

gtm_ctl -Z gtm_proxy start -D /home/pgxc/gtm_proxy

启动完gtm_proxy就可以启动Datanodes了。在上述三台机器分别运行如下命令来启动：

pg_ctl start -D /home/pgxc/pgdata -Z datanode

最后启动Coordinators。在上述三台机器上运行如下命令启动:

pg_ctl start -D /home/pgxc/coordinator -Z coordinator

十二、配置集群节点信息

在各个Coordinator上，执行如下命令：

下面就执行172.16.0.103主机器上的。其它的大家自己操作。

psql -p 5432 postgres

[pgxc@cd1 ~]$ psql -p 5432 postgres

psql (PGXC , based on PG 9.3.2)

Type "help" for help.

postgres=# select * from pgxc_node;

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

co1 | C | 5432 | localhost | f | f

| 1344656819

postgres=#create node dn1 with(type='datanode',host='172.16.0.103',port=5433,primary,preferred);

postgres=#create node dn2 with(type='datanode',host='172.16.0.104',port=5433);

postgres=#create node dn3 with(type='datanode',host='172.16.0.105',port=5433);

postgres=#create node co2 with(type='coordinator',host='172.16.0.104',port=5432);

postgres=#create node co3 with(type='coordinator',host='172.16.0.105',port=5432);

postgres=# select * from pgxc_node;

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

co1 | C | 5432 | localhost | f | f | 1344656819

dn1 | D | 5433 | 172.16.0.103 | t | t | -560021589

dn2 | D | 5433 | 172.16.0.104 | f | f | 352366662

dn3 | D | 5433 | 172.16.0.105 | f | f | -700122826

co2 | C | 5432 | 172.16.0.104 | f | f | 474101254

co3 | C | 5432 | 172.16.0.105 | f | f | -1046823559

(6 rows)

postgres=#select pgxc_pool_reload();

十三、测试

[pgxc@cd1 ~]$ psql -p 5432 postgres

psql (PGXC , based on PG 9.3.2)

Type "help" for help.

postgres=# select * from pgxc_node;

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

co1 | C | 5432 | localhost | f | f | 1344656819

dn1 | D | 5433 | 172.16.0.103 | t | t | -560021589

dn2 | D | 5433 | 172.16.0.104 | f | f | 352366662

dn3 | D | 5433 | 172.16.0.105 | f | f | -700122826

co2 | C | 5432 | 172.16.0.104 | f | f | 474101254

co3 | C | 5432 | 172.16.0.105 | f | f | -1046823559

(6 rows)

postgres=# create database text;

CREATE DATABASE

postgres=# \l

List of databases

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

postgres | pgxc | UTF8 | C | C |

template0 | pgxc | UTF8 | C | C | =c/pgxc +

template1 | pgxc | UTF8 | C | C | =c/pgxc +

text | pgxc | UTF8 | C | C |

(4 rows)

以上说明全部安装成功。

1 0