postgresql主从搭建和主备切换

postgresql standby主备搭建和切换
1主库配置hba文件
 host    replication    postgres        172.17.5.0/24            md5
 host     all            all            172.17.5.0/24            trust
 2配置posgresql。conf文件
listen_addresses = '*'
max_wal_senders = 5
wal_level = hot_standby
3在standby上面生成
pg_basebackup -h 10.2.13.1 -U postgres -F p -P -x -R -D /var/lib/pgsql/9.4/data基础备份
4设置备库recovery.conf文件
-bash-4.1$ cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=postgres password=postgres host=172.17.5.45 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
trigger_file = '/var/lib/pgsql/9.4/data/haha'
5４配置ｃｏｎｆｉｇ文件　ｈｏｔ＿ｓｔａｎｄｂｙ＝ｏｎ
搭建完主库日志文件
 2015-08-14 02:52:04.959 EDT >LOG:  MultiXact member wraparound protections are now enabled
< 2015-08-14 02:52:04.968 EDT >LOG:  autovacuum launcher started
< 2015-08-14 02:52:04.969 EDT >LOG:  database system is ready to accept connectio
备库的日志文件
< 2015-08-14 02:52:04.959 EDT >LOG:  MultiXact member wraparound protections are now enabled
< 2015-08-14 02:52:04.968 EDT >LOG:  autovacuum launcher started
< 2015-08-14 02:52:04.969 EDT >LOG:  database system is ready to accept connections
< 2015-08-14 05:06:25.655 EDT >LOG:  database system was interrupted; last known up at 2015-08-14 03:16:51 EDT
< 2015-08-14 05:06:25.701 EDT >LOG:  entering standby mode
< 2015-08-14 05:06:25.705 EDT >LOG:  redo starts at 0/6000028
< 2015-08-14 05:06:25.707 EDT >LOG:  consistent recovery state reached at 0/60000F0
< 2015-08-14 05:06:25.709 EDT >LOG:  database system is ready to accept read only connections
< 2015-08-14 05:06:25.731 EDT >LOG:  started streaming WAL from primary at 0/7000000 on timeline 2
下面是主备库切换的过程
1主库模拟掉电关闭
-bash-4.1$ /usr/pgsql-9.4/bin/pg_ctl stop
waiting for server to shut down.... done
server stopped
2备库日志文件
 2015-08-14 05:14:59.379 EDT >LOG:  replication terminated by primary server
< 2015-08-14 05:14:59.379 EDT >DETAIL:  End of WAL reached on timeline 2 at 0/7006E48.
< 2015-08-14 05:14:59.380 EDT >FATAL:  could not send end-of-streaming message to primary: no COPY in progress


< 2015-08-14 05:14:59.380 EDT >LOG:  record with zero length at 0/7006E48
< 2015-08-14 05:15:14.403 EDT >FATAL:  could not connect to the primary server: could not connect to server: Connection refused
 Is the server running on host "172.17.5.45" and accepting
 TCP/IP connections on port 5432?
 4建立triigre文件 
 -bash-4.1$ touch /var/lib/pgsql/9.4/data/haha
 查看原备库的日志文件
 < 2015-08-14 05:19:04.973 EDT >LOG:  trigger file found: /var/lib/pgsql/9.4/data/haha
< 2015-08-14 05:19:04.973 EDT >LOG:  redo done at 0/7006DE0
< 2015-08-14 05:19:04.973 EDT >LOG:  last completed transaction was at log time 2015-08-14 05:10:24.457304-04
< 2015-08-14 05:19:04.978 EDT >LOG:  selected new timeline ID: 3
< 2015-08-14 05:19:05.073 EDT >LOG:  archive recovery complete
< 2015-08-14 05:19:05.077 EDT >LOG:  MultiXact member wraparound protections are now enabled
< 2015-08-14 05:19:05.085 EDT >LOG:  database system is ready to accept connections
< 2015-08-14 05:19:05.086 EDT >LOG:  autovacuum launcher started
这时原来的备库已经是readwrite状态了，原来主库切回来还没实现，其实个人感觉重新搭建也是一个不错的选择，
其中遇到的问题
1搭建的时候备库pg——basebackup连接不上主库可以检查hba文件看看是否设置了允许远程连接和复制的权限
第二种情况就是防火墙的问题了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29898569/viewspace-1771757/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29898569/viewspace-1771757/