前言
最近在学习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;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+--------------+----------------+------------------+-------------
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;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+--------------+----------------+------------------+-------------
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;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+--------------+----------------+------------------+-------------
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
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
postgres | pgxc | UTF8 | C | C |
template0 | pgxc | UTF8 | C | C | =c/pgxc +
| | | | | pgxc=CTc/pgxc
template1 | pgxc | UTF8 | C | C | =c/pgxc +
| | | | | pgxc=CTc/pgxc
text | pgxc | UTF8 | C | C |
(4 rows)
以上说明全部安装成功。
本文转自 jxzhfei 51CTO博客，原文链接:http://blog.51cto.com/jxzhfei/1679908