$Header: /cvsroot/pgpool/pgpool/README.euc_jp,v 1.12 2006/06/04 10:03:30 t-ishii Exp $

pgpool version 3.0(kalekale) README

1. pgpoolȤ

   pgpoolϡPostgreSQLѤΤ֥ͥסץФǡ 
   PostgreSQL饤ȤPostgreSQLФδ֤ξԤΩ򤷤ޤ
   pgpoolѤ뤳ȤˤäơPostgreSQLФؤ³Хإå
   㸺ǤƥΤΥ롼ץåȤ夵뤳ȤǤޤ

   PostgreSQLΥ饤Ȥpgpool³ȡPostgreSQLؤ
   ³pgpoolΩޤ饤ȤλƤPostreSQLؤ
   ³Ǥ뤳ȤʤĤꡤƱ桼ǡ١
   pgpoolؤ³äȤˤϤPostgreSQLؤ³Ѥ
   

   pgpoolˤϾ㳲ˡ֥ե륪С(ͥס⡼ɻ)
   뤤ϡֽ౿žסʥץꥱ⡼ɻˤεǽΤǡ
   ƥΥ󥿥Ǿ¤˲뤳ȤǤޤ

   ʤե륪Ф౿žϲ㳲Ȥȥꥬˤ
   ưޤᥤƥʥ󥹤ʤɤΤ˰տŪ˥ե륪Ф
   ౿ž⡼ɤ˰ܹԤ뤳ȤǤޤܺ٤ϡ9. åС
   

1.1 ץꥱˤĤ

   pgpoolϡ֥ץꥱפǽǤץꥱ
   뤳ȤˤꡤʪŪ2Υǥ˥ꥢ륿ǥϡɥǥ
   ΥХååפ뤳ȤǤ˥ǥ㳲ȯƤⱿ
   ³뤳ȤǤޤpgpoolΥץꥱƱ䤤碌
   2ΥФ뤳ȤˤäƹԤޤΤǡƱץ
   ΰȤȤˤʤޤʤΤȤץꥱ
   2Τߤǡ3ʾΥץꥱϤǤޤ

   Τ褦ʼǤΤǡpgpoolǤƱ䤤碌äƤۤʤ
   ̤֤褦ʥǡȤȥ󥶥IDOIDSERIAL
   󥹡CURRENT_TIMETSTAMPΤ褦ʤΤ˴ؤƤϥץꥱ
   Ϥޤ2ΥۥȤǤޤäƱͤԡݾڤϤ
   

   SERIALˤĤƤϡҤ"insert_lock"ȤȤˤä
   ǽǤܺ٤ϡ6. pgpoolˡפinsert_lockι򻲾Ȥ
   Ƥ

   ץꥱ˾㳲ȯȡưŪ˽౿žԤޤ
   򼨤ޤ

   master(backend_host_name, backend_portǻ)
   secondary(secondary_backend_host_name, secondary_backend_portǻ)

   2ǥץꥱԤäƤȤޤ

   1) 

      master, secondaryƱΥǡץꥱ󤵤롥

   2) master˾㳲ȯ

      masterڤΥsecondaryΤߤDZѤ³

   3) secondary˾㳲ȯ

      secondaryڤΥmasterΤߤDZѤ³

   4) mastersecondaryΥǡ԰פ򸡽

      pgpoolǤϤޤ󤬡׽׽mastersecondaryΥǡ
      פƤ뤫ɤåƤޤȤСƱơ
      ֥ʤΤۤʤ褦ʥϡ۾ȸʤޤΤ
      ʰ۾郎ФȡsecondaryΤߤDZѤ³ޤ

   ౿žȰʸmastersecondaryΥǡϰפʤ֤ˤʤ
   ǽޤ٥ץꥱžˡöξϤ
   ߤrsyncʤɤȤäƼưǥǡ١饹ξ֤פ
   κݡ㳲ξˤäƤɬmaster¦ǿȤϸ
   ʤΤդƤmastersecondaryΤɤǽ౿ž
   Ƥ뤫ϡpgpoolpsqlʤɤ"show pool_status"ޥɤꤲ
   ȤdzǧǤޤ(ܺٸ)

