PostgreSQL 流复制配置方法.docx
背景：在两个或多个部署了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/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.rootmasterpostgresql#catpghba.confgrep-Ev"Ff"2root$lave*÷修改master服务器postgresqlconf参数配置如下，为注释listen.addresses=*'port=5432ma×.connections=2000SUPerUSejreSerVed.connections=10wa1.evel=logicalfu1.page.writes=onWa1.log_hints=offarchive.mode=onarchive.Command="bintrue'max_Wa1.SenderS-50hot.standby=onIog.destination=,csvloglogging_collector=onlog_directory='log,log_filename=,postgresql-%Y-%m-%d-%H%M%S'log_rOtatior1.age=Idlog,OtatiOn_SiZe=IOMBIog.statement=,mod'root0masterpostgresql#catpostgresql.cofgrep-Ev"F""#(change#(change#(change#(changerequiresrequiresrequiresrequiresrestart)restart)restart)restart)listenaddresses=#whatIPaddress(es)tolistenon;#comma-separatedlistofaddr#defaultsto'Iocalhosf;usemaxconnections=26#(change#(change#(change#(change#(change#(changerequiresrequiresrequiresrequiresrequiresrequiresrestart)restart)restart)restart)restart)restart)sharedbuffers=128b#selects#selects#selects#min128kBthethethesystemdefaultsystemdefaultsystemdefault(changerequiresrestart)dynamicsharedmemorytype=posix(changerequiresrestart)(changerequiresrestart)thedefaultisthefirstoptsupportedbytheoperatingsPoStgreSMASTER>pg_ctl-Ddatapostgresqlstartwaitingforservertostart.2020-09-1815:00:21.403CST96091.OG:listeningonIPv4address"0.0.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-log".doneserverstarted2root®slave-1rtC,maste11datapostgr-rootmasterpostgresql#servicepostgresqldstatuspgctl:serverisrunning(PID:54426)usrlocalpostgresqlbinpostgres""D""datapostgresql"rootmasterpostgresql#postgres=#A1.TERUSERpostgresWITHPASSWORD'pSSwOrd'A1.TERRO1.Epostgres=#CREATEUSERreplWITHPASSWORD'psSWord'REP1.ICATION;CREATERO1.Epostgres=#du1.fKrlRolenameAttributesMemberofpostgresSuperuser,Createrole,CreateDB,Replication,BypassR1.SreplIReplication创建使用的测试数据库pgpool和表pgpoolpostgres=#CREATEDATABASEpgpool;CREATEDATABASEpostgres=#cpgpoolYouarenowconnectedtodatabase"pgpool"asuser"postgres".pgpool=#pgpool=#CREATETAB1.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服务器主库搭建完毕二、搭建slave服务器备库slaveSIaVe服务器创建数据库data目录PoStgreSS1.AVE:/date>mkdirpostgresqlSIaVe服务器使用pg_basebackup命令在线创建一个备库,使用该命令请确保主库已经启动postgresS1.AVE:/data/postgresql>pg_basebackup-h172.16.1.11-p5432-Urepl-w-Fp-Xs-Pv-R-Ddatapostgresqlpg-basebackup:initiatingbasebackup,waitingforcheckpointtocompletepg_basebackup:checkpointcompletedpg_basebackup:write-aheadlogstartpoint:0/2000028ontimeline1pg_basebackup:startingbackgroundWA1.receiver31133/31133kB(100%),1/1tablespacepg_basebackup:write-aheadlogendpoint:020000F8pg_basebackup:waitingforbackgroundprocesstofinishstreaming.pg_basebackup:basebackupcompleted参数说明-h启动的主库数据库地址-P主库数据库端口-U流登制用户-W不使用密码验证-Fp备份茹出正常的数据库目录-Xs使用流豆制的方式进行豆制-Pv输出豆制过程的详细信息-R为备库创建recovery.conf文件-D指定创建的备库的数据库目录SIaVe服务器在recovery.conf添加appliCatior1.name为SlaVe,配置如下注：12版本以上无须此操作（且无此文件），默认使用IP做为名称PoStgreSS1.AVE:/data/POStgresql>vimrecovery.confstandby_mode='on,Primary一ConninfO=,application-name=slaveuser=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/POStgresql>chmod700ZdataZpostgresqI/slave服务器启动备库PoStgreSS1.AVE:/data/POStgresql>pg_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.5432-2020-09-1815:20:20.007CST23907J1.OG:redirectinglogoutputtologgingcollectorprocess2020-09-1815:20:20.007CST23907HINT:Futurelogoutputwillappearindirectory"log".doneserverstartedSlaVe服务器连接数据库pgpool,有看数据是否同步PoStgreSS1.AVE:/data/postgresql>psql-h172.16.1.12-p5432-UpostgrespgpoolPasswordforuserpostgres:psq(10.3)Type"help"forhelp.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;idIageinserttime土+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)Type"help"forhelp.postgres=#select*frompg_is_in_recovery();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)postgres=#参数说明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.
