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/sla

15、ve服务器启动备库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:20:19.970CST239071.OG:listeningonUnixsocket7tmp.s.PGSQ1

16、.5432-2020-09-1815:20:20.007CST23907J1.OG:redirectinglogoutputtologgingcollectorprocess2020-09-1815:20:20.007CST23907HINT:Futurelogoutputwillappearindirectorylog.doneserverstartedSlaVe服务器连接数据库pgpool,有看数据是否同步PoStgreSS1.AVE:/data/postgresqlpsql-h172.16.1.12-p5432-UpostgrespgpoolPasswordforuserpostgres

17、:psq(10.3)Typehelpforhelp.pgpool=#dtSchemaNameTypeOwnerpublicIpgpooltablepostgres(1row)root9slavexsupostgres(postgresslaverootpsqlcouldnotchangedirectorytoroof:权限不够psql(11.7)Type-help*forhelp.ostgres=select*frompg_is_in_recovery();pg_is_in_recovery(1row)ostgres.pgpool=#select*frompgpool;idIageinsert

18、time土+1I1I2020-09-1815:07:03.329849(1row)pgpool=#参数说明-h备库数据库服务器地址-P备库数据库端口-U连接数据库的用户SlaVe服务器查看数据库是否为备库，t表示为备库pgpool=#select*frompg_is_in_recovery();pg_is_in_recovery(1row)root0masterpostgresql#supostgres(postgres(3masterpostgresql$psqlPSql(11.7)Typehelpforhelp.postgres=#select*frompg_is_in_recovery(

19、);pg_is_in_recoveryf(1row)postgres=#master服务器查看数据库的状态postgres=#selectClient_addr,usename,backend_start,application_name,SynjState,synjpriorityFROMpg_stat_replication;client_addrusenamebackend_startapplication-namesync_stateSynjPriOrity+172.16.1.12Irepl2020-09-1815:20:20.066431+08slaveasync0(1row)pos

20、tgres=#参数说明client_addr:备库服务器的地址usename:使用的流复制用户backend_Start:流复制开始的时间application.name:备库的名称SynjState:备库与主库的同步状态sync_priority:备库与主库变成同步状态的优先级至此slave服务器备库slave搭建完毕三、故障迁移思路由于流复制为主备架构，默认只有主库才会写入数据，可通过命令把备库改为主库进行写入数据在备库执行后，可在备库写入数据postgresslave-#/postgresql/bin/pg_ctlpromote-Ddatapostgresql思路：2台服务器配JS流巨制，通过pgpool或者keepalive进行VIP迁移.脚本通过测试连接VlP进入数据库，当出现无法连接时，主库把VlP关闭,备库执行上述命令，进行写入就数据，使得备库可写入数据，并启用VIP.