【PostgreSQL】PostgreSQL读写分离之Hot Standby

平台：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.测试流复制配置正常

由于环境为本地虚拟机，同步几乎为实时

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!