pgpool流复制&failover故障切换

参看：

http://blog.163.com/czg_e/blog/static/461045612012129105020629/

http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-zh_cn.html#connection_pool_mode

http://dz.sdut.edu.cn/blog/subaochen/?p=430

同一台机子不同端口

下载安装pgpool:

http://blog.163.com/binglan_1208@126/blog/static/234575252014229112048666

配置pool_hba.conf

cp /opt/pgpool/etc/pool_hba.conf.sample /opt/pgpool/etc/pool_hba.conf

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host all all 127.0.0.1 trust

配置pgpool.conf:

cp /opt/pgpool/etc/pool_hba.conf.sample /opt/pgpool/etc/pool_hba.conf

listen_addresses = '*'
port = 9999
pcp_port = 9898
# - Backend Connection Settings -

backend_hostname0 = '127.0.0.1'
                                   # Host name or IP address to connect to for backend 0
backend_port0 = 5001
                                   # Port number for backend 0
backend_weight0 = 1
                                   # Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/home/pg93/ms/master'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
backend_hostname1 = '127.0.0.1'
backend_port1 = 5002
backend_weight1 = 1
backend_data_directory1 = '/home/pg93/ms/slave'
backend_flag1 = 'ALLOW_TO_FAILOVER'

log_statement = on
log_per_node_statement= on
pid_file_name = '/opt/pgpool/pgpool.pid'
logdir = '/opt/pgpool/log'
failover_command = '/opt/pgpool/etc/failedcommand.sh %d %H /tmp/trigger_file'
replication_mode= off
master_slave_mode =on
master_slave_sub_mode = 'stream' 
sr_check_user = 'postgres'
#sr_check_password = ''   #如果是trust则注释掉
load_balance_mode = on
log_connections = on
log_hostname = on
debug_level = 1
delay_threshold = 1000

master:

initdb -D /home/pg93/ms/master -E UTF8 --locale=C -U postgres -W

配置pg_hba.conf

# "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        127.0.0.1/32            trust
host    replication     postgres        ::1/128                 trust
host all all 0.0.0.0/0 trust
host replication postgres 127.0.0.1/32 trust

配置postgresql.conf

listen_addresses = '*' 
port = 5001   
# primary server需要
wal_level = hot_standby
# 这个不是必需的
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.1/main/archive/%f'
# primary server需要
max_wal_senders = 32
# standby server需要

hot_standby = onlogging_collector = on
synchronous_commit = off
wal_keep_segments = 32
synchronous_standby_names = '*'

pg_ctl start -D /home/pg93/ms/master

psql -h 127.0.0.1 -p 5001 -U postgres postgres

select pg_start_backup('tag_xx');

select pg_is_in_backup();#查看是否在备份中backup。返回"t"表示true。“f”表示false。

\q 退出#

cp -r /home/pg93/ms/master/* /home/pg93/ms/slave/.

psql -h 127.0.0.1 -p 5001 -U postgres postgres

select pg_stop_backup();

\q

slaver:

配置postgresql.conf

port = 5002
hot_standby = on

pg_ctl start -D /home/pg93/ms/slave

启动pgpool:

pgpool -nd

检查pgpool节点:

psql -h 127.0.0.1 -p 9999

show pool_nodes;

digoal=# show pool_nodes;
 node_id | hostname  | port | status | lb_weight |  role   
---------+-----------+------+--------+-----------+---------
 0       | 127.0.0.1 | 5001 | 3      | 0.500000  | standby
 1       | 127.0.0.1 | 5002 | 2      | 0.500000  | standby
(2 rows)

测试pgpool流复制:

psql -h 127.0.0.1 -p 9999

create table xxv(a int);

psql -h 127.0.0.1 -p 5002

select  * from xxv;

测试pgpool的failover故障恢复:

pg_ctl -m fast stop -D /home/pg93/ms/master

psql -h 127.0.0.1 -p 9999#如果能够正常访问则无问题。