实验环境：
PGPOOII 主机:108.88.3.246
primary postgresql 主机:108.88.3.237
standby postgresql 主机:108.88.3.236
Setp 1、 分别在主从和PGPOOL节点安装postgresql数据库
#tar -jxvf postgresql-9.1.3.tar.bz2
#cd postgresql-9.1.3
#./configure --prefix=/u03/postgresql
#make
#make install
#mkdir -p /u03/postgresql/data
#chown postgres /u03/postgresql/data/
#mkdir -p /backup/archive
#chown postgres -R /backup/archive/
#su - postgres
#unset LANG
#/u03/postgresql/bin/initdb -D /u03/postgresql/data/ -E UTF8
#vi pg_hba.conf //修改主节点pg_hba.conf文件
-------pg_hba.conf 主节点--------------------------------
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres trust
host replication postgres 108.88.3.236/32 trust
host all all 108.88.3.246/32 trust
#host replication postgres ::1/128 trust
－－－－pg_hba.conf------------------------------------------------
#vi postgresql.conf //修改主节点postgresql.conf文件
--------主节点 postgresql.conf------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432 # (change requires restart)
.........
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = 'cp %p /backup/archive/%f' # command to use to archive a logf
ile segment
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
# - Settings -
wal_level = hot_standby
# - Master Server -
# These settings are ignored on a standby server
max_wal_senders = 3
-----------主节点 postgresql.conf--------------------------------
#/u03/postgresql/bin/pg_ctl -D /u03/postgresql/data -l logfile start //启动主节点postgresql
主节点安装完成
setup 2、备节点安装postgresql
#tar -jxvf postgresql-9.1.3.tar.bz2
#cd postgresql-9.1.3
#./configure --prefix=/u03/postgresql
#make
#make install
#mkdir -p /u03/postgresql/data
#chown postgres /u03/postgresql/data/
#mkdir -p /backup/archive
#chown postgres -R /backup/archive/
#su - postgres
setup 3、主节点备份pgdata
#su - postgres
#psql -c "SELECT pg_start_backup('label', true)"
#tar -zcvf data_primay.tar.gz data/
#psql -c "SELECT pg_stop_backup()"
#cd /backup/
#tar -zcvf archive.tar.gz archive/
setup 4、将主节点的data,archice包分别解压到备用节点(注意相关文件权限)
#cd /u03/postgresql/
#mv data data_bak
#scp 108.88.3.237:/u03/postgresql/data_primay.tar.gz .
#tar -zxvf data_primay.tar.gz
#cd data
#rm postmaster.pid
#vi pg_hba.conf
------------------------standby pg_hba.conf----------------------
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres trust
host replication postgres 108.88.3.236/32 trust
host replication postgres 108.88.3.237/32 trust
host all all 108.88.3.246/32 trust
#host replication postgres ::1/128 trust
-----------------------standby pg_hba.conf--------------------------
# vi postgresql.conf
----------------------standby postgresql.conf ----------------------
........................
# - Standby Servers -
# These settings are ignored on a master server
hot_standby = on # "on" allows queries during recovery
.......................
----------------------standby postgresql.conf ----------------------
# vi recovery.conf
----------------------standby recovery.conf ------------------------------------------------
# Note that recovery.conf must be in $PGDATA directory.
# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode = 'on'
# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo = 'host=108.88.3.237 port=5432 user=postgres'
# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/backup/trigger'
# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = 'cp /backup/archive/%f "%p"'
----------------------standby recovery.conf ------------------------------------------------------------
# scp 108.88.3.237:/backup/archive.tar.gz /backup/.
# tar -zxvf archive.tar.gz
setup 5、启动standby节点
# /u03/postgresql/bin/pg_ctl -D /u03/postgresql/data -l logfile start
Setup 6、分别在主从节点查看当前xlog是否一致
$ psql -c "SELECT pg_current_xlog_location()" (primary host)
pg_current_xlog_location
--------------------------
0/2000000
(1 row)
$ psql -c "select pg_last_xlog_receive_location()" (standby host)
pg_last_xlog_receive_location
-------------------------------
0/2000000
(1 row)
$ psql -c "select pg_last_xlog_replay_location()" (standby host)
pg_last_xlog_replay_location
------------------------------
0/2000000
(1 row)
# The displayed LSNs indicate the byte position that the standby server has
# written up to in the xlogs.
[primary] $ ps -ef | grep sender
postgres 6879 6831 0 10:31 ? 00:00:00 postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000
[standby] $ ps -ef | grep receiver
postgres 6878 6872 1 10:31 ? 00:00:01 postgres: wal receiver process streaming 0/2000000
到这里主节流复完成
setup 7、安装PGPOOll
#tar -zxvf pgpool-II-3.2.1.tar.gz
#cd pgpool-II-3.2.1
#./configure
#locate libpq.so
#./configure --with-pgsql=/u03/postgresql
#make
#make install
mkdir -p /var/run/pgpool
#cp /usr/local/etc/pgpool.conf.sample-stream /usr/local/etc/pgpool.conf
#vi pgpool.conf
-----------------pgpoool.conf----------------------------
# - pgpool Connection Settings -
listen_addresses = '*'
# Host name or IP address to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
port = 9999
# Port number
# (change requires restart)
socket_dir = '/tmp'
# Unix domain socket path
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
# - pgpool Communication Manager Connection Settings -
pcp_port = 9898
# Port number for pcp
# (change requires restart)
pcp_socket_dir = '/tmp'
# Unix domain socket path for pcp
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
# - Backend Connection Settings -
backend_hostname0 = '108.88.3.237'
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_weight0 = 1
# Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/u03/postgresql/data'
# Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Controls various backend behavior
# ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
backend_hostname1 = '108.88.3.236'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/u03/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
................................................................................................................
# - Streaming -
sr_check_period = 10
# Streaming replication check period
# Disabled (0) by default
sr_check_user = 'postgres'
# Streaming replication check user
# This is neccessary even if you disable streaming
# replication delay check by sr_check_period = 0
sr_check_password = ''
# Password for streaming replication check user
delay_threshold = 10000000
# Threshold before not dispatching query to standby node
# Unit is in bytes
# Disabled (0) by default
.........................................
-----------------------------------------------------------------------------------------------
# cd /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf
#vi pcp.conf
-------------------pcp.conf-------------------------------
# Example:
postgres:e8a48653851e28c69d0506508fb27fc5
------------------pcp.conf--------------------------------
启动pgpooll
[root@localhost etc]# /usr/local/bin/pgpool -n -f /usr/local/etc/pgpool.conf
[postgres@localhost etc]$ psql -p 9999 -c "show pool_nodes;" -Upostgres
node_id | hostname | port | status | lb_weight | role
---------+--------------+------+--------+-----------+---------
0 | 108.88.3.236 | 5432 | 2 | 0.500000 | standby
1 | 108.88.3.237 | 5432 | 2 | 0.500000 | primary
(2 rows)
可以尝试连按9999端口及写入数据，呵呵，应该可以了，
测试关闭主节点提升从节点为主节点。
1、在主节关闭postgresql数据库
/u03/postgresql/bin/pg_ctl -D /u03/postgresql/data -l logfile stop -m fast
2、在从节点中添加trigger文件
touch /backup/trigger
3、查看从节点是否提为主节点，
ps -ef ^post
4、查看pgooll中的各节点状态
[postgres@localhost etc]$ psql -p 9999 -c "show pool_nodes;" -Upostgres
node_id | hostname | port | status | lb_weight | role
---------+--------------+------+--------+-----------+---------
0 | 108.88.3.236 | 5432 | 2 | 0.500000 | primary
1 | 108.88.3.237 | 5432 | 3 | 0.500000 | standby
(2 rows)
5、再次PGPOOL写入数据.
postgres=# insert into test values(1,'ghan');
INSERT 0 1
postgres=# insert into test values(1,'ghan');
INSERT 0 1
postgres=# insert into test values(1,'ghan');
INSERT 0 1
postgres=#
到这里基本测试完成了，还有很多精彩的测试还在后头，，，，
