public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bo Peng <[email protected]>
To: [email protected] <[email protected]>
Subject: Proposal: make the online recovery database configurable
Date: Fri, 27 Jun 2025 00:00:00 +0000
Message-ID: <TYWP286MB2633AE885026741B927F84BBF27AA@TYWP286MB2633.JPNP286.PROD.OUTLOOK.COM> (raw)
Currently, the online recovery database is hardcoded to "template1".
It would be helpful in some use cases to make this configurable.
The attached patch adds a new configuration parameter, "recovery_database",
which allows the online recovery database to be configured.
The default is set to "template1" for backward compatibility.
Any suggestions on changing the default value to "postgres"?
---
Bo Peng <[email protected]>
SRA OSS K.K.
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/
Attachments:
[application/octet-stream] recovery_database_v1.patch (20.7K, 2-recovery_database_v1.patch)
download | inline diff:
diff --git a/doc.ja/src/sgml/example-cluster.sgml b/doc.ja/src/sgml/example-cluster.sgml
index 809f1f050..ef92431e9 100644
--- a/doc.ja/src/sgml/example-cluster.sgml
+++ b/doc.ja/src/sgml/example-cluster.sgml
@@ -813,7 +813,7 @@ PCP_USER=pgpool
<sect3 id="example-cluster-pgpool-config-online-recovery">
<title>オンラインリカバリの設定</title>
<para>
- 続いて、オンラインリカバリを実行する<productname>PostgreSQL</productname>ユーザ及びオンラインリカバリ時に呼び出されるスクリプトを設定します。
+ 続いて、オンラインリカバリ（<xref linkend="runtime-online-recovery">）を実行する<productname>PostgreSQL</productname>ユーザ及びオンラインリカバリ時に呼び出されるスクリプトを設定します。
オンラインリカバリで実行される<function>pgpool_recovery</function>関数は
<productname>PostgreSQL</productname>のスーパーユーザ権限が必要なため、<varname>recovery_user</varname>に<emphasis>スーパーユーザ</emphasis>を指定しなければなりません。
ここでは、<literal>postgres</literal>ユーザを指定します。
@@ -852,9 +852,9 @@ PGHOME=/usr/pgsql-17
</programlisting>
<para>
- また、オンラインリカバリ機能を使用するには、<function>pgpool_recovery</function>、
- <function>pgpool_remote_start</function>、<function>pgpool_switch_xlog</function>という関数が必要になるので、
- <literal>server1</literal>のtemplate1に<function>pgpool_recovery</function>をインストールしておきます。
+ また、オンラインリカバリ機能を使用するには、<function>pgpool_recovery</function>、<function>pgpool_remote_start</function>、<function>pgpool_switch_xlog</function>の各関数をあらかじめ<xref linkend="guc-recovery-database">で指定されたデータベース（デフォルトは<literal>'template1'</literal>です）に作成しておく必要があります。
+ <literal>server1</literal>上で以下のコマンドを実行し、これらの関数を<literal>template1</literal>に作成します。
+ <xref linkend="guc-recovery-database">にデフォルト以外の値が設定されている場合は、<literal>template1</literal>をその設定値に置き換えてください。
</para>
<programlisting>
[root@server1 ~]# psql -U postgres template1 -c "CREATE EXTENSION pgpool_recovery"
diff --git a/doc.ja/src/sgml/example-replication-si-mode.sgml b/doc.ja/src/sgml/example-replication-si-mode.sgml
index 4ed4ee3e1..51d007970 100644
--- a/doc.ja/src/sgml/example-replication-si-mode.sgml
+++ b/doc.ja/src/sgml/example-replication-si-mode.sgml
@@ -567,13 +567,12 @@ PGHOME=/usr/pgsql-15
</programlisting>
<para>
- また、オンラインリカバリ機能を使用するには、<function>pgpool_recovery</function>、
- <function>pgpool_remote_start</function>、<function>pgpool_switch_xlog</function>という関数が必要になるので、
- <literal>server1</literal>のtemplate1に<function>pgpool_recovery</function>をインストールしておきます。
+ また、オンラインリカバリ機能を使用するには、<function>pgpool_recovery</function>、<function>pgpool_remote_start</function>、<function>pgpool_switch_xlog</function>の各関数をあらかじめ<xref linkend="guc-recovery-database">で指定されたデータベース（デフォルトは<literal>'template1'</literal>です）に作成しておく必要があります。
+ <literal>server1</literal>上で以下のコマンドを実行し、これらの関数を<literal>template1</literal>に作成します。
+ <xref linkend="guc-recovery-database">にデフォルト以外の値が設定されている場合は、<literal>template1</literal>をその設定値に置き換えてください。
</para>
<programlisting>
-[server1]# su - postgres
-[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
+[server1]# psql -U postgres template1 -c "CREATE EXTENSION pgpool_recovery"
</programlisting>
<note>
<para>
diff --git a/doc.ja/src/sgml/installation.sgml b/doc.ja/src/sgml/installation.sgml
index 439f77ffe..b7282a1c8 100644
--- a/doc.ja/src/sgml/installation.sgml
+++ b/doc.ja/src/sgml/installation.sgml
@@ -526,7 +526,7 @@ make install
It is enough, if these function installed in template1 first. These
function do not needed that install in all databases.
-->
- 後述の オンラインリカバリ の機能を使う場合には、 pgpool_recovery, pgpool_remote_start, pgpool_switch_xlog という関数が必要です。
+ 後述のオンラインリカバリの機能を使う場合には、 <function>pgpool_recovery</function>、<function>pgpool_remote_start</function>、および<function>pgpool_switch_xlog</function>という関数が必要です。
また管理ツールである pgpoolAdmin の画面上から、バックエンドノードの PostgreSQL を停止・再起動・設定再読み込みを行なうことができますが、これには pgpool_pgctl という関数が使われます。
これらの関数は、すべてのデータベースにインストールされている必要はなく、template1 にだけで 構いません。
</para>
@@ -543,9 +543,17 @@ $ make
$ make install
</programlisting>
<!--
- After this:
+ After this, execute the following command to create the functions
+ <function>pgpool_recovery</function>, <function>pgpool_remote_start</function>
+ and <function>pgpool_switch_xlog</function> in the database specified in
+ <xref linkend="guc-recovery-database">
+ (The default is <literal>'template1'</literal>).
+ If a value other than the default is configured for
+ <xref linkend="guc-recovery-database">, replace <literal>"template1"</literal>
+ with the setting of <xref linkend="guc-recovery-database">.
-->
- この後に以下か、
+ この後、以下のコマンドを実行して、<xref linkend="guc-recovery-database">で指定されたデータベースに（デフォルトは<literal>'template1'</literal>です）<function>pgpool_recovery</function>、<function>pgpool_remote_start</function>および<function>pgpool_switch_xlog</function>関数を作成します。
+ <xref linkend="guc-recovery-database">にデフォルト以外の値が設定されている場合は、<literal>"template1"</literal>をその設定値に置き換えてください。
<programlisting>
$ psql template1
=# CREATE EXTENSION pgpool_recovery;
diff --git a/doc.ja/src/sgml/online-recovery.sgml b/doc.ja/src/sgml/online-recovery.sgml
index aa0617b1b..a391c7471 100644
--- a/doc.ja/src/sgml/online-recovery.sgml
+++ b/doc.ja/src/sgml/online-recovery.sgml
@@ -346,6 +346,58 @@
</listitem>
</varlistentry>
+ <varlistentry id="guc-recovery-database" xreflabel="recovery_database">
+ <term><varname>recovery_database</varname> (<type>string</type>)
+ <indexterm>
+ <!--
+ <primary><varname>recovery_database</varname> configuration parameter</primary>
+ -->
+ <primary><varname>recovery_database</varname> 設定パラメータ</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ <!--
+ Specifies the PostgreSQL database name to be used for online recovery.
+ The default is <literal>'template1'</literal>. If not specified,
+ <productname>Pgpool-II</productname> will attempt to use the
+ <literal>"postgres"</literal> database.
+ -->
+ オンラインリカバリを行うための<productname>PostgreSQL</>データベースです。
+ デフォルトは<literal>'template1'</literal>です。
+ 空文字（''）が指定された場合、<productname>Pgpool-II</>は
+ <literal>「postgres」</literal>データベースの使用を試みます。
+ </para>
+ <para>
+ <!--
+ This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
+ -->
+ このパラメータは<productname>Pgpool-II</>の設定を再読み込みすることで変更可能です。
+ </para>
+ <note>
+ <para>
+ <!--
+ To perform online recovery, the functions <varname>pgpool_recovery</varname>,
+ <varname>pgpool_remote_start</varname> and
+ <varname>pgpool_switch_xlog</varname> must be created in the database
+ specified by the <xref linkend="guc-recovery-database"> parameter.
+ To install these functions in the default database
+ <literal>"template1"</literal>, execute the following command
+ (<emphasis>if a value other than the default is configured, replace
+ <literal>"template1"</literal> with the setting of
+ <varname>recovery_database</varname></emphasis>):
+ -->
+ オンラインリカバリを実行するには、<xref linkend="guc-recovery-database">パラメータで指定されたデータベースに、pgpool_recovery、pgpool_remote_start、および pgpool_switch_xlog 関数を作成しておく必要があります。
+ これらの関数をデフォルトのデータベース<literal>template1</literal>に作成するには、以下のコマンドを実行してください。
+ （デフォルト値以外が設定されている場合は、<literal>template1</literal>を<xref linkend="guc-recovery-database">に設定された値に置き換えてください）
+ <programlisting>
+$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
+ </programlisting>
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-recovery-1st-stage-command" xreflabel="recovery_1st_stage_command">
<term><varname>recovery_1st_stage_command</varname> (<type>string</type>)
<indexterm>
diff --git a/doc/src/sgml/example-cluster.sgml b/doc/src/sgml/example-cluster.sgml
index d5a4150ea..71f3f474e 100644
--- a/doc/src/sgml/example-cluster.sgml
+++ b/doc/src/sgml/example-cluster.sgml
@@ -866,7 +866,8 @@ PCP_USER=pgpool
<sect3 id="example-cluster-pgpool-config-online-recovery">
<title>Pgpool-II Online Recovery Configurations</title>
<para>
- Next, configure the required parameters to perform online recovery.
+ Next, configure the required parameters to perform online recovery
+ (<xref linkend="runtime-online-recovery">).
Because <emphasis>Superuser</emphasis> privilege in <productname>PostgreSQL</productname>
is required for performing online recovery, we specify <literal>postgres</literal>
user in <xref linkend="GUC-RECOVERY-USER">. In this example, we leave
@@ -909,11 +910,16 @@ PGHOME=/usr/pgsql-17
</programlisting>
<para>
- In order to use the online recovery functionality, the functions of
- <function>pgpool_recovery</function>, <function>pgpool_remote_start</function>,
- <function>pgpool_switch_xlog</function> are required, so we need to install
- <function>pgpool_recovery</function> on template1 of <productname>PostgreSQL</productname> server
- <literal>server1</literal>.
+ In addition, to perform online recovery, the functions
+ <function>pgpool_recovery</function>, <function>pgpool_remote_start</function>
+ and <function>pgpool_switch_xlog</function> must be created in advance
+ in the database specified by <xref linkend="guc-recovery-database">
+ (the default is <literal>'template1'</literal>).
+ Execute the following command to create these functions in
+ <literal>template1</literal> on <literal>server1</literal>.
+ If a value other than the default is configured for
+ <xref linkend="guc-recovery-database">, replace <literal>template1</literal>
+ with the configured value.
</para>
<programlisting>
[root@server1 ~]# psql -U postgres template1 -c "CREATE EXTENSION pgpool_recovery"
diff --git a/doc/src/sgml/example-replication-si-mode.sgml b/doc/src/sgml/example-replication-si-mode.sgml
index e06b91e77..395c7c23c 100644
--- a/doc/src/sgml/example-replication-si-mode.sgml
+++ b/doc/src/sgml/example-replication-si-mode.sgml
@@ -559,15 +559,19 @@ PGHOME=/usr/pgsql-14
</programlisting>
<para>
- In order to use the online recovery functionality, the functions of
- <function>pgpool_recovery</function>, <function>pgpool_remote_start</function>,
- <function>pgpool_switch_xlog</function> are required, so we need to install
- <function>pgpool_recovery</function> on template1 of <productname>PostgreSQL</productname> server
- <literal>server1</literal>.
+ In addition, to perform online recovery, the functions
+ <function>pgpool_recovery</function>, <function>pgpool_remote_start</function>
+ and <function>pgpool_switch_xlog</function> must be created in advance
+ in the database specified by <xref linkend="guc-recovery-database">
+ (the default is <literal>'template1'</literal>).
+ Execute the following command to create these functions in
+ <literal>template1</literal> on <literal>server1</literal>.
+ If a value other than the default is configured for
+ <xref linkend="guc-recovery-database">, replace <literal>template1</literal>
+ with the configured value.
</para>
<programlisting>
-[server1]# su - postgres
-[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
+[server1]# psql -U postgres template1 -c "CREATE EXTENSION pgpool_recovery"
</programlisting>
<note>
<para>
diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml
index aae7de632..422ee8c63 100644
--- a/doc/src/sgml/installation.sgml
+++ b/doc/src/sgml/installation.sgml
@@ -455,7 +455,14 @@ $ cd pgpool-II-&version/src/sql/pgpool-recovery
$ make
$ make install
</programlisting>
- After this:
+ After this, execute the following command to create the functions
+ <function>pgpool_recovery</function>, <function>pgpool_remote_start</function>
+ and <function>pgpool_switch_xlog</function> in the database specified in
+ <xref linkend="guc-recovery-database">
+ (The default is <literal>'template1'</literal>).
+ If a value other than the default is configured for
+ <xref linkend="guc-recovery-database">, replace <literal>"template1"</literal>
+ with the setting of <xref linkend="guc-recovery-database">.
<programlisting>
$ psql template1
=# CREATE EXTENSION pgpool_recovery;
diff --git a/doc/src/sgml/online-recovery.sgml b/doc/src/sgml/online-recovery.sgml
index 55092a744..6207ed230 100644
--- a/doc/src/sgml/online-recovery.sgml
+++ b/doc/src/sgml/online-recovery.sgml
@@ -248,6 +248,41 @@
</listitem>
</varlistentry>
+ <varlistentry id="guc-recovery-database" xreflabel="recovery_database">
+ <term><varname>recovery_database</varname> (<type>string</type>)
+ <indexterm>
+ <primary><varname>recovery_database</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the PostgreSQL database name to be used for online recovery.
+ The default is <literal>'template1'</literal>. If an empty string ('')
+ is specified, <productname>Pgpool-II</productname> will attempt to use
+ the <literal>"postgres"</literal> database.
+ </para>
+ <para>
+ This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
+ </para>
+ <note>
+ <para>
+ To perform online recovery, the functions <varname>pgpool_recovery</varname>,
+ <varname>pgpool_remote_start</varname> and
+ <varname>pgpool_switch_xlog</varname> must be created in the database
+ specified by the <xref linkend="guc-recovery-database"> parameter.
+ To install these functions in the default database
+ <literal>"template1"</literal>, execute the following command
+ (<emphasis>if a value other than the default is configured, replace
+ <literal>"template1"</literal> with the setting of
+ <varname>recovery_database</varname></emphasis>):
+ <programlisting>
+$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
+ </programlisting>
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-recovery-1st-stage-command" xreflabel="recovery_1st_stage_command">
<term><varname>recovery_1st_stage_command</varname> (<type>string</type>)
<indexterm>
diff --git a/src/config/pool_config_variables.c b/src/config/pool_config_variables.c
index 5e61f02db..734cfc7c9 100644
--- a/src/config/pool_config_variables.c
+++ b/src/config/pool_config_variables.c
@@ -1027,6 +1027,16 @@ static struct config_string ConfigureNamesString[] =
NULL, NULL, NULL, NULL
},
+ {
+ {"recovery_database", CFGCXT_RELOAD, RECOVERY_CONFIG,
+ "The database name for online recovery.",
+ CONFIG_VAR_TYPE_STRING, false, 0
+ },
+ &g_pool_config.recovery_database,
+ "template1",
+ NULL, NULL, NULL, NULL
+ },
+
{
{"recovery_1st_stage_command", CFGCXT_RELOAD, RECOVERY_CONFIG,
"Command to execute in first stage recovery.",
diff --git a/src/include/pool_config.h b/src/include/pool_config.h
index 96b15af54..4dece394e 100644
--- a/src/include/pool_config.h
+++ b/src/include/pool_config.h
@@ -184,8 +184,7 @@ typedef struct
int health_check_period; /* health check period */
char *health_check_user; /* PostgreSQL user name for health check */
char *health_check_password; /* password for health check username */
- char *health_check_database; /* database name for health check
- * username */
+ char *health_check_database; /* database name for health check */
int health_check_max_retries; /* health check max retries */
int health_check_retry_delay; /* amount of time to wait between
* retries */
@@ -352,8 +351,7 @@ typedef struct
int health_check_period; /* health check period */
char *health_check_user; /* PostgreSQL user name for health check */
char *health_check_password; /* password for health check username */
- char *health_check_database; /* database name for health check
- * username */
+ char *health_check_database; /* database name for health check */
int health_check_max_retries; /* health check max retries */
int health_check_retry_delay; /* amount of time to wait between
* retries */
@@ -386,6 +384,7 @@ typedef struct
char *recovery_user; /* PostgreSQL user name for online recovery */
char *recovery_password; /* PostgreSQL user password for online
* recovery */
+ char *recovery_database; /* Database name for recovery */
char *recovery_1st_stage_command; /* Online recovery command in 1st
* stage */
char *recovery_2nd_stage_command; /* Online recovery command in 2nd
diff --git a/src/pcp_con/recovery.c b/src/pcp_con/recovery.c
index a104b4c06..f2381d04e 100644
--- a/src/pcp_con/recovery.c
+++ b/src/pcp_con/recovery.c
@@ -463,16 +463,25 @@ connect_backend_libpq(BackendInfo * backend)
{
char port_str[16];
PGconn *conn;
+ char *dbname;
char *password = get_pgpool_config_user_password(pool_config->recovery_user,
pool_config->recovery_password);
snprintf(port_str, sizeof(port_str),
"%d", backend->backend_port);
+ /*
+ * If database is not specified, "postgres" database is assumed.
+ */
+ if (*pool_config->recovery_database == '\0')
+ dbname = "postgres";
+ else
+ dbname = pool_config->recovery_database;
+
conn = PQsetdbLogin(backend->backend_hostname,
port_str,
NULL,
NULL,
- "template1",
+ pool_config->recovery_database,
pool_config->recovery_user,
password ? password : "");
diff --git a/src/sample/pgpool.conf.sample-stream b/src/sample/pgpool.conf.sample-stream
index 217e9d055..bfc05cb10 100644
--- a/src/sample/pgpool.conf.sample-stream
+++ b/src/sample/pgpool.conf.sample-stream
@@ -666,6 +666,9 @@ backend_clustering_mode = 'streaming_replication'
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
+#recovery_database = 'template1'
+ # Database name for online recovery
+
#recovery_1st_stage_command = ''
# Executes a command in first stage
#recovery_2nd_stage_command = ''
diff --git a/src/utils/pool_process_reporting.c b/src/utils/pool_process_reporting.c
index 71f871bc4..506265d29 100644
--- a/src/utils/pool_process_reporting.c
+++ b/src/utils/pool_process_reporting.c
@@ -774,6 +774,11 @@ get_config(int *nrows)
StrNCpy(status[i].desc, "online recovery user", POOLCONFIG_MAXDESCLEN);
i++;
+ StrNCpy(status[i].name, "recovery_database", POOLCONFIG_MAXNAMELEN);
+ snprintf(status[i].value, POOLCONFIG_MAXVALLEN, "%s", pool_config->recovery_database);
+ StrNCpy(status[i].desc, "database name for online recovery", POOLCONFIG_MAXDESCLEN);
+ i++;
+
StrNCpy(status[i].name, "recovery_1st_stage_command", POOLCONFIG_MAXNAMELEN);
snprintf(status[i].value, POOLCONFIG_MAXVALLEN, "%s", pool_config->recovery_1st_stage_command);
StrNCpy(status[i].desc, "execute a command in first stage.", POOLCONFIG_MAXDESCLEN);
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]
Subject: Re: Proposal: make the online recovery database configurable
In-Reply-To: <TYWP286MB2633AE885026741B927F84BBF27AA@TYWP286MB2633.JPNP286.PROD.OUTLOOK.COM>
* 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