# ---------------------------- # pgPool-II configuration file # ---------------------------- # # This file consists of lines of the form: # # name = value # # Whitespace may be used. Comments are introduced with "#" anywhere on a line. # The complete list of parameter names and allowed values can be found in the # pgPool-II documentation. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use "pgpool reload". Some # parameters, which are marked below, require a server shutdown and restart to # take effect. # #------------------------------------------------------------------------------ # BACKEND CLUSTERING MODE # Choose one of: 'streaming_replication', 'native_replication', # 'logical_replication', 'slony', 'raw' or 'snapshot_isolation' # (change requires restart) # streaming_replication = recommended clustering mode, PostgreSQL is responsible to replicate on each servers. # native_replication = makes Pgpool-II replicate data between PostgreSQL backends. # logical_replication = PostgreSQL is responsible to replicate on each servers. # raw = Pgpool-II does not care about the database synchronization. It's user's responsibility to make the whole system does a meaningful thing. Load balancing is not possible in the mode. #------------------------------------------------------------------------------ backend_clustering_mode = 'streaming_replication' #------------------------------------------------------------------------------ # CONNECTIONS #------------------------------------------------------------------------------ # - pgpool Connection Settings - listen_addresses = '*' # what host name(s) or IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 6000 # Port number # (change requires restart) unix_socket_directories = '/home/postgres/config/6000/run/pid' # Unix domain socket path(s) # The Debian package defaults to # /var/run/postgresql # (change requires restart) # - pgpool Communication Manager Connection Settings - pcp_listen_addresses = '*' # what host name(s) or IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) pcp_port = 6001 # Port number for pcp # (change requires restart) pcp_socket_dir = '/home/postgres/config/6000/run/pid' # Unix domain socket path for pcp # The Debian package defaults to # /var/run/postgresql # (change requires restart) listen_backlog_multiplier = 2 # Set the backlog parameter of listen(2) to # num_init_children * listen_backlog_multiplier. # (change requires restart) # - Backend Connection Settings - backend_hostname0 = 'psql01-pie2-prd.vz.points.com' # Host name or IP address to connect to for backend 0 backend_port0 = 5432 # Port number for backend 0 backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode) backend_data_directory0 = '/data/14.5/data' # Data directory for backend 0 backend_flag0 = 'ALLOW_TO_FAILOVER' # Controls various backend behavior # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER # or ALWAYS_PRIMARY backend_application_name0 = 'psql01-pie2-prd.vz.points.com' # walsender's application_name, used for "show pool_nodes" command backend_hostname1 = 'psql1-pie2-prd.pr1.points.com' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/data/14.5/data' backend_flag1 = 'ALLOW_TO_FAILOVER' backend_application_name1 = 'psql1-pie2-prd.pr1.points.com' backend_hostname2 = 'psql2-pie2-prd.pr1.points.com' backend_port2 = 5432 backend_weight2 = 1 backend_data_directory2 = '/data/14.5/data' backend_flag2 = 'ALLOW_TO_FAILOVER' backend_application_name2 = 'psql2-pie2-prd.pr1.points.com' # - Authentication - enable_pool_hba = on # Use pool_hba.conf for client authentication pool_passwd = 'pool_passwd' # File name of pool_passwd for md5 authentication. # "" disables pool_passwd. # (change requires restart) #------------------------------------------------------------------------------ # POOLS #------------------------------------------------------------------------------ # - Concurrent session and pool size - process_management_mode = dynamic # process management mode for child processes # Valid options: # static: all children are pre-forked at startup # dynamic: child processes are spawned on demand. # number of idle child processes at any time are # configured by min_spare_children and max_spare_children process_management_strategy = lazy # process management strategy to satisfy spare processes # Valid options: # # lazy: In this mode, the scale-down is performed gradually # and only gets triggered when excessive spare processes count # remains high for more than 5 mins # # gentle: In this mode, the scale-down is performed gradually # and only gets triggered when excessive spare processes count # remains high for more than 2 mins # # aggressive: In this mode, the scale-down is performed aggressively # and gets triggered more frequently in case of higher spare processes. # This mode uses faster and slightly less smart process selection criteria # to identify the child processes that can be serviced to satisfy # max_spare_children # # (Only applicable for dynamic process management mode) num_init_children = 3000 # Maximum Number of concurrent sessions allowed # (change requires restart) min_spare_children = 1000 # Minimum number of spare child processes waiting for connection # (Only applicable for dynamic process management mode) max_spare_children = 2000 # Maximum number of idle child processes waiting for connection # (Only applicable for dynamic process management mode) max_pool = 1 # Number of connection pool caches per connection # (change requires restart) # - Life time - child_life_time = 600 # Pool exits after being idle for this many seconds child_max_connections = 1 # Pool exits after receiving that many connections # 0 means no exit connection_life_time = 0 # Connection to backend closes after being idle for this many seconds # 0 means no close client_idle_limit = 0 # Client is disconnected after being idle for that many seconds # (even inside an explicit transactions!) # 0 means no disconnection #------------------------------------------------------------------------------ # LOGS #------------------------------------------------------------------------------ # - Where to log - log_destination = 'stderr' # Where to log # Valid values are combinations of stderr, # and syslog. Default to stderr. # - What to log - log_line_prefix = '%m %a PROCESS: %P DB: %d USER: %U APPL: %a ' # printf-style string to output at beginning of each log. log_connections = on # Log connections log_disconnections = on # Log disconnections log_hostname = on # Hostname will be shown in ps status # and in logs if connections are logged log_statement = off # Log all statements #log_per_node_statement = off # Log all statements # with node and backend informations log_client_messages = off # Log any client messages #log_standby_delay = 'if_over_threshold' # Log standby delay # Valid values are combinations of always, # if_over_threshold, none # - Syslog specific - syslog_facility = 'LOCAL2' # Syslog local facility. Default to LOCAL0 syslog_ident = 'pgpool' # Syslog program identification string # Default to 'pgpool' # - Debug - log_error_verbosity = default # terse, default, or verbose messages client_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error log_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr # into log files. # (change requires restart) # -- Only used if logging_collector is on --- log_directory = '/home/postgres/config/6000/logs' # directory where log files are written, # can be absolute log_filename = 'pgpool-%a.log' # log file name pattern, # can include strftime() escapes log_file_mode = 0600 # creation mode for log files, # begin with 0 to use octal notation log_truncate_on_rotation = on # If on, an existing log file with the # same name as the new log file will be # truncated rather than appended to. # But such truncation only occurs on # time-driven rotation, not on restarts # or size-driven rotation. Default is # off, meaning append to existing files # in all cases. log_rotation_age = 1440 # Automatic rotation of logfiles will # happen after that (minutes)time. # 0 disables time based rotation. log_rotation_size = 0 # Automatic rotation of logfiles will # happen after that much (KB) log output. # 0 disables size based rotation. #------------------------------------------------------------------------------ # FILE LOCATIONS #------------------------------------------------------------------------------ pid_file_name = '/home/postgres/config/6000/run/pid/pgpool.pid' # PID file name # Can be specified as relative to the" # location of pgpool.conf file or # as an absolute path # (change requires restart) logdir = '/home/postgres/config/6000/logs' # Directory of pgPool status file # (change requires restart) #------------------------------------------------------------------------------ # CONNECTION POOLING #------------------------------------------------------------------------------ #------------------------------------------------------------------------------ # REPLICATION MODE #------------------------------------------------------------------------------ #------------------------------------------------------------------------------ # LOAD BALANCING MODE #------------------------------------------------------------------------------ load_balance_mode = off # Activate load balancing mode # (change requires restart) disable_load_balance_on_write = 'always' # Load balance behavior when write query is issued # in an explicit transaction. # # Valid values: # # 'transaction' (default): # if a write query is issued, subsequent # read queries will not be load balanced # until the transaction ends. # # 'trans_transaction': # if a write query is issued, subsequent # read queries in an explicit transaction # will not be load balanced until the session ends. # # 'dml_adaptive': # Queries on the tables that have already been # modified within the current explicit transaction will # not be load balanced until the end of the transaction. # # 'always': # if a write query is issued, read queries will # not be load balanced until the session ends. # # Note that any query not in an explicit transaction # is not affected by the parameter except 'always'. #------------------------------------------------------------------------------ # STREAMING REPLICATION MODE #------------------------------------------------------------------------------ # - Streaming - sr_check_user = 'pgpool' # Streaming replication check user # This is neccessary even if you disable streaming # replication delay check by sr_check_period = 0 sr_check_password = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx' # Password for streaming replication check user # Leaving it empty will make Pgpool-II to first look for the # Password in pool_passwd file before using the empty password sr_check_database = 'postgres' # Database name for streaming replication check #delay_threshold = 10000000 # Threshold before not dispatching query to standby node # Unit is in bytes # Disabled (0) by default # - Special commands - # follow_primary_command = '/home/postgres/dba_scripts/standby_follow.sh %H > /home/postgres/config/6000/logs/standby_follow.log' # Executes this command after main node failover # Special values: # %d = failed node id # %h = failed node host name # %p = failed node port number # %D = failed node database cluster path # %m = new main node id # %H = new main node hostname # %M = old main node id # %P = old primary node id # %r = new main port number # %R = new main database cluster path # %N = old primary node hostname # %S = old primary node port number # %% = '%' character #------------------------------------------------------------------------------ # HEALTH CHECK GLOBAL PARAMETERS #------------------------------------------------------------------------------ health_check_period = 30 # Health check period # Disabled (0) by default health_check_timeout = 10 # Health check timeout # 0 means no timeout health_check_user = 'pgpool' # Health check user health_check_password = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx' # Password for health check user # Leaving it empty will make Pgpool-II to first look for the # Password in pool_passwd file before using the empty password health_check_database = 'postgres' # Database name for health check. If '', tries 'postgres' frist, health_check_max_retries = 5 # Maximum number of times to retry a failed health check before giving up. health_check_retry_delay = 10 # Amount of time to wait (in seconds) between retries. connect_timeout = 20000 # Timeout value in milliseconds before giving up to connect to backend. # Default is 10000 ms (10 second). Flaky network user may want to increase # the value. 0 means no timeout. # Note that this value is not only used for health check, # but also for ordinary conection to backend. #------------------------------------------------------------------------------ # HEALTH CHECK PER NODE PARAMETERS (OPTIONAL) #------------------------------------------------------------------------------ #------------------------------------------------------------------------------ # FAILOVER AND FAILBACK #------------------------------------------------------------------------------ failover_command = '/home/postgres/dba_scripts/failover.sh %h %H > /home/postgres/config/6000/logs/failover.log' # Executes this command at failover # Special values: # %d = failed node id # %h = failed node host name # %p = failed node port number # %D = failed node database cluster path # %m = new main node id # %H = new main node hostname # %M = old main node id # %P = old primary node id # %r = new main port number # %R = new main database cluster path # %N = old primary node hostname # %S = old primary node port number # %% = '%' character search_primary_node_timeout = 10 # Timeout in seconds to search for the # primary node when a failover occurs. # 0 means no timeout, keep searching # for a primary node forever. #------------------------------------------------------------------------------ # ONLINE RECOVERY #------------------------------------------------------------------------------ recovery_user = 'pgpool' # Online recovery user recovery_password = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx' # Online recovery password # Leaving it empty will make Pgpool-II to first look for the # Password in pool_passwd file before using the empty password auto_failback = on # Dettached backend node reattach automatically # if replication_state is 'streaming'. #------------------------------------------------------------------------------ # WATCHDOG #------------------------------------------------------------------------------ # - Enabling - use_watchdog = on # Activates watchdog # (change requires restart) # -Connection to up stream servers - trusted_servers = 'pgpool1-pie2-prd.vz.points.com,pgpool2-pie2-prd.vz.points.com,pgpool3-pie2-prd.vz.points.com' # trusted server list which are used # to confirm network connection # (hostA,hostB,hostC,...) # (change requires restart) # - Watchdog communication Settings - hostname0 = 'pgpool1-pie2-prd.vz.points.com' # Host name or IP address of pgpool node # for watchdog connection # (change requires restart) wd_port0 = 9000 # Port number for watchdog service # (change requires restart) pgpool_port0 = 6000 # Port number for pgpool # (change requires restart) hostname1 = 'pgpool2-pie2-prd.vz.points.com' wd_port1 = 9000 pgpool_port1 = 6000 hostname2 = 'pgpool3-pie2-prd.vz.points.com' wd_port2 = 9000 pgpool_port2 = 6000 wd_priority = 15 # priority of this watchdog in leader election # (change requires restart) wd_ipc_socket_dir = '/home/postgres/config/6000/run' # Unix domain socket path for watchdog IPC socket # The Debian package defaults to # /var/run/postgresql # (change requires restart) # - Virtual IP control Setting - delegate_ip = '10.69.21.11' # delegate IP address # If this is empty, virtual IP never bring up. # (change requires restart) if_cmd_path = '/sbin' # path to the directory where if_up/down_cmd exists # If if_up/down_cmd starts with "/", if_cmd_path will be ignored. # (change requires restart) if_up_cmd = '/usr/bin/sudo /sbin/ip addr add 10.69.21.11/32 dev ens3' # startup delegate IP command # (change requires restart) if_down_cmd = '/usr/bin/sudo /sbin/ip addr del 10.69.21.11/32 dev ens3' # shutdown delegate IP command # (change requires restart) arping_path = '/usr/sbin' # arping command path # If arping_cmd starts with "/", if_cmd_path will be ignored. # (change requires restart) arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U 10.69.21.11 -w 1 -i ens3' # arping command # (change requires restart) ping_path = '/usr/bin' # ping command path # (change requires restart) # - Watchdog consensus settings for failover - failover_when_quorum_exists = on # Only perform backend node failover # when the watchdog cluster holds the quorum # (change requires restart) failover_require_consensus = on # Perform failover when majority of Pgpool-II nodes # aggrees on the backend node status change # (change requires restart) enable_consensus_with_half_votes = on # apply majority rule for consensus and quorum computation # at 50% of votes in a cluster with even number of nodes. # when enabled the existence of quorum and consensus # on failover is resolved after receiving half of the # total votes in the cluster, otherwise both these # decisions require at least one more vote than # half of the total votes. # (change requires restart) # - Lifecheck Setting - # -- common -- wd_lifecheck_method = 'heartbeat' # Method of watchdog lifecheck ('heartbeat' or 'query' or 'external') # (change requires restart) wd_interval = 15 # lifecheck interval (sec) > 0 # (change requires restart) # -- heartbeat mode -- heartbeat_hostname0 = 'pgpool1-pie2-prd.vz.points.com' # Host name or IP address used # for sending heartbeat signal. # (change requires restart) heartbeat_port0 = 9001 # Port number used for receiving/sending heartbeat signal # Usually this is the same as heartbeat_portX. # (change requires restart) #heartbeat_device0 = '' # Name of NIC device (such like 'eth0') # used for sending/receiving heartbeat # signal to/from destination 0. # This works only when this is not empty # and pgpool has root privilege. # (change requires restart) heartbeat_hostname1 = 'pgpool2-pie2-prd.vz.points.com' heartbeat_port1 = 9001 #heartbeat_device1 = '' heartbeat_hostname2 = 'pgpool3-pie2-prd.vz.points.com' heartbeat_port2 = 9001 #heartbeat_device2 = '' wd_heartbeat_keepalive = 5 # Interval time of sending heartbeat signal (sec) # (change requires restart) wd_heartbeat_deadtime = 30 # Deadtime interval for heartbeat signal (sec) # (change requires restart) # -- query mode -- #wd_lifecheck_user = 'pgpool' # watchdog user monitoring pgpools in lifecheck # (change requires restart) #wd_lifecheck_password = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx' # Password for watchdog user in lifecheck # Leaving it empty will make Pgpool-II to first look for the # Password in pool_passwd file before using the empty password # (change requires restart) #------------------------------------------------------------------------------ # OTHERS #------------------------------------------------------------------------------ relcache_expire = 300 # Life time of relation cache in seconds. # 0 means no cache expiration(the default). # The relation cache is used for cache the # query result against PostgreSQL system # catalog to obtain various information # including table structures or if it's a # temporary table or not. The cache is # maintained in a pgpool child local memory # and being kept as long as it survives. # If someone modify the table by using # ALTER TABLE or some such, the relcache is # not consistent anymore. # For this purpose, cache_expiration # controls the life time of the cache. #------------------------------------------------------------------------------ # IN MEMORY QUERY MEMORY CACHE #------------------------------------------------------------------------------