CentOS 6.5下PostgreSQL 故障切换实现 - 数据库编程

.0.0:9898? ? ? ? ? ? ? ? 0.0.0.0:*? ? ? ? ? ? ? ? ? LISTEN? ? ? 16664/pgpool? ? ? ?
?tcp? ? ? ? 0? ? ? 0 0.0.0.0:9999? ? ? ? ? ? ? ? 0.0.0.0:*? ? ? ? ? ? ? ? ? LISTEN? ? ? 16664/pgpool? ? ? ?
?tcp? ? ? ? 0? ? ? 0 :::22? ? ? ? ? ? ? ? ? ? ? :::*? ? ? ? ? ? ? ? ? ? ? ? LISTEN? ? ? -? ? ? ? ? ? ? ? ? ?
?tcp? ? ? ? 0? ? ? 0 ::1:25? ? ? ? ? ? ? ? ? ? ? :::*? ? ? ? ? ? ? ? ? ? ? ? LISTEN? ? ? -? ? ? ? ? ? ? ? ? ?
?tcp? ? ? ? 0? ? ? 0 :::9999? ? ? ? ? ? ? ? ? ? :::*? ? ? ? ? ? ? ? ? ? ? ? LISTEN? ? ? 16664/pgpool

?
登录

[postgres@db1 etc]$ psql -U postgres -h db1 -p 9999
?psql (9.2.1)
?Type "help" for help.
?postgres=# show pool_nodes;
?node_id | hostname | port | status | lb_weight |? role? ?
---------+----------+------+--------+-----------+---------
?0? ? ? | db1? ? ? | 5432 | 2? ? ? | 0.500000? | primary
?1? ? ? | db2? ? ? | 5432 | 2? ? ? | 0.500000? | standby
(2 rows)
?postgres=# create database db0;
?CREATE DATABASE

2：启动
3：死啦

测试可以登录，可以读写

四、故障切换

首先停止DBmaster
[postgres@db1 etc]$ pg_ctl -m fast stop

登录查看

[postgres@db1 etc]$ psql -U postgres -h db1 -p 9999
?postgres=# show pool_nodes;
?node_id | hostname | port | status | lb_weight |? role? ?
---------+----------+------+--------+-----------+---------
?0? ? ? | db1? ? ? | 5432 | 3? ? ? | 0.500000? | standby
?1? ? ? | db2? ? ? | 5432 | 2? ? ? | 0.500000? | primary
(2 rows)

此时DBslave显示的日志信息

[postgres@db2 data]$ FATAL:? replication terminated by primary server
LOG:? record with zero length at 0/10000FE0
LOG:? trigger file found: /tmp/trigger_file
LOG:? redo done at 0/10000F80
LOG:? last completed transaction was at log time 2015-06-17 11:05:44.379009+08
LOG:? selected new timeline ID: 2
LOG:? archive recovery complete
LOG:? database system is ready to accept connections
LOG:? autovacuum launcher started

DBmaster 已经死啦，状态切换为standby；DBslave切换为primary；测试可读写
日志提示，发现 trigger_file文件，进行切换
DBslave 切换为主服务器
recover.conf自动更改为recover.done

[postgres@db2 data]$ ll /opt/data/recovery.done
recovery.done

故障切换成功

五、恢复DBmaster

1、同步DBmaster至DBslave

pg_basebackup -D $PGDATA -Fp -Xs -v? -h db1 -p 5432 -U postgres

2、修改配置文件

listen_addresses = '*'
port = 5432
hot_standby = on

3、修改recover文件

mv recover.done? recover.conf
vim recover.conf
primary_conninfo = 'host=172.16.0.133 port=5432 user=postgres'

4、启动DBslave
5、添加node

pcp_attach_node -d 5 db1 9898 postgres postgres 0
pcp_attach_node -d 5 db1 9898 postgres postgres 1

登录查看

postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight |? role? ?
---------+----------+------+--------+-----------+---------
?0? ? ? | db1? ? ? | 5432 | 2? ? ? | 0.500000? | primary
?1? ? ? | db2? ? ? | 5432 | 2? ? ? | 0.500000? | standby
(2 rows)

恢复正常
Game Over ！

------------------------------------华丽丽的分割线------------------------------------

CentOS 6.5下PostgreSQL 故障切换实现(二)