环境描述
主服务器
IP:192.168.1.200/24
计算机名称:pg1
操作系统:linux
数据库:postgresql-9.1.2
备服务器
IP:192.168.1.201/24
计算机名称:pg2
操作系统:linux
数据库:postgresql-9.1.2
Hot standby配置
Primary
[root@pg1 ~]# su – postgres
[postgres@pg1 ~]$ psql
postgres=# create user repli replication login connection limit 2 encrypted password 'repuser';
postgres=# \q
[postgres@pg1 ~]$ vi /u01/pg9.1/data/pg_hba.conf
host replication repli 192.168.1.201/24 md5
[postgres@pg1 ~]$ mkdir -p /u01/pg9.1/data/archivedir
[postgres@pg1 ~]$ vi /u01/pg9.1/data/postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command ='cp -i %p /u01/pg9.1/data/archivedir/%f </dev/null'
max_wal_senders = 1 -----允许连接备库数量
wal_keep_segments = 64
log_connections = on
[postgres@pg1 ~]$ pg_ctl restart
[postgres@pg1 ~]$ psql
postgres=# select pg_start_backup('base backup for log streaming');
postgres=# \q
[postgres@pg1 ~]$ exit
[root@pg1 postgresql-9.1.2]# cd /u01/pg9.1
[root@pg1 pg9.1]# tar cvfj data.tar.bz2 data
[root@pg1 pg9.1]# $ scp data.tar.bz2 root@192.168.1.201:/u01/pg9.1
Standby
[root@pg2 /]# cd /u01/pg9.1
[root@pg2 pg9.1]# tar xfv data.tar.bz2
[root@pg2 pg9.1]# chown postgres data
[root@pg2 pg9.1]# cd data
[root@pg2 data]# vi postgresql.conf
listen_addresses = '*'
#wal_level = hot_standby
#archive_mode = on
#archive_command ='cp -i %p /u01/pg9.1/data/archivedir/%f </dev/null'
#max_wal_senders = 1 -----允许连接备库数量
#wal_keep_segments = 64
hot_standby = on
[root@pg2 data]# cp /u01/pg9.1/share/postgresql/recovery.conf.sample recovery.conf
[root@pg2 data]# vi recovery.conf
standby_mode = 'on'
primary_conninfo='host=192.168.1.200 port=5432 user=repli password=repuser'
trigger_file = '/tmp/postgresql.trigger.5432'
[root@pg2 data]# rm -rf postmaster.pid
主库
[postgres@pg1 pg9.1]$ psql
postgres=# select pg_stop_backup(), current_timestamp;
从库
[root@pg2 data]# su - postgres
[postgres@pg2 ~]$ pg_ctl start
检查
1、检查备库日志
[postgres@pg2 ~]$ cd /u01/pg9.1/data/pg_log/
2、
主库
postgres=# select pg_current_xlog_location();
postgres=# select pg_switch_xlog();
postgres=# select pg_current_xlog_location();
备库
postgres=# select pg_last_xlog_receive_location();