git.rot13.org Git - pgpool-online-recovery/blob - pgpool.conf

   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 = 5431
  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
  40
  41 # - pgpool Communication Manager Connection Settings -
  42
  43 pcp_port = 9898
  44                                    # Port number for pcp
  45                                    # (change requires restart)
  46 pcp_socket_dir = '/var/run/postgresql'
  47                                    # Unix domain socket path for pcp
  48                                    # The Debian package defaults to
  49                                    # /var/run/postgresql
  50                                    # (change requires restart)
  51
  52 # - Backend Connection Settings -
  53
  54 #backend_hostname0 = 'host1'
  55                                    # Host name or IP address to connect to for backend 0
  56 #backend_port0 = 5432
  57                                    # Port number for backend 0
  58 #backend_weight0 = 1
  59                                    # Weight for backend 0 (only in load balancing mode)
  60 #backend_data_directory0 = '/data'
  61                                    # Data directory for backend 0
  62 #backend_flag0 = 'ALLOW_TO_FAILOVER'
  63                                    # Controls various backend behavior
  64                                    # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
  65 backend_hostname0='master.foo.bar'
  66 backend_port0 = 5432
  67 backend_weight0 = 1
  68 backend_data_directory0 = '/var/lib/postgres/9.1/main/'
  69 backend_flag0 = 'ALLOW_TO_FAILOVER'
  70
  71 backend_hostname1='slave.foo.bar'
  72 backend_port1 = 5432
  73 backend_weight1 = 1
  74 backend_data_directory1 = '/var/lib/postgres/9.1/main/'
  75 backend_flag1 = 'ALLOW_TO_FAILOVER'
  76
  77
  78 # - Authentication -
  79
  80 enable_pool_hba = on
  81                                    # Use pool_hba.conf for client authentication
  82 authentication_timeout = 60
  83                                    # Delay in seconds to complete client authentication
  84                                    # 0 means no timeout.
  85
  86 # - SSL Connections -
  87
  88 ssl = off
  89                                    # Enable SSL support
  90                                    # (change requires restart)
  91 #ssl_key = './server.key'
  92                                    # Path to the SSL private key file
  93                                    # (change requires restart)
  94 #ssl_cert = './server.cert'
  95                                    # Path to the SSL public certificate file
  96                                    # (change requires restart)
  97 #ssl_ca_cert = ''
  98                                    # Path to a single PEM format file
  99                                    # containing CA root certificate(s)
 100                                    # (change requires restart)
 101 #ssl_ca_cert_dir = ''
 102                                    # Directory containing CA root certificate(s)
 103                                    # (change requires restart)
 104
 105
 106 #------------------------------------------------------------------------------
 107 # POOLS
 108 #------------------------------------------------------------------------------
 109
 110 # - Pool size -
 111
 112 num_init_children = 32
 113                                    # Number of pools
 114                                    # (change requires restart)
 115 max_pool = 4
 116                                    # Number of connections per pool
 117                                    # (change requires restart)
 118
 119 # - Life time -
 120
 121 child_life_time = 300
 122                                    # Pool exits after being idle for this many seconds
 123 child_max_connections = 100
 124                                    # Pool exits after receiving that many connections
 125                                    # 0 means no exit
 126 connection_life_time = 0
 127                                    # Connection to backend closes after being idle for this many seconds
 128                                    # 0 means no close
 129 client_idle_limit = 120
 130                                    # Client is disconnected after being idle for that many seconds
 131                                    # (even inside an explicit transactions!)
 132                                    # 0 means no disconnection
 133
 134
 135 #------------------------------------------------------------------------------
 136 # LOGS
 137 #------------------------------------------------------------------------------
 138
 139 # - Where to log -
 140
 141 log_destination = 'stderr'
 142                                    # Where to log
 143                                    # Valid values are combinations of stderr,
 144                                    # and syslog. Default to stderr.
 145
 146 # - What to log -
 147
 148 print_timestamp = on
 149                                    # Print timestamp on each line
 150                                    # (change requires restart)
 151
 152 log_connections = on
 153                                    # Log connections
 154 log_hostname = off
 155                                    # Hostname will be shown in ps status
 156                                    # and in logs if connections are logged
 157 log_statement = off
 158                                    # Log all statements
 159 log_per_node_statement = off
 160                                    # Log all statements
 161                                    # with node and backend informations
 162 log_standby_delay = 'none'
 163                                    # Log standby delay
 164                                    # Valid values are combinations of always,
 165                                    # if_over_threshold, none
 166
 167 # - Syslog specific -
 168
 169 syslog_facility = 'LOCAL0'
 170                                    # Syslog local facility. Default to LOCAL0
 171 syslog_ident = 'pgpool'
 172                                    # Syslog program identification string
 173                                    # Default to 'pgpool'
 174
 175 # - Debug -
 176
 177 debug_level = 0
 178                                    # Debug message verbosity level
 179                                    # 0 means no message, 1 or more mean verbose
 180
 181
 182 #------------------------------------------------------------------------------
 183 # FILE LOCATIONS
 184 #------------------------------------------------------------------------------
 185
 186 pid_file_name = '/var/run/postgresql/pgpool.pid'
 187                                    # PID file name
 188                                    # (change requires restart)
 189 logdir = '/var/log/postgresql'
 190                                    # Directory of pgPool status file
 191                                    # (change requires restart)
 192
 193
 194 #------------------------------------------------------------------------------
 195 # CONNECTION POOLING
 196 #------------------------------------------------------------------------------
 197
 198 connection_cache = on
 199                                    # Activate connection pools
 200                                    # (change requires restart)
 201
 202                                    # Semicolon separated list of queries
 203                                    # to be issued at the end of a session
 204                                    # The default is for 8.3 and later
 205 reset_query_list = 'ABORT; DISCARD ALL'
 206                                    # The following one is for 8.2 and before
 207 #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
 208
 209
 210 #------------------------------------------------------------------------------
 211 # REPLICATION MODE
 212 #------------------------------------------------------------------------------
 213
 214 replication_mode = off
 215                                    # Activate replication mode
 216                                    # (change requires restart)
 217 replicate_select = off
 218                                    # Replicate SELECT statements
 219                                    # when in replication or parallel mode
 220                                    # replicate_select is higher priority than
 221                                    # load_balance_mode.
 222
 223 insert_lock = off
 224                                    # Automatically locks a dummy row or a table
 225                                    # with INSERT statements to keep SERIAL data
 226                                    # consistency
 227                                    # Without SERIAL, no lock will be issued
 228 lobj_lock_table = ''
 229                                    # When rewriting lo_creat command in
 230                                    # replication mode, specify table name to
 231                                    # lock
 232
 233 # - Degenerate handling -
 234
 235 replication_stop_on_mismatch = off
 236                                    # On disagreement with the packet kind
 237                                    # sent from backend, degenerate the node
 238                                    # which is most likely "minority"
 239                                    # If off, just force to exit this session
 240
 241 failover_if_affected_tuples_mismatch = off
 242                                    # On disagreement with the number of affected
 243                                    # tuples in UPDATE/DELETE queries, then
 244                                    # degenerate the node which is most likely
 245                                    # "minority".
 246                                    # If off, just abort the transaction to
 247                                    # keep the consistency
 248
 249
 250 #------------------------------------------------------------------------------
 251 # LOAD BALANCING MODE
 252 #------------------------------------------------------------------------------
 253
 254 load_balance_mode = on
 255                                    # Activate load balancing mode
 256                                    # (change requires restart)
 257 ignore_leading_white_space = on
 258                                    # Ignore leading white spaces of each query
 259 white_function_list = ''
 260                                    # Comma separated list of function names
 261                                    # that don't write to database
 262                                    # Regexp are accepted
 263 black_function_list = 'currval,lastval,nextval,setval,sp_.*'
 264                                    # Comma separated list of function names
 265                                    # that write to database
 266                                    # Regexp are accepted
 267
 268
 269 #------------------------------------------------------------------------------
 270 # MASTER/SLAVE MODE
 271 #------------------------------------------------------------------------------
 272
 273 master_slave_mode = on
 274                                    # Activate master/slave mode
 275                                    # (change requires restart)
 276 master_slave_sub_mode = 'stream'
 277                                    # Master/slave sub mode
 278                                    # Valid values are combinations slony or
 279                                    # stream. Default is slony.
 280                                    # (change requires restart)
 281
 282 # - Streaming -
 283
 284 sr_check_period = 0
 285                                    # Streaming replication check period
 286                                    # Disabled (0) by default
 287 sr_check_user = 'postgres'
 288                                    # Streaming replication check user
 289                                    # This is necessary even if you disable
 290                                    # streaming replication delay check with
 291                                    # sr_check_period = 0
 292 sr_check_password = 'nopass'
 293                                    # Password for streaming replication check user
 294 delay_threshold = 0
 295                                    # Threshold before not dispatching query to standby node
 296                                    # Unit is in bytes
 297                                    # Disabled (0) by default
 298
 299 # - Special commands -
 300
 301 follow_master_command = 'echo %M > /tmp/postgres_master'
 302                                    # Executes this command after master failover
 303                                    # Special values:
 304                                    #   %d = node id
 305                                    #   %h = host name
 306                                    #   %p = port number
 307                                    #   %D = database cluster path
 308                                    #   %m = new master node id
 309                                    #   %H = hostname of the new master node
 310                                    #   %M = old master node id
 311                                    #   %P = old primary node id
 312                                    #   %% = '%' character
 313
 314
 315 #------------------------------------------------------------------------------
 316 # PARALLEL MODE AND QUERY CACHE
 317 #------------------------------------------------------------------------------
 318
 319 parallel_mode = off
 320                                    # Activates parallel query mode
 321                                    # (change requires restart)
 322 enable_query_cache = off
 323                                    # Activates query cache
 324                                    # (change requires restart)
 325
 326 pgpool2_hostname = ''
 327                                    # Set pgpool2 hostname
 328                                    # (change requires restart)
 329
 330 # - System DB info -
 331
 332 system_db_hostname  = 'localhost'
 333                                    # (change requires restart)
 334 system_db_port = 5432
 335                                    # (change requires restart)
 336 system_db_dbname = 'pgpool'
 337                                    # (change requires restart)
 338 system_db_schema = 'pgpool_catalog'
 339                                    # (change requires restart)
 340 system_db_user = 'pgpool'
 341                                    # (change requires restart)
 342 system_db_password = ''
 343                                    # (change requires restart)
 344
 345
 346 #------------------------------------------------------------------------------
 347 # HEALTH CHECK
 348 #------------------------------------------------------------------------------
 349
 350 health_check_period = 30
 351                                    # Health check period
 352                                    # Disabled (0) by default
 353 health_check_timeout = 20
 354                                    # Health check timeout
 355                                    # 0 means no timeout
 356 health_check_user = 'postgres'
 357                                    # Health check user
 358 health_check_password = ''
 359                                    # This parameter is not yet implemented.
 360                                    # Password for health check user
 361
 362
 363 #------------------------------------------------------------------------------
 364 # FAILOVER AND FAILBACK
 365 #------------------------------------------------------------------------------
 366
 367 failover_command = '/usr/local/bin/failover.sh %d %H %P /tmp/trigger_file'
 368                                    # Executes this command at failover
 369                                    # Special values:
 370                                    #   %d = failed node id
 371                                    #   %h = failed host name
 372                                    #   %p = port number
 373                                    #   %D = database cluster path
 374                                    #   %m = new master node id
 375                                    #   %H = hostname of the new master node
 376                                    #   %M = old master node id
 377                                    #   %P = old primary node id
 378                                    #   %% = '%' character
 379 failback_command = ''
 380                                    # Executes this command at failback.
 381                                    # Special values:
 382                                    #   %d = node id
 383                                    #   %h = host name
 384                                    #   %p = port number
 385                                    #   %D = database cluster path
 386                                    #   %m = new master node id
 387                                    #   %H = hostname of the new master node
 388                                    #   %M = old master node id
 389                                    #   %P = old primary node id
 390                                    #   %% = '%' character
 391
 392 fail_over_on_backend_error = on
 393                                    # Initiates failover when writing to the
 394                                    # backend communication socket fails
 395                                    # This is the same behaviour of pgpool-II
 396                                    # 2.2.x and previous releases
 397                                    # If set to off, pgpool will report an
 398                                    # error and disconnect the session.
 399
 400
 401 #------------------------------------------------------------------------------
 402 # ONLINE RECOVERY
 403 #------------------------------------------------------------------------------
 404
 405 recovery_user = 'postgres'
 406                                    # Online recovery user
 407 recovery_password = 'nopass'
 408                                    # Online recovery password
 409 #recovery_1st_stage_command = 'basebackup.sh'
 410                                    # Executes a command in first stage
 411 recovery_2nd_stage_command = ''
 412                                    # Executes a command in second stage
 413 recovery_timeout = 30
 414                                    # Timeout in seconds to wait for the
 415                                    # recovering node's postmaster to start up
 416                                    # 0 means no wait
 417 client_idle_limit_in_recovery = 0
 418                                    # Client is disconnected after being idle
 419                                    # for that many seconds in the second stage
 420                                    # of online recovery
 421                                    # 0 means no disconnection
 422                                    # -1 means immediate disconnection
 423
 424
 425 #------------------------------------------------------------------------------
 426 # OTHERS
 427 #------------------------------------------------------------------------------
 428
 429 relcache_expire = 0
 430                                    # Life time of relation cache in seconds.
 431                                    # 0 means no cache expiration(the default).
 432                                    # The relation cache is used for cache the
 433                                    # query result against PostgreSQL system
 434                                    # catalog to obtain various information
 435                                    # including table structures or if it's a
 436                                    # temporary table or not. The cache is
 437                                    # maintained in a pgpool child local memory
 438                                    # and being kept as long as it survives.
 439                                    # If someone modify the table by using
 440                                    # ALTER TABLE or some such, the relcache is
 441                                    # not consistent anymore.
 442                                    # For this purpose, cache_expiration
 443                                    # controls the life time of the cache.
 444