不同IDC之间postgresql 9.2 流复制配置过程

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)

参考

postgresq配置主从复制