1.2 ץꥱˤ»

   ޤäƱ䤤碌2ĤΥФȯԤ뤳Ȥˤäƥץꥱ
   ԤpgpoolǤϡñΤPostgreSQLǤȯʤǥåɥå
   ֤ޤͳϡpgpoolǤϡ䤤碌

   1) 䤤碌ΥꥯȤ master, secondary νꤲ

   2) ̤Ԥ碌 master, secondary ν˹Ԥ

   Ȥ褦˽뤫Ǥǥåɥå򼨤ޤǤϡ

   BEGIN;
   LOCK TABLE t1;
   END;

   Ȥñʥȥ󥶥ͤޤʲοޤǤϡbegin, end
   άƤޤޤ2ĤΥåsession 0session 1ʹԤ
   ΤȤޤ

	    master		       secondary
   session 0	session 1	session 0   session 1
   ------------------------------------------------------
   lock	
		lock
					     lock
				lock
   ------------------------------------------------------

   session1mastersession0masterԤ³ޤsecondary
   Ƥϡsession1˥åᡤsession0session1
   Ԥ³ƤޤΤ褦ˤƤߤˤߤԤ³ǥåɥ
   ֤ȯޤ

   pgpoolǤϡн褹뤿ᡤʲ2ĤˡѰդƤޤ

   1) pgpool.confreplication_stricttrueˤ

      ȡsecondarymaster䤤碌äƤ
      䤤碌褦ˤʤޤǥåɥåȯ붲
      ʤʤޤmastersecondaryǤʤʤ뤿ᡤ
      ѥեޥ󥹤ǽޤäȤˡǤ
      pgpoolϥǥեȤǤΥ⡼ɤưޤ

      ʤ碌Τstrict⡼ɤˤʤϡ
      /*NO STRICT*/Ȥ碌ƬˤƤ

   2) ǥåɥåȯǽΤ䤤碌Ƭ̤ʥ
      ɡ/*STRICT*/פ

      ȤоǸ

      /*STRICT*/ LOCK TABLE t1;

      Ȥޤȡ䤤碌mastersecondary
      ʤʤΤǡǥåɥåȯޤ/*STRICT*/
      Ƥʤ¾䤤碌ΤǡΨɤʤ
      ޤ

      ʤPostgreSQLǤϡ/**/ޤǤϥȤȤưΤǡ
      DB˱ƶϤޤ

1.3 replication_timeoutˤĤ

   ǥåɥåȯȤreplication_timeoutꤹ
   뤳ȤǤޤñ̤ϥߥ(1/1000)Ǥ⤷masterν
   äƤsecondaryreplication_timeout˱֤ʤϡ
   åλ뤳Ȥˤäƥǥåɥå֤ä
   

   ʤmaster١secondaryǽˤsecondary
   ٤졤ǥåɥåǤϤʤΤ˥ॢȤˤʤ뤳Ȥ
   ξŬreplication_timeout礭뤫
   replication_stricttrue ˤޤ

   : replication_stricttrueξˤϤΥѥ᡼̵뤵
   

1.4 ʬ(load balance)ˤĤ

   ץꥱͭʾ硤pgpool.conf"load_balance_mode"
   trueꤹ뤳ȤˤSELECTʸʬǽǤʲξ
   Ȥǥ˥ޥȥSELECTʸȯԤ졤ǽ
   夬ԤǤޤ

   1) ץȥСV3Ǥ뤳ȡʤХåɤ
      PostgreSQL 7.4ʸǤ뤳

   2) SQLʸΤ˹ƬSELECT(뤤select)ǻϤޤäƤ뤳

   3) SELECTʸŪʥȥ󥶥֥åǼ¹ԤƤʤ
      

   ʤΤȤʤSELECT FOR UPDATE䡤ȼؿƤӽФ
   褦ѤΤSELECTʸѤƤ꤬ޤ
   褦ʾload_balance_mode"falseˤ뤫SELECTʸιƬ˥ڡ
   /*NO LOAD BALANCE*/Τ褦ʥȤʬʤ
   ˤƤ

   ʤߤˡregression testˤϤΤ褦SELECTʸޤޤƤ뤿
   (ȤconstraintsƥȤΡSELECT 'one' AS one,
   nextval('insert_seq');)ɥХ󥹥⡼ɤͭˤʤäƤ
   regression test̤ޤ

   ʤExtended query(Pare/Prepare/Bind/ExecuteѤ)䤤碌
   ʬоݤˤʤޤ󡣤ϡExecuteSELECTʸɤ
   Ƚ꤬ǤʤǤȤСPostgreSQL 8.0ʹߤJDBCɥ饤
   ǤɬExtended queryȤᡢºȤʬǤޤ

2. pgpoolΥå

   ˤpgpoolʳˤǡ١ѤΥͥס륵Ф
   ¸ߤޤӤpgpoolΥåȤޤ

   (1) ץꥱѹɬפޤ

       ͥסѤΥեȤˤѤ̤API(ץꥱ
       ץ।󥿡ե)ͳʤѤǤʤΤ⤢
       ޤpgpoolϥ饤Ȥ鸫̤PostgreSQL
       ˸뤿ᡤΤ褦APIѤɬפޤ󡥺ޤ
       PostgreSQLȤäƤץꥱϡ³Υݡֹ
       ۥȤѹǤpgpoolѤǤ褦ˤʤޤ
       (Ĥ»बޤ)

   (2) ɤθǤȤޤ

        pgpoolPostgreSQLΥ饤Ȥ䥢ץꥱ󤫤鸫
	̤PostgreSQLФ˸ޤäPHP, Perl, Javaʤ
	ƸѤPostgreSQLAPIΤޤ޻Ȥޤ

   (3) preforkƥ

       ApacheʤɤƱ͡餫᥵ХץforkƤ
       ƥѡͥ׵٤˥Хץfork
       ١ХإåɤʤͥƤޤ

   (4) PostgreSQLؤ³¤ǽ

       PHPPostgreSQL˥硤Ʊ³ApacheФ
       ץʤ뤿ᡤPostgreSQL٤ˤʤ꤬Ǥ
       pgpoolǤPostgreSQLؤ³preforkpgpoolΥп
       ä¤뤿ᡤPostgreSQLäȤΨɤưƱ
       ³ݻ뤳ȤǤޤ

   (5) ե륪Сǽ

       pgpoolˤϡ֥ե륪Сפεǽޤʤ
       PostgreSQLФ2ꤷƤ1椬󤷤ݤ˼ưŪ2
       ܤΥФڤؤ뤳ȤǤޤե륪Сǽ
       ꡤƥΥ󥿥Ǿ¤˲뤳ȤǤޤ
       2PostgreSQLФΥǡƱ뵡ǽϤޤ
       ΤǡɬפʤdbmirrorʤɤΥץꥱѥåʻ
       ѤƤ

       ޤե륪Сκݤˤϥ饤ȤȤΥͥ
       Ǥޤäơե륪Сϥ饤ȤϺ
       pgpool³ɬפޤ

   (6) ץꥱǽ顼֥ȤΥץꥱ
       ǽǤ

   (7) ʬǽSELECTʸʬǤޤ

3. pgpoolΥǥå

   (1) Хإåɤޤ

       PostgreSQLФ륢Ϥ٤Ƥäpgpoolͳ뤿ᡤ
       ʬΥХإåɤޤХإåɤ̤Ͼˤä
       ۤʤΤǰ쳵ˤϸޤ󤬡䤬pgbenchǻ¤Ǥ7-
       15%٤ǽǧޤ

   (2) ٤Ƥlibpqץȥ뤬ݡȤƤޤ

       ǤϡʲεǽݡȤƤޤ

       o trust, clear text passwordʳǧ(ץꥱ⡼
        ɤޤϥޥ/졼֥⡼ɻ)

       o trust, clear text password, crypt, md5ʳǧ(ץ
         ⡼ɻ)

       o pgpoolФơpg_hba.confˤ륢ϥݡȤ
         ޤ(pgpool³PostgreSQLǤϤpg_hba.conf
         륢椬ͭˤʤޤ)

   (3) pgpoolΤpg_hba.confˤ륢¤Ϥޤ

       TCP/IPͥĤƤ(Ҥ
       allow_inet_domain_socket1ξ)pgpoolΤˤϤɤΥۥȤ
       Ǥ³ǤƤޤޤɬפʤiptablesʤɤȤäƥ
       ¤򤫤Ʋ(pgpool³PostgreSQL
       ФǤpg_hba.confˤ륢¤ͭǤ)

   (5) »

       o ץꥱˤ»ˤĤƤ1.2

       o template1, regressionȤ̾Υǡ١ϥͥס
         оݤˤʤޤ

       o CREATE TEMP TABLEǺ줿ơ֥ϥեȥɤå
         λƤޤ󡥤ϡͥסθ
         ̤ǥХåɤ鸫ȥå󤬷³Ƥ褦˸
         뤫ǤåνλŪDROP TABLE뤫
         󥶥֥åCREATE TEMP TABLE ... ON COMMIT
         DROP򤪻Ȥ

4. pgpoolβƯĶ

   pgpoolϡlibpqץȥversion 2(PostgreSQL 6.4-7.3.xǺ)
   version 3(PostgreSQL 7.4ʹ)бƤޤåν
   λФǤ䤤碌˰㤤ޤPostgreSQLΥС
   ˱ưʲΤ褦pgpool.confꤷƤ

   7.1뤤ϤΥС:
   reset_query_list = 'ABORT'

   7.2.x:
   reset_query_list = 'ABORT; RESET ALL'

   7.3ʹ:
   reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'

   ѤƤ(Ǹpgpool.confΥǥեͤʤΤǡºݤˤ
   ɬפϤޤ)

   version 2ץȥversion 3ΥեȥɤȥХåɤ
   ƤƤꤢޤpgpoolϥץȥСưȽ̤
   Ŭڤ˽ޤʤޥȥPostgreSQLΥץȥ
   СϹ碌ɬפޤˡ

   бOSä¤ޤ󡥰ʲưǧ줿ĶǤ

   Vine Linux 3.2 (kernel 2.4.31-0vl1.10)/PostgreSQL 8.1.1
   Vine Linux 2.6r4 (kernel 2.4.22-0vl2.12)/PostgreSQL 8.1.0
   Vine Linux 2.6r4 (kernel 2.4.22-0vl2.12)/PostgreSQL 8.0.3
   Vine Linux 2.6r4 (kernel 2.4.22-0vl2.12)/PostgreSQL 8.0
   Vine Linux 2.6r4 (kernel 2.4.22-0vl2.12)/PostgreSQL 7.4.5
   Vine Linux 2.6r4 (kernel 2.4.22-0vl2.12)/PostgreSQL 7.4.3
   Vine Linux 2.6r4 (kernel 2.4.22-0vl2.12)/PostgreSQL 7.4.2
   Vine Linux 2.6r4 (kernel 2.4.22-0vl2.12)/PostgreSQL 7.3.7
   Vine Linux 2.6r4 (kernel 2.4.22-0vl2.12)/PostgreSQL 7.3.6
   Vine Linux 2.6r3 (kernel 2.4.22-0vl2.8)/PostgreSQL 7.4.2
   Vine Linux 2.6r3 (kernel 2.4.22-0vl2.8)/PostgreSQL 7.3.6
   Vine Linux 2.6CR (kernel 2.4.20-0vl29.1)/PostgreSQL 7.3.4
   RedHat Linux 9.0 (kernel 2.4.20)/PostgreSQL 7.3.6
   RedHat Linux 8.0 (kernel 2.4.18-14)/PostgreSQL 7.3.2
   RedHat Linux 6.2 (kernel 2.2.24)/PostgreSQL 7.2.1
   FreeBSD 5.2.1-RELEASE(AMD64)/PostgreSQL 7.4.3
   FreeBSD 5.2.1-RELEASE/PostgreSQL 7.3?
   FreeBSD 4.7-RELEASE/PostgreSQL 7.2.4 
   FreeBSD 4.2-RELEASE/PostgreSQL 7.3.2
   Sparc/Solaris8/PostgreSQL 7.3
   Sparc/Solaris8/PostgreSQL 7.4.3

5. pgpoolΥ󥹥ȡˡ

   (1) pgpoolΥ󥹥ȡ

      ./configure
      make
      make install

      ǥ󥹥ȡ뤬λޤ(GNU makeɬפʤΤǡFreeBSDʤɤǤ
      makegmakeɤؤƤ)ǥեȤΥ󥹥ȡϡ
      /usr/localʲǰʲΤ褦ʥե뤬󥹥ȡ뤵ޤ

      /usr/local/bin/pgpool	ץ
      /usr/local/etc/pgpool.conf.sample	ե륵ץ

      󥹥ȡѹϡconfigure --prefix=path... Ȥ
      Ƥ

6. pgpoolˡ

   pgpoolѥ᡼pgpool.confꤷޤpgpool.conf.sample
   pgpool.conf˥ԡԤäƤ

   pgpool.confν񼰤ϰʲǤ

   (1)  =  ΥڥԤޤ

   (2) ͤͤξϤΤޤ޿񤭤ޤʸξϸ§
       '(ñ)dzޤ:'foo' 򤬴ޤޤʤñ
       άƤ⹽ޤͤιܤˤĤƤϡtrue()
       false()1()0()ǵҤޤ

   (3) Ԥ̵뤵ޤ

   (4) ԤƬ"#"(㡼)Ԥ̵뤵ޤȹԤȤ
       Ȥ

   pgpool.confˤϰʲΤ褦ܤޤ

   listen_address

   TCP/IPͥդ륢ɥ쥹ۥ̾ޤIPɥ쥹
   ꤷޤ*פꤹȤ٤ƤIP󥿥եΥͥ
   դޤ''פꤹTCP/IPͥդ
   󡥥ǥեͤϡlocalhostפǤ
   
   UNIXɥᥤ󥽥åȷͳΥͥϾ˼դޤ

   ʤΥСȸߴݤĤᡤallow_inet_domain_socket
   ǥ쥯ƥ֤Ѥ뤳ȤǤޤ

   allow_inet_domain_socket = 1 ϡ listen_addresses = '*' Ʊǡ
   allow_inet_domain_socket = 0  listen_addresses = '' ƱǤ

   port

   pgpoolͥդݡֹǤǥեͤ9999
   Ǥ

   socket_dir

   pgpoolͥդUnix domain socketǥ쥯ȥǤ
   ǥեͤ'/tmp'Ǥ

   backend_host_name

   pgpool³PostgreSQL(postmaster)ưƤۥȤ̾
   (ʸ)Ǥǥեͤ''ǡξUNIXɥᥤ󥽥åȤ
   ̤³Ԥޤ''ʳʤХۥ̾ȸʤINETɥᥤ
   åȷͳ³ޤξ硤PostgreSQL¦ǤTCP/IPͥ
   դ褦ˤʤäƤ뤳ȡޤpg_hba.confŬڤꤵ
   Ƥpgpool³Ĥ褦ˤʤäƤɬפޤ
   PostgreSQL鸫ȡpgpoolϥ饤Ȥ˸뤳ȤפФ
   Ƥˡ

   backend_port

   PostgreSQLФΥݡֹǤǥեͤ5432Ǥ

   backend_socket_dir

   PostgreSQLФUnix domain socketΥǥ쥯ȥǤǥեͤ'/tmp'Ǥ

   secondary_backend_host_name

   ե륪СǽޤϥץꥱǽѤ硤2
   PostgreSQLФΥۥ̾ꤷޤǥեͤ''Ǥ

   secondary_backend_port

   ե륪СǽޤϥץꥱǽѤ硤2
   PostgreSQLФΥݡֹꤷޤ0ꤹȥե륪
   Фޤ󡥥ǥեͤ0Ǥ

   num_init_children

   preforkpgpoolΥХץοǤǥեͤ32ˤʤä
   ޤ

   ʤ䤤碌Υ󥻥Ԥ̾ΥͥȤ̤˿
   ʥͥĥޤäơ٤ƤΥͥ󤬻
   ξ䤤碌Υ󥻥뤬ǤʤäƤޤΤǡղ
   䤤碌Υ󥻥ɬݾڤϡꤵ륳
   ܤͤꤹ뤳Ȥ򤪤ᤷޤ

   max_pool

   pgpoolγƥХץפPostgreSQLؤκ祳ͥ
   Ǥpgpoolϡ桼̾ǡ١ƱʤХͥ
   ѤޤǤʤпPostgreSQLؤΥͥ
   Ω褦ȤޤäơǤꤵ[桼̾:ǡ١
   ̾]Υڥμοmax_pool˻ꤷƤɬפޤ
   max_poolȤäƤޤäϰָŤͥǤ
   ΥåȤѤޤ

   max_poolΥǥեͤ4Ǥ

   ʤpgpoolΤȤƤϡnum_init_children*max_pool ʬ
   PostgreSQLؤΥͥĥդƤ

   child_life_time

   pgpoolλҥץμ̿Ǥɥ֤ˤʤäƤ
   child_life_time÷в᤹ȡöλƿץưޤ
   ꡼꡼¾ξ㳲ͽ֤Ǥchild_life_timeΥ
   եͤ300áʤ5ʬǤ0ꤹȤεǽƯޤ
   ʤʤưˡʤޤ٤⥳ͥ
   դƤʤץˤchild_life_timeŬѤޤ

   connection_life_time

   ͥסΥ֤ͥͭñ̤ǻꤷޤ0
   ꤹ̵֤ͭ¤ˤʤޤconnection_life_timeΥǥե
   ͤ0Ǥ

   child_max_connections

   pgpoolҥץؤ³ͤĶȡλҥץ
   λޤchild_life_timeconnection_life_timeʤ
   ˻ФǡPostgreSQLХåɤ粽ΤɤΤͭ
   Ǥ

   logdir

   pgpoolγƼեǼǥ쥯ȥǤߤΤȤ
   pgpool.pidȤץIDǼե褦ˤʤ
   ƤޤlogdirΥǥեͤ'/tmp'Ǥ

   replication_mode

   ץꥱ⡼ɤưtrueꤷƤ
   եͤfalseǤ

   replication_strict

   Υץtrueˤȡmaster䤤碌δλԤä
   secondaryν˰ܤޤǥåɥåδϤʤʤޤ
   mastersecondaryδ֤䤤碌ԤʤʤΤǽ
   㲼⤢ޤ

   ʤ٤Ƥ䤤碌ǤϤʤơSQLΥȤȤäư䤤
   碌Τstrictư뤳ȤǤޤܺ٤1.2򻲾ȤƤ
   

   ΥץΥǥեͤtrueǤ

   replication_timeout

   replication_strictfalseΤȤ˥ǥåɥåƻ뤹뤿Υ
   Ȼ֤ߥñ̤ǻꤷޤǥեͤ5000ʤ5
   Ǥ0ꤹȥॢȤʤʤޤ

   load_balance_mode

   trueꤹȥץꥱ⡼ɤκݤˡSELECTʸޥȥ
   δ֤ǥɥХ󥹤ޤǥեͤfalseǤ

   weight_master
   weight_secondary

   ɥХ󥹥⡼ɤλˡޥȥSELECT򿶤ʬ
   ֽŤߡפޤޥȥΥϡɥǽ˺
   ȤͭѤǤ

   Ťߤweight_masterweight_secondaryꤵ줿ͤΨǷꤵ
   ޤȤС

   weight_master = 0.5
   weight_secondary = 0.5

   

   weight_master = 1
   weight_secondary = 1

   ϤޥȥνŤߤʤޤʥǥեȤǤˡ

   weight_master = 1
   weight_secondary = 0.5

   ʤޥˤϥ2ܤγΨSELECT꿶ޤ

   weight_master = 1
   weight_secondary = 0
   
   ȤǽǡξϥޥSELECT꿶褦
   ˤʤޤINSERTʤɤιϤ䤤碌ϥޥȥ
   ξ˾ꤲޤ

   replication_stop_on_mismatch

   trueꤹȥޥȥδ֤ǥǡ԰פä
   ˶Ū˽౿žޤΥץfalseξϡ
   䤤碌Ū˽λαޤǥեͤfalseǤ

   reset_query_list

   å󤬽λȤ˥ͥ뤿SQLޥ
   ;פǶڤä󤷤ޤǥեȤϰʲΤ褦ˤʤäƤޤ
   ǤդSQLʸɲäƤ⹽ޤ

   reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'

   PostgreSQLΥСˤäƻѤǤSQLޥɤ㤦Τǡ
   PostgreSQL 7.3ǤդƤ(4. pgpoolβƯĶ׻)

   ʤABORTפϡPostgreSQL 7.4ʾǤϥȥ󥶥֥å
   ˤʤˤȯԤޤ

   print_timestamp

   trueʤpgpoolΥ˥ॹפɲäޤǥեȤ
   trueǤ

   master_slave_mode

   trueʤХޥ/졼֥⡼ɤpgpoolžޤܺ٤14򸫤
   ǥեȤfalseǤΥ⡼ɤreplication_modeȤξ
   Ωޤ

   connection_cache

   trueʤ饳ͥ򥭥å夷ޤǥեȤtrueǤ

   health_check_timeout

   pgpoolϥо㳲ͥåȥ㳲Τ뤿ˡŪ˥Х
   ɤ³ߤޤ֥إ륹åפȸޤ㳲
   Τȡե륪Ф౿žߤޤ

    ѥ᡼ϡͥåȥ֥뤬ȴݤʤɤ˥إ륹
   ĹԤΤɤΥॢͤñ̤ǻꤷ
   ǥեȤ20äǤ0ꤹȥॢȽ򤷤ޤ

   ʤإ륹åͭˤȡإ륹åΤ;ʬ
   ³1ɬפˤʤޤΤǡPostgreSQLpostgresql.confܤ
   max_connections򾯤Ȥ1䤹褦ˤƤ

   health_check_period

   إ륹åԤֳ֤ñ̤ǻꤷޤ0ꤹȥإ륹
   åԤޤ󡥥ǥեȤ0Ǥ(Ĥޤإ륹åԤ
   ޤ)

   health_check_user

   إ륹åԤPostgreSQL桼̾Ǥ

   insert_lock

   SERIALȤäƤơ֥ץꥱ󤹤ȡSERIAL
   ͤޥȥǰפʤʤ뤳Ȥޤϡ
   ơ֥Ū˥å뤳ȤDzǤޤ(ȥ
   ¹ϵˤʤޤ)Τˤϡ

   INSERT INTO ...

   

   BEGIN;
   LOCK TABLE ...
   INSERT INTO ...
   COMMIT;

   ˽񤭴ʤФʤޤinsert_locktrueˤȼưŪ˥ȥ
   󥶥γϡơ֥åȥ󥶥νλԤä
   Τǡ֤ʤȤǤޤʤǤ˥ȥ󥶥
   󤬳ϤƤLOCK TABLE...¹Ԥޤˡ

   Υơ֥SERIALȤƤ뤫ɤȽꤵ
   櫓ǤϤʤΤǡSERIALȤäƤʤơ֥Ǥ⤳
   ԤƤޤޤʵǽپ㤬櫓ǤϤʤΤǤ®
   ٤ʤäƤޤޤˡ

   򤹤ˤ2Ĥˡޤ

   1) insert_locktrueˤơINSERTʸƬ/*NO INSERT LOCK*/
      Ȥɲä롥ΥȤȡơ֥åϹԤޤ
      

   2) insert_lockfalseˤơINSERTʸƬ/*INSERT LOCK*/
      Ȥɲä롥ΥȤȡINSERTʸФƤΤߥơ
      ֥åԤޤ

   SERIALȤäƤơ֥뤬¿1)򡤾ʤ2)
   ɤǤ礦

   insert_lockΥǥեͤfalseǤ

   ʤinsert_lockͭˤregression test¹ԤȡȤ
   PostgreSQL 8.0Ǥtransactions, privileges, rules, alter_table
   failޤruleǤϡviewФLOCK򤷤褦ȤƤޤȡ
   ۤΤΤ

   ! ERROR:  current transaction is aborted, commands ignored until end of transaction block

   Ȥ褦ʥåФƤޤǤȤСtransactions 
   Ǥϡ¸ߤʤơ֥ФINSERTԤƥȤޤޤƤꡤ
   pgpoolǽ¸ߤʤơ֥ФLOCKԤ̡顼ˤʤ
   ƥȥ󥶥󤬥ܡȾ֤ˤʤꡤ³INSERTǾ嵭顼
   Ƥޤޤ

   ignore_leading_white_space

   trueʤСload balanceκݤSQLʸƬζ̵뤷ޤ(ѥ
   ڡ̵뤵ޤ)ϡDBI/DBD:PgΤ褦ˡ˹Ƭ˥
   磻ȥڡɲä褦APIȤɥХ󥹤Ȥ
   ͭǤ

   log_statement

   trueʤSQLʸϤޤܤPostgreSQL
   log_statementץȻƤơǥХåץ󤬤ʤȤǤ
   䤤碌ϤĴ٤뤳ȤǤΤǤ

7. pgpoolεư

   pgpoolưäȤñˡϡ

   $ pgpool

   ȤǤ/usr/local/etc/pgpool.confɤ߹ߡ
   ǥФưޤ

   ǽʥץϼ̤Ǥ

   -f path

   ǥեȰʳΥե졼եΥѥ̾ꤷޤ

   -n

   ǡ⡼ɤǵưޤ󡥤Υץloggerrotatelogs
   ɤȤäƥ顼åե˽ϤȤʤɤǤ

   -d

   ǥХåå̤˽Ϥޤ

   -h

   إץåϤƽλޤ

8. pgpoolνλ

   stopץץȤޤ

   $ pgpool [-f path_to_configuration_file][-m {s[mart]|f[ast]|i[mmediate]}] stop

   ߤǤޤư-fץե򤷤ƤȤϡ
   stopˤ-fץեꤷƤ

   ޤեȥɤ³λƤʤҥץȡ
   pgpoolϽλˤ³λΤԤޤstopꥯȤ
   ȯԤ줿ʸϡ˥饤Ȥpgpool³뤳ȤϤǤ
   

   Ԥ˶Ū˽λˤϡʲΤ褦ˤޤ

   $ pgpool -m fast stop

   ޤ

   $ pgpool -m immediate stop

   fast"f"immediate"i"Ⱦά뤳ȤǤޤʤߤˡ

   $ pgpool -m smart stop

   ޤ

   $ pgpool -m s stop

   Ȥȡpgpool stopƱư򤷤ޤ

9. å

   ᥤƥʥ󥹤ʤɤΤ˰տŪ˥ե륪Ф౿ž⡼ɤ
   ܹԤ뤳ȤǤޤ

   $ pgpool [-f config_file] [-s {m[aster]|s[econdary]] switch

   -sץάȡޥ¦Ȥ˥ե륪
    (ͥס⡼ɤư)ޤϽ(ץꥱ⡼
    ɤư)ޤȤϡ

   $ pgpool [-f config_file] -s s[econdary] switch

   Ȥޤ

   ʤPostgreSQLФ1ξ֤ưƤϡߤΥ饤
   ȤpgpoolؤΥͥŪ˰Ǥpgpoolλҥץ
   Ƶưޤ

10. μ

   pgpool-nץդǵưȡstderr(ɸ२顼)˥顼
   ʾ(ȤХե륪Ф褦ʾ)˴ؤå
   ϤޤΤޤޥե˥쥯Ȥ뤳Ȥˤꡤ
   pgpoolΥ뤳ȤǤޤ

   [¹]

   pgpool -n >& /tmp/pgpool.log &
   ޤ
   pgpool -n > /tmp/pgpool.log 2>&1 &

   ΤȤprint_timestamptrueˤƤȡॹפ
   ղäޤ(print_timestampϥǥեȤtrueǤ)

   ޤloggerޥɤȤäsyslog˥åϤΤ褤
   ˡǤ

   [¹]

   pgpool -n 2>&1 |logger -t pgpool -p local0.info&

   ˤꡤʲΤ褦ʥåsyslog˽Ϥޤ

   Apr 13 15:07:11 srapc1977  4 13 15:07:11 pgpool: log: pid 2038: starting failover from (5432) to (5433)
   Apr 13 15:07:11 srapc1977  4 13 15:07:11 pgpool: log: pid 2038: failover from (5432) to (5433) done.


11. pgpoolμ

    pgpoolǧƤե(pgpool.conf)Ƥ䡤ߤΥ
    ꥱξ֤SQLȯԤ뤳ȤˤäƼ뤳ȤǤ
    ȤСpgpoolƱۥȤΥݡ9999ưƤȤȡ
    ʲΤ褦ˤƾǤޤ

    psql -p 9999 -c 'show pool_status' template1

    (ǡ١̾ϲǤ⤫ޤޤ)

             item             |                        value                         |                              description                               
------------------------------+------------------------------------------------------+------------------------------------------------------------------------
 listen_addresses             | *                                                    | host name(s) or IP address(es) to listen to
 port                         | 9998                                                 | pgpool accepting port number
 socket_dir                   | /tmp                                                 | pgpool socket directory
 backend_host_name            |                                                      | master backend host name
 backend_port                 | 5432                                                 | master backend port number
 secondary_backend_host_name  |                                                      | secondary backend host name
 secondary_backend_port       | 5433                                                 | secondary backend port number
 num_init_children            | 32                                                   | # of children initially pre-forked
 child_life_time              | 0                                                    | if idle for this seconds, child exits
 connection_life_time         | 0                                                    | if idle for this seconds, connection closes
 max_pool                     | 2                                                    | max # of connection pool per child
 logdir                       | /tmp                                                 | logging directory
 backend_socket_dir           | /tmp                                                 | Unix domain socket directory for the PostgreSQL server
 replication_mode             | 1                                                    | non 0 if operating in replication mode
 replication_strict           | 1                                                    | non 0 if operating in strict mode
 replication_timeout          | 5000                                                 | if secondary does not respond in this milli seconds, abort the session
 load_balance_mode            | 0                                                    | non 0 if operating in load balancing mode
 weight_master                | 1.000000                                             | weight of master
 weight_secondary             | 1.000000                                             | weight of secondary
 replication_stop_on_mismatch | 0                                                    | stop replication mode on fatal error
 reset_query_list             | ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT; | queries issued at the end of session
 print_timestamp              | 1                                                    | if true print time stamp to each log line
 master_slave_mode            | 0                                                    | if true, operate in master/slave mode
 connection_cache             | 1                                                    | if true, cache connection pool
 health_check_timeout         | 5                                                    | health check timeout
 health_check_period          | 0                                                    | health check period
 health_check_user            | t-ishii                                              | health check user
 insert_lock                  | 1                                                    | insert lock
 current_backend_host_name    |                                                      | current master host name
 current_backend_port         | 5432                                                 | current master port #
 replication_enabled          | 1                                                    | non 0 if actually operating in replication mode
 master_slave_enabled         | 0                                                    | non 0 if actually operating in master/slave
 num_reset_queries            | 3                                                    | number of queries in reset_query_list
 server_status                | master( on 5432) up secondary( on 5433) up           | server status
(34 rows)

contrib/dblinkȤСʲΤ褦ˤưη̤򸫤뤳ȤǤ
ޤ

test=# SELECT * FROM dblink('port=9999 dbname=test', 'show pool_status')
	AS c1(pname text, val text) WHERE pname = 'port';
 pname | val  
-------+------
 port  | 9999
(1 row)

12. regression testμ»

   ʲΤ褦ˤpgpoolʻѤregression testԤȤǤޤ

   $ cd /usr/local/src/postgresql-7.4.5/src/test/regress
   $ make all
   $ ./pg_regress --schedule=parallel_schedule --port=9999

   : PostgreSQL 8.0Ǥϡsrc/test/regressʲ˥ƥѤΥơ֥
   ڡ뤿ᡤƱۥȾ2ĤΥǡ١饹ꡤ
   ץꥱ⡼ɤregression test򤹤ɬtablespaceΥƥ
   Ȥfailޤϰ۾ǤϤޤ

13. ٥ޡμ»

   ٥ޡ»ܤˤϤˡޤǤ
   pgbenchPHPabȤäñˡ򤴾Ҳ𤷤ޤ

   ޤ٥ޡǡ١ޤ

   $ pgbench -i test
  
   abưޤåȤȤʤPHPץȤϤǤϰʲΤ褦
   ñʤΤȤޤ

   <?php
    ini_set("track_errors", "1");
    define_syslog_variables();

    $con = pg_connect("dbname=test user=postgres port=9999");
    if ($con == FALSE) {
      syslog(LOG_ERR, "could not connect $php_errormsg");
      trigger_error("Could not connect to DB", E_USER_ERROR);
      exit(1);
    }
    $aid = rand(1,10000);
    pg_query($con, "SELECT * FROM accounts WHERE aid = $aid");
    pg_close($con);
  ?>

  $ /usr/local/apache/bin/ab -c 100 -n 1000 "http://localhost/bench.php"

14. master/slave⡼

  master/slave⡼ɤϡSlony-IΤ褦ʡmaster/slaveΥץꥱ
  󥽥եȤ˥ץꥱޤ⡼ɤǤΥ⡼ɤǻȤ
  ϡץꥱ⡼ɤƱ褦ˡޥȥΥۥȾ
  򥻥åȤmaster_slave_modeload_balance_modetrueˤޤΤ
  䤤碌ˤäƥޥ䤤碌ȡޥ
  Τɤ餫˥ɥХ󥹤䤤碌礬
  ޤ

  1) ʲξ郎٤Ƥ硤䤤碌ϥޥȥ
    ʬޤ

     - PostgreSQLΥС7.4ʹ	
     - 䤤碌Τ"SELECT"(ʸ/ʸ̵̤뤵ޤ)ǻϤޤ
       Ƥ
     - 䤤碌Ūʥȥ󥯥֥å¦ˤʤ(Ĥޤꡤ
       BEGINȯԤƤʤ)

  2) 1)ʳξϡޥ䤤碌ޤ

15. ʣpgpoolƱۥȤΩ夲ˤ

  ʣpgpoolƱۥȤΩ夲ˤ(Ȥ2ĤDB饹̡
  pgpoolǰ)2Ĥեޤ

  줾pgpool-fץդǵư줾եǧ
  ޤ:

  pgpool -f /usr/local/etc/pgpool.1.conf
  pgpool -f /usr/local/etc/pgpool.2.conf

  ʤ2ĤեǤϰʲΥѥ᡼̡ͤˤɬפ
  ޤ

  port
  logdir (pgpoolpidե֤)

  ʤpgpoolߤݤˤ-fץȤޤ㡧

  pgpool -f /usr/local/etc/pgpool.1.conf -m smart stop

  Υޥɤˤäơκǽpgpoolߤޤ