PostgreSQL 流复制配置方法.docx

《PostgreSQL 流复制配置方法.docx》由会员分享，可在线阅读，更多相关《PostgreSQL 流复制配置方法.docx（12页珍藏版）》请在优知文库上搜索。

1、背景：在两个或多个部署了Postgresql数据库上，进行配置流豆制.或基于原来的流良制架构进行犷展备用节点.出现问题可进行手动切换主库.环境如下：Postgresql版本为11.7master172.16.1.11主库slave172.16.1.12备库建汉：数据库服务器之间配背ntp进行时间定期（加到定期任务crontab-e）同步，否则可能会导致数据异常。一、搭建master服务器主库master1、修改master服务器PgJba.conf,添加如下TYPEDATABASEUSERADDRESSMETHOD表示允许任意网段的用户通过MD5进行认证连接hostallall0.0.0.0/

2、0mdS表示允许该网段172.16.1.0的repl用户进行流豆制hostreplicationrepl172.16.1.0/0trusthostreplicationall:1/128trustlocalallalltrusthostallall127.1/32trusthostallall:1/128trustlocalreplicationalltrusthostreplicationall127.1/32trusthostreplicationall:1/128trusthostallall172.16.l.md51rtO11terddtA/pmtgr.rootmasterpostgr

3、esql#catpghba.confgrep-EvFf2root$lave*修改master服务器postgresqlconf参数配置如下，为注释listen.addresses=*port=5432ma.connections=2000SUPerUSejreSerVed.connections=10wa1.evel=logicalfu1.page.writes=onWa1.log_hints=offarchive.mode=onarchive.Command=bintruemax_Wa1.SenderS-50hot.standby=onIog.destination=,csvlogloggi

4、ng_collector=onlog_directory=log,log_filename=,postgresql-%Y-%m-%d-%H%M%Slog_rOtatior1.age=Idlog,OtatiOn_SiZe=IOMBIog.statement=,modroot0masterpostgresql#catpostgresql.cofgrep-EvF#(change#(change#(change#(changerequiresrequiresrequiresrequiresrestart)restart)restart)restart)listenaddresses=#whatIPad

5、dress(es)tolistenon;#comma-separatedlistofaddr#defaultstoIocalhosf;usemaxconnections=26#(change#(change#(change#(change#(change#(changerequiresrequiresrequiresrequiresrequiresrequiresrestart)restart)restart)restart)restart)restart)sharedbuffers=128b#selects#selects#selects#min128kBthethethesystemdef

6、aultsystemdefaultsystemdefault(changerequiresrestart)dynamicsharedmemorytype=posix(changerequiresrestart)(changerequiresrestart)thedefaultisthefirstoptsupportedbytheoperatingsPoStgreSMASTERpg_ctl-Ddatapostgresqlstartwaitingforservertostart.2020-09-1815:00:21.403CST96091.OG:listeningonIPv4address0.0.

7、0.0,port54322020-09-1815:00:21.403CST96091.OG:listeningonIPv6address，port54322020-09-1815:00:21.405CST96091.OG:listeningonUnixsocketVtmp/.s.PGSQ1.5432-2020-09-1815:00:21.412CST96091.OG:redirectinglogoutputtologgingcollectorprocess2020-09-1815:00:21.412CST9609HINT:Futurelogoutputwillappearindirectory

8、-log.doneserverstarted2rootslave-1rtC,maste11datapostgr-rootmasterpostgresql#servicepostgresqldstatuspgctl:serverisrunning(PID:54426)usrlocalpostgresqlbinpostgresDdatapostgresqlrootmasterpostgresql#postgres=#A1.TERUSERpostgresWITHPASSWORDpSSwOrd;A1.TERRO1.Epostgres=#CREATEUSERreplWITHPASSWORDpsSWord

9、REP1.ICATION;CREATERO1.Epostgres=#du1.fKrlRolenameAttributesMemberofpostgresSuperuser,Createrole,CreateDB,Replication,BypassR1.SreplIReplication创建使用的测试数据库pgpool和表pgpoolpostgres=#CREATEDATABASEpgpool;CREATEDATABASEpostgres=#cpgpoolYouarenowconnectedtodatabasepgpoolasuserpostgres.pgpool=#pgpool=#CREAT

10、ETAB1.Epgpool(idserial,agebigint,insertTimetimestampdefaultnow();CREATETAB1.Epgpool=#insertintopgpool(age)values(1);INSERT01pgpool=#select*frompgpool;idIageinserttime1I1I2020-09-1815:07:03.329849(1row)查看数据库是否为主库f表示为主库postgres=#select*frompg_is_in_recovery();pg_is_in_recovery(1row)至此master服务器主库搭建完毕二、

11、搭建slave服务器备库slaveSIaVe服务器创建数据库data目录PoStgreSS1.AVE:/datemkdirpostgresqlSIaVe服务器使用pg_basebackup命令在线创建一个备库,使用该命令请确保主库已经启动postgresS1.AVE:/data/postgresqlpg_basebackup-h172.16.1.11-p5432-Urepl-w-Fp-Xs-Pv-R-Ddatapostgresqlpg-basebackup:initiatingbasebackup,waitingforcheckpointtocompletepg_basebackup:chec

12、kpointcompletedpg_basebackup:write-aheadlogstartpoint:0/2000028ontimeline1pg_basebackup:startingbackgroundWA1.receiver31133/31133kB(100%),1/1tablespacepg_basebackup:write-aheadlogendpoint:020000F8pg_basebackup:waitingforbackgroundprocesstofinishstreaming.pg_basebackup:basebackupcompleted参数说明-h启动的主库数

13、据库地址-P主库数据库端口-U流登制用户-W不使用密码验证-Fp备份茹出正常的数据库目录-Xs使用流豆制的方式进行豆制-Pv输出豆制过程的详细信息-R为备库创建recovery.conf文件-D指定创建的备库的数据库目录SIaVe服务器在recovery.conf添加appliCatior1.name为SlaVe,配置如下注：12版本以上无须此操作（且无此文件），默认使用IP做为名称PoStgreSS1.AVE:/data/POStgresqlvimrecovery.confstandby_mode=on,Primary一ConninfO=,application-name=slaveuser

14、=replpassfile=homepostgres.pgpass,host=172.16.1.11port=5432sslmode=disableSslcompression=Itarget_session_attrs=any*slave服务器修改postgresql.conf参数如下maxfonnections=2000#允许的最大数据库连接数max_Wa1.SenderS=100#该参数需要大于主库，否则可能导致备库无法读操作SlaVe服务器分配700给数据库data目录PoStgreSS1.AVE:/data/POStgresqlchmod700ZdataZpostgresqI/slave服务器启动备库PoStgreSS1.AVE:/data/POStgresqlpg_ctl-Ddatapostgresqlstartwaitingforservertostart.2020-09-1815:20:19,966CST239071.OG:listeningonIPv4address,0.0.0.0,port54322020-09-1815:20:19.966CST239071.OG:listeningonIPv6address:,port54322020-09-1815