public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tatsuo Ishii <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Proposal: recent access based routing for primary-replica setups
Date: Tue, 06 Jan 2026 08:52:57 +0900 (JST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACeKOO2dOpTECY95pdHDZkeGOcW6srNYPqw+Kqs1=Qq2xYaHMQ@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CACeKOO2dOpTECY95pdHDZkeGOcW6srNYPqw+Kqs1=Qq2xYaHMQ@mail.gmail.com>
> 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
Attachments:
[text/sgml] stream-check.sgml (15.9K, 2-stream-check.sgml)
download | inline:
<!-- doc/src/sgml/config.sgml -->
<sect1 id="runtime-streaming-replication-check">
<title>Streaming Replication Check</title>
<para>
<productname>Pgpool-II</productname> can work with <productname>PostgreSQL</> native
Streaming Replication, that is available since <productname>PostgreSQL</> 9.0.
To configure <productname>Pgpool-II</productname> with streaming
replication, set
<xref linkend="guc-backend-clustering-mode"> to <literal>'streaming-replication'</literal>.
</para>
<para>
<productname>Pgpool-II</productname> assumes that Streaming Replication
is configured with Hot Standby on PostgreSQL, which means that the
standby database can handle read-only queries.
</para>
<variablelist>
<varlistentry id="guc-sr-check-period" xreflabel="sr_check_period">
<term><varname>sr_check_period</varname> (<type>integer</type>)
<indexterm>
<primary><varname>sr_check_period</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
Specifies the time interval in seconds to check the streaming
replication delay.
The default is 10.
</para>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-sr-check-user" xreflabel="sr_check_user">
<term><varname>sr_check_user</varname> (<type>string</type>)
<indexterm>
<primary><varname>sr_check_user</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
Specifies the <productname>PostgreSQL</> user name to perform streaming replication check.
Default is <literal>''</literal>(empty).
The user must have LOGIN privilege and exist on all the
<productname>PostgreSQL</> backends.
Moreover the user must be a <productname>PostgreSQL</productname>
super user or in "pg_monitor" group.
<note>
<para>
To make <xref linkend="guc-sr-check-user"> in pg_monitor
group, execute following SQL command
by <productname>PostgreSQL</productname> super user (replace
"sr_check_user" with the setting of <xref linkend="guc-sr-check-user">):
<programlisting>
GRANT pg_monitor TO sr_check_user;
</programlisting>
For <productname>PostgreSQL</productname> 9.6, there's no
pg_monitor group and <xref linkend="guc-sr-check-user"> must
be <productname>PostgreSQL</productname> super user.
</para>
</note>
</para>
<para>
If <link linkend="runtime-ssl">SSL</link> is enabled, the
streaming replication check process may use SSL connection.
</para>
<note>
<para>
<xref linkend="guc-sr-check-user"> and <xref
linkend="guc-sr-check-password"> are used even when <xref
linkend="guc-sr-check-period"> is set to 0 (disabled) for the
identification of the primary server.
</para>
</note>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-sr-check-password" xreflabel="sr_check_password">
<term><varname>sr_check_password</varname> (<type>string</type>)
<indexterm>
<primary><varname>sr_check_password</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
Specifies the password of the <xref linkend="guc-sr-check-user"> <productname>PostgreSQL</> user
to perform the streaming replication checks.
Use <literal>''</literal> (empty string) if the user does not requires a password.
</para>
<para>
If <varname>sr_check_password</varname> is left blank <productname>Pgpool-II</productname>
will first try to get the password for <xref linkend="guc-sr-check-user"> from
<xref linkend="guc-pool-passwd"> file before using the empty password.
</para>
<para>
<productname>Pgpool-II</productname> accepts following forms
of password in either <varname>sr_check_password</varname>
or <xref linkend="guc-pool-passwd"> file:
<variablelist>
<varlistentry>
<term>AES256-CBC encrypted password</term>
<listitem>
<para>
Most secure and recommended way to store password. The
password string must be prefixed
with <literal>AES</literal>.
You can use <xref linkend="PG-ENC"> utility to create the correctly formatted
<literal>AES</literal> encrypted password strings.
<productname>Pgpool-II</productname> will require a valid decryption key at the
startup to use the encrypted passwords.
see <xref linkend="auth-aes-decryption-key"> for more details on providing the
decryption key to <productname>Pgpool-II</productname>
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>MD5 hashed password</term>
<listitem>
<para>
Not so secure as AES256, but still better than clear
text password. The password string must be prefixed
with <literal>MD5</literal>. Note that the backend
must set up MD5 authentication as well. You can
use <xref linkend="PG-MD5"> utility to create the
correctly formatted
<literal>MD5</literal> hashed password strings.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>Plain text password</term>
<listitem>
<para>
Not encrypted, clear text password. You should avoid
to use this if possible. The password string must be
prefixed with <literal>TEXT</literal>. For example if
you want to set <literal>mypass</literal> as a
password, you should
specify <literal>TEXTmypass</literal> in the password
field. In the absence of a valid
prefix, <productname>Pgpool-II</productname> will
considered the string as a plain text password.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-sr-check-database" xreflabel="sr_check_database">
<term><varname>sr_check_database</varname> (<type>string</type>)
<indexterm>
<primary><varname>sr_check_database</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
Specifies the database to perform streaming replication delay checks.
The default is <literal>"postgres"</literal>.
</para>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-delay-threshold" xreflabel="delay_threshold">
<term><varname>delay_threshold</varname> (<type>integer</type>)
<indexterm>
<primary><varname>delay_threshold</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
Specifies the maximum tolerance level of replication delay in
<acronym>WAL</acronym> bytes on the standby server against the
primary server. If the delay exceeds this configured level,
<productname>Pgpool-II</productname> stops sending the <acronym>
SELECT</acronym> queries to the standby server and starts routing
everything to the primary server even if <xref linkend="guc-load-balance-mode">
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 <xref linkend="guc-sr-check-period">.
Default is 0.
</para>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-delay-threshold-by-time" xreflabel="delay_threshold_by_time">
<term><varname>delay_threshold_by_time</varname> (<type>integer</type>)
<indexterm>
<primary><varname>delay_threshold_by_time</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
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, <xref linkend="guc-delay-threshold"> is ignored. If the delay
exceeds this configured level,
<productname>Pgpool-II</productname> stops sending the <acronym>
SELECT</acronym> queries to the standby server and starts routing
everything to the primary server even if <xref linkend="guc-load-balance-mode">
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 <xref linkend="guc-sr-check-period">.
Default is 0.
</para>
<para>
Replication delay is taken
from <productname>PostgreSQL</productname>'s system
view <structname>pg_stat_replication</structname>.<structfield>replay_lag</structfield>. The
view is available <productname>PostgreSQL</productname> 10 or
later. If earlier version
of <productname>PostgreSQL</productname> is
used, <productname>Pgpool-II</productname> automatically falls
back to <xref linkend="guc-delay-threshold">
and <xref linkend="guc-delay-threshold-by-time"> is ignored.
</para>
<para>
This parameter relies
on <xref linkend="guc-backend-application-name"> being correctly
set and matching <varname>application_name</varname> in
your <productname>PostgreSQL</productname> standby's
primary_conninfo.
</para>
<para>
If this parameter is
enabled, <xref linkend="sql-show-pool-nodes">
and <xref linkend="pcp-node-info"> show replication delay in
seconds, rather than bytes.
</para>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-prefer-lower-delay-standby" xreflabel="prefer_lower_delay_standby">
<term><varname>prefer_lower_delay_standby</varname> (<type>boolean</type>)
<indexterm>
<primary><varname>prefer_lower_delay_standby</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
This parameter is valid only
when <xref linkend="guc-delay-threshold">
or <xref linkend="guc-delay-threshold-by-time"> is set to
greater than 0. When set to on, if the delay of the load
balancing node is greater
than <xref linkend="guc-delay-threshold">
or <xref linkend="guc-delay-threshold-by-time">,
<productname>Pgpool-II</productname> 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 <xref linkend="guc-delay-threshold">
or <xref linkend="guc-delay-threshold-by-time"> the primary
selected as the load balancing node
first, <productname>Pgpool-II</productname> sends to the
primary. Default is off.
</para>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</productname> configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-log-standby-delay" xreflabel="log_standby_delay">
<term><varname>log_standby_delay</varname> (<type>enum</type>)
<indexterm>
<primary><varname>log_standby_delay</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
Specifies when to log the replication delay. Below table contains the list
of all valid values for the parameter.
</para>
<table id="log-standby-delay-table">
<title>Log standby delay options</title>
<tgroup cols="2">
<thead>
<row>
<entry>Value</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry><literal>none</literal></entry>
<entry>Never log the standby delay</entry>
</row>
<row>
<entry><literal>always</literal></entry>
<entry>Log the standby delay if it's greater than 0, every time the replication delay is checked</entry>
</row>
<row>
<entry><literal>if_over_threshold</literal></entry>
<entry>Only log the standby delay, when it exceeds <xref linkend="guc-delay-threshold"> or <xref linkend="guc-delay-threshold-by-time"> value (the default)</entry>
</row>
</tbody>
</tgroup>
</table>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-replication-delay-source-cmd" xreflabel="replication_delay_source_cmd">
<term><varname>replication_delay_source_cmd</varname> (<type>string</type>)
<indexterm>
<primary><varname>replication_delay_source_cmd</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
Specifies an external command to retrieve replication delay information
for replica nodes. When this parameter is set and not
empty, <productname>Pgpool-II</productname> uses the external command
instead of built-in database queries to obtain replication delays. The
command is executed as the <productname>Pgpool-II</productname> process
user.
</para>
<para>
The command receives replica node identifiers as positional arguments,
with the primary node omitted. Each identifier is in the
format <literal><hostname>:<port></literal>, for
example <literal>server1:5432 server2:5432</literal>. The order matches
<productname>Pgpool-II</productname>'s backend order (excluding the
primary), allowing the script to correlate external metrics (such as from
AWS CloudWatch for Aurora) to the correct nodes.
</para>
<para>
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.
</para>
<para>
Special value: <literal>-1</literal> indicates a replica that is down but
not yet detected by <productname>Pgpool-II</productname>'s health
checks. <productname>Pgpool-II</productname> 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 <literal>-1</literal>.
</para>
<para>
Example for a 3-node cluster (1 primary + 2 replicas): if the command
receives arguments
<literal>server1:5432 server2:5432</literal>, it should
output <literal>"25.5 100"</literal> to indicate the first replica has
25.5ms delay and the second has 100ms delay.
</para>
<para>
Default is empty (use built-in replication delay queries).
</para>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</>
configurations.
</para>
</listitem>
</varlistentry>
<varlistentry id="guc-replication-delay-source-timeout" xreflabel="replication_delay_source_timeout">
<term><varname>replication_delay_source_timeout</varname> (<type>integer</type>)
<indexterm>
<primary><varname>replication_delay_source_timeout</varname> configuration parameter</primary>
</indexterm>
</term>
<listitem>
<para>
Specifies the timeout in seconds for the external command specified by
<xref linkend="guc-replication-delay-source-cmd">. If the command does
not finish within the timeout, <productname>Pgpool-II</productname> logs
an error and continues using the built-in method.
</para>
<para>
Default is 10 seconds. Valid range is 1-3600 seconds.
</para>
<para>
This parameter can be changed by reloading the <productname>Pgpool-II</>
configurations.
</para>
</listitem>
</varlistentry>
</variablelist>
</sect1>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Proposal: recent access based routing for primary-replica setups
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox