hot standby

 

 

 

环境描述

主服务器

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();