PostgreSQL 12搭建流复制的过程是什么

2025-03-13 技术教程

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

主库
创建复制用户

[pg12@localhostpg120db]$psql-c"CREATEUSERreplicatorWITHREPLICATIONENCRYPTEDPASSWORD'test'"-dtestdbTimingison.Expandeddisplayisusedautomatically.CREATEROLETime:30.796ms

常规参数配置

[pg12@localhostpg120db]$grep'listen'postgresql.conflisten_addresses='*'#whatIPaddress(es)tolistenon;pg12@localhostpg120db]$grep'replication'pg_hba.conf#DATABASEcanbe"all","sameuser","samerole","replication",a#keyworddoesnotmatch"replication".Accesstoreplication#"all","sameuser","samerole"or"replication"makesthenamelose#Allowreplicationconnectionsfromlocalhost,byauserwiththe#replicationprivilege.localreplicationalltrusthostreplicationall127.0.0.1/32trusthostreplicationall192.168.0.0/16md5hostreplicationall::1/128trust[pg12@localhostpg120db]$

重新加载配置参数

[pg12@localhostpg120db]$psql-c"selectpg_reload_conf()"-dtestdbTimingison.Expandeddisplayisusedautomatically.pg_reload_conf----------------t(1row)Time:454.580ms

确认该库为master主库

[pg12@localhostpg120db]$psql-c"selectpg_is_in_recovery()"-dtestdbTimingison.Expandeddisplayisusedautomatically.pg_is_in_recovery-------------------f(1row)Time:23.530ms[pg12@localhostpg120db]$

备库
使用pg_basebackup执行主库备份

[pg12@localhost~]$pg_basebackup-h192.168.26.28-Ureplicator-p5432-D$PGDATA-Fp-Xs-P-RPassword:426401/9113562kB(4%),0/1tablespace

其中-Fp表示以plain格式数据，-Xs表示以stream方式包含所需的WAL文件，-P表示显示进度，-R表示为replication写配置信息。
备份完成，使用-R选项，在data目录下自动生成standby.signal“信号”文件（可手工使用touch命令生成）以及更新了postgresql.auto.conf文件，postgresql.auto.conf中写入了主库的连接信息（可手工添加primary_conninfo信息）。

[pg12@localhost~]$pg_basebackup-h192.168.26.28-Ureplicator-p5432-D$PGDATA-Fp-Xs-P-RPassword:9113571/9113571kB(100%),1/1tablespace[pg12@localhost~]$[pg12@localhost~]$cd$PGDATA[pg12@localhosttestdb]$lsbackup_labelpg_commit_tspg_logpg_replslotpg_stat_tmpPG_VERSIONpostgresql.confbasepg_dynshmempg_logicalpg_serialpg_subtranspg_walstandby.signalcurrent_logfilespg_hba.confpg_multixactpg_snapshotspg_tblspcpg_xactglobalpg_ident.confpg_notifypg_statpg_twophasepostgresql.auto.conf[pg12@localhosttestdb]$llstandby.signal-rw-------.1pg12pg120Nov1216:35standby.signal[pg12@localhosttestdb]$[pg12@localhosttestdb]$catpostgresql.auto.conf#Donoteditthisfilemanually!#ItwillbeoverwrittenbytheALTERSYSTEMcommand.primary_conninfo='user=replicatorpassword=testhost=192.168.26.28port=5432sslmode=prefersslcompression=0gssencmode=preferkrbsrvname=postgrestarget_session_attrs=any'[pg12@localhosttestdb]$[pg12@localhosttestdb]$grep'primary_conninfo'postgresql.*postgresql.auto.conf:primary_conninfo='user=replicatorpassword=testhost=192.168.26.28port=5432sslmode=prefersslcompression=0gssencmode=preferkrbsrvname=postgrestarget_session_attrs=any'postgresql.conf:#primary_conninfo=''#connectionstringtosendingserver[pg12@localhosttestdb]$

在PG 11中，需要创建recovery.conf文件，在此文件中配置standby_mode和primary_conninfo参数，PG 12已不再需要该文件，改为standby.signal文件以及通过配置参数直接设置。

备库启动数据库，通过pg_is_in_recovery确认是否正常配置

[pg12@localhosttestdb]$pg_ctl-D$PGDATAstartwaitingforservertostart....2019-11-1216:46:31.635CST[20436]LOG:startingPostgreSQL12.0onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-36),64-bit2019-11-1216:46:31.636CST[20436]LOG:listeningonIPv4address"0.0.0.0",port54322019-11-1216:46:31.636CST[20436]LOG:listeningonIPv6address"::",port54322019-11-1216:46:31.638CST[20436]LOG:listeningonUnixsocket"/tmp/.s.PGSQL.5432"2019-11-1216:46:31.750CST[20436]LOG:redirectinglogoutputtologgingcollectorprocess2019-11-1216:46:31.750CST[20436]HINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted[pg12@localhosttestdb]$psql-c"selectpg_is_in_recovery()"-dtestdbpg_is_in_recovery-------------------t(1row)[pg12@localhosttestdb]$

完成搭建
通过pg_stat_replication可查询复制状态（异步复制）

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