平台：OEL 5.8 x64
主库：192.168.70.10 edb1 postgresql 9.3.9
备库：192.168.70.11 edb2 postgresql 9.3.9
备库也要创建好归档目录
mkdir -p/home/pg/pgsql/backup/archived_log/
1.配置主备库互信
主库：
[pg@edb1~]$ssh-keygen -t rsa
[pg@edb1~]$ cat/home/pg/.ssh/id_rsa.pub >> /home/pg/.ssh/authorized_keys
备库：
[pg@edb2~]$ssh-keygen -t rsa
主库：
[pg@edb1~]$ sshedb2 cat /home/pg/.ssh/id_rsa.pub >>/home/pg/.ssh/authorized_keys
[pg@edb1~]$ scp/home/pg/.ssh/authorized_keys edb2:/home/pg/.ssh/authorized_keys
如果配置完不通的话去检查一下权限id_rsa 600 .ssh 700 权限不能多也不能少 否则都不能实现ssh互信
2.配置文件(主备都要)
vi pg_hba.conf
#TYPEDATABASEUSERADDRESSMETHOD
localallalltrust
hostallall127.0.0.1/32trust
hostallall192.168.70.0/24trust
#localreplicationpgtrust
#hostreplicationpg::1/128trust
hostreplicationpguser192.168.70.0/24trust
(192.168.70.0/24为双方所在网络的网段)
vipostgresql.conf --确认以下参数
listen_addresses= '*'
max_connections =100
shared_buffers =128MB
wal_level =hot_standby
fsync = on
full_page_writes= on
checkpoint_segments= 32
archive_mode = on
archive_command ='cp %p /home/pg/pgsql/backup/archived_log/%f && scp %ppg@edb2:/home/pg/pgsql/backup/archived_log/%f '
max_wal_senders =2
wal_keep_segments= 250
hot_standby = on
max_standby_archive_delay= 300s
max_standby_streaming_delay= 300s
wal_receiver_status_interval= 10s
hot_standby_Feedback= on
log_line_prefix ='[%t]'
基础参数如下：
listen_addresses= '*'
wal_level =hot_standby
max_wal_senders =3
wal_keep_segments= 8
archive_mode = on
archive_command ='cp %p /var/lib/pgsql/archive/%f && scp %ppostgres@192.168.0.5:/var/lib/pgsql/archive/%f'
checkpoint_segments= 8
然后重启数据库 pg_ctl-D /home/pg/pgsql/data restart
3.主库创建用户
[pg@edb1 data]$psql -c "CREATE USER repuser REPLICATION LOGIN ENCRYPTED PASSWORD'repuser';"
CREATE ROLE
4.主库开始备份
[pg@edb1 data]$psql -U pg -h 192.168.70.10 -c "SELECT pg_start_backup('label',true)"
pg_start_backup
-----------------
0/9000028
(1 row)
5.rsync同步主库数据库目录到备库
[pg@edb1 ~]$rsynv -a /home/pg/pgsql/data 192.168.70.11:/home/pg/pgsql/data --excludepostmaster.pid
6.主库停止备份
[pg@edb1 data]$psql -U pg -h 192.168.70.10 -c "SELECT pg_stop_backup()"
NOTICE: pg_stop_backup complete,all required WALsegments have been archived
pg_stop_backup
----------------
0/90000F0
(1 row)
7.备库配置recovery.conf
[pg@edb2 ~]$ cp/home/pg/pgsql/share/recovery.conf.sample /home/pg/pgsql/data/recovery.conf
[pg@edb2 ~]$ vi/home/pg/pgsql/data/recovery.conf
standby_mode = on
primary_conninfo= 'host=192.168.70.10 port=5432 user=repuser password=repuser' # e.g.'host=localhost port=5432'
trigger_file ='/tmp/trigger.replication'
restore_command ='cp /home/pg/pgsql/backup/archived_log/%f "%p"'
8.启动备库
[pg@edb2 ~]$pg_ctl -D /home/pg/pgsql/data/ start
server starting
[pg@edb2 ~]$[2015-09-22 17:17:49 CST]LOG: databasesystem was shut down in recovery at 2015-09-22 02:40:22 CST
[2015-09-2217:17:49 CST]LOG: entering standby mode
cp: cannot stat`/home/pg/pgsql/backup/archived_log/00000002.history': No such file ordirectory
cp: cannot stat`/home/pg/pgsql/backup/archived_log/000000020000000000000014': No such file ordirectory
[2015-09-2217:17:49 CST]LOG: redo starts at0/140011B8
[2015-09-2217:17:49 CST]LOG: consistent recoverystate reached at 0/140142B8
[2015-09-2217:17:49 CST]LOG: database system isready to accept read only connections
9.测试流复制配置正常
由于环境为本地虚拟机，同步几乎为实时
原文链接：https://www.f2er.com/postgresql/194768.html