ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(31) - Backup&Recovery#4(搭建流复制)

PostgreSQL DBA(31) - Backup&Recovery#4(搭建流复制)

原创 PostgreSQL 作者:husthxd 时间:2019-03-13 14:20:13 0 删除 编辑

PostgreSQL通过流复制Streaming Replication可轻松实现高可用HA环境的搭建.本节简单介绍了搭建流复制环境的基本步骤.

Step 1 主库:创建用户
创建复制用户replicator


testdb=# CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator';
CREATE ROLE

Step 2 主库:参数配置
配置archive_mode等参数


archive_mode = ON
wal_level = replica
max_wal_senders = 10
archive_command = '/home/xdb/archive.sh'
listen_addresses = '*'

也可用alter system命令修改


ALTER SYSTEM SET wal_level TO 'replica';
ALTER SYSTEM SET archive_mode TO 'ON';
ALTER SYSTEM SET max_wal_senders TO '10';
ALTER SYSTEM SET listen_addresses TO '*';

重启数据库


pg_ctl -D $PGDATA restart -mf

Step 3 主库:访问配置
修改pg_hba.conf文件


host replication replicator 192.168.26.26/32 md5

生效配置


pg_ctl -D $PGDATA reload

Step 4 从库:从主库备份中恢复
在从库上使用pg_basebackup创建备库
192.168.26.25是主库IP,192.168.26.26是从库IP


pg_basebackup -h 192.168.26.25 -U replicator -p 5432 -D $PGDATA -P -Xs -R

配置从库postgres.conf


hot_standby = ON
hot_standby_feedback = ON
ALTER SYSTEM SET hot_standby TO 'ON';
ALTER SYSTEM SET hot_standby_feedback TO 'ON';

配置从库recovery.conf


$ cat $PGDATA/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.26.25 port=5432 user=replicator password=replicator'
restore_command = 'cp /data/archivelog/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/archivelog %r'

Step 5 从库:启动数据库


[xdb@localhost testdb]$ pg_ctl -D $PGDATA start
waiting for server to start....2019-03-13 12:13:30.239 CST [1870] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-03-13 12:13:30.239 CST [1870] LOG:  listening on IPv6 address "::", port 5432
2019-03-13 12:13:30.252 CST [1870] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-03-13 12:13:30.379 CST [1870] LOG:  redirecting log output to logging collector process
2019-03-13 12:13:30.379 CST [1870] HINT:  Future log output will appear in directory "pg_log".
 done
server started

Step 6 验证复制环境
确认相关进程是否已启动


#主库
[xdb@localhost testdb]$ ps -ef|grep sender
xdb       1646  1532  0 12:13 ?        00:00:00 postgres: walsender replicator 192.168.26.26(35294) streaming 0/43000140
xdb       1659  1440  0 12:17 pts/1    00:00:00 grep --color=auto sender
[xdb@localhost testdb]$ 
#从库
[xdb@localhost testdb]$ ps -ef|grep receiver
xdb       1879  1870  0 12:13 ?        00:00:00 postgres: walreceiver   streaming 0/43000140
xdb       1884  1799  0 12:18 pts/0    00:00:00 grep --color=auto receiver
[xdb@localhost testdb]$ ps -ef|grep startup
xdb       1872  1870  0 12:13 ?        00:00:00 postgres: startup   recovering 000000100000000000000043
xdb       1887  1799  0 12:18 pts/0    00:00:00 grep --color=auto startup
[xdb@localhost testdb]$

Step 7 监控
查询pg_stat_replication数据字典表


testdb=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 1646
usesysid         | 90113
usename          | replicator
application_name | walreceiver
client_addr      | 192.168.26.26
client_hostname  | 
client_port      | 35294
backend_start    | 2019-03-13 12:13:30.852269+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/43000140
write_lsn        | 0/43000140
flush_lsn        | 0/43000140
replay_lsn       | 0/43000140
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
testdb=#

同步复制
从库配置参数recovery.conf,在primary_conninfo中添加application_name


primary_conninfo = 'user=replicator password=replicator host=192.168.26.25 port=5432 application_name = standby_26'

主库配置参数


synchronous_standby_names = 'standby_26'
synchronous_commit = on

重启数据库,验证是否配置成功


testdb=# \x
Expanded display is on.
testdb=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 2257
usesysid         | 90113
usename          | replicator
application_name | standby_26
client_addr      | 192.168.26.26
client_hostname  | 
client_port      | 35418
backend_start    | 2019-03-13 15:17:57.330573+08
backend_xmin     | 634
state            | streaming
sent_lsn         | 0/54D4DBD0
write_lsn        | 0/54D4DBD0
flush_lsn        | 0/54D4DBD0
replay_lsn       | 0/54D4DBD0
write_lag        | 00:00:00.00101
flush_lag        | 00:00:00.001954
replay_lag       | 00:00:00.002145
sync_priority    | 1
sync_state       | sync

参考资料
pgbasebackup
Setting up Streaming Replication in PostgreSQL

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2638258/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,对Oracle、PostgreSQL以及大数据等相关技术有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1196
  • 访问量
    3665047