Advertisement
Guest User

pgpool.conf_original

a guest
Jun 5th, 2016
155
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 34.74 KB | None | 0 0
  1. # ----------------------------
  2. # pgPool-II configuration file
  3. # ----------------------------
  4. #
  5. # This file consists of lines of the form:
  6. #
  7. # name = value
  8. #
  9. # Whitespace may be used. Comments are introduced with "#" anywhere on a line.
  10. # The complete list of parameter names and allowed values can be found in the
  11. # pgPool-II documentation.
  12. #
  13. # This file is read on server startup and when the server receives a SIGHUP
  14. # signal. If you edit the file on a running system, you have to SIGHUP the
  15. # server for the changes to take effect, or use "pgpool reload". Some
  16. # parameters, which are marked below, require a server shutdown and restart to
  17. # take effect.
  18. #
  19.  
  20.  
  21. #------------------------------------------------------------------------------
  22. # CONNECTIONS
  23. #------------------------------------------------------------------------------
  24.  
  25. # - pgpool Connection Settings -
  26.  
  27. listen_addresses = '*'
  28. # Host name or IP address to listen on:
  29. # '*' for all, '' for no TCP/IP connections
  30. # (change requires restart)
  31. port = 5433
  32. # Port number
  33. # (change requires restart)
  34. socket_dir = '/var/run/postgresql'
  35. # Unix domain socket path
  36. # The Debian package defaults to
  37. # /var/run/postgresql
  38. # (change requires restart)
  39. listen_backlog_multiplier = 2
  40. # Set the backlog parameter of listen(2) to
  41. # num_init_children * listen_backlog_multiplier.
  42. # (change requires restart)
  43.  
  44. # - pgpool Communication Manager Connection Settings -
  45.  
  46. pcp_listen_addresses = '*'
  47. # Host name or IP address for pcp process to listen on:
  48. # '*' for all, '' for no TCP/IP connections
  49. # (change requires restart)
  50. pcp_port = 9898
  51. # Port number for pcp
  52. # (change requires restart)
  53. pcp_socket_dir = '/var/run/postgresql'
  54. # Unix domain socket path for pcp
  55. # The Debian package defaults to
  56. # /var/run/postgresql
  57. # (change requires restart)
  58.  
  59. # - Backend Connection Settings -
  60.  
  61. backend_hostname0 = 'master'
  62. # Host name or IP address to connect to for backend 0
  63. backend_port0 = 5432
  64. # Port number for backend 0
  65. backend_weight0 = 1
  66. # Weight for backend 0 (only in load balancing mode)
  67. backend_data_directory0 = '/var/lib/database/postgresql/9.3/main'
  68. # Data directory for backend 0
  69. backend_flag0 = 'DISALLOW_TO_FAILOVER'
  70. # Controls various backend behavior
  71. # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
  72.  
  73. backend_hostname1 = 'slave01'
  74. backend_port1 = 5432
  75. backend_weight1 = 2
  76. backend_data_directory1 = '/var/lib/database/postgresql/9.3/main'
  77. backend_flag1 = 'DISALLOW_TO_FAILOVER'
  78.  
  79. backend_hostname2 = 'slave02'
  80. backend_port2 = 5432
  81. backend_weight2 = 1
  82. backend_data_directory2 = '/var/lib/database/postgresql/9.3/main'
  83. backend_flag2 = 'DISALLOW_TO_FAILOVER'
  84.  
  85. # - Authentication -
  86.  
  87. enable_pool_hba = on
  88. # Use pool_hba.conf for client authentication
  89. pool_passwd = 'pool_passwd'
  90. # File name of pool_passwd for md5 authentication.
  91. # "" disables pool_passwd.
  92. # (change requires restart)
  93. authentication_timeout = 60
  94. # Delay in seconds to complete client authentication
  95. # 0 means no timeout.
  96.  
  97. # - SSL Connections -
  98.  
  99. ssl = off
  100. # Enable SSL support
  101. # (change requires restart)
  102. #ssl_key = './server.key'
  103. # Path to the SSL private key file
  104. # (change requires restart)
  105. #ssl_cert = './server.cert'
  106. # Path to the SSL public certificate file
  107. # (change requires restart)
  108. #ssl_ca_cert = ''
  109. # Path to a single PEM format file
  110. # containing CA root certificate(s)
  111. # (change requires restart)
  112. #ssl_ca_cert_dir = ''
  113. # Directory containing CA root certificate(s)
  114. # (change requires restart)
  115.  
  116.  
  117. #------------------------------------------------------------------------------
  118. # POOLS
  119. #------------------------------------------------------------------------------
  120.  
  121. # - Pool size -
  122.  
  123. num_init_children = 150
  124. # Number of pools
  125. # (change requires restart)
  126. max_pool = 1
  127. # Number of connections per pool
  128. # (change requires restart)
  129.  
  130. # - Life time -
  131.  
  132. child_life_time = 180
  133. # Pool exits after being idle for this many seconds
  134. child_max_connections = 100
  135. # Pool exits after receiving that many connections
  136. # 0 means no exit
  137. connection_life_time = 120
  138. # Connection to backend closes after being idle for this many seconds
  139. # 0 means no close
  140. client_idle_limit = 180
  141. # Client is disconnected after being idle for that many seconds
  142. # (even inside an explicit transactions!)
  143. # 0 means no disconnection
  144.  
  145.  
  146. #------------------------------------------------------------------------------
  147. # LOGS
  148. #------------------------------------------------------------------------------
  149.  
  150. # - Where to log -
  151.  
  152. log_destination = 'syslog'
  153. # Where to log
  154. # Valid values are combinations of stderr,
  155. # and syslog. Default to stderr.
  156.  
  157. # - What to log -
  158.  
  159. log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.
  160.  
  161. log_connections = on
  162. # Log connections
  163. log_hostname = on
  164. # Hostname will be shown in ps status
  165. # and in logs if connections are logged
  166. log_statement = off
  167. # Log all statements
  168. log_per_node_statement = off
  169. # Log all statements
  170. # with node and backend informations
  171. log_standby_delay = 'none'
  172. # Log standby delay
  173. # Valid values are combinations of always,
  174. # if_over_threshold, none
  175.  
  176. # - Syslog specific -
  177.  
  178. syslog_facility = 'LOCAL0'
  179. # Syslog local facility. Default to LOCAL0
  180. syslog_ident = 'pgpool'
  181. # Syslog program identification string
  182. # Default to 'pgpool'
  183.  
  184. # - Debug -
  185.  
  186. debug_level = 0
  187. # Debug message verbosity level
  188. # 0 means no message, 1 or more mean verbose
  189.  
  190. #log_error_verbosity = default # terse, default, or verbose messages
  191.  
  192. #client_min_messages = notice # values in order of decreasing detail:
  193. # debug5
  194. # debug4
  195. # debug3
  196. # debug2
  197. # debug1
  198. # log
  199. # notice
  200. # warning
  201. # error
  202.  
  203. #log_min_messages = warning # values in order of decreasing detail:
  204. # debug5
  205. # debug4
  206. # debug3
  207. # debug2
  208. # debug1
  209. # info
  210. # notice
  211. # warning
  212. # error
  213. # log
  214. # fatal
  215. # panic
  216.  
  217. #------------------------------------------------------------------------------
  218. # FILE LOCATIONS
  219. #------------------------------------------------------------------------------
  220.  
  221. pid_file_name = '/var/run/postgresql/pgpool.pid'
  222. # PID file name
  223. # (change requires restart)
  224. logdir = '/var/log/postgresql'
  225. # Directory of pgPool status file
  226. # (change requires restart)
  227.  
  228.  
  229. #------------------------------------------------------------------------------
  230. # CONNECTION POOLING
  231. #------------------------------------------------------------------------------
  232.  
  233. connection_cache = off
  234. # Activate connection pools
  235. # (change requires restart)
  236.  
  237. # Semicolon separated list of queries
  238. # to be issued at the end of a session
  239. # The default is for 8.3 and later
  240. reset_query_list = 'ABORT; DISCARD ALL'
  241. # The following one is for 8.2 and before
  242. #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
  243.  
  244.  
  245. #------------------------------------------------------------------------------
  246. # REPLICATION MODE
  247. #------------------------------------------------------------------------------
  248.  
  249. replication_mode = off
  250. # Activate replication mode
  251. # (change requires restart)
  252. replicate_select = off
  253. # Replicate SELECT statements
  254. # when in replication mode
  255. # replicate_select is higher priority than
  256. # load_balance_mode.
  257.  
  258. insert_lock = on
  259. # Automatically locks a dummy row or a table
  260. # with INSERT statements to keep SERIAL data
  261. # consistency
  262. # Without SERIAL, no lock will be issued
  263. lobj_lock_table = ''
  264. # When rewriting lo_creat command in
  265. # replication mode, specify table name to
  266. # lock
  267.  
  268. # - Degenerate handling -
  269.  
  270. replication_stop_on_mismatch = off
  271. # On disagreement with the packet kind
  272. # sent from backend, degenerate the node
  273. # which is most likely "minority"
  274. # If off, just force to exit this session
  275.  
  276. failover_if_affected_tuples_mismatch = off
  277. # On disagreement with the number of affected
  278. # tuples in UPDATE/DELETE queries, then
  279. # degenerate the node which is most likely
  280. # "minority".
  281. # If off, just abort the transaction to
  282. # keep the consistency
  283.  
  284.  
  285. #------------------------------------------------------------------------------
  286. # LOAD BALANCING MODE
  287. #------------------------------------------------------------------------------
  288.  
  289. load_balance_mode = on
  290. # Activate load balancing mode
  291. # (change requires restart)
  292. ignore_leading_white_space = on
  293. # Ignore leading white spaces of each query
  294. white_function_list = ''
  295. # Comma separated list of function names
  296. # that don't write to database
  297. # Regexp are accepted
  298. black_function_list = 'nextval,setval'
  299. # Comma separated list of function names
  300. # that write to database
  301. # Regexp are accepted
  302.  
  303. database_redirect_preference_list = ''
  304. # comma separated list of pairs of database and node id.
  305. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
  306. # valid for streaming replicaton mode only.
  307.  
  308. app_name_redirect_preference_list = ''
  309. # comma separated list of pairs of app name and node id.
  310. # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
  311. # valid for streaming replicaton mode only.
  312. allow_sql_comments = off
  313. # if on, ignore SQL comments when judging if load balance or
  314. # query cache is possible.
  315. # If off, SQL comments effectively prevent the judgment
  316. # (pre 3.4 behavior).
  317.  
  318. #------------------------------------------------------------------------------
  319. # MASTER/SLAVE MODE
  320. #------------------------------------------------------------------------------
  321.  
  322. master_slave_mode = on
  323. # Activate master/slave mode
  324. # (change requires restart)
  325. master_slave_sub_mode = 'stream'
  326. # Master/slave sub mode
  327. # Valid values are combinations slony or
  328. # stream. Default is slony.
  329. # (change requires restart)
  330.  
  331. # - Streaming -
  332.  
  333. sr_check_period = 30
  334. # Streaming replication check period
  335. # Disabled (0) by default
  336. sr_check_user = 'pgpool'
  337. # Streaming replication check user
  338. # This is necessary even if you disable
  339. # streaming replication delay check with
  340. # sr_check_period = 0
  341. sr_check_password = 'xxxxx'
  342. # Password for streaming replication check user
  343. delay_threshold = 0
  344. # Threshold before not dispatching query to standby node
  345. # Unit is in bytes
  346. # Disabled (0) by default
  347.  
  348. # - Special commands -
  349.  
  350. follow_master_command = ''
  351. # Executes this command after master failover
  352. # Special values:
  353. # %d = node id
  354. # %h = host name
  355. # %p = port number
  356. # %D = database cluster path
  357. # %m = new master node id
  358. # %H = hostname of the new master node
  359. # %M = old master node id
  360. # %P = old primary node id
  361. # %r = new master port number
  362. # %R = new master database cluster path
  363. # %% = '%' character
  364.  
  365. #------------------------------------------------------------------------------
  366. # HEALTH CHECK
  367. #------------------------------------------------------------------------------
  368.  
  369. health_check_period = 30
  370. # Health check period
  371. # Disabled (0) by default
  372. health_check_timeout = 20
  373. # Health check timeout
  374. # 0 means no timeout
  375. health_check_user = 'pgpool'
  376. # Health check user
  377. health_check_password = 'xxxxx'
  378. # Password for health check user
  379. health_check_max_retries = 3
  380. # Maximum number of times to retry a failed health check before giving up.
  381. health_check_retry_delay = 5
  382. # Amount of time to wait (in seconds) between retries.
  383. connect_timeout = 10000
  384. # Timeout value in milliseconds before giving up to connect to backend.
  385. # Default is 10000 ms (10 second). Flaky network user may want to increase
  386. # the value. 0 means no timeout.
  387. # Note that this value is not only used for health check,
  388. # but also for ordinary conection to backend.
  389.  
  390. #------------------------------------------------------------------------------
  391. # FAILOVER AND FAILBACK
  392. #------------------------------------------------------------------------------
  393.  
  394. failover_command = ''
  395. # Executes this command at failover
  396. # Special values:
  397. # %d = node id
  398. # %h = host name
  399. # %p = port number
  400. # %D = database cluster path
  401. # %m = new master node id
  402. # %H = hostname of the new master node
  403. # %M = old master node id
  404. # %P = old primary node id
  405. # %r = new master port number
  406. # %R = new master database cluster path
  407. # %% = '%' character
  408. failback_command = ''
  409. # Executes this command at failback.
  410. # Special values:
  411. # %d = node id
  412. # %h = host name
  413. # %p = port number
  414. # %D = database cluster path
  415. # %m = new master node id
  416. # %H = hostname of the new master node
  417. # %M = old master node id
  418. # %P = old primary node id
  419. # %r = new master port number
  420. # %R = new master database cluster path
  421. # %% = '%' character
  422.  
  423. fail_over_on_backend_error = on
  424. # Initiates failover when reading/writing to the
  425. # backend communication socket fails
  426. # If set to off, pgpool will report an
  427. # error and disconnect the session.
  428.  
  429. search_primary_node_timeout = 10
  430. # Timeout in seconds to search for the
  431. # primary node when a failover occurs.
  432. # 0 means no timeout, keep searching
  433. # for a primary node forever.
  434.  
  435. #------------------------------------------------------------------------------
  436. # ONLINE RECOVERY
  437. #------------------------------------------------------------------------------
  438.  
  439. recovery_user = 'nobody'
  440. # Online recovery user
  441. recovery_password = ''
  442. # Online recovery password
  443. recovery_1st_stage_command = ''
  444. # Executes a command in first stage
  445. recovery_2nd_stage_command = ''
  446. # Executes a command in second stage
  447. recovery_timeout = 90
  448. # Timeout in seconds to wait for the
  449. # recovering node's postmaster to start up
  450. # 0 means no wait
  451. client_idle_limit_in_recovery = 0
  452. # Client is disconnected after being idle
  453. # for that many seconds in the second stage
  454. # of online recovery
  455. # 0 means no disconnection
  456. # -1 means immediate disconnection
  457.  
  458.  
  459. #------------------------------------------------------------------------------
  460. # WATCHDOG
  461. #------------------------------------------------------------------------------
  462.  
  463. # - Enabling -
  464.  
  465. use_watchdog = off
  466. # Activates watchdog
  467. # (change requires restart)
  468.  
  469. # -Connection to up stream servers -
  470.  
  471. trusted_servers = ''
  472. # trusted server list which are used
  473. # to confirm network connection
  474. # (hostA,hostB,hostC,...)
  475. # (change requires restart)
  476. ping_path = '/bin'
  477. # ping command path
  478. # (change requires restart)
  479.  
  480. # - Watchdog communication Settings -
  481.  
  482. wd_hostname = ''
  483. # Host name or IP address of this watchdog
  484. # (change requires restart)
  485. wd_port = 9000
  486. # port number for watchdog service
  487. # (change requires restart)
  488. wd_authkey = ''
  489. # Authentication key for watchdog communication
  490. # (change requires restart)
  491.  
  492. # - Virtual IP control Setting -
  493.  
  494. delegate_IP = ''
  495. # delegate IP address
  496. # If this is empty, virtual IP never bring up.
  497. # (change requires restart)
  498. ifconfig_path = '/sbin'
  499. # ifconfig command path
  500. # (change requires restart)
  501. if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
  502. # startup delegate IP command
  503. # (change requires restart)
  504. if_down_cmd = 'ifconfig eth0:0 down'
  505. # shutdown delegate IP command
  506. # (change requires restart)
  507.  
  508. arping_path = '/usr/sbin' # arping command path
  509. # (change requires restart)
  510.  
  511. arping_cmd = 'arping -U $_IP_$ -w 1'
  512. # arping command
  513. # (change requires restart)
  514.  
  515. # - Behaivor on escalation Setting -
  516.  
  517. clear_memqcache_on_escalation = on
  518. # Clear all the query cache on shared memory
  519. # when standby pgpool escalate to active pgpool
  520. # (= virtual IP holder).
  521. # This should be off if client connects to pgpool
  522. # not using virtual IP.
  523. # (change requires restart)
  524. wd_escalation_command = ''
  525. # Executes this command at escalation on new active pgpool.
  526. # (change requires restart)
  527.  
  528. # - Lifecheck Setting -
  529.  
  530. # -- common --
  531.  
  532. wd_lifecheck_method = 'heartbeat'
  533. # Method of watchdog lifecheck ('heartbeat' or 'query')
  534. # (change requires restart)
  535. wd_interval = 10
  536. # lifecheck interval (sec) > 0
  537. # (change requires restart)
  538.  
  539. # -- heartbeat mode --
  540.  
  541. wd_heartbeat_port = 9694
  542. # Port number for receiving heartbeat signal
  543. # (change requires restart)
  544. wd_heartbeat_keepalive = 2
  545. # Interval time of sending heartbeat signal (sec)
  546. # (change requires restart)
  547. wd_heartbeat_deadtime = 30
  548. # Deadtime interval for heartbeat signal (sec)
  549. # (change requires restart)
  550. heartbeat_destination0 = 'host0_ip1'
  551. # Host name or IP address of destination 0
  552. # for sending heartbeat signal.
  553. # (change requires restart)
  554. heartbeat_destination_port0 = 9694
  555. # Port number of destination 0 for sending
  556. # heartbeat signal. Usually this is the
  557. # same as wd_heartbeat_port.
  558. # (change requires restart)
  559. heartbeat_device0 = ''
  560. # Name of NIC device (such like 'eth0')
  561. # used for sending/receiving heartbeat
  562. # signal to/from destination 0.
  563. # This works only when this is not empty
  564. # and pgpool has root privilege.
  565. # (change requires restart)
  566.  
  567. #heartbeat_destination1 = 'host0_ip2'
  568. #heartbeat_destination_port1 = 9694
  569. #heartbeat_device1 = ''
  570.  
  571. # -- query mode --
  572.  
  573. wd_life_point = 3
  574. # lifecheck retry times
  575. # (change requires restart)
  576. wd_lifecheck_query = 'SELECT 1'
  577. # lifecheck query to pgpool from watchdog
  578. # (change requires restart)
  579. wd_lifecheck_dbname = 'template1'
  580. # Database name connected for lifecheck
  581. # (change requires restart)
  582. wd_lifecheck_user = 'pgpool'
  583. # watchdog user monitoring pgpools in lifecheck
  584. # (change requires restart)
  585. wd_lifecheck_password = 'xxxxx'
  586. # Password for watchdog user in lifecheck
  587. # (change requires restart)
  588.  
  589. # - Other pgpool Connection Settings -
  590.  
  591. #other_pgpool_hostname0 = 'host0'
  592. # Host name or IP address to connect to for other pgpool 0
  593. # (change requires restart)
  594. #other_pgpool_port0 = 5432
  595. # Port number for othet pgpool 0
  596. # (change requires restart)
  597. #other_wd_port0 = 9000
  598. # Port number for othet watchdog 0
  599. # (change requires restart)
  600. #other_pgpool_hostname1 = 'host1'
  601. #other_pgpool_port1 = 5432
  602. #other_wd_port1 = 9000
  603.  
  604.  
  605. #------------------------------------------------------------------------------
  606. # OTHERS
  607. #------------------------------------------------------------------------------
  608. relcache_expire = 0
  609. # Life time of relation cache in seconds.
  610. # 0 means no cache expiration(the default).
  611. # The relation cache is used for cache the
  612. # query result against PostgreSQL system
  613. # catalog to obtain various information
  614. # including table structures or if it's a
  615. # temporary table or not. The cache is
  616. # maintained in a pgpool child local memory
  617. # and being kept as long as it survives.
  618. # If someone modify the table by using
  619. # ALTER TABLE or some such, the relcache is
  620. # not consistent anymore.
  621. # For this purpose, cache_expiration
  622. # controls the life time of the cache.
  623.  
  624. relcache_size = 256
  625. # Number of relation cache
  626. # entry. If you see frequently:
  627. # "pool_search_relcache: cache replacement happend"
  628. # in the pgpool log, you might want to increate this number.
  629.  
  630. check_temp_table = on
  631. # If on, enable temporary table check in SELECT statements.
  632. # This initiates queries against system catalog of primary/master
  633. # thus increases load of master.
  634. # If you are absolutely sure that your system never uses temporary tables
  635. # and you want to save access to primary/master, you could turn this off.
  636. # Default is on.
  637.  
  638. check_unlogged_table = on
  639. # If on, enable unlogged table check in SELECT statements.
  640. # This initiates queries against system catalog of primary/master
  641. # thus increases load of master.
  642. # If you are absolutely sure that your system never uses unlogged tables
  643. # and you want to save access to primary/master, you could turn this off.
  644. # Default is on.
  645.  
  646. #------------------------------------------------------------------------------
  647. # IN MEMORY QUERY MEMORY CACHE
  648. #------------------------------------------------------------------------------
  649. memory_cache_enabled = off
  650. # If on, use the memory cache functionality, off by default
  651. memqcache_method = 'shmem'
  652. # Cache storage method. either 'shmem'(shared memory) or
  653. # 'memcached'. 'shmem' by default
  654. # (change requires restart)
  655. memqcache_memcached_host = 'localhost'
  656. # Memcached host name or IP address. Mandatory if
  657. # memqcache_method = 'memcached'.
  658. # Defaults to localhost.
  659. # (change requires restart)
  660. memqcache_memcached_port = 11211
  661. # Memcached port number. Mondatory if memqcache_method = 'memcached'.
  662. # Defaults to 11211.
  663. # (change requires restart)
  664. memqcache_total_size = 67108864
  665. # Total memory size in bytes for storing memory cache.
  666. # Mandatory if memqcache_method = 'shmem'.
  667. # Defaults to 64MB.
  668. # (change requires restart)
  669. memqcache_max_num_cache = 1000000
  670. # Total number of cache entries. Mandatory
  671. # if memqcache_method = 'shmem'.
  672. # Each cache entry consumes 48 bytes on shared memory.
  673. # Defaults to 1,000,000(45.8MB).
  674. # (change requires restart)
  675. memqcache_expire = 0
  676. # Memory cache entry life time specified in seconds.
  677. # 0 means infinite life time. 0 by default.
  678. # (change requires restart)
  679. memqcache_auto_cache_invalidation = on
  680. # If on, invalidation of query cache is triggered by corresponding
  681. # DDL/DML/DCL(and memqcache_expire). If off, it is only triggered
  682. # by memqcache_expire. on by default.
  683. # (change requires restart)
  684. memqcache_maxcache = 409600
  685. # Maximum SELECT result size in bytes.
  686. # Must be smaller than memqcache_cache_block_size. Defaults to 400KB.
  687. # (change requires restart)
  688. memqcache_cache_block_size = 1048576
  689. # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.
  690. # Defaults to 1MB.
  691. # (change requires restart)
  692. memqcache_oiddir = '/var/log/pgpool/oiddir'
  693. # Temporary work directory to record table oids
  694. # (change requires restart)
  695. white_memqcache_table_list = ''
  696. # Comma separated list of table names to memcache
  697. # that don't write to database
  698. # Regexp are accepted
  699. black_memqcache_table_list = ''
  700. # Comma separated list of table names not to memcache
  701. # that don't write to database
  702. # Regexp are accepted
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement