API tools faq
paste
Advertisement
Guest User

Untitled

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