postgresql 9.2 流复制配置过程
拓扑
master和slave分别位于两个不同地域的idc，通过vpn连接
wal pg master-----------gateway------------>> pg slave 10.0.1.31 10.0.0.249 10.2.1.10
步骤
master 上执行
- 修改master pg_hba.conf 增加replica用户用于流复制
host replication replica 10.0.0.249/32 md5
- 设置replica用户密码
-bash-4.2$ psql
psql (9.2.14)
Type "help" for help.
postgres=# create role replica login replication encrypted password 'replica';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
replica | Replication | {}
- 修改postgresql.conf
wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 32 ## 最多32个logfile段，每个16M replication_timeout = 60s max_connections = 1000
备份机操作步骤
-
安装略过
-
首次复制master数据
-bash-4.2$ pg_basebackup -F p --progress -D /goluk/pgsql/data -h 10.0.1.31 -p 5432 -U replica --password Password: 159896/159896 kB (100%), 1/1 tablespace NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
- 复制recovery.conf
-bash-4.2$ cp /usr/share/pgsql/recovery.conf.sample recovery.conf
- 修改recovery.conf 内容
standby_mode = on primary_conninfo = 'host=10.0.1.31 port=5432 user=replica password=replica' recovery_target_timeline = 'latest'
- 修改postgresql.conf
hot_standby = on # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
hot_standby_feedback = on # send info from standby to prevent
# query conflicts
- 启动
[root@bjdb04 data]# systemctl start postgresql [root@bjdb04 data]# tail -f /xxx/pgsql/data/pg_log/postgresql-Thu.log ...... FATAL: the database system is starting up LOG: entering standby mode LOG: streaming replication successfully connected to primary LOG: redo starts at 14/45000020 LOG: consistent recovery state reached at 14/45000DC0 LOG: database system is ready to accept read only connections
确认复制状态
- master上查看发送进程
postgres 23634 22199 0 13:11 ? 00:00:00 postgres: wal sender process replica 10.0.0.249(57950) streaming 14/46163E60
- slaver上查看接收进程
postgres 7540 7538 0 13:11 ? 00:00:00 postgres: startup process recovering 000000010000001400000046 postgres 7542 7538 0 13:11 ? 00:00:00 postgres: wal receiver process streaming 14/46176E08
- 查看复制状态
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname |
client_port | backend_start | state | sent_location | write
_location | flush_location | replay_location | sync_priority | sync_state
-------+----------+---------+------------------+-------------+-----------------+
-------------+-------------------------------+-----------+---------------+------
----------+----------------+-----------------+---------------+------------
23634 | 19011 | replica | walreceiver | 10.0.0.249 | |
57950 | 2018-03-08 13:11:46.686495+08 | streaming | 14/4618F768 | 14/46
18F768 | 14/4618F768 | 14/4618F6C8 | 0 | async
(1 row)