PostgreSQL halbrep.docx
- 文档编号:11462456
- 上传时间:2023-03-01
- 格式:DOCX
- 页数:32
- 大小:37.62KB
PostgreSQL halbrep.docx
《PostgreSQL halbrep.docx》由会员分享,可在线阅读,更多相关《PostgreSQL halbrep.docx(32页珍藏版)》请在冰豆网上搜索。
PostgreSQLhalbrep
Chapter25.HighAvailability,LoadBalancing,andReplication
Chapter25.HighAvailability,LoadBalancing,andReplication
TableofContents
25.1. Comparisonofdifferentsolutions
25.2. Log-ShippingStandbyServers
25.2.1. Planning
25.2.2. StandbyServerOperation
25.2.3. PreparingtheMasterforStandbyServers
25.2.4. SettingUpaStandbyServer
25.2.5. StreamingReplication
25.3. Failover
25.4. Alternativemethodforlogshipping
25.4.1. Implementation
25.4.2. Record-basedLogShipping
25.5. HotStandby
25.5.1. User'sOverview
25.5.2. Handlingqueryconflicts
25.5.3. Administrator'sOverview
25.5.4. HotStandbyParameterReference
25.5.5. Caveats
Databaseserverscanworktogethertoallowasecondservertotakeoverquicklyiftheprimaryserverfails(highavailability),ortoallowseveralcomputerstoservethesamedata(loadbalancing).Ideally,databaseserverscouldworktogetherseamlessly.Webserversservingstaticwebpagescanbecombinedquiteeasilybymerelyload-balancingwebrequeststomultiplemachines.Infact,read-onlydatabaseserverscanbecombinedrelativelyeasilytoo.Unfortunately,mostdatabaseservershavearead/writemixofrequests,andread/writeserversaremuchhardertocombine.Thisisbecausethoughread-onlydataneedstobeplacedoneachserveronlyonce,awritetoanyserverhastobepropagatedtoallserverssothatfuturereadrequeststothoseserversreturnconsistentresults.
Thissynchronizationproblemisthefundamentaldifficultyforserversworkingtogether.Becausethereisnosinglesolutionthateliminatestheimpactofthesyncproblemforallusecases,therearemultiplesolutions.Eachsolutionaddressesthisprobleminadifferentway,andminimizesitsimpactforaspecificworkload.
Somesolutionsdealwithsynchronizationbyallowingonlyoneservertomodifythedata.Serversthatcanmodifydataarecalledread/write, master or primary servers.Serversthattrackchangesinthemasterarecalled standby or slave servers.Astandbyserverthatcannotbeconnectedtountilitispromotedtoamasterserveriscalleda warmstandby server,andonethatcanacceptconnectionsandservesread-onlyqueriesiscalleda hotstandby server.
Somesolutionsaresynchronous,meaningthatadata-modifyingtransactionisnotconsideredcommitteduntilallservershavecommittedthetransaction.Thisguaranteesthatafailoverwillnotloseanydataandthatallload-balancedserverswillreturnconsistentresultsnomatterwhichserverisqueried.Incontrast,asynchronoussolutionsallowsomedelaybetweenthetimeofacommitanditspropagationtotheotherservers,openingthepossibilitythatsometransactionsmightbelostintheswitchtoabackupserver,andthatloadbalancedserversmightreturnslightlystaleresults.Asynchronouscommunicationisusedwhensynchronouswouldbetooslow.
Solutionscanalsobecategorizedbytheirgranularity.Somesolutionscandealonlywithanentiredatabaseserver,whileothersallowcontrolattheper-tableorper-databaselevel.
Performancemustbeconsideredinanychoice.Thereisusuallyatrade-offbetweenfunctionalityandperformance.Forexample,afullysynchronoussolutionoveraslownetworkmightcutperformancebymorethanhalf,whileanasynchronousonemighthaveaminimalperformanceimpact.
Theremainderofthissectionoutlinesvariousfailover,replication,andloadbalancingsolutions.A glossary isalsoavailable.
25.1.Comparisonofdifferentsolutions
SharedDiskFailover
Shareddiskfailoveravoidssynchronizationoverheadbyhavingonlyonecopyofthedatabase.Itusesasinglediskarraythatissharedbymultipleservers.Ifthemaindatabaseserverfails,thestandbyserverisabletomountandstartthedatabaseasthoughitwererecoveringfromadatabasecrash.Thisallowsrapidfailoverwithnodataloss.
Sharedhardwarefunctionalityiscommoninnetworkstoragedevices.Usinganetworkfilesystemisalsopossible,thoughcaremustbetakenthatthefilesystemhasfullPOSIX behavior(see Section17.2.1).Onesignificantlimitationofthismethodisthatiftheshareddiskarrayfailsorbecomescorrupt,theprimaryandstandbyserversarebothnonfunctional.Anotherissueisthatthestandbyservershouldneveraccessthesharedstoragewhiletheprimaryserverisrunning.
FileSystem(Block-Device)Replication
Amodifiedversionofsharedhardwarefunctionalityisfilesystemreplication,whereallchangestoafilesystemaremirroredtoafilesystemresidingonanothercomputer.Theonlyrestrictionisthatthemirroringmustbedoneinawaythatensuresthestandbyserverhasaconsistentcopyofthefilesystem—specifically,writestothestandbymustbedoneinthesameorderasthoseonthemaster. DRBD isapopularfilesystemreplicationsolutionforLinux.
WarmandHotStandbyUsingPoint-In-TimeRecovery(PITR)
Warmandhotstandbyserverscanbekeptcurrentbyreadingastreamofwrite-aheadlog(WAL)records.Ifthemainserverfails,thestandbycontainsalmostallofthedataofthemainserver,andcanbequicklymadethenewmasterdatabaseserver.Thisisasynchronousandcanonlybedonefortheentiredatabaseserver.
APITRstandbyservercanbeimplementedusingfile-basedlogshipping(Section25.2)orstreamingreplication(see Section25.2.5),oracombinationofboth.Forinformationonhotstandby,see Section25.5.
Trigger-BasedMaster-StandbyReplication
Amaster-standbyreplicationsetupsendsalldatamodificationqueriestothemasterserver.Themasterserverasynchronouslysendsdatachangestothestandbyserver.Thestandbycananswerread-onlyquerieswhilethemasterserverisrunning.Thestandbyserverisidealfordatawarehousequeries.
Slony-I isanexampleofthistypeofreplication,withper-tablegranularity,andsupportformultiplestandbyservers.Becauseitupdatesthestandbyserverasynchronously(inbatches),thereispossibledatalossduringfailover.
Statement-BasedReplicationMiddleware
Withstatement-basedreplicationmiddleware,aprograminterceptseverySQLqueryandsendsittooneorallservers.Eachserveroperatesindependently.Read-writequeriesaresenttoallservers,whileread-onlyqueriescanbesenttojustoneserver,allowingthereadworkloadtobedistributed.
Ifqueriesaresimplybroadcastunmodified,functionslike random(), CURRENT_TIMESTAMP,andsequencescanhavedifferentvaluesondifferentservers.Thisisbecauseeachserveroperatesindependently,andbecauseSQLqueriesarebroadcast(andnotactualmodifiedrows).Ifthisisunacceptable,eitherthemiddlewareortheapplicationmustquerysuchvaluesfromasingleserverandthenusethosevaluesinwritequeries.Anotheroptionistousethisreplicationoptionwithatraditionalmaster-standbysetup,i.e.datamodificationqueriesaresentonlytothemasterandarepropagatedtothestandbyserversviamaster-standbyreplication,notbythereplicationmiddleware.Caremustalsobetakenthatalltransactionseithercommitorabortonallservers,perhapsusingtwo-phasecommit(PREPARETRANSACTION and COMMITPREPARED. Pgpool-II and Sequoia areexamplesofthistypeofreplication.
AsynchronousMultimasterReplication
Forserversthatarenotregularlyconnected,likelaptopsorremoteservers,keepingdataconsistentamongserversisachallenge.Usingasynchronousmultimasterreplication,eachserverworksindependently,andperiodicallycommunicateswiththeotherserverstoidentifyconflictingtransactions.Theconflictscanberesolvedbyusersorconflictresolutionrules.Bucardoisanexampleofthistypeofreplication.
SynchronousMultimasterReplication
Insynchronousmultimasterreplication,eachservercanacceptwriterequests,andmodifieddataistransmittedfromtheoriginalservertoeveryotherserverbeforeeachtransactioncommits.Heavywriteactivitycancauseexcessivelocking,leadingtopoorperformance.Infact,writeperformanceisoftenworsethanthatofasingleserver.Readrequestscanbesenttoanyserver.Someimplementationsuseshareddisktoreducethecommunicationoverhead.Synchronousmultimasterreplicationisbestformostlyreadworkloads,thoughitsbigadvantageisthatanyservercanacceptwriterequests—thereisnoneedtopartitionworkloadsbetweenmasterandstandbyservers,andbecausethedatachangesaresentfromoneservertoanother,thereisnoproblemwithnon-deterministicfunctionslike random().
PostgreSQL doesnotofferthistypeofreplication,though PostgreSQL two-phasecommit(PREPARETRANSACTION and COMMITPREPARED)canbeusedtoimplementthisinapplicationcodeormiddleware.
CommercialSolutions
Because PostgreSQL isopensourceandeasilyextended,anumberofcompanieshavetaken PostgreSQL andcreatedcommercialclosed-sourcesolutionswithuniquefailover,replication,andloadbalancingcapabilities.
Table25-1 summarizesthecapabilitiesofthevarioussolutionslistedabove.
Table25-1.HighAvailability,LoadBalancing,andReplicationFeatureMatrix
Feature
SharedDiskFailover
FileSystemReplication
Hot/WarmStandbyUsingPITR
Trigger-BasedMaster-StandbyReplication
Statement-BasedReplicationMiddleware
AsynchronousMultimasterReplication
SynchronousMultimasterReplication
MostCommonImplementation
NAS
DRBD
PITR
Slony
pgpool-II
Bucardo
CommunicationMethod
shareddisk
diskblocks
WAL
tablerows
SQL
tablerows
tablerowsandrowlocks
Nospecialhardwarerequired
•
•
•
•
•
•
Allowsmultiplemasterservers
•
•
•
Nomasterserveroverhead
•
•
•
Nowaitingformultipleservers
•
•
•
•
Masterfailurewillneverlosedata
•
•
•
•
Standbyacceptread-onlyqueri
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PostgreSQL halbrep
![提示](https://static.bdocx.com/images/bang_tan.gif)