Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vcuNw-00C2ar-1H for pgpool-hackers@arkaria.postgresql.org; Mon, 05 Jan 2026 23:53:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vcuNv-0052ps-14 for pgpool-hackers@arkaria.postgresql.org; Mon, 05 Jan 2026 23:53:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vcuNv-0052pS-0E for pgpool-hackers@lists.postgresql.org; Mon, 05 Jan 2026 23:53:15 +0000 Received: from meldrar.postgresql.org ([2a02:c0:301:0:ffff::31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vcuNo-004lqf-1A for pgpool-hackers@lists.postgresql.org; Mon, 05 Jan 2026 23:53:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Content-Transfer-Encoding:Content-Type: Mime-Version:References:In-Reply-To:From:Subject:Cc:To:Message-Id:Date:Sender :Reply-To:Content-ID:Content-Description; bh=L2ggAzBqRKb9I2u9HLStpkCJOT513/sSUIg1TSf+L5k=; b=WlpEQkkVTYz7iGYUF9D2AgnnHV B9nHhsmMUzV4HrGf1CFblDkEYMUR7C1boq20fh+WbntDCQfYT9/co6S0el+CY+k6pXSBC+hxC8LIh am3XvSIuTetoShGaZNmQvCf2M0hiJ2FnIRPBgj5BefYWE38y/CGe4tk0PlnWWUHuU+eEzzKzGNx0N xRQmDOFpFFbahdzf9LlzGPSJeoR4pDufwO3wJGFAN8ZsYg9k6VS1kEF4ff8cnXcfpPzfMFuM31AsS HFyLmpdN1lSFSmt2If/QNOzc0BGfEn8jhEUnwoDDPSxRgiKMaAkDbjy/GBZ9ReEfd/z83CZd7MRWm Z0vHWXGQ==; Received: from [2409:11:4120:300:ead8:5ce8:950c:74b4] (helo=localhost) by meldrar.postgresql.org with esmtpsa (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vcuNl-009dVq-2G; Mon, 05 Jan 2026 23:53:08 +0000 Date: Tue, 06 Jan 2026 08:52:57 +0900 (JST) Message-Id: <20260106.085257.1146983017764501399.ishii@postgresql.org> To: nadav@tailorbrands.com Cc: pgpool-hackers@lists.postgresql.org Subject: Re: Proposal: recent access based routing for primary-replica setups From: Tatsuo Ishii In-Reply-To: References: <20251229.084844.1964712636774742758.ishii@postgresql.org> <20251229.085803.2270812288334644179.ishii@postgresql.org> X-Mailer: Mew version 6.8 on Emacs 29.3 Mime-Version: 1.0 Content-Type: Multipart/Mixed; boundary="--Next_Part(Tue_Jan__6_08_52_57_2026_651)--" Content-Transfer-Encoding: 7bit X-Host-Lookup-Failed: Reverse DNS lookup failed for 2409:11:4120:300:ead8:5ce8:950c:74b4 (failed) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ----Next_Part(Tue_Jan__6_08_52_57_2026_651)-- Content-Type: Text/Plain; charset=us-ascii Content-Transfer-Encoding: 7bit > Thanks for the help! please find attached the latest version with all > changes and test passing. Thanks for updating patch! I confirmed that all test have passed on my Ubunu box. Now I am working on Japanese document. While working on it, I did followings to the English document. (see attached) - Reformat it so that each line is not too long. Like PostgreSQL, I bend each line at most 78 chars. (I know other parts of document do not follow the rule but I do not want to add more lines not following the rule). - Move replication_delay_source_cmd (string) and replication_delay_source_timeout (integer) at the bottom of "5.12. Streaming Replication Check" section. We usually add the new parameters at the bottom of the page if there's no particular reason. Previously they were in between prefer_lower_delay_standby and log_standby_delay. - Add following to replication_delay_source_cmd. "The line can be terminated with or without a new line character." This is observed from the implementation. I believe this matters for those who try to implement replication_delay_source_cmd. Lastly I have one question. replication_delay_source_timeout (integer) Specifies the timeout in seconds for the external command specified by replication_delay_source_cmd. If the command does not finish within the timeout, Pgpool-II logs an error and continues using the built-in method. It seems this ("continues using the built-in method") is different from the actual behavior. It seems that after timeout, the external command is tried and timeout.... Do you want to fix the source code to match with the document? Or change (just remove ""continues using the built-in method") the document? I am fine with changing the document. Best regards, -- Tatsuo Ishii SRA OSS K.K. English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp ----Next_Part(Tue_Jan__6_08_52_57_2026_651)-- Content-Type: Text/Sgml; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="stream-check.sgml" Streaming Replication Check Pgpool-II can work with PostgreSQL native Streaming Replication, that is available since PostgreSQL 9.0. To configure Pgpool-II with streaming replication, set to 'streaming-replication'. Pgpool-II assumes that Streaming Replication is configured with Hot Standby on PostgreSQL, which means that the standby database can handle read-only queries. sr_check_period (integer) sr_check_period configuration parameter Specifies the time interval in seconds to check the streaming replication delay. The default is 10. This parameter can be changed by reloading the Pgpool-II configurations. sr_check_user (string) sr_check_user configuration parameter Specifies the PostgreSQL user name to perform streaming replication check. Default is ''(empty). The user must have LOGIN privilege and exist on all the PostgreSQL backends. Moreover the user must be a PostgreSQL super user or in "pg_monitor" group. To make in pg_monitor group, execute following SQL command by PostgreSQL super user (replace "sr_check_user" with the setting of ): GRANT pg_monitor TO sr_check_user; For PostgreSQL 9.6, there's no pg_monitor group and must be PostgreSQL super user. If SSL is enabled, the streaming replication check process may use SSL connection. and are used even when is set to 0 (disabled) for the identification of the primary server. This parameter can be changed by reloading the Pgpool-II configurations. sr_check_password (string) sr_check_password configuration parameter Specifies the password of the PostgreSQL user to perform the streaming replication checks. Use '' (empty string) if the user does not requires a password. If sr_check_password is left blank Pgpool-II will first try to get the password for from file before using the empty password. Pgpool-II accepts following forms of password in either sr_check_password or file: AES256-CBC encrypted password Most secure and recommended way to store password. The password string must be prefixed with AES. You can use utility to create the correctly formatted AES encrypted password strings. Pgpool-II will require a valid decryption key at the startup to use the encrypted passwords. see for more details on providing the decryption key to Pgpool-II MD5 hashed password Not so secure as AES256, but still better than clear text password. The password string must be prefixed with MD5. Note that the backend must set up MD5 authentication as well. You can use utility to create the correctly formatted MD5 hashed password strings. Plain text password Not encrypted, clear text password. You should avoid to use this if possible. The password string must be prefixed with TEXT. For example if you want to set mypass as a password, you should specify TEXTmypass in the password field. In the absence of a valid prefix, Pgpool-II will considered the string as a plain text password. This parameter can be changed by reloading the Pgpool-II configurations. sr_check_database (string) sr_check_database configuration parameter Specifies the database to perform streaming replication delay checks. The default is "postgres". This parameter can be changed by reloading the Pgpool-II configurations. delay_threshold (integer) delay_threshold configuration parameter Specifies the maximum tolerance level of replication delay in WAL bytes on the standby server against the primary server. If the delay exceeds this configured level, Pgpool-II stops sending the SELECT queries to the standby server and starts routing everything to the primary server even if is enabled, until the standby catches-up with the primary. Setting this parameter to 0 disables the delay checking. This delay threshold check is performed every . Default is 0. This parameter can be changed by reloading the Pgpool-II configurations. delay_threshold_by_time (integer) delay_threshold_by_time configuration parameter Specifies the maximum tolerance level of replication delay on the standby server against the primary server. If this value is specified without units, it is taken as milliseconds. If the specified value is greater than 0, is ignored. If the delay exceeds this configured level, Pgpool-II stops sending the SELECT queries to the standby server and starts routing everything to the primary server even if is enabled, until the standby catches-up with the primary. Setting this parameter to 0 disables the delay checking. This delay threshold check is performed every . Default is 0. Replication delay is taken from PostgreSQL's system view pg_stat_replication.replay_lag. The view is available PostgreSQL 10 or later. If earlier version of PostgreSQL is used, Pgpool-II automatically falls back to and is ignored. This parameter relies on being correctly set and matching application_name in your PostgreSQL standby's primary_conninfo. If this parameter is enabled, and show replication delay in seconds, rather than bytes. This parameter can be changed by reloading the Pgpool-II configurations. prefer_lower_delay_standby (boolean) prefer_lower_delay_standby configuration parameter This parameter is valid only when or is set to greater than 0. When set to on, if the delay of the load balancing node is greater than or , Pgpool-II does not send read queries to the primary node but the least delay standby with backend_weight to greater than 0. If delay of all standby nodes are greater than or the primary selected as the load balancing node first, Pgpool-II sends to the primary. Default is off. This parameter can be changed by reloading the Pgpool-II configurations. log_standby_delay (enum) log_standby_delay configuration parameter Specifies when to log the replication delay. Below table contains the list of all valid values for the parameter. Log standby delay options Value Description none Never log the standby delay always Log the standby delay if it's greater than 0, every time the replication delay is checked if_over_threshold Only log the standby delay, when it exceeds or value (the default)
This parameter can be changed by reloading the Pgpool-II configurations. replication_delay_source_cmd (string) replication_delay_source_cmd configuration parameter Specifies an external command to retrieve replication delay information for replica nodes. When this parameter is set and not empty, Pgpool-II uses the external command instead of built-in database queries to obtain replication delays. The command is executed as the Pgpool-II process user. The command receives replica node identifiers as positional arguments, with the primary node omitted. Each identifier is in the format <hostname>:<port>, for example server1:5432 server2:5432. The order matches Pgpool-II's backend order (excluding the primary), allowing the script to correlate external metrics (such as from AWS CloudWatch for Aurora) to the correct nodes. The command must write a single line to stdout containing one whitespace-separated delay value per replica, in milliseconds, in the same order as the arguments. The line can be terminated with or without a new line character. The primary node's delay is implicitly zero and should not be included in the output. Delay values can be integers or floating-point numbers. Special value: -1 indicates a replica that is down but not yet detected by Pgpool-II's health checks. Pgpool-II will log this condition but rely on its own health-check logic to decide whether to trigger failover; no failover is triggered solely by receiving -1. Example for a 3-node cluster (1 primary + 2 replicas): if the command receives arguments server1:5432 server2:5432, it should output "25.5 100" to indicate the first replica has 25.5ms delay and the second has 100ms delay. Default is empty (use built-in replication delay queries). This parameter can be changed by reloading the Pgpool-II configurations. replication_delay_source_timeout (integer) replication_delay_source_timeout configuration parameter Specifies the timeout in seconds for the external command specified by . If the command does not finish within the timeout, Pgpool-II logs an error and continues using the built-in method. Default is 10 seconds. Valid range is 1-3600 seconds. This parameter can be changed by reloading the Pgpool-II configurations. ----Next_Part(Tue_Jan__6_08_52_57_2026_651)----