From 3cc3d2d848a9e0c0646961ab4fa7904fafac2d5f Mon Sep 17 00:00:00 2001 From: Bo Peng Date: Thu, 17 Jul 2025 14:31:02 +0900 Subject: [PATCH] Feature: Make online recovery database configurable Prior to version 4.6, the online recovery database was hardcoded to "template1". This commit introduces a new configuration parameter, "recovery_database", which allows users to specify the database used for online recovery. The default value is "postgres". --- doc.ja/src/sgml/example-cluster.sgml | 10 ++-- .../src/sgml/example-replication-si-mode.sgml | 9 ++-- doc.ja/src/sgml/installation.sgml | 21 +++++--- doc.ja/src/sgml/online-recovery.sgml | 52 +++++++++++++++++++ doc/src/sgml/example-cluster.sgml | 20 ++++--- doc/src/sgml/example-replication-si-mode.sgml | 18 ++++--- doc/src/sgml/installation.sgml | 15 ++++-- doc/src/sgml/online-recovery.sgml | 35 +++++++++++++ src/config/pool_config_variables.c | 10 ++++ src/include/pool_config.h | 7 ++- src/pcp_con/recovery.c | 11 +++- src/sample/pgpool.conf.sample-stream | 3 ++ src/test/pgpool_setup.in | 1 + src/test/watchdog_setup.in | 1 + src/utils/pool_process_reporting.c | 5 ++ 15 files changed, 176 insertions(+), 42 deletions(-) diff --git a/doc.ja/src/sgml/example-cluster.sgml b/doc.ja/src/sgml/example-cluster.sgml index a9fa53135..2950c59c5 100644 --- a/doc.ja/src/sgml/example-cluster.sgml +++ b/doc.ja/src/sgml/example-cluster.sgml @@ -813,7 +813,7 @@ PCP_USER=pgpool オンラインリカバリの設定 - 続いて、オンラインリカバリを実行するPostgreSQLユーザ及びオンラインリカバリ時に呼び出されるスクリプトを設定します。 + 続いて、オンラインリカバリ（）を実行するPostgreSQLユーザ及びオンラインリカバリ時に呼び出されるスクリプトを設定します。 オンラインリカバリで実行されるpgpool_recovery関数は PostgreSQLのスーパーユーザ権限が必要なため、recovery_userにスーパーユーザを指定しなければなりません。 ここでは、postgresユーザを指定します。 @@ -852,12 +852,12 @@ PGHOME=/usr/pgsql-17 - また、オンラインリカバリ機能を使用するには、pgpool_recovery、 - pgpool_remote_start、pgpool_switch_xlogという関数が必要になるので、 - server1のtemplate1にpgpool_recoveryをインストールしておきます。 + また、オンラインリカバリ機能を使用するには、pgpool_recovery、pgpool_remote_start、pgpool_switch_xlogの各関数をあらかじめで指定されたデータベース（デフォルトは'postgres'です）に作成しておく必要があります。 + server1上で以下のコマンドを実行し、これらの関数をpostgresに作成します。 + にデフォルト以外の値が設定されている場合は、postgresをその設定値に置き換えてください。 -[root@server1 ~]# psql -U postgres template1 -c "CREATE EXTENSION pgpool_recovery" +[root@server1 ~]# psql -U postgres postgres -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 605a44278..2915ccbb8 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 - また、オンラインリカバリ機能を使用するには、pgpool_recovery、 - pgpool_remote_start、pgpool_switch_xlogという関数が必要になるので、 - server1のtemplate1にpgpool_recoveryをインストールしておきます。 + また、オンラインリカバリ機能を使用するには、pgpool_recovery、pgpool_remote_start、pgpool_switch_xlogの各関数をあらかじめで指定されたデータベース（デフォルトは'postgres'です）に作成しておく必要があります。 + server1上で以下のコマンドを実行し、これらの関数をpostgresに作成します。 + にデフォルト以外の値が設定されている場合は、postgresをその設定値に置き換えてください。 -[server1]# su - postgres -[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery" +[server1]# psql -U postgres postgres -c "CREATE EXTENSION pgpool_recovery" diff --git a/doc.ja/src/sgml/installation.sgml b/doc.ja/src/sgml/installation.sgml index 439f77ffe..bc73e82ca 100644 --- a/doc.ja/src/sgml/installation.sgml +++ b/doc.ja/src/sgml/installation.sgml @@ -523,12 +523,9 @@ make install when you use the online recovery that describes latter. Also pgpoolAdmin of management tool, stop, restart or reload a PostgreSQL on the screen by use pgpool_pgctl. - 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 という関数が必要です。 + 後述のオンラインリカバリの機能を使う場合には、 pgpool_recovery、pgpool_remote_start、およびpgpool_switch_xlogという関数が必要です。 また管理ツールである pgpoolAdmin の画面上から、バックエンドノードの PostgreSQL を停止・再起動・設定再読み込みを行なうことができますが、これには pgpool_pgctl という関数が使われます。 - これらの関数は、すべてのデータベースにインストールされている必要はなく、template1 にだけで 構いません。 @@ -543,11 +540,19 @@ $ make $ make install - この後に以下か、 + この後、以下のコマンドを実行して、で指定されたデータベースに（デフォルトは'postgres'です）pgpool_recovery、pgpool_remote_startおよびpgpool_switch_xlog関数を作成します。 + にデフォルト以外の値が設定されている場合は、"postgres"をその設定値に置き換えてください。 -$ psql template1 +$ psql postgres =# CREATE EXTENSION pgpool_recovery; @@ -556,7 +561,7 @@ $ psql template1 --> または -$ psql -f pgpool-recovery.sql template1 +$ psql -f pgpool-recovery.sql postgres を実行します。 diff --git a/doc.ja/src/sgml/online-recovery.sgml b/doc.ja/src/sgml/online-recovery.sgml index aa0617b1b..8b895dad0 100644 --- a/doc.ja/src/sgml/online-recovery.sgml +++ b/doc.ja/src/sgml/online-recovery.sgml @@ -346,6 +346,58 @@ + + recovery_database (string) + + + recovery_database 設定パラメータ + + + + + + オンラインリカバリを行うためのPostgreSQLデータベースです。 + デフォルトは'postgres'です。 + 空文字（''）が指定された場合、Pgpool-IIは + 「postgres」データベースの使用を試みます。 + + + + このパラメータはPgpool-IIの設定を再読み込みすることで変更可能です。 + + + + + オンラインリカバリを実行するには、パラメータで指定されたデータベースに、pgpool_recovery、pgpool_remote_start、および pgpool_switch_xlog 関数を作成しておく必要があります。 + これらの関数をデフォルトのデータベースpostgresに作成するには、以下のコマンドを実行してください。 + （デフォルト値以外が設定されている場合は、postgresをに設定された値に置き換えてください） + +$ psql postgres -c "CREATE EXTENSION pgpool_recovery" + + + + + + recovery_1st_stage_command (string) diff --git a/doc/src/sgml/example-cluster.sgml b/doc/src/sgml/example-cluster.sgml index 91b86491e..1f9835e0c 100644 --- a/doc/src/sgml/example-cluster.sgml +++ b/doc/src/sgml/example-cluster.sgml @@ -866,7 +866,8 @@ PCP_USER=pgpool Pgpool-II Online Recovery Configurations - Next, configure the required parameters to perform online recovery. + Next, configure the required parameters to perform online recovery + (). Because Superuser privilege in PostgreSQL is required for performing online recovery, we specify postgres user in . In this example, we leave @@ -909,14 +910,19 @@ PGHOME=/usr/pgsql-17 - In order to use the online recovery functionality, the functions of - pgpool_recovery, pgpool_remote_start, - pgpool_switch_xlog are required, so we need to install - pgpool_recovery on template1 of PostgreSQL server - server1. + In addition, to perform online recovery, the functions + pgpool_recovery, pgpool_remote_start + and pgpool_switch_xlog must be created in advance + in the database specified by + (the default is 'postgres'). + Execute the following command to create these functions in + postgres on server1. + If a value other than the default is configured for + , replace postgres + with the configured value. -[root@server1 ~]# psql -U postgres template1 -c "CREATE EXTENSION pgpool_recovery" +[root@server1 ~]# psql -U postgres postgres -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 e4280837a..1100a754e 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 - In order to use the online recovery functionality, the functions of - pgpool_recovery, pgpool_remote_start, - pgpool_switch_xlog are required, so we need to install - pgpool_recovery on template1 of PostgreSQL server - server1. + In addition, to perform online recovery, the functions + pgpool_recovery, pgpool_remote_start + and pgpool_switch_xlog must be created in advance + in the database specified by + (the default is 'postgres'). + Execute the following command to create these functions in + postgres on server1. + If a value other than the default is configured for + , replace postgres + with the configured value. -[server1]# su - postgres -[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery" +[server1]# psql -U postgres postgres -c "CREATE EXTENSION pgpool_recovery" diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml index aae7de632..893c9c0e3 100644 --- a/doc/src/sgml/installation.sgml +++ b/doc/src/sgml/installation.sgml @@ -443,8 +443,6 @@ make install when you use the online recovery that describes latter. Also pgpoolAdmin of management tool, stop, restart or reload a PostgreSQL on the screen by use pgpool_pgctl. - It is enough, if these function installed in template1 first. These - function do not needed that install in all databases. @@ -455,15 +453,22 @@ $ cd pgpool-II-&version/src/sql/pgpool-recovery $ make $ make install - After this: + After this, execute the following command to create the functions + pgpool_recovery, pgpool_remote_start + and pgpool_switch_xlog in the database specified in + + (The default is 'postgres'). + If a value other than the default is configured for + , replace "postgres" + with the setting of . -$ psql template1 +$ psql postgres =# CREATE EXTENSION pgpool_recovery; or -$ psql -f pgpool-recovery.sql template1 +$ psql -f pgpool-recovery.sql postgres diff --git a/doc/src/sgml/online-recovery.sgml b/doc/src/sgml/online-recovery.sgml index 55092a744..e8ee40528 100644 --- a/doc/src/sgml/online-recovery.sgml +++ b/doc/src/sgml/online-recovery.sgml @@ -248,6 +248,41 @@ + + recovery_database (string) + + recovery_database configuration parameter + + + + + Specifies the PostgreSQL database name to be used for online recovery. + The default is 'postgres'. If an empty string ('') + is specified, Pgpool-II will attempt to use + the "postgres" database. + + + This parameter can be changed by reloading the Pgpool-II configurations. + + + + To perform online recovery, the functions pgpool_recovery, + pgpool_remote_start and + pgpool_switch_xlog must be created in the database + specified by the parameter. + To install these functions in the default database + "postgres", execute the following command + (if a value other than the default is configured, replace + "postgres" with the setting of + recovery_database): + +$ psql postgres -c "CREATE EXTENSION pgpool_recovery" + + + + + + recovery_1st_stage_command (string) diff --git a/src/config/pool_config_variables.c b/src/config/pool_config_variables.c index 5e61f02db..83ce0c2bf 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, + "postgres", + 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 598c1a28d..cc4dd5e14 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 301289c92..bec929dcd 100644 --- a/src/sample/pgpool.conf.sample-stream +++ b/src/sample/pgpool.conf.sample-stream @@ -667,6 +667,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 = 'postgres' + # Database name for online recovery + #recovery_1st_stage_command = '' # Executes a command in first stage #recovery_2nd_stage_command = '' diff --git a/src/test/pgpool_setup.in b/src/test/pgpool_setup.in index 4eff7dd73..926b8f8ac 100644 --- a/src/test/pgpool_setup.in +++ b/src/test/pgpool_setup.in @@ -881,6 +881,7 @@ function set_pgpool_conf { echo "sr_check_password = ''" >> $CONF echo "recovery_user = '$WHOAMI'" >> $CONF echo "recovery_password = ''" >> $CONF + echo "recovery_database = 'template1'" >> $CONF echo "recovery_1st_stage_command = 'basebackup.sh'" >> $CONF if [ $MODE = "r" -o $MODE = "i" ];then diff --git a/src/test/watchdog_setup.in b/src/test/watchdog_setup.in index 42ba2f752..d84a5b619 100644 --- a/src/test/watchdog_setup.in +++ b/src/test/watchdog_setup.in @@ -107,6 +107,7 @@ function set_pgpool_conf { echo "sr_check_user = '$WHOAMI'" >> $CONF echo "recovery_user = '$WHOAMI'" >> $CONF echo "recovery_password = ''" >> $CONF + echo "recovery_database = 'template1'" >> $CONF echo "recovery_1st_stage_command = 'basebackup.sh'" >> $CONF if [ $MODE = "r" || $MODE = "i" ];then diff --git a/src/utils/pool_process_reporting.c b/src/utils/pool_process_reporting.c index 01761c73e..ce4117a4b 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); -- 2.39.5