Advertisement
Guest User

Untitled

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