pgpoolpostgresql异步流复制数据库集群及负载的配置方法

.pgpool+postgres集群、负载、主备的配置文档系统环境：虚拟机（centos6.5）db1:postgres（master）+pgpool(master)感谢阅读node1:ip:45db2:postgres（salve）+pgpool(salve)谢谢阅读node2:ip:46db3:postgres(salve)node3:ip:47第一章初始化配置首先配置3台主机3台主机新加用户postgres；3台主机上新建文件vi/home/postgre/postgresql.log谢谢阅读数据库的启动和pgpool的启动全部都是使用postgres用户；感谢阅读postgres用户互相SSH信任；数据库的安装目录为：/usr/local/pgsql/感谢阅读pgpool的安装目录为：/usr/local/pgpool/(pgpool安装的时候，./configure–prefix=/usr/local/pgpool要用命令指定安装目录，在以后的配置中比较方便，个人建议)。谢谢阅读数据库安装之后，要给postgresql和pgpool添加环境变量具体的添加如下：感谢阅读用root账户编辑/etc/profile文件，在文件的最后面添加以下代码exportPATH=/usr/local/pgsql/bin:$PATH:/usr/local/pgpool/binexportPGDATA=/usr/local/pgsql/dataexportPGHOME=/usr/local/pgsql感谢阅读exportLANG=zh_CN.UTF-8exportPGPORT=5432保存文件后，需要使用postgres用户使用source /etc/profile命令使环境变量生效。谢谢阅读使用root账户，对/usr/local/pgsql的那个目录使用chown-Rpostgres:postgrespgsql谢谢阅读.以node1，配置ntpd服务，确保node1,node2,node3的时间保持一致，在node2和node3中要加上定时任务去同步node1的ntp服务。感谢阅读主数据库的数据库需要初始化，备数据库不用数据库初始化。谢谢阅读主数据库的初始化的方法：在node1的/usr/local/pgsql/目录下面新建一个文件夹叫data,使用数据库的初始化的命令：感谢阅读initdb-D/usr/local/pgsql/data--locale=zh_CN.UTF8精品文档放心下载数据库的启动命令：pg_ctl-D/usr/local/pgsql-l/home/postgres/postgresql.logstart精品文档放心下载给数据库的postgres用户添加密码：在终端中输入psql命令后，进入数据库，然后使用以下命令改密码谢谢阅读alteruserpostgreswithpassword'123456';谢谢阅读备主机的数据库的目录下面也需要建data文件夹.第二章数据库的流复制配置2.1在主库中创建流复制用户CREATEUSERrepuserreplicationLOGINCONNECTIONLIMIT5ENCRYPTEDPASSWORD'123456';精品文档放心下载2.2修改主库pg_hba.conf文件（目录在/usr/local/pgsql/data）感谢阅读在最后添加如下行。hosthosthost

replicationallall

repuser/24all/24postgres/24精品文档放心下载

trusttrusttrust2.3修改主库postgresql.conf文件精品文档放心下载修改如下几个参数listen_addresses='*'wal_level=hot_standbymax_wal_senders=2hot_standby=onmax_wal_senders是Slave库的节点数，有多少个slave库就设多少。感谢阅读wal_level是writeaheadlog参数值,设置流复制务必将此值更新成hot_standby。精品文档放心下载使用postgres用户启动主数据库，命令见上一章。精品文档放心下载2.4在salve主机上使用命令：在备机上使用命令来跟主库进行同步：pg_basebackup-h45-Urepuser-Fp-P-x-R-D/usr/local/pgsql/data/-lnode1dbbackup160619谢谢阅读.在两台备主机上的数据库安装目录下面的data文件夹中都有了数据。在两台备机的/usr/local/pgsql/data/下面同时有了recovery.conf文件谢谢阅读$virecovery.conf--新增以下三行感谢阅读standby_mode='on'trigger_file='/usr/local/pgsql/data/pg.trigger'感谢阅读primary_conninfo='host=45port=5432user=repuserpassword=123456keepalives_idle=60'recovery_target_timeline='latest'精品文档放心下载2.5启动两台备机的postgres数据库测试：在主数据库上通过：psql进入数据库命令使用默认的数据库CREATETABLErep_test(testvarchar(40));谢谢阅读插入数据：INSERTINTOrep_testVALUES('dataone');精品文档放心下载INSERTINTOrep_testVALUES('somemorewords');INSERTINTOrep_testVALUES('lalala');感谢阅读INSERTINTOrep_testVALUES('hellothere');INSERTINTOrep_testVALUES('blahblah');在备机上通过psql命令进入数据库：谢谢阅读查询rep_test表，看数据是否插入成功；也可以在主机的数据库中使用命令查看流复制的连接备机情况了：selectpid,stateclient_addr,sync_priority,sync_statefrompg_stat_replication;精品文档放心下载.查看备库落后主库多少字节的wal日志命令：select感谢阅读pg_xlog_location_diff(pg_current_xlog_location(),replay_location)frompg_stat_replication;精品文档放心下载pgpool的配置3.1pgpool的安装tarzxvfpgpool-II-3.4.6.tar.gz感谢阅读cdpgpool-II-3.4.6mkdir-p/opt/pgpool./configure--prefix=/us/local/pgpool-with-pgsql=path-with-谢谢阅读pgsql=/usr/local/pgsql/makemakeinstall3.2pgpool相关函数的安装pgpool函数不是必需安装，但建议安装pgpool_regclass,pgpool_recovery函数。精品文档放心下载cd/pgpool-II-3.4.6/src/sql精品文档放心下载makemakeinstall安装完成后可以在/opt/PostgreSQL/93/share/postgresql/extension/看到pgpool相关文件。感谢阅读ls-l/usl/local/pgsqlshare/postgresql/extension/total36谢谢阅读pgpool_recovery--1.0.sql.pgpool_recovery.controlpgpool-recovery.sqlpgpool_regclass--1.0.sqlpgpool_regclass.controlpgpool-regclass.sqlplpgsql--1.0.sqlplpgsql.controlplpgsql--unpackaged--1.0.sql感谢阅读登陆需要安装的库中，和安装插件一样执行以下两条命令(一定要执行)。谢谢阅读createextensionpgpool_regclass;精品文档放心下载createextensionpgpool_recovery;精品文档放心下载psql-Upostgres-h45-p5432-f/usr/local/pgsql/share/extension/pgpool-recovery.sqltemplate1感谢阅读执行以上命令时，会在主数据库上的template1表空间下面创建几个recovery需要的函数。精品文档放心下载3.3配置pcp.confcd/usr/local/pgpool/etc/cppcp.conf.samplepcp.conf精品文档放心下载pgpool提供pcp接口，可以查看、管理pgpool的状态，并且可以远程操作pgpool。pcp.conf是用来对pcp相关命令认证的文件，格式为USERID:MD5PASSWD。谢谢阅读执行pg_md5-upostgres -p命令后，让输入密码，然后会出现md5加密后的感谢阅读字符串password:123456XXXXXXXXXXXXXXXXXXXXXXX然后在pcp.conf中添加postgres:XXXXXXXXXXXXXX配置pgpool.conf感谢阅读#----------------------------谢谢阅读#pgPool-IIconfigurationfile谢谢阅读#----------------------------感谢阅读##Thisfileconsistsoflinesoftheform:感谢阅读## name=value##Whitespacemaybeused. Commentsareintroducedwith"#"anywhereonaline.谢谢阅读.#Thecompletelistofparameternamesandallowedvaluescanbefoundinthe精品文档放心下载#pgPool-IIdocumentation.精品文档放心下载##ThisfileisreadonserverstartupandwhentheserverreceivesaSIGHUP精品文档放心下载#signal. Ifyoueditthefileonarunningsystem,youhavetoSIGHUPthe谢谢阅读#serverforthechangestotakeeffect,oruse"pgpoolreload".Some谢谢阅读#parameters,whicharemarkedbelow,requireaservershutdownandrestartto精品文档放心下载#takeeffect.##------------------------------------------------------------------------------精品文档放心下载#CONNECTIONS#------------------------------------------------------------------------------精品文档放心下载#-pgpoolConnectionSettings-谢谢阅读listen_addresses='*'#HostnameorIPaddresstolistenon:感谢阅读#'*'forall,''fornoTCP/IPconnections感谢阅读#(changerequiresrestart)精品文档放心下载port=9999#Portnumber#(changerequiresrestart)谢谢阅读socket_dir='/tmp'#Unixdomainsocketpath#TheDebianpackagedefaultsto谢谢阅读#/var/run/postgresql#(changerequiresrestart)精品文档放心下载#-pgpoolCommunicationManagerConnectionSettings-谢谢阅读pcp_listen_addresses='*'感谢阅读#HostnameorIPaddressforpcpprocesstolistenon:谢谢阅读#'*'forall,''fornoTCP/IPconnections谢谢阅读#(changerequiresrestart)谢谢阅读pcp_port=9898#Portnumberforpcp#(changerequiresrestart)感谢阅读pcp_socket_dir='/tmp'#Unixdomainsocketpathforpcp精品文档放心下载#TheDebianpackagedefaultsto谢谢阅读#/var/run/postgresql#(changerequiresrestart)谢谢阅读listen_backlog_multiplier=2谢谢阅读#Setthebacklogparameteroflisten(2)to精品文档放心下载#num_init_children*listen_backlog_multiplier.感谢阅读#(changerequiresrestart)感谢阅读#-BackendConnectionSettings-谢谢阅读#HostnameorIPaddresstoconnecttoforbackend0谢谢阅读#Portnumberforbackend0感谢阅读#Weightforbackend0(onlyinloadbalancingmode)精品文档放心下载#Datadirectoryforbackend0谢谢阅读.#Controlsvariousbackendbehavior谢谢阅读#ALLOW_TO_FAILOVERorDISALLOW_TO_FAILOVER精品文档放心下载#-Authentication-enable_pool_hba=on#Usepool_hba.confforclientauthentication精品文档放心下载pool_passwd='pool_passwd'精品文档放心下载#Filenameofpool_passwdformd5authentication.谢谢阅读#""disablespool_passwd.感谢阅读#(changerequiresrestart)精品文档放心下载authentication_timeout=60精品文档放心下载#Delayinsecondstocompleteclientauthentication谢谢阅读#0meansnotimeout.#-SSLConnections-ssl=off#EnableSSLsupport#(changerequiresrestart)感谢阅读#ssl_key='./server.key'#PathtotheSSLprivatekeyfile精品文档放心下载#(changerequiresrestart)精品文档放心下载#ssl_cert='./server.cert'谢谢阅读#PathtotheSSLpubliccertificatefile精品文档放心下载#(changerequiresrestart)感谢阅读#ssl_ca_cert=''#PathtoasinglePEMformatfile谢谢阅读#containingCArootcertificate(s)谢谢阅读#(changerequiresrestart)感谢阅读#ssl_ca_cert_dir=''#DirectorycontainingCArootcertificate(s)精品文档放心下载#(changerequiresrestart)精品文档放心下载#------------------------------------------------------------------------------谢谢阅读#POOLS#------------------------------------------------------------------------------感谢阅读#-Poolsize-num_init_children=32#Numberofpools#(changerequiresrestart)感谢阅读max_pool=4#Numberofconnectionsperpool谢谢阅读#(changerequiresrestart)谢谢阅读#-Lifetime-child_life_time=300#Poolexitsafterbeingidleforthismanyseconds精品文档放心下载child_max_connections=0#Poolexitsafterreceivingthatmanyconnections谢谢阅读#0meansnoexitconnection_life_time=0.#Connectiontobackendclosesafterbeingidleforthismanyseconds#0meansnoclose谢谢阅读client_idle_limit=0#Clientisdisconnectedafterbeingidleforthatmanyseconds谢谢阅读#(eveninsideanexplicittransactions!)精品文档放心下载#0meansnodisconnection谢谢阅读#------------------------------------------------------------------------------感谢阅读#LOGS#------------------------------------------------------------------------------感谢阅读#-Wheretolog-log_destination='stderr'感谢阅读#Wheretolog#Validvaluesarecombinationsofstderr,精品文档放心下载#andsyslog.Defaulttostderr.感谢阅读#-Whattolog-log_line_prefix='%t:pid%p:' #printf-stylestringtooutputatbeginningofeachlogline.感谢阅读log_connections=off#Logconnectionslog_hostname=off#Hostnamewillbeshowninpsstatus感谢阅读#andinlogsifconnectionsarelogged谢谢阅读log_statement=off#Logallstatementslog_per_node_statement=off谢谢阅读#Logallstatements#withnodeandbackendinformations谢谢阅读log_standby_delay='if_over_threshold'精品文档放心下载#Logstandbydelay#Validvaluesarecombinationsofalways,谢谢阅读#if_over_threshold,none#-Syslogspecific-syslog_facility='LOCAL0'感谢阅读#Sysloglocalfacility.DefaulttoLOCAL0精品文档放心下载syslog_ident='pgpool'#Syslogprogramidentificationstring感谢阅读#Defaultto'pgpool'#-Debug-debug_level=0#Debugmessageverbositylevel精品文档放心下载#0meansnomessage,1ormoremeanverbose感谢阅读#log_error_verbosity=default感谢阅读

#terse,default,orverbosemessages谢谢阅读#client_min_messages=notice谢谢阅读

#valuesinorderofdecreasingdetail:谢谢阅读# debug5.# debug4# debug3# debug2# debug1# log# notice# warning# error#log_min_messages=warning #valuesinorderofdecreasingdetail:精品文档放心下载# debug5# debug4# debug3# debug2# debug1# info# notice# warning# error# log# fatal# panic#------------------------------------------------------------------------------精品文档放心下载#FILELOCATIONS#------------------------------------------------------------------------------谢谢阅读pid_file_name='/usr/local/pgpool/pgpool.pid'精品文档放心下载#PIDfilename#(changerequiresrestart)谢谢阅读logdir='/tmp'#DirectoryofpgPoolstatusfile谢谢阅读#(changerequiresrestart)感谢阅读#------------------------------------------------------------------------------感谢阅读#CONNECTIONPOOLING#------------------------------------------------------------------------------谢谢阅读connection_cache=on#Activateconnectionpools感谢阅读#(changerequiresrestart)感谢阅读#Semicolonseparatedlistofqueries感谢阅读#tobeissuedattheendofasession精品文档放心下载#Thedefaultisfor8.3andlater谢谢阅读reset_query_list='ABORT;DISCARDALL'谢谢阅读#Thefollowingoneisfor8.2andbefore精品文档放心下载#reset_query_list='ABORT;RESETALL;SETSESSIONAUTHORIZATIONDEFAULT'感谢阅读#------------------------------------------------------------------------------谢谢阅读#REPLICATIONMODE#------------------------------------------------------------------------------精品文档放心下载replication_mode=off.#Activatereplicationmode感谢阅读#(changerequiresrestart)谢谢阅读replicate_select=off#ReplicateSELECTstatements精品文档放心下载#wheninreplicationmode精品文档放心下载#replicate_selectishigherprioritythan精品文档放心下载#load_balance_mode.insert_lock=off#Automaticallylocksadummyroworatable感谢阅读#withINSERTstatementstokeepSERIALdata感谢阅读#consistency#WithoutSERIAL,nolockwillbeissued谢谢阅读lobj_lock_table=''#Whenrewritinglo_creatcommandin谢谢阅读#replicationmode,specifytablenameto谢谢阅读#lock#-Degeneratehandling-replication_stop_on_mismatch=off感谢阅读#Ondisagreementwiththepacketkind谢谢阅读#sentfrombackend,degeneratethenode精品文档放心下载#whichismostlikely"minority"感谢阅读#Ifoff,justforcetoexitthissession感谢阅读failover_if_affected_tuples_mismatch=off精品文档放心下载#Ondisagreementwiththenumberofaffected精品文档放心下载#tuplesinUPDATE/DELETEqueries,then感谢阅读#degeneratethenodewhichismostlikely精品文档放心下载#"minority".#Ifoff,justabortthetransactionto感谢阅读#keeptheconsistency#------------------------------------------------------------------------------感谢阅读#LOADBALANCINGMODE#------------------------------------------------------------------------------精品文档放心下载load_balance_mode=on#Activateloadbalancingmode谢谢阅读#(changerequiresrestart)谢谢阅读ignore_leading_white_space=on谢谢阅读#Ignoreleadingwhitespacesofeachquery感谢阅读white_function_list=''#Commaseparatedlistoffunctionnames谢谢阅读#thatdon'twritetodatabase感谢阅读#Regexpareacceptedblack_function_list='currval,lastval,nextval,setval'谢谢阅读#Commaseparatedlistoffunctionnames谢谢阅读#thatwritetodatabase#Regexpareaccepteddatabase_redirect_preference_list=''感谢阅读#commaseparatedlistofpairsofdatabaseandnodeid.谢谢阅读#example:postgres:primary,mydb[0-4]:1,mydb[5-9]:2'精品文档放心下载.#validforstreamingreplicatonmodeonly.精品文档放心下载app_name_redirect_preference_list=''精品文档放心下载#commaseparatedlistofpairsofappnameandnodeid.谢谢阅读#example:'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'谢谢阅读#validforstreamingreplicatonmodeonly.精品文档放心下载allow_sql_comments=off#ifon,ignoreSQLcommentswhenjudgingifloadbalanceor精品文档放心下载#querycacheispossible.谢谢阅读#Ifoff,SQLcommentseffectivelypreventthejudgment谢谢阅读#(pre3.4behavior).#------------------------------------------------------------------------------精品文档放心下载#MASTER/SLAVEMODE#------------------------------------------------------------------------------精品文档放心下载master_slave_mode=on#Activatemaster/slavemode感谢阅读#(changerequiresrestart)感谢阅读master_slave_sub_mode='stream'精品文档放心下载#Master/slavesubmode#Validvaluesarecombinationsslonyor谢谢阅读#stream.Defaultisslony.精品文档放心下载#(changerequiresrestart)精品文档放心下载#-Streaming-sr_check_period=5#Streamingreplicationcheckperiod感谢阅读#Disabled(0)bydefaultsr_check_user='repuser'#Streamingreplicationcheckuser精品文档放心下载#Thisisneccessaryevenifyoudisablestreaming谢谢阅读#replicationdelaycheckbysr_check_period=0感谢阅读sr_check_password='123456'谢谢阅读#Passwordforstreamingreplicationcheckuser精品文档放心下载delay_threshold=10000000感谢阅读#Thresholdbeforenotdispatchingquerytostandbynode精品文档放心下载#Unitisinbytes#Disabled(0)bydefault#-Specialcommands-follow_master_command=''谢谢阅读#Executesthiscommandaftermasterfailover谢谢阅读#Specialvalues:# %d=nodeid# %h=hostname# %p=portnumber# %D=databaseclusterpath感谢阅读# %m=newmasternodeid# %H=hostnameofthenewmasternode精品文档放心下载# %M=oldmasternodeid# %P=oldprimarynodeid谢谢阅读# %r=newmasterportnumber精品文档放心下载# %R=newmasterdatabaseclusterpath谢谢阅读.# %%='%'character#------------------------------------------------------------------------------精品文档放心下载#HEALTHCHECK#------------------------------------------------------------------------------谢谢阅读health_check_period=0#Healthcheckperiod#Disabled(0)bydefaulthealth_check_timeout=20#Healthchecktimeout#0meansnotimeouthealth_check_user='nobody'精品文档放心下载#Healthcheckuserhealth_check_password=''感谢阅读#Passwordforhealthcheckuser谢谢阅读health_check_max_retries=0谢谢阅读#Maximumnumberoftimestoretryafailedhealthcheckbeforegivingup.精品文档放心下载health_check_retry_delay=1谢谢阅读#Amountoftimetowait(inseconds)betweenretries.精品文档放心下载connect_timeout=10000#Timeoutvalueinmillisecondsbeforegivinguptoconnecttobackend.感谢阅读#Defaultis10000ms(10second).Flakynetworkusermaywanttoincrease精品文档放心下载#thevalue.0meansnotimeout.感谢阅读#Notethatthisvalueisnotonlyusedforhealthcheck,感谢阅读#butalsoforordinaryconectiontobackend.感谢阅读#------------------------------------------------------------------------------谢谢阅读#FAILOVERANDFAILBACK#------------------------------------------------------------------------------感谢阅读#failover_command=''failover_command='/home/postgres/scripts/failover_stream.sh%d%H/usr/local/pgsql/data/pg.trigger'精品文档放心下载#Executesthiscommandatfailover谢谢阅读#Specialvalues:# %d=nodeid# %h=hostname# %p=portnumber# %D=databaseclusterpath谢谢阅读# %m=newmasternodeid# %H=hostnameofthenewmasternode精品文档放心下载# %M=oldmasternodeid# %P=oldprimarynodeid谢谢阅读# %r=newmasterportnumber精品文档放心下载# %R=newmasterdatabaseclusterpath感谢阅读# %%='%'characterfailback_command=''#Executesthiscommandatfailback.精品文档放心下载#Specialvalues:# %d=nodeid# %h=hostname# %p=portnumber# %D=databaseclusterpath感谢阅读# %m=newmasternodeid# %H=hostnameofthenewmasternode精品文档放心下载# %M=oldmasternodeid# %P=oldprimarynodeid感谢阅读.# %r=newmasterportnumber精品文档放心下载# %R=newmasterdatabaseclusterpath谢谢阅读# %%='%'characterfail_over_on_backend_error=on感谢阅读#Initiatesfailoverwhenreading/writingtothe谢谢阅读#backendcommunicationsocketfails感谢阅读#Ifsettooff,pgpoolwillreportan感谢阅读#erroranddisconnectthesession.精品文档放心下载search_primary_node_timeout=10感谢阅读#Timeoutinsecondstosearchforthe感谢阅读#primarynodewhenafailoveroccurs.精品文档放心下载#0meansnotimeout,keepsearching感谢阅读#foraprimarynodeforever.感谢阅读#------------------------------------------------------------------------------谢谢阅读#ONLINERECOVERY#------------------------------------------------------------------------------感谢阅读recovery_user='postgres'谢谢阅读#Onlinerecoveryuserrecovery_password='123456'精品文档放心下载#Onlinerecoverypassword谢谢阅读recovery_1st_stage_command='basebackup.sh'谢谢阅读#Executesacommandinfirststage谢谢阅读recovery_2nd_stage_command=''精品文档放心下载#Executesacommandinsecondstage精品文档放心下载recovery_timeout=90#Timeoutinsecondstowaitforthe感谢阅读#recoveringnode'spostmastertostartup精品文档放心下载#0meansnowaitclient_idle_limit_in_recovery=0谢谢阅读#Clientisdisconnectedafterbeingidle精品文档放心下载#forthatmanysecondsinthesecondstage谢谢阅读#ofonlinerecovery#0meansnodisconnection感谢阅读#-1meansimmediatedisconnection精品文档放心下载#------------------------------------------------------------------------------精品文档放心下载#WATCHDOG#------------------------------------------------------------------------------精品文档放心下载#-Enabling-use_watchdog=off#Activateswatchdog#(changerequiresrestart)谢谢阅读#-Connectiontoupstreamservers-谢谢阅读trusted_servers=''#trustedserverlistwhichareused谢谢阅读#toconfirmnetworkconnection谢谢阅读#(hostA,hostB,hostC,...).#(changerequiresrestart)感谢阅读ping_path='/bin'#pingcommandpath#(changerequiresrestart)感谢阅读#-WatchdogcommunicationSettings-精品文档放心下载wd_hostname=''#HostnameorIPaddressofthiswatchdog感谢阅读#(changerequiresrestart)感谢阅读wd_port=9000#portnumberforwatchdogservice谢谢阅读#(changerequiresrestart)感谢阅读wd_authkey=''#Authenticationkeyforwatchdogcommunication感谢阅读#(changerequiresrestart)感谢阅读#-VirtualIPcontrolSetting-感谢阅读delegate_IP=''#delegateIPaddress#Ifthisisempty,virtualIPneverbringup.感谢阅读#(changerequiresrestart)谢谢阅读ifconfig_path='/sbin'#ifconfigcommandpath#(changerequiresrestart)感谢阅读if_up_cmd='ifconfigeth0:0inet$_IP_$netmask'精品文档放心下载#startupdelegateIPcommand感谢阅读#(changerequiresrestart)感谢阅读if_down_cmd='ifconfigeth0:0down'精品文档放心下载#shutdowndelegateIPcommand精品文档放心下载#(changerequiresrestart)感谢阅读arping_path='/usr/sbin' #arpingcommandpath谢谢阅读#(changerequiresrestart)精品文档放心下载arping_cmd='arping-U$_IP_$-w1'精品文档放心下载#arpingcommand#(changerequiresrestart)谢谢阅读#-BehaivoronescalationSetting-感谢阅读clear_memqcache_on_escalation=on感谢阅读#Clearallthequerycacheonsharedmemory感谢阅读#whenstandbypgpoolescalatetoactivepgpool精品文档放心下载#(=virtualIPholder).#Thisshouldbeoffifclientconnectstopgpool感谢阅读#notusingvirtualIP.#(changerequiresrestart)精品文档放心下载wd_escalation_command=''谢谢阅读#Executesthiscommandatescalationonnewactivepgpool.精品文档放心下载#(changerequiresrestart)精品文档放心下载#-LifecheckSetting-#--common--.wd_lifecheck_method='heartbeat'精品文档放心下载#Methodofwatchdoglifecheck('heartbeat'or'query')谢谢阅读#(changerequiresrestart)精品文档放心下载wd_interval=10#lifecheckinterval(sec)>0谢谢阅读#(changerequiresrestart)谢谢阅读#--heartbeatmode--wd_heartbeat_port=9694#Portnumberforreceivingheartbeatsignal谢谢阅读#(changerequiresrestart)精品文档放心下载wd_heartbeat_keepalive=2精品文档放心下载#Intervaltimeofsendingheartbeatsignal(sec)精品文档放心下载#(changerequiresrestart)谢谢阅读wd_heartbeat_deadtime=30谢谢阅读#Deadtimeintervalforheartbeatsignal(sec)谢谢阅读#(changerequiresrestart)感谢阅读heartbeat_destination0='host0_ip1'精品文档放心下载#HostnameorIPaddressofdestination0谢谢阅读#forsendingheartbeatsignal.感谢阅读#(changerequiresrestart)谢谢阅读heartbeat_destination_port0=9694谢谢阅读#Portnumberofdestination0forsending感谢阅读#heartbeatsignal.Usuallythisisthe感谢阅读#sameaswd_heartbeat_port.精品文档放心下载#(changerequiresrestart)感谢阅读heartbeat_device0=''#NameofNICdevice(suchlike'eth0')精品文档放心下载#usedforsending/receivingheartbeat谢谢阅读#signalto/fromdestination0.感谢阅读#Thisworksonlywhenthisisnotempty谢谢阅读#andpgpoolhasrootprivilege.谢谢阅读#(changerequiresrestart)谢谢阅读#heartbeat_destination1='host0_ip2'谢谢阅读#heartbeat_destination_port1=9694谢谢阅读#heartbeat_device1=''#--querymode--wd_life_point=3#lifecheckretrytimes#(changerequiresrestart)谢谢阅读wd_lifecheck_query='SELECT1'谢谢阅读#lifecheckquerytopgpoolfromwatchdog谢谢阅读#(changerequiresrestart)感谢阅读wd_lifecheck_dbname='template1'精品文档放心下载#Databasenameconnectedforlifecheck谢谢阅读#(changerequiresrestart)感谢阅读wd_lifecheck_user='nobody'感谢阅读#watchdogusermonitoringpgpoolsinlifecheck感谢阅读#(changerequiresrestart)精品文档放心下载wd_lifecheck_password=''精品文档放心下载#Passwordforwatchdoguserinlifecheck精品文档放心下载.#(changerequiresrestart)精品文档放心下载#-OtherpgpoolConnectionSettings-精品文档放心下载#other_pgpool_hostname0='host0'感谢阅读#HostnameorIPaddresstoconnecttoforotherpgpool0精品文档放心下载#(changerequiresrestart)精品文档放心下载#other_pgpool_port0=5432感谢阅读#Portnumberforothetpgpool0谢谢阅读#(changerequiresrestart)感谢阅读#other_wd_port0=9000#Portnumberforothetwatchdog0精品文档放心下载#(changerequiresrestart)谢谢阅读#other_pgpool_hostname1='host1'谢谢阅读#other_pgpool_port1=5432精品文档放心下载#other_wd_port1=9000#------------------------------------------------------------------------------精品文档放心下载#OTHERS#------------------------------------------------------------------------------感谢阅读relcache_expire=0#Lifetimeofrelationcacheinseconds.谢谢阅读#0meansnocacheexpiration(thedefault).精品文档放心下载#Therelationcacheisusedforcachethe感谢阅读#queryresultagainstPostgreSQLsystem精品文档放心下载#catalogtoobtainvariousinformation感谢阅读#includingtablestructuresorifit'sa感谢阅读#temporarytableornot.Thecacheis谢谢阅读#maintainedinapgpoolchildlocalmemory精品文档放心下载#andbeingkeptaslongasitsurvives.精品文档放心下载#Ifsomeonemodifythetablebyusing感谢阅读#ALTERTABLEorsomesuch,therelcacheis谢谢阅读#notconsistentanymore.#Forthispurpose,cache_expiration精品文档放心下载#controlsthelifetimeofthecache.谢谢阅读relcache_size=256#Numberofrelationcache感谢阅读#entry.Ifyouseefrequently:谢谢阅读#"pool_search_relcache:cachereplacementhappend"谢谢阅读#inthepgpoollog,youmightwanttoincreatethisnumber.精品文档放心下载check_temp_table=on#Ifon,enabletemporarytablecheckinSELECTstatements.谢谢阅读#Thisinitiatesqueriesagainstsystemcatalogofprimary/master谢谢阅读#thusincreasesloadofmaster.感谢阅读#Ifyouareabsolutelysurethatyoursystemneverusestemporarytables感谢阅读#andyouwanttosaveaccesstoprimary/master,youcouldturnthisoff.精品文档放心下载#Defaultison.check_unlogged_table=on#Ifon,enableunloggedtablecheckinSELECTstatements.谢谢阅读#Thisinitiatesqueriesagainstsystemcatalogofprimary/master精品文档放心下载#thusincreasesloadofmaster.精品文档放心下载#Ifyouareabsolutelysurethatyoursystemneverusesunloggedtables感谢阅读#andyouwanttosaveaccesstoprimary/master,youcouldturnthisoff.感谢阅读.#Defaultison.#------------------------------------------------------------------------------感谢阅读#INMEMORYQUERYMEMORYCACHE感谢阅读#------------------------------------------------------------------------------精品文档放心下载memory_cache_enabled=off谢谢阅读#Ifon,usethememorycachefunctionality,offbydefault谢谢阅读memqcache_method='shmem'精品文档放心下载#Cachestoragemethod.either'shmem'(sharedmemory)or谢谢阅读#'memcached'.'shmem'bydefault感谢阅读#(changerequiresrestart)感谢阅读memqcache_memcached_host='localhost'感谢阅读#MemcachedhostnameorIPaddress.Mandatoryif感谢阅读#memqcache_method='memcached'.感谢阅读#Defaultstolocalhost.#(changerequiresrestart)谢谢阅读memqcache_memcached_port=11211精品文档放心下载#Memcachedportnumber.Mondatoryifmemqcache_method='memcached'.精品文档放心下载#Defaultsto11211.#(changerequiresrestart)感谢阅读memqcache_total_size=67108864精品文档放心下载#Totalmemorysizeinbytesforstoringmemorycache.精品文档放心下载#Mandatoryifmemqcache_method='shmem'.感谢阅读#Defaultsto64MB.#(changerequiresrestart)精品文档放心下载memqcache_max_num_cache=1000000感谢阅读#Totalnumberofcacheentries.Mandatory感谢阅读#ifmemqcache_method='shmem'.谢谢阅读#Eachcacheentryconsumes48bytesonsharedmemory.精品文档放心下载#Defaultsto1,000,000(45.8MB).感谢阅读#(changerequiresrestart)精品文档放心下载memqcache_expire=0#Memorycacheentrylifetimespecifiedinseconds.感谢阅读#0meansinfinitelifetime.0bydefault.谢谢阅读#(changerequiresrestart)感谢阅读memqcache_auto_cache_invalidation=on感谢阅读#Ifon,invalidationofquerycacheistriggeredbycorresponding谢谢阅读#DDL/DML/DCL(andmemqcache_expire).Ifoff,itisonlytriggered精品文档放心下载#bymemqcache_expire. onbydefault.感谢阅读#(changerequiresrestart)感谢阅读memqcache_maxcache=409600感谢阅读#MaximumSELECTresultsizeinbytes.精品文档放心下载#Mustbesmallerthanmemqcache_cache_block_size.Defaultsto400KB.感谢阅读#(changerequiresrestart)谢谢阅读memqcache_cache_block_size=1048576感谢阅读#Cacheblocksizeinbytes.Mandatoryifmemqcache_method='shmem'.谢谢阅读#Defaultsto1MB.#(changerequiresrestart)谢谢阅读memqcache_oiddir='/usr/local/pgpool/oiddir'感谢阅读#Temporaryworkdirectorytorecordtableoids谢谢阅读#(changerequiresrestart)谢谢阅读white_memqcache_table_list=''精品文档放心下载#Commaseparatedlistoftablenamestomemcache感谢阅读#thatdon'twritetodatabase感谢阅读#Regexpareacceptedblack_memqcache_table_list=''精品文档放心下载.#Commaseparatedlistoftablenamesnottomemcache谢谢阅读#thatdon'twritetodatabase感谢阅读#Regexpareacceptedbackend_hostname0='45'精品文档放心下载backend_port0=5432backend_weight0=1backend_data_directory0='/usr/local/pgsql/data'感谢阅读backend_flag0='ALLOW_TO_FAILOVER'精品文档放心下载backend_hostname1='46'谢谢阅读backend_port1=5432backend_weight1=2backend_data_directory1='/usr/local/pgsql/data'精品文档放心下载backend_flag1='ALLOW_TO_FAILOVER'谢谢阅读backend_hostname2='47'精品文档放心下载backend_port2=5432backend_weight2=2backend_data_directory2='/usr/local/pgsql/data'谢谢阅读backend_flag2='ALLOW_TO_FAILOVER'精品文档放心下载在主库的主机上/home/postgres/scripts下新建文件failover_stream.sh感谢阅读#!/bin/sh#Failovercommandforstreamingreplication.谢谢阅读#ThisscriptassumesthatDBnode0isprimary,and1isstandby.谢谢阅读##Ifstandbygoesdown,donothing.Ifprimarygoesdown,createa感谢阅读#t