本篇内容主要讲解“PostgreSQL怎么搭建流复制”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL怎么搭建流复制”吧!

PostgreSQL通过流复制Streaming Replication可轻松实现高可用HA环境的搭建.

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

testdb=#CREATEUSERreplicatorWITHREPLICATIONENCRYPTEDPASSWORD'replicator';CREATEROLE

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

archive_mode=ONwal_level=replicamax_wal_senders=10archive_command='/home/xdb/archive.sh%p%f'listen_addresses='*'

也可用alter system命令修改

ALTERSYSTEMSETwal_levelTO'replica';ALTERSYSTEMSETarchive_modeTO'ON';ALTERSYSTEMSETmax_wal_sendersTO'10';ALTERSYSTEMSETlisten_addressesTO'*';

重启数据库

pg_ctl-D$PGDATArestart-mf

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

hostreplicationreplicator192.168.26.26/32md5

生效配置

pg_ctl-D$PGDATAreload

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

pg_basebackup-h192.168.26.25-Ureplicator-p5432-D$PGDATA-P-Xs-R

配置从库postgres.conf

hot_standby=ONhot_standby_feedback=ONALTERSYSTEMSEThot_standbyTO'ON';ALTERSYSTEMSEThot_standby_feedbackTO'ON';

配置从库recovery.conf

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

Step 5 从库:启动数据库

[xdb@localhosttestdb]$pg_ctl-D$PGDATAstartwaitingforservertostart....2019-03-1312:13:30.239CST[1870]LOG:listeningonIPv4address"0.0.0.0",port54322019-03-1312:13:30.239CST[1870]LOG:listeningonIPv6address"::",port54322019-03-1312:13:30.252CST[1870]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-03-1312:13:30.379CST[1870]LOG:redirectinglogoutputtologgingcollectorprocess2019-03-1312:13:30.379CST[1870]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted

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

#主库[xdb@localhosttestdb]$ps-ef|grepsenderxdb16461532012:13?00:00:00postgres:walsenderreplicator192.168.26.26(35294)streaming0/43000140xdb16591440012:17pts/100:00:00grep--color=autosender[xdb@localhosttestdb]$#从库[xdb@localhosttestdb]$ps-ef|grepreceiverxdb18791870012:13?00:00:00postgres:walreceiverstreaming0/43000140xdb18841799012:18pts/000:00:00grep--color=autoreceiver[xdb@localhosttestdb]$ps-ef|grepstartupxdb18721870012:13?00:00:00postgres:startuprecovering000000100000000000000043xdb18871799012:18pts/000:00:00grep--color=autostartup[xdb@localhosttestdb]$

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

testdb=#SELECT*FROMpg_stat_replication;-[RECORD1]----+------------------------------pid|1646usesysid|90113usename|replicatorapplication_name|walreceiverclient_addr|192.168.26.26client_hostname|client_port|35294backend_start|2019-03-1312:13:30.852269+08backend_xmin|state|streamingsent_lsn|0/43000140write_lsn|0/43000140flush_lsn|0/43000140replay_lsn|0/43000140write_lag|flush_lag|replay_lag|sync_priority|0sync_state|asynctestdb=#

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

primary_conninfo='user=replicatorpassword=replicatorhost=192.168.26.25port=5432application_name=standby_26'

主库配置参数

synchronous_standby_names='standby_26'synchronous_commit=on

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

testdb=#\xExpandeddisplayison.testdb=#SELECT*FROMpg_stat_replication;-[RECORD1]----+------------------------------pid|2257usesysid|90113usename|replicatorapplication_name|standby_26client_addr|192.168.26.26client_hostname|client_port|35418backend_start|2019-03-1315:17:57.330573+08backend_xmin|634state|streamingsent_lsn|0/54D4DBD0write_lsn|0/54D4DBD0flush_lsn|0/54D4DBD0replay_lsn|0/54D4DBD0write_lag|00:00:00.00101flush_lag|00:00:00.001954replay_lag|00:00:00.002145sync_priority|1sync_state|sync

到此，相信大家对“PostgreSQL怎么搭建流复制”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！