PostgreSQL halbrep.docx

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