centos6.5下postgres-XC集群安装与配置（两台）
一、系统环境系统平台:centos 6.5
postgres-XC版本:pgxc-v1.2.1.tar.gz
防火墙关闭 selinux设置SELINUX=disabled
主机名
IP地址
角色
端口
nodename
数据目录
postgresql01
172.16.0.134
GTM
6666
gtm
/gtm
Coordinator
1921
coord1
/coordinator/cd1
Coordinator
1925
coord2
/coordinator/cd2
postgresql02
172.16.0.135
Datanode
15431
db1
/datanode/dn1
Datanode
15432
db2
/datanode/dn2
二、安装依赖包
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
五、创建存放路径
在172.16.0.134主机操作如下：
# mkdir /gtm
# mkdir -p /coordinator/cd1
# mkdir -p /coordinator/cd2
# chown -R pgxc:pgxc /gtm
# chown -R pgxc:pgxc /coordinator
在172.16.0.135主机操作如下：
# mkdir -p /datanode/dn1
# mkdir -p /datanode/dn2
# chown -R pgxc:pgxc /datanode
六、配置环境变量
在172.16.0.134主机上
$ vi .bash_profile
export PGPORT=1921
export PGDATA=/pgsql/data
export.utf8
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
$ vi .bash_profile
export PGPORT=15431
export PGDATA=/datanode/dn1
export.utf8
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
七、初始化
1.在172.16.0.134操作如下：
# su - pgxc
# initgtm -Z gtm -D /gtm
# initdb -D /coordinator/cd1 --nodename coord1 -E UTF8 --locale=C -U pgxc -W
# initdb -D /coordinator/cd2 --nodename coord2 -E UTF8 --locale=C -U pgxc -W
2.配置参数
配置gtm
$ cd /gtm/
$ vi gtm.conf
nodename = 'gtm'
listen_addresses = '*'
port = 6666
startup = ACT
配置coordinator
$ cd /coordinator/cd1/
$ vi postgresql.conf
# - Connection Settings -
listen_addresses = '*'
port = 1921
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.134'
gtm_port = 6666
pgxc_node_name = 'coord1'
$ vi 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
$ cd /coordinator/cd2/
$ vi postgresql.conf
# - Connection Settings -
listen_addresses = '*'
port = 1925
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.134'
gtm_port = 6666
pgxc_node_name = 'coord2'
$ vi 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.135操作如下:
# su - pgxc
$ initdb -D /datanode/dn1 --nodename db1 -E UTF8 --local=C -U pgxc -W
$ initdb -D /datanode/dn2 --nodename db2 -E UTF8 --local=C -U pgxc -W
4.配置参数
$ cd /datanode/dn1/
$ vi postgresql.conf
CONNECTIONS AND AUTHENTICATION
#------------------------------------
listen_addresses = '*'
port = 15431
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.134'
gtm_port = 6666
pgxc_node_name = 'db1'
$ vi 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
$ cd /datanode/dn2/
# - Connection Settings -
listen_addresses = '*'
port = 15432
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.134'
gtm_port = 6666
pgxc_node_name = 'db2'
$ vi 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
八、启动
1.在172.16.0.134启动gtm
$ gtm -D /gtm &
2.查看是否启动成功
$ gtm_ctl status -Z gtm -D /gtm
gtm_ctl: server is running (PID: 2153)
"-D" "/gtm"
1 master
3.在172.16.0.135主机启动datanode
$ pg_ctl start -D /datanode/dn1 -Z datanode
$ pg_ctl start -D /datanode/dn2 -Z datanode
4.查看是否启动成功
$ ps -ef | grep pgxc
root 208720470 21:54 pts/0 00:00:00 su - pgxc
pgxc 208820870 21:54 pts/0 00:00:00 -bash
pgxc 2168 10 22:13 pts/0 00:00:00 /opt/pgxc/bin/postgres --datanode -D /datanode/dn1
pgxc 217021680 22:13 ? 00:00:00 postgres: checkpointer process
pgxc 217121680 22:13 ? 00:00:00 postgres: writer process
pgxc 217221680 22:13 ? 00:00:00 postgres: wal writer process
pgxc 217321680 22:13 ? 00:00:00 postgres: autovacuum launcher process
pgxc 217421680 22:13 ? 00:00:00 postgres: stats collector process
pgxc 2179 10 22:14 pts/0 00:00:00 /opt/pgxc/bin/postgres --datanode -D /datanode/dn2
pgxc 218121790 22:14 ? 00:00:00 postgres: checkpointer process
pgxc 218221790 22:14 ? 00:00:00 postgres: writer process
pgxc 218321790 22:14 ? 00:00:00 postgres: wal writer process
pgxc 218421790 22:14 ? 00:00:00 postgres: autovacuum launcher process
pgxc 218521790 22:14 ? 00:00:00 postgres: stats collector process
pgxc 219020880 22:14 pts/0 00:00:00 ps -ef
pgxc 219120880 22:14 pts/0 00:00:00 grep pgxc
5.在172.16.0.134主机启动coordinator
$ pg_ctl start -D /coordinator/cd1 -Z coordinator
$ pg_ctl start -D /coordinator/cd2 -Z coordinator
6.查看是否成功
$ ps -ef | grep pgxc
root 205520360 21:42 pts/0 00:00:00 su - pgxc
pgxc 205620550 21:42 pts/0 00:00:00 -bash
pgxc 215320560 22:11 pts/0 00:00:00 gtm -D /gtm
pgxc 2168 10 22:16 pts/0 00:00:00 /opt/pgxc/bin/postgres --coordinator -D /coordinator/cd1
pgxc 217021680 22:16 ? 00:00:00 postgres: pooler process
pgxc 217121680 22:16 ? 00:00:00 postgres: checkpointer process
pgxc 217221680 22:16 ? 00:00:00 postgres: writer process
pgxc 217321680 22:16 ? 00:00:00 postgres: wal writer process
pgxc 217421680 22:16 ? 00:00:00 postgres: autovacuum launcher process
pgxc 217521680 22:16 ? 00:00:00 postgres: stats collector process
pgxc 2180 10 22:17 pts/0 00:00:00 /opt/pgxc/bin/postgres --coordinator -D /coordinator/cd2
pgxc 218221800 22:17 ? 00:00:00 postgres: pooler process
pgxc 218321800 22:17 ? 00:00:00 postgres: checkpointer process
pgxc 218421800 22:17 ? 00:00:00 postgres: writer process
pgxc 218521800 22:17 ? 00:00:00 postgres: wal writer process
pgxc 218621800 22:17 ? 00:00:00 postgres: autovacuum launcher process
pgxc 218721800 22:17 ? 00:00:00 postgres: stats collector process
pgxc 220120560 22:17 pts/0 00:00:00 ps -ef
pgxc 220220560 22:17 pts/0 00:00:00 grep pgxc
九、配置集群节点信息
在172.16.0.134配置集群信息
$ psql -p1921 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
-----------+-----------+-----------+--------------+----------------+------------------+-------------
coord1 | C | 5432 | localhost | f | f |1885696643
postgres=# create node db1 with(type='datanode',host='172.16.0.135',port=15431,primary,preferred);
postgres=# create node db2 with(type='datanode',host='172.16.0.135',port=15432);
postgres=# create node coord2 with(type='coordinator',host='172.16.0.134',port=1925);
postgres=# select * from pgxc_node;
node_name | node_type | node_port |node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+--------------+----------------+------------------+-------------
coord1 | C | 5432 | localhost | f | f |1885696643
db1 | D | 15431 | 172.16.0.135 | t | t | -2885965
db2 | D | 15432 | 172.16.0.135 | f | f | -79866771
coord2 | C | 1925 | 172.16.0.134 | f | f | -1197102633
postgres=# select pgxc_pool_reload();
十、测试
$ psql -p 1921 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
-----------+-----------+-----------+--------------+----------------+------------------+-------------
coord1 | C | 5432 | localhost | f | f |1885696643
db1 | D | 15431 | 172.16.0.135 | t | t | -2885965
db2 | D | 15432 | 172.16.0.135 | f | f | -79866771
coord2 | C | 1925 | 172.16.0.134 | f | f | -1197102633
postgres=# create database test_xc;
CREATE DATABASE
postgres=#
说明上面成功
页:
[1]