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
185 #client_min_messages = notice # values in order of decreasing detail:
196 #log_min_messages = warning # values in order of decreasing detail:
210 #------------------------------------------------------------------------------
212 #------------------------------------------------------------------------------
214 pid_file_name = '/tmp/pgpool.pid'
216 # Can be specified as relative to the"
217 # location of pgpool.conf file or
218 # as an absolute path
219 # (change requires restart)
220 logdir = '/var/log/pgpool'
221 # Directory of pgPool status file
222 # (change requires restart)
225 #------------------------------------------------------------------------------
227 #------------------------------------------------------------------------------
229 connection_cache = on
230 # Activate connection pools
231 # (change requires restart)
233 # Semicolon separated list of queries
234 # to be issued at the end of a session
235 # The default is for 8.3 and later
236 reset_query_list = 'ABORT; DISCARD ALL'
237 # The following one is for 8.2 and before
238 #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
241 #------------------------------------------------------------------------------
243 #------------------------------------------------------------------------------
245 replication_mode = off
246 # Activate replication mode
247 # (change requires restart)
248 replicate_select = off
249 # Replicate SELECT statements
250 # when in replication mode
251 # replicate_select is higher priority than
255 # Automatically locks a dummy row or a table
256 # with INSERT statements to keep SERIAL data
258 # Without SERIAL, no lock will be issued
260 # When rewriting lo_creat command in
261 # replication mode, specify table name to
264 # - Degenerate handling -
266 replication_stop_on_mismatch = off
267 # On disagreement with the packet kind
268 # sent from backend, degenerate the node
269 # which is most likely "minority"
270 # If off, just force to exit this session
272 failover_if_affected_tuples_mismatch = off
273 # On disagreement with the number of affected
274 # tuples in UPDATE/DELETE queries, then
275 # degenerate the node which is most likely
277 # If off, just abort the transaction to
278 # keep the consistency
281 #------------------------------------------------------------------------------
282 # LOAD BALANCING MODE
283 #------------------------------------------------------------------------------
285 load_balance_mode = on
286 # Activate load balancing mode
287 # (change requires restart)
288 ignore_leading_white_space = on
289 # Ignore leading white spaces of each query
290 white_function_list = ''
291 # Comma separated list of function names
292 # that don't write to database
293 # Regexp are accepted
294 black_function_list = 'nextval,setval,nextval,setval'
295 # Comma separated list of function names
296 # that write to database
297 # Regexp are accepted
299 database_redirect_preference_list = ''
300 # comma separated list of pairs of database and node id.
301 # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
302 # valid for streaming replicaton mode only.
304 app_name_redirect_preference_list = ''
305 # comma separated list of pairs of app name and node id.
306 # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
307 # valid for streaming replicaton mode only.
308 allow_sql_comments = off
309 # if on, ignore SQL comments when judging if load balance or
310 # query cache is possible.
311 # If off, SQL comments effectively prevent the judgment
312 # (pre 3.4 behavior).
314 #------------------------------------------------------------------------------
316 #------------------------------------------------------------------------------
318 master_slave_mode = on
319 # Activate master/slave mode
320 # (change requires restart)
321 master_slave_sub_mode = 'stream'
322 # Master/slave sub mode
323 # Valid values are combinations slony or
324 # stream. Default is slony.
325 # (change requires restart)
330 # Streaming replication check period
331 # Disabled (0) by default
332 sr_check_user = 'postgres'
333 # Streaming replication check user
334 # This is necessary even if you disable
335 # streaming replication delay check with
336 # sr_check_period = 0
337 sr_check_password = ''
338 # Password for streaming replication check user
339 sr_check_database = 'postgres'
340 # Database name for streaming replication check
342 # Threshold before not dispatching query to standby node
344 # Disabled (0) by default
346 # - Special commands -
348 follow_master_command = 'echo %M > /tmp/postgres_master'
349 # Executes this command after master failover
354 # %D = database cluster path
355 # %m = new master node id
356 # %H = hostname of the new master node
357 # %M = old master node id
358 # %P = old primary node id
359 # %r = new master port number
360 # %R = new master database cluster path
363 #------------------------------------------------------------------------------
365 #------------------------------------------------------------------------------
367 health_check_period = 3
368 # Health check period
369 # Disabled (0) by default
370 health_check_timeout = 2
371 # Health check timeout
373 health_check_user = 'postgres'
375 health_check_password = ''
376 # Password for health check user
377 health_check_database = ''
378 # Database name for health check. If '', tries 'postgres' frist, then 'template1'
380 health_check_max_retries = 2
381 # Maximum number of times to retry a failed health check before giving up.
382 health_check_retry_delay = 1
383 # Amount of time to wait (in seconds) between retries.
384 connect_timeout = 1000
385 # Timeout value in milliseconds before giving up to connect to backend.
386 # Default is 10000 ms (10 second). Flaky network user may want to increase
387 # the value. 0 means no timeout.
388 # Note that this value is not only used for health check,
389 # but also for ordinary conection to backend.
391 #------------------------------------------------------------------------------
392 # FAILOVER AND FAILBACK
393 #------------------------------------------------------------------------------
395 failover_command = '/etc/pgpool-II/failover.sh %d %H %P /tmp/trigger_file'
396 # Executes this command at failover
401 # %D = database cluster path
402 # %m = new master node id
403 # %H = hostname of the new master node
404 # %M = old master node id
405 # %P = old primary node id
406 # %r = new master port number
407 # %R = new master database cluster path
409 #failback_command = ''
410 # Executes this command at failback.
415 # %D = database cluster path
416 # %m = new master node id
417 # %H = hostname of the new master node
418 # %M = old master node id
419 # %P = old primary node id
420 # %r = new master port number
421 # %R = new master database cluster path
424 fail_over_on_backend_error = on
425 # Initiates failover when reading/writing to the
426 # backend communication socket fails
427 # If set to off, pgpool will report an
428 # error and disconnect the session.
430 search_primary_node_timeout = 3
431 # Timeout in seconds to search for the
432 # primary node when a failover occurs.
433 # 0 means no timeout, keep searching
434 # for a primary node forever.
436 #------------------------------------------------------------------------------
438 #------------------------------------------------------------------------------
440 recovery_user = 'nobody'
441 # Online recovery user
442 recovery_password = ''
443 # Online recovery password
444 recovery_1st_stage_command = ''
445 # Executes a command in first stage
446 recovery_2nd_stage_command = ''
447 # Executes a command in second stage
448 recovery_timeout = 90
449 # Timeout in seconds to wait for the
450 # recovering node's postmaster to start up
452 client_idle_limit_in_recovery = 0
453 # Client is disconnected after being idle
454 # for that many seconds in the second stage
456 # 0 means no disconnection
457 # -1 means immediate disconnection
460 #------------------------------------------------------------------------------
462 #------------------------------------------------------------------------------
468 # (change requires restart)
470 # -Connection to up stream servers -
473 # trusted server list which are used
474 # to confirm network connection
475 # (hostA,hostB,hostC,...)
476 # (change requires restart)
479 # (change requires restart)
481 # - Watchdog communication Settings -
483 wd_hostname = '__HOSTNAME__THIS__'
484 # Host name or IP address of this watchdog
485 # (change requires restart)
487 # port number for watchdog service
488 # (change requires restart)
490 # priority of this watchdog in leader election
491 # (change requires restart)
494 # Authentication key for watchdog communication
495 # (change requires restart)
497 wd_ipc_socket_dir = '/var/run/postgresql'
498 # Unix domain socket path for watchdog IPC socket
499 # The Debian package defaults to
500 # /var/run/postgresql
501 # (change requires restart)
504 # - Virtual IP control Setting -
506 delegate_IP = '10.200.1.60'
507 # delegate IP address
508 # If this is empty, virtual IP never bring up.
509 # (change requires restart)
510 if_cmd_path = '/etc/pgpool-II/sbin'
511 # path to the directory where if_up/down_cmd exists
512 # (change requires restart)
513 if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
514 # startup delegate IP command
515 # (change requires restart)
516 if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
517 # shutdown delegate IP command
518 # (change requires restart)
519 arping_path = '/etc/pgpool-II/sbin'
520 # arping command path
521 # (change requires restart)
522 arping_cmd = 'arping -U $_IP_$ -w 1 -I eth0'
523 # XXX arping doesn't work without explicit interface!
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 = 'any' # 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 = 5
570 # Deadtime interval for heartbeat signal (sec)
571 # (change requires restart)
572 heartbeat_destination0 = '__HOSTNAME__OTHER__'
573 # XXX heartbeat_destination0 should match other_pgpool_hostname0 if you have just one interface
574 # connecting both hosts.
575 # Host name or IP address of destination 0
576 # for sending heartbeat signal.
577 # (change requires restart)
578 heartbeat_destination_port0 = 9694
579 # Port number of destination 0 for sending
580 # heartbeat signal. Usually this is the
581 # same as wd_heartbeat_port.
582 # (change requires restart)
583 heartbeat_device0 = ''
584 # Name of NIC device (such like 'eth0')
585 # used for sending/receiving heartbeat
586 # signal to/from destination 0.
587 # This works only when this is not empty
588 # and pgpool has root privilege.
589 # (change requires restart)
591 #heartbeat_destination1 = 'edozvola-db-02'
592 #heartbeat_destination_port1 = 9694
593 #heartbeat_device1 = ''
598 # lifecheck retry times
599 # (change requires restart)
600 wd_lifecheck_query = 'SELECT 1'
601 # lifecheck query to pgpool from watchdog
602 # (change requires restart)
603 wd_lifecheck_dbname = 'template1'
604 # Database name connected for lifecheck
605 # (change requires restart)
606 wd_lifecheck_user = 'nobody'
607 # watchdog user monitoring pgpools in lifecheck
608 # (change requires restart)
609 wd_lifecheck_password = ''
610 # Password for watchdog user in lifecheck
611 # (change requires restart)
613 # - Other pgpool Connection Settings -
615 #other_pgpool_hostname0 = 'host0'
616 # Host name or IP address to connect to for other pgpool 0
617 # (change requires restart)
618 #other_pgpool_port0 = 5432
619 # Port number for othet pgpool 0
620 # (change requires restart)
621 #other_wd_port0 = 9000
622 # Port number for othet watchdog 0
623 # (change requires restart)
624 other_pgpool_hostname0 = '__HOSTNAME__OTHER__'
625 other_pgpool_port0 = 9999
626 other_wd_port0 = 9000
627 # XXX other_pgpool_portX is used for sending wd_lifecheck_query (SELCT 1)
628 # in query mode. In addition, other_pgpool_port is used in also heartbeat mode as
629 # "identifier" of watchdog. For example, this is necessary to know which of pgpools
630 # is sending heartbeat signal, especially when there are two pgpools in one host.
633 #------------------------------------------------------------------------------
635 #------------------------------------------------------------------------------
637 # Life time of relation cache in seconds.
638 # 0 means no cache expiration(the default).
639 # The relation cache is used for cache the
640 # query result against PostgreSQL system
641 # catalog to obtain various information
642 # including table structures or if it's a
643 # temporary table or not. The cache is
644 # maintained in a pgpool child local memory
645 # and being kept as long as it survives.
646 # If someone modify the table by using
647 # ALTER TABLE or some such, the relcache is
648 # not consistent anymore.
649 # For this purpose, cache_expiration
650 # controls the life time of the cache.
653 # Number of relation cache
654 # entry. If you see frequently:
655 # "pool_search_relcache: cache replacement happend"
656 # in the pgpool log, you might want to increate this number.
658 check_temp_table = on
659 # If on, enable temporary table check in SELECT statements.
660 # This initiates queries against system catalog of primary/master
661 # thus increases load of master.
662 # If you are absolutely sure that your system never uses temporary tables
663 # and you want to save access to primary/master, you could turn this off.
666 check_unlogged_table = on
667 # If on, enable unlogged table check in SELECT statements.
668 # This initiates queries against system catalog of primary/master
669 # thus increases load of master.
670 # If you are absolutely sure that your system never uses unlogged tables
671 # and you want to save access to primary/master, you could turn this off.
674 #------------------------------------------------------------------------------
675 # IN MEMORY QUERY MEMORY CACHE
676 #------------------------------------------------------------------------------
677 memory_cache_enabled = off
678 # If on, use the memory cache functionality, off by default
679 memqcache_method = 'shmem'
680 # Cache storage method. either 'shmem'(shared memory) or
681 # 'memcached'. 'shmem' by default
682 # (change requires restart)
683 memqcache_memcached_host = 'localhost'
684 # Memcached host name or IP address. Mandatory if
685 # memqcache_method = 'memcached'.
686 # Defaults to localhost.
687 # (change requires restart)
688 memqcache_memcached_port = 11211
689 # Memcached port number. Mondatory if memqcache_method = 'memcached'.
691 # (change requires restart)
692 memqcache_total_size = 67108864
693 # Total memory size in bytes for storing memory cache.
694 # Mandatory if memqcache_method = 'shmem'.
696 # (change requires restart)
697 memqcache_max_num_cache = 1000000
698 # Total number of cache entries. Mandatory
699 # if memqcache_method = 'shmem'.
700 # Each cache entry consumes 48 bytes on shared memory.
701 # Defaults to 1,000,000(45.8MB).
702 # (change requires restart)
704 # Memory cache entry life time specified in seconds.
705 # 0 means infinite life time. 0 by default.
706 # (change requires restart)
707 memqcache_auto_cache_invalidation = on
708 # If on, invalidation of query cache is triggered by corresponding
709 # DDL/DML/DCL(and memqcache_expire). If off, it is only triggered
710 # by memqcache_expire. on by default.
711 # (change requires restart)
712 memqcache_maxcache = 409600
713 # Maximum SELECT result size in bytes.
714 # Must be smaller than memqcache_cache_block_size. Defaults to 400KB.
715 # (change requires restart)
716 memqcache_cache_block_size = 1048576
717 # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.
719 # (change requires restart)
720 memqcache_oiddir = '/var/log/pgpool/oiddir'
721 # Temporary work directory to record table oids
722 # (change requires restart)
723 white_memqcache_table_list = ''
724 # Comma separated list of table names to memcache
725 # that don't write to database
726 # Regexp are accepted
727 black_memqcache_table_list = ''
728 # Comma separated list of table names not to memcache
729 # that don't write to database
730 # Regexp are accepted