1 # ----------------------------
2 # pgPool-II configuration file
3 # ----------------------------
5 # This file consists of lines of the form:
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.
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
21 #------------------------------------------------------------------------------
23 #------------------------------------------------------------------------------
25 # - pgpool Connection Settings -
27 listen_addresses = '*'
28 # Host name or IP address to listen on:
29 # '*' for all, '' for no TCP/IP connections
30 # (change requires restart)
33 # (change requires restart)
35 # Unix domain socket path
36 # The Debian package defaults to
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 serialize_accept = off
44 # whether to serialize accept() call to avoid thundering herd problem
45 # (change requires restart)
47 # - pgpool Communication Manager Connection Settings -
49 pcp_listen_addresses = '*'
50 # Host name or IP address for pcp process to listen on:
51 # '*' for all, '' for no TCP/IP connections
52 # (change requires restart)
55 # (change requires restart)
56 pcp_socket_dir = '/tmp/'
57 # Unix domain socket path for pcp
58 # The Debian package defaults to
60 # (change requires restart)
62 # - Backend Connection Settings -
64 backend_hostname0 = 'edozvola-db-01'
65 # Host name or IP address to connect to for backend 0
67 # Port number for backend 0
69 # Weight for backend 0 (only in load balancing mode)
70 backend_data_directory0 = '/var/lib/pgsql/9.6/data/'
71 # Data directory for backend 0
72 backend_flag0 = 'ALLOW_TO_FAILOVER'
73 # Controls various backend behavior
74 # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
76 backend_hostname1 = 'edozvola-db-02'
79 backend_data_directory1 = '/var/lib/pgsql/9.6/data/'
80 backend_flag1 = 'ALLOW_TO_FAILOVER'
85 # Use pool_hba.conf for client authentication
86 pool_passwd = 'pool_passwd'
87 # File name of pool_passwd for md5 authentication.
88 # "" disables pool_passwd.
89 # (change requires restart)
90 authentication_timeout = 60
91 # Delay in seconds to complete client authentication
98 # (change requires restart)
99 #ssl_key = './server.key'
100 # Path to the SSL private key file
101 # (change requires restart)
102 #ssl_cert = './server.cert'
103 # Path to the SSL public certificate file
104 # (change requires restart)
106 # Path to a single PEM format file
107 # containing CA root certificate(s)
108 # (change requires restart)
109 #ssl_ca_cert_dir = ''
110 # Directory containing CA root certificate(s)
111 # (change requires restart)
114 #------------------------------------------------------------------------------
116 #------------------------------------------------------------------------------
118 # - Concurrent session and pool size -
120 num_init_children = 32
121 # Number of concurrent sessions allowed
122 # (change requires restart)
124 # Number of connection pool caches per connection
125 # (change requires restart)
129 child_life_time = 300
130 # Pool exits after being idle for this many seconds
131 child_max_connections = 0
132 # Pool exits after receiving that many connections
134 connection_life_time = 0
135 # Connection to backend closes after being idle for this many seconds
137 client_idle_limit = 0
138 # Client is disconnected after being idle for that many seconds
139 # (even inside an explicit transactions!)
140 # 0 means no disconnection
143 #------------------------------------------------------------------------------
145 #------------------------------------------------------------------------------
149 log_destination = 'syslog'
151 # Valid values are combinations of stderr,
152 # and syslog. Default to stderr.
156 log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.
158 log_connections = off
161 # Hostname will be shown in ps status
162 # and in logs if connections are logged
165 log_per_node_statement = off
167 # with node and backend informations
168 log_standby_delay = 'none'
170 # Valid values are combinations of always,
171 # if_over_threshold, none
173 # - Syslog specific -
175 syslog_facility = 'LOCAL0'
176 # Syslog local facility. Default to LOCAL0
177 syslog_ident = 'pgpool'
178 # Syslog program identification string
179 # Default to 'pgpool'
183 #log_error_verbosity = default # terse, default, or verbose messages
184 log_error_verbosity = verbose
186 #client_min_messages = notice # values in order of decreasing detail:
197 #log_min_messages = warning # values in order of decreasing detail:
211 #------------------------------------------------------------------------------
213 #------------------------------------------------------------------------------
215 pid_file_name = '/tmp/pgpool.pid'
217 # Can be specified as relative to the"
218 # location of pgpool.conf file or
219 # as an absolute path
220 # (change requires restart)
221 logdir = '/var/log/pgpool'
222 # Directory of pgPool status file
223 # (change requires restart)
226 #------------------------------------------------------------------------------
228 #------------------------------------------------------------------------------
230 connection_cache = on
231 # Activate connection pools
232 # (change requires restart)
234 # Semicolon separated list of queries
235 # to be issued at the end of a session
236 # The default is for 8.3 and later
237 reset_query_list = 'ABORT; DISCARD ALL'
238 # The following one is for 8.2 and before
239 #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
242 #------------------------------------------------------------------------------
244 #------------------------------------------------------------------------------
246 replication_mode = off
247 # Activate replication mode
248 # (change requires restart)
249 replicate_select = off
250 # Replicate SELECT statements
251 # when in replication mode
252 # replicate_select is higher priority than
256 # Automatically locks a dummy row or a table
257 # with INSERT statements to keep SERIAL data
259 # Without SERIAL, no lock will be issued
261 # When rewriting lo_creat command in
262 # replication mode, specify table name to
265 # - Degenerate handling -
267 replication_stop_on_mismatch = off
268 # On disagreement with the packet kind
269 # sent from backend, degenerate the node
270 # which is most likely "minority"
271 # If off, just force to exit this session
273 failover_if_affected_tuples_mismatch = off
274 # On disagreement with the number of affected
275 # tuples in UPDATE/DELETE queries, then
276 # degenerate the node which is most likely
278 # If off, just abort the transaction to
279 # keep the consistency
282 #------------------------------------------------------------------------------
283 # LOAD BALANCING MODE
284 #------------------------------------------------------------------------------
286 load_balance_mode = on
287 # Activate load balancing mode
288 # (change requires restart)
289 ignore_leading_white_space = on
290 # Ignore leading white spaces of each query
291 white_function_list = ''
292 # Comma separated list of function names
293 # that don't write to database
294 # Regexp are accepted
295 black_function_list = 'nextval,setval,nextval,setval'
296 # Comma separated list of function names
297 # that write to database
298 # Regexp are accepted
300 database_redirect_preference_list = ''
301 # comma separated list of pairs of database and node id.
302 # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
303 # valid for streaming replicaton mode only.
305 app_name_redirect_preference_list = ''
306 # comma separated list of pairs of app name and node id.
307 # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
308 # valid for streaming replicaton mode only.
309 allow_sql_comments = off
310 # if on, ignore SQL comments when judging if load balance or
311 # query cache is possible.
312 # If off, SQL comments effectively prevent the judgment
313 # (pre 3.4 behavior).
315 #------------------------------------------------------------------------------
317 #------------------------------------------------------------------------------
319 master_slave_mode = on
320 # Activate master/slave mode
321 # (change requires restart)
322 master_slave_sub_mode = 'stream'
323 # Master/slave sub mode
324 # Valid values are combinations slony or
325 # stream. Default is slony.
326 # (change requires restart)
331 # Streaming replication check period
332 # Disabled (0) by default
333 sr_check_user = 'postgres'
334 # Streaming replication check user
335 # This is necessary even if you disable
336 # streaming replication delay check with
337 # sr_check_period = 0
338 sr_check_password = ''
339 # Password for streaming replication check user
340 sr_check_database = 'postgres'
341 # Database name for streaming replication check
342 delay_threshold = 10000000
343 # Threshold before not dispatching query to standby node
345 # Disabled (0) by default
347 # - Special commands -
349 follow_master_command = 'echo %M > /tmp/postgres_master'
350 # Executes this command after master failover
355 # %D = database cluster path
356 # %m = new master node id
357 # %H = hostname of the new master node
358 # %M = old master node id
359 # %P = old primary node id
360 # %r = new master port number
361 # %R = new master database cluster path
364 #------------------------------------------------------------------------------
366 #------------------------------------------------------------------------------
368 health_check_period = 3
369 # Health check period
370 # Disabled (0) by default
371 health_check_timeout = 2
372 # Health check timeout
374 health_check_user = 'postgres'
376 health_check_password = ''
377 # Password for health check user
378 health_check_database = ''
379 # Database name for health check. If '', tries 'postgres' frist, then 'template1'
381 health_check_max_retries = 2
382 # Maximum number of times to retry a failed health check before giving up.
383 health_check_retry_delay = 1
384 # Amount of time to wait (in seconds) between retries.
385 connect_timeout = 1000
386 # Timeout value in milliseconds before giving up to connect to backend.
387 # Default is 10000 ms (10 second). Flaky network user may want to increase
388 # the value. 0 means no timeout.
389 # Note that this value is not only used for health check,
390 # but also for ordinary conection to backend.
392 #------------------------------------------------------------------------------
393 # FAILOVER AND FAILBACK
394 #------------------------------------------------------------------------------
396 failover_command = '/etc/pgpool-II/failover.sh %d %H %P /tmp/trigger_file'
397 # Executes this command at failover
402 # %D = database cluster path
403 # %m = new master node id
404 # %H = hostname of the new master node
405 # %M = old master node id
406 # %P = old primary node id
407 # %r = new master port number
408 # %R = new master database cluster path
410 #failback_command = ''
411 # Executes this command at failback.
416 # %D = database cluster path
417 # %m = new master node id
418 # %H = hostname of the new master node
419 # %M = old master node id
420 # %P = old primary node id
421 # %r = new master port number
422 # %R = new master database cluster path
425 fail_over_on_backend_error = on
426 # Initiates failover when reading/writing to the
427 # backend communication socket fails
428 # If set to off, pgpool will report an
429 # error and disconnect the session.
431 search_primary_node_timeout = 3
432 # Timeout in seconds to search for the
433 # primary node when a failover occurs.
434 # 0 means no timeout, keep searching
435 # for a primary node forever.
437 #------------------------------------------------------------------------------
439 #------------------------------------------------------------------------------
441 recovery_user = 'nobody'
442 # Online recovery user
443 recovery_password = ''
444 # Online recovery password
445 recovery_1st_stage_command = ''
446 # Executes a command in first stage
447 recovery_2nd_stage_command = ''
448 # Executes a command in second stage
449 recovery_timeout = 90
450 # Timeout in seconds to wait for the
451 # recovering node's postmaster to start up
453 client_idle_limit_in_recovery = 0
454 # Client is disconnected after being idle
455 # for that many seconds in the second stage
457 # 0 means no disconnection
458 # -1 means immediate disconnection
461 #------------------------------------------------------------------------------
463 #------------------------------------------------------------------------------
469 # (change requires restart)
471 # -Connection to up stream servers -
473 trusted_servers = 'edozvola-db-01,edozvola-db-02'
474 # trusted server list which are used
475 # to confirm network connection
476 # (hostA,hostB,hostC,...)
477 # (change requires restart)
480 # (change requires restart)
482 # - Watchdog communication Settings -
484 wd_hostname = '__HOSTNAME__THIS__'
485 # Host name or IP address of this watchdog
486 # (change requires restart)
488 # port number for watchdog service
489 # (change requires restart)
491 # priority of this watchdog in leader election
492 # (change requires restart)
495 # Authentication key for watchdog communication
496 # (change requires restart)
498 wd_ipc_socket_dir = '/var/run/postgresql'
499 # Unix domain socket path for watchdog IPC socket
500 # The Debian package defaults to
501 # /var/run/postgresql
502 # (change requires restart)
505 # - Virtual IP control Setting -
507 delegate_IP = '10.210.1.60'
508 # delegate IP address
509 # If this is empty, virtual IP never bring up.
510 # (change requires restart)
511 if_cmd_path = '/etc/pgpool-II/sbin'
512 # path to the directory where if_up/down_cmd exists
513 # (change requires restart)
514 if_up_cmd = 'ifconfig eth0:0 $_IP_$ netmask 255.255.255.0 up'
515 # startup delegate IP command
516 # (change requires restart)
517 if_down_cmd = 'ifconfig eth0:0 down'
518 # shutdown delegate IP command
519 # (change requires restart)
520 arping_path = '/etc/pgpool-II/sbin'
521 # arping command path
522 # (change requires restart)
523 arping_cmd = 'arping -U $_IP_$ -w 1'
525 # (change requires restart)
527 # - Behaivor on escalation Setting -
529 clear_memqcache_on_escalation = on
530 # Clear all the query cache on shared memory
531 # when standby pgpool escalate to active pgpool
532 # (= virtual IP holder).
533 # This should be off if client connects to pgpool
534 # not using virtual IP.
535 # (change requires restart)
536 wd_escalation_command = ''
537 # Executes this command at escalation on new active pgpool.
538 # (change requires restart)
539 wd_de_escalation_command = ''
540 # Executes this command when master pgpool resigns from being master.
541 # (change requires restart)
543 # - Lifecheck Setting -
547 wd_monitoring_interfaces_list = '' # Comma separated list of interfaces names to monitor.
548 # if any interface from the list is active the watchdog will
549 # consider the network is fine
550 # 'any' to enable monitoring on all interfaces except loopback
551 # '' to disable monitoring
554 wd_lifecheck_method = 'heartbeat'
555 # Method of watchdog lifecheck ('heartbeat' or 'query' or 'external')
556 # (change requires restart)
558 # lifecheck interval (sec) > 0
559 # (change requires restart)
561 # -- heartbeat mode --
563 wd_heartbeat_port = 9694
564 # Port number for receiving heartbeat signal
565 # (change requires restart)
566 wd_heartbeat_keepalive = 2
567 # Interval time of sending heartbeat signal (sec)
568 # (change requires restart)
569 wd_heartbeat_deadtime = 30
570 # Deadtime interval for heartbeat signal (sec)
571 # (change requires restart)
572 heartbeat_destination0 = 'edozvola-db-01'
573 # Host name or IP address of destination 0
574 # for sending heartbeat signal.
575 # (change requires restart)
576 heartbeat_destination_port0 = 9694
577 # Port number of destination 0 for sending
578 # heartbeat signal. Usually this is the
579 # same as wd_heartbeat_port.
580 # (change requires restart)
581 heartbeat_device0 = ''
582 # Name of NIC device (such like 'eth0')
583 # used for sending/receiving heartbeat
584 # signal to/from destination 0.
585 # This works only when this is not empty
586 # and pgpool has root privilege.
587 # (change requires restart)
589 heartbeat_destination1 = 'edozvola-db-02'
590 heartbeat_destination_port1 = 9694
591 heartbeat_device1 = ''
596 # lifecheck retry times
597 # (change requires restart)
598 wd_lifecheck_query = 'SELECT 1'
599 # lifecheck query to pgpool from watchdog
600 # (change requires restart)
601 wd_lifecheck_dbname = 'template1'
602 # Database name connected for lifecheck
603 # (change requires restart)
604 wd_lifecheck_user = 'nobody'
605 # watchdog user monitoring pgpools in lifecheck
606 # (change requires restart)
607 wd_lifecheck_password = ''
608 # Password for watchdog user in lifecheck
609 # (change requires restart)
611 # - Other pgpool Connection Settings -
613 #other_pgpool_hostname0 = 'host0'
614 # Host name or IP address to connect to for other pgpool 0
615 # (change requires restart)
616 #other_pgpool_port0 = 5432
617 # Port number for othet pgpool 0
618 # (change requires restart)
619 #other_wd_port0 = 9000
620 # Port number for othet watchdog 0
621 # (change requires restart)
622 other_pgpool_hostname0 = '__HOSTNAME__OTHER__'
623 other_pgpool_port0 = 5432
624 other_wd_port0 = 9000
627 #------------------------------------------------------------------------------
629 #------------------------------------------------------------------------------
631 # Life time of relation cache in seconds.
632 # 0 means no cache expiration(the default).
633 # The relation cache is used for cache the
634 # query result against PostgreSQL system
635 # catalog to obtain various information
636 # including table structures or if it's a
637 # temporary table or not. The cache is
638 # maintained in a pgpool child local memory
639 # and being kept as long as it survives.
640 # If someone modify the table by using
641 # ALTER TABLE or some such, the relcache is
642 # not consistent anymore.
643 # For this purpose, cache_expiration
644 # controls the life time of the cache.
647 # Number of relation cache
648 # entry. If you see frequently:
649 # "pool_search_relcache: cache replacement happend"
650 # in the pgpool log, you might want to increate this number.
652 check_temp_table = on
653 # If on, enable temporary table check in SELECT statements.
654 # This initiates queries against system catalog of primary/master
655 # thus increases load of master.
656 # If you are absolutely sure that your system never uses temporary tables
657 # and you want to save access to primary/master, you could turn this off.
660 check_unlogged_table = on
661 # If on, enable unlogged table check in SELECT statements.
662 # This initiates queries against system catalog of primary/master
663 # thus increases load of master.
664 # If you are absolutely sure that your system never uses unlogged tables
665 # and you want to save access to primary/master, you could turn this off.
668 #------------------------------------------------------------------------------
669 # IN MEMORY QUERY MEMORY CACHE
670 #------------------------------------------------------------------------------
671 memory_cache_enabled = off
672 # If on, use the memory cache functionality, off by default
673 memqcache_method = 'shmem'
674 # Cache storage method. either 'shmem'(shared memory) or
675 # 'memcached'. 'shmem' by default
676 # (change requires restart)
677 memqcache_memcached_host = 'localhost'
678 # Memcached host name or IP address. Mandatory if
679 # memqcache_method = 'memcached'.
680 # Defaults to localhost.
681 # (change requires restart)
682 memqcache_memcached_port = 11211
683 # Memcached port number. Mondatory if memqcache_method = 'memcached'.
685 # (change requires restart)
686 memqcache_total_size = 67108864
687 # Total memory size in bytes for storing memory cache.
688 # Mandatory if memqcache_method = 'shmem'.
690 # (change requires restart)
691 memqcache_max_num_cache = 1000000
692 # Total number of cache entries. Mandatory
693 # if memqcache_method = 'shmem'.
694 # Each cache entry consumes 48 bytes on shared memory.
695 # Defaults to 1,000,000(45.8MB).
696 # (change requires restart)
698 # Memory cache entry life time specified in seconds.
699 # 0 means infinite life time. 0 by default.
700 # (change requires restart)
701 memqcache_auto_cache_invalidation = on
702 # If on, invalidation of query cache is triggered by corresponding
703 # DDL/DML/DCL(and memqcache_expire). If off, it is only triggered
704 # by memqcache_expire. on by default.
705 # (change requires restart)
706 memqcache_maxcache = 409600
707 # Maximum SELECT result size in bytes.
708 # Must be smaller than memqcache_cache_block_size. Defaults to 400KB.
709 # (change requires restart)
710 memqcache_cache_block_size = 1048576
711 # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.
713 # (change requires restart)
714 memqcache_oiddir = '/var/log/pgpool/oiddir'
715 # Temporary work directory to record table oids
716 # (change requires restart)
717 white_memqcache_table_list = ''
718 # Comma separated list of table names to memcache
719 # that don't write to database
720 # Regexp are accepted
721 black_memqcache_table_list = ''
722 # Comma separated list of table names not to memcache
723 # that don't write to database
724 # Regexp are accepted