pgpool2.git / summary / log / commit / refs
commit a8d1b96c6010a372b4eef488848e3563f771e90b
Author: Bo Peng <pengbo@sraoss.co.jp>
Date: Tue Sep 22 13:08:18 2020 +0000
Doc: update "Pgpool-II + Watchdog Setup Example" and sample scripts.
doc.ja/src/sgml/example-cluster.sgml | 623 ++++++++++++------------
doc/src/sgml/example-cluster.sgml | 668 +++++++++++++-------------
src/sample/scripts/failover.sh.sample | 21 +-
src/sample/scripts/follow_master.sh.sample | 171 -------
src/sample/scripts/follow_primary.sh.sample | 22 +-
src/sample/scripts/pgpool_remote_start.sample | 12 +-
src/sample/scripts/recovery_1st_stage.sample | 12 +-
7 files changed, 659 insertions(+), 870 deletions(-)
diff --git a/doc.ja/src/sgml/example-cluster.sgml b/doc.ja/src/sgml/example-cluster.sgml
index b3e811782..b46cc4212 100644
--- a/doc.ja/src/sgml/example-cluster.sgml
+++ b/doc.ja/src/sgml/example-cluster.sgml
@@ -6,7 +6,7 @@
ここでは、ストリーミングレプリケーション構成の<productname>PostgreSQL</productname>を<productname>Pgpool-II</productname>で管理するシステムの構成例を示します。この例では、3台の<productname>Pgpool-II</productname>を使って<productname>PostgreSQL</productname>を管理し、単一障害点やスプリットブレインの起きない堅牢なクラスタを運用することが可能です。
</para>
<para>
- この設定例では<productname>PostgreSQL</productname> 11を使っていますが、各種スクリプトは<productname>PostgreSQL</productname> 12でも動作確認を行っています。
+ この設定例では<productname>PostgreSQL</productname> 13を使っていますが、各種スクリプトは<productname>PostgreSQL</productname> 9.5以降での動作確認を行っています。
</para>
<sect2 id="example-cluster-requirement">
<title>前提条件</title>
@@ -77,7 +77,7 @@
<tbody>
<row>
<entry>PostgreSQLバージョン</entry>
- <entry>11.1</entry>
+ <entry>13.0</entry>
<entry>-</entry>
</row>
<row>
@@ -87,7 +87,7 @@
</row>
<row>
<entry>$PGDATA</entry>
- <entry>/var/lib/pgsql/11/data</entry>
+ <entry>/var/lib/pgsql/13/data</entry>
<entry>-</entry>
</row>
<row>
@@ -123,7 +123,7 @@
<tbody>
<row>
<entry>Pgpool-IIバージョン</entry>
- <entry>4.1</entry>
+ <entry>4.2.0</entry>
<entry>-</entry>
</row>
<row>
@@ -171,27 +171,63 @@
</tbody>
</tgroup>
</table>
+
+ <table id="example-cluster-table-sample-scripts">
+ <title>Various sample scripts included in rpm package</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Feature</entry>
+ <entry>Script</entry>
+ <entry>Detail</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry morerows='1'>Failover</entry>
+ <entry><ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/failover.sh.sample;hb=refs/heads/V4_2_STABLE">/etc/pgpool-II/failover.sh.sample</ulink></entry>
+ <entry>Run by <xref linkend="GUC-FAILOVER-COMMAND"> to perform failover</entry>
+ </row>
+ <row>
+ <entry><ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/follow_primary.sh.sample;hb=refs/heads/V4_2_STABLE">/etc/pgpool-II/follow_primary.sh.sample</ulink></entry>
+ <entry>Run by <xref linkend="GUC-FOLLOW-PRIMARY-COMMAND"> to synchronize the Standby with the new Primary after failover.</entry>
+ </row>
+ <row>
+ <entry morerows='1'>Online recovery</entry>
+ <entry><ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/recovery_1st_stage.sample;hb=refs/heads/V4_2_STABLE">/etc/pgpool-II/recovery_1st_stage.sample</ulink></entry>
+ <entry>Run by <xref linkend="GUC-RECOVERY-1ST-STAGE-COMMAND"> to recovery a Standby node</entry>
+ </row>
+ <row>
+ <entry><ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/pgpool_remote_start.sample;hb=refs/heads/V4_2_STABLE">/etc/pgpool-II/pgpool_remote_start.sample</ulink></entry>
+ <entry>Run after <xref linkend="GUC-RECOVERY-1ST-STAGE-COMMAND"> to start the Standby node</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ <para>
+ 上記各種スクリプトはRPMパッケージに同梱されており、必要に応じてカスタマイズできます。
+ </para>
</sect2>
<sect2 id="example-cluster-installation">
<title>インストール</title>
<para>
- すべてのサーバに<productname>PostgreSQL</productname> 11.1と<productname>Pgpool-II</productname> 4.1をRPMからインストールします。
+ すべてのサーバに<productname>PostgreSQL</productname> 13.0と<productname>Pgpool-II</productname> 4.2をRPMからインストールします。
</para>
<para>
<productname>PostgreSQL</productname>のインストールは<productname>PostgreSQL</productname>コミュニティのリポジトリを使います。
</para>
<programlisting>
- # yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
- # yum install postgresql11-server
+ # yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
+ # yum install -y postgresql13-server
</programlisting>
<para>
<productname>Pgpool-II</productname>のインストールは<productname>Pgpool-II</productname>開発コミュニティが提供するYumリポジトリを用いてインストールします。
</para>
<programlisting>
- # yum install http://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm
- # yum install pgpool-II-pg11-*
+ # yum install http://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-release-4.2-1.noarch.rpm
+ # yum install pgpool-II-pg13-*
</programlisting>
</sect2>
@@ -265,7 +301,7 @@
<row>
<entry>pgpool</entry>
<entry>pgpool</entry>
- <entry>Pgpool-IIのレプリケーション遅延チェック、ヘルスチェック専用ユーザ</entry>
+ <entry>Pgpool-IIのレプリケーション遅延チェック(<xref linkend="GUC-HEALTH-CHECK-USER">)、ヘルスチェック専用ユーザ(<xref linkend="GUC-SR-CHECK-USER">)</entry>
</row>
<row>
<entry>postgres</entry>
@@ -365,33 +401,101 @@
</itemizedlist>
</sect2>
+ <sect2 id="example-cluster-pgpool-node-id">
+ <title>pgpool_node_idファイルの作成</title>
+ <para>
+ <productname>Pgpool-II</productname> 4.2以降、すべての設定パラメーターがすべてのホストで同一になりました。
+ <literal>Watchdog</literal>機能が有効になっている場合、どの設定がどのホストであるかを区別するには、
+ <filename>pgpool_node_id</filename>ファイルの設定が必要になります。
+ <filename>pgpool_node_id</filename>ファイルを作成し、そのファイルにpgpool(watchdog)ホストを識別するためのノード番号(0、1、2など)を追加します。
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>server1</literal>
+ </para>
+ <programlisting>
+[server1]# cat /etc/pgpool-II/pgpool_node_id
+0
+ </programlisting>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>server2</literal>
+ </para>
+ <programlisting>
+[server2]# cat /etc/pgpool-II/pgpool_node_id
+1
+ </programlisting>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>server3</literal>
+ </para>
+ <programlisting>
+[server3]# cat /etc/pgpool-II/pgpool_node_id
+2
+ </programlisting>
+ </listitem>
+ </itemizedlist>
+ </sect2>
+
<sect2 id="example-cluster-pgpool-config">
<title><productname>Pgpool-II</productname>の設定</title>
- <sect3 id="example-cluster-pgpool-config-common">
- <title>共通設定</title>
+ <para>
+ <productname>Pgpool-II</productname> 4.2以降、すべての設定パラメーターがすべてのホストで同一になったので、どれか一つのノード上で<filename>pgpool.conf</filename>を編集し、編集した<filename>pgpool.conf</filename>ファイルを他のpgpoolノードにコピーすれば良いです。
+ </para>
+
+ <sect3 id="example-cluster-pgpool-config-config-file">
+ <title>クラスタリングモード</title>
<para>
- 以下の操作は<literal>server1</literal>, <literal>server2</literal>, <literal>server3</literal>での共通の設定です。
+ <productname>Pgpool-II</productname>にはいくつかのクラスタリングモードがあります。
+ クラスタリングモードの設定には<xref linkend="GUC-BACKEND-CLUSTERING-MODE">を使用します。
+ 今回の設定例では、ストリーミングレプリケーションモードを設定します。
</para>
<para>
- RPMからインストールした場合、すべての<productname>Pgpool-II</productname>の設定ファイルは<filename>/etc/pgpool-II</filename>にあります。今回はストリーミングレプリケーションモードのテンプレートとして<filename>pgpool.conf.sample-stream</filename>サンプルファイルを使用します。
+ RPMからインストールした場合、すべての<productname>Pgpool-II</productname>のサンプル設定ファイルは<filename>/etc/pgpool-II</filename>にあります。今回はストリーミングレプリケーションモードのテンプレートとして<filename>pgpool.conf.sample-stream</filename>サンプルファイルを使用します。
</para>
<programlisting>
[全サーバ]# cp -p /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf
</programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-listen-addresses">
+ <title>listen_addresses</title>
<para>
<productname>Pgpool-II</productname>が全てのIPアドレスから接続を受け付けるように、<xref linkend="GUC-LISTEN-ADDRESSES">パラメータに<literal>'*'</literal>を設定します。
</para>
<programlisting>
listen_addresses = '*'
</programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-port">
+ <title>port</title>
+ <para>
+ <productname>Pgpool-II</productname>が接続を受け付けるために監視するポート番号を指定します。
+ </para>
+ <programlisting>
+port = 9999
+ </programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-sr-check">
+ <title>ストリーミングレプリケーションのチェック</title>
<para>
レプリケーションの遅延チェックユーザ<xref linkend="GUC-SR-CHECK-USER">にpgpoolユーザを設定します。
- この設定例では、<xref linkend="GUC-SR-CHECK-PASSWORD">は<filename>pgpool.conf</filename>に指定せず、<xref linkend="GUC-POOL-PASSWD">ファイルに作成します。<productname>Pgpool-II</productname> 4.0から、<xref linkend="GUC-SR-CHECK-PASSWORD">が空白の場合、<productname>Pgpool-II</productname>は空のパスワードを使用する前にまず<xref linkend="GUC-POOL-PASSWD">ファイルから<xref linkend="GUC-SR-CHECK-USER">に指定したユーザのパスワードを取得できるか試みます。
+ この設定例では、<xref linkend="GUC-SR-CHECK-PASSWORD">は<filename>pgpool.conf</filename>に指定せず、<xref linkend="GUC-POOL-PASSWD">ファイルに作成します。作成方法については後述の<xref linkend="example-cluster-pgpool-config-auth">を参照ください。
+ <productname>Pgpool-II</productname> 4.0から、<xref linkend="GUC-SR-CHECK-PASSWORD">が空白の場合、<productname>Pgpool-II</productname>は空のパスワードを使用する前にまず<xref linkend="GUC-POOL-PASSWD">ファイルから<xref linkend="GUC-SR-CHECK-USER">に指定したユーザのパスワードを取得できるか試みます。
</para>
<programlisting>
sr_check_user = 'pgpool'
sr_check_password = ''
</programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-health-check">
+ <title>ヘルスチェック</title>
<para>
自動フェイルオーバのため、ヘルスチェックを有効にします。<xref linkend="GUC-HEALTH-CHECK-PERIOD">のデフォルト値が0で、これはヘルスチェックが無効であることを意味します。
また、ネットワークが不安定な場合には、バックエンドが正常であるにも関わらず、ヘルスチェックに失敗し、フェイルオーバや縮退運転が発生してしまう可能性があります。そのようなヘルスチェックの誤検知を防止するため、ヘルスチェックのリトライ回数を<varname>health_check_max_retries = 3</varname> に設定しておきます。
@@ -409,6 +513,10 @@
health_check_max_retries = 3
</programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-backend-settings">
+ <title>バックエンドの設定</title>
<para>
また、バックエンド情報を前述の<literal>server1</literal>、<literal>server2</literal>及び<literal>server3</literal>の設定に従って設定しておきます。複数バックエンドノードを定義する場合、以下のbackend_*などのパラメータ名の末尾にノードIDを表す数字を付加することで複数のバックエンドを指定することができます。
</para>
@@ -421,7 +529,7 @@
# Port number for backend 0
backend_weight0 = 1
# Weight for backend 0 (only in load balancing mode)
- backend_data_directory0 = '/var/lib/pgsql/11/data'
+ backend_data_directory0 = '/var/lib/pgsql/13/data'
# Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Controls various backend behavior
@@ -429,24 +537,24 @@
backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
- backend_data_directory1 = '/var/lib/pgsql/11/data'
+ backend_data_directory1 = '/var/lib/pgsql/13/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = 'server3'
backend_port2 = 5432
backend_weight2 = 1
- backend_data_directory2 = '/var/lib/pgsql/11/data'
+ backend_data_directory2 = '/var/lib/pgsql/13/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
</programlisting>
<para>
<xref linkend="SQL-SHOW-POOL-NODES">コマンドでレプリケーション状態と同期レプリケーション状態を表示するには、<xref linkend="GUC-BACKEND-APPLICATION-NAME">パラメータを設定する必要があります。ここではそれぞれのホスト名を設定します。(<productname>Pgpool-II</productname> 4.1以降)
<programlisting>
- ...
+ ...
backend_application_name0 = 'server1'
- ...
+ ...
backend_application_name1 = 'server2'
- ...
+ ...
backend_application_name2 = 'server3'
</programlisting>
</para>
@@ -462,8 +570,8 @@
それぞれの実行スクリプトの引数は、それぞれ実行時に<productname>Pgpool-II</productname>によってバックエンドの具体的な情報に置き換えられます。各引数の意味は<xref linkend="GUC-FAILOVER-COMMAND">をご参照ください。
</para>
<programlisting>
- failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
- follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
+failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
+follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
</programlisting>
<note>
<para>
@@ -472,26 +580,53 @@
</para>
</note>
<para>
- サンプルスクリプト<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/failover.sh.sample;hb=refs/heads/V4_1_STABLE">failover.sh</ulink>及び<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/follow_primary.sh.sample;hb=refs/heads/V4_1_STABLE">follow_primary.sh</ulink>は
+ サンプルスクリプト<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/failover.sh.sample;hb=refs/heads/V4_2_STABLE">failover.sh</ulink>及び<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/follow_primary.sh.sample;hb=refs/heads/V4_2_STABLE">follow_primary.sh</ulink>は
<filename>/etc/pgpool-II/</filename>配下にインストールされていますので、これらのファイルをコピーして作成します。
</para>
<programlisting>
- # cp /etc/pgpool-II/failover.sh{.sample,}
- # cp /etc/pgpool-II/follow_primary.sh{.sample,}
+[all servers]# cp -p /etc/pgpool-II/failover.sh{.sample,}
+[all servers]# cp -p /etc/pgpool-II/follow_primary.sh{.sample,}
+[all servers]# chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}
</programlisting>
<para>
基本的には<emphasis>PGHOME</emphasis>を環境に合わせて変更すれば、動作します。
</para>
<programlisting>
- [server1]# vi /etc/pgpool-II/failover.sh
- ...
- PGHOME=/usr/pgsql-11
- ...
-
- [server1]# vi /etc/pgpool-II/follow_primary.sh
- ...
- PGHOME=/usr/pgsql-11
- ...
+[all servers]# vi /etc/pgpool-II/failover.sh
+...
+PGHOME=/usr/pgsql-13
+...
+
+[all servers]# vi /etc/pgpool-II/follow_primary.sh
+...
+PGHOME=/usr/pgsql-13
+...
+ </programlisting>
+ <para>
+ <literal>PCP</literal>コマンドを使用するにはユーザ認証が必要になるので、ユーザ名と<literal>md5</literal>ハッシュに変換されたパスワードを"<literal>username:encrypted password</literal>"の形式で<filename>pcp.conf</filename>ファイルに設定します。
+ </para>
+ <para>
+ <filename>follow_primary.sh</filename>で"pgpool" userが<varname>PCP_USER</varname>に指定している場合、
+ </para>
+ <programlisting>
+# cat /etc/pgpool-II/follow_primary.sh
+...
+PCP_USER=pgpool
+...
+ </programlisting>
+ <para>
+ 以下のコマンドを実行し、ハッシュ化されたpgpoolユーザのパスワードエントリを<filename>/etc/pgpool-II/pcp.conf</filename>に追加します。
+ </para>
+ <programlisting>
+[全サーバ]# echo 'pgpool:'`pg_md5 PCPコマンドパスワード` >> /etc/pgpool-II/pcp.conf
+ </programlisting>
+ <para>
+ 前述の<filename>follow_primary.sh</filename>のスクリプトでパスワード入力なしで<literal>PCP</literal>コマンドを実行できるように、すべてのサーバで<productname>Pgpool-II</productname>の起動ユーザのホームディレクトリに<filename>.pcppass</filename>を作成します。
+ </para>
+ <programlisting>
+[全サーバ]# su - postgres
+[全サーバ]$ echo 'localhost:9898:pgpool:<pgpool user's password>' > ~/.pcppass
+[全サーバ]$ chmod 600 ~/.pcppass
</programlisting>
</sect3>
@@ -502,44 +637,44 @@
オンラインリカバリで実行される<function>pgpool_recovery</function>関数は<productname>PostgreSQL</productname>のスーパーユーザ権限が必要なため、<varname>recovery_user</varname>に<emphasis>スーパーユーザ</emphasis>を指定しなければなりません。ここでは、postrgesユーザを指定します。
</para>
<programlisting>
- recovery_user = 'postgres'
- # Online recovery user
- recovery_password = ''
- # Online recovery password
+recovery_user = 'postgres'
+ # Online recovery user
+recovery_password = ''
+ # Online recovery password
- recovery_1st_stage_command = 'recovery_1st_stage'
+recovery_1st_stage_command = 'recovery_1st_stage'
</programlisting>
<para>
- オンラインリカバリ用のサンプルスクリプト<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/recovery_1st_stage.sample;hb=refs/heads/V4_1_STABLE">recovery_1st_stage</ulink>
- 及び<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/pgpool_remote_start.sample;hb=refs/heads/V4_1_STABLE">pgpool_remote_start</ulink>は
+ オンラインリカバリ用のサンプルスクリプト<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/recovery_1st_stage.sample;hb=refs/heads/V4_2_STABLE">recovery_1st_stage</ulink>
+ 及び<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/pgpool_remote_start.sample;hb=refs/heads/V4_2_STABLE">pgpool_remote_start</ulink>は
<filename>/etc/pgpool-II/</filename>配下にインストールされていますので、これらのファイルをプライマリサーバ(server1)のデータベースクラスタ配下に配置します。
</para>
<programlisting>
- [server1]# cp /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/11/data/
- [server1]# cp /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/11/data/
- [server1]# chown postgres:postgres /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start}
+[server1]# cp -p /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/13/data/recovery_1st_stage
+[server1]# cp -p /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/13/data/pgpool_remote_start
+[server1]# chown postgres:postgres /var/lib/pgsql/13/data/{recovery_1st_stage,pgpool_remote_start}
</programlisting>
<para>
基本的には<emphasis>PGHOME</emphasis>を環境に合わせて変更すれば、動作します。
</para>
<programlisting>
- [server1]# vi /var/lib/pgsql/11/data/recovery_1st_stage
- ...
- PGHOME=/usr/pgsql-11
- ...
-
- [server1]# vi /var/lib/pgsql/11/data/pgpool_remote_start
- ...
- PGHOME=/usr/pgsql-11
- ...
+[server1]# vi /var/lib/pgsql/13/data/recovery_1st_stage
+...
+PGHOME=/usr/pgsql-13
+...
+
+[server1]# vi /var/lib/pgsql/13/data/pgpool_remote_start
+...
+PGHOME=/usr/pgsql-13
+...
</programlisting>
<para>
また、オンラインリカバリ機能を使用するには、<function>pgpool_recovery</function>、<function>pgpool_remote_start</function>、<function>pgpool_switch_xlog</function>という関数が必要になるので、<literal>server1</literal>のtemplate1に<function>pgpool_recovery</function>をインストールしておきます。
</para>
<programlisting>
- [server1]# su - postgres
- [server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
+[server1]# su - postgres
+[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
</programlisting>
</sect3>
@@ -551,14 +686,14 @@
デフォルトでは<filename>pool_hba.conf</filename>による認証は無効になっているので、<filename>pgpool.conf</filename>では以下の設定をonに変更します。
</para>
<programlisting>
- enable_pool_hba = on
+enable_pool_hba = on
</programlisting>
<para>
<filename>pool_hba.conf</filename>のフォーマットは<productname>PostgreSQL</productname>の<filename>pg_hba.conf</filename>とほとんど同じです。<literal>pgpool</literal>と<literal>postgres</literal>ユーザを<acronym>scram-sha-256</acronym>認証に設定します。
</para>
<programlisting>
- host all pgpool 0.0.0.0/0 scram-sha-256
- host all postgres 0.0.0.0/0 scram-sha-256
+host all pgpool 0.0.0.0/0 scram-sha-256
+host all postgres 0.0.0.0/0 scram-sha-256
</programlisting>
<note>
<para>
@@ -573,24 +708,24 @@
<literal>scram-sha-256</literal>認証を利用する場合、<productname>Pgpool-II</productname>はそれらのパスワードを復号化するために復号鍵が必要となります。全サーバで復号鍵ファイルを<productname>Pgpool-II</productname>の起動ユーザ<literal>postgres</literal>(<productname>Pgpool-II</productname> 4.1以降)のホームディレクトリ配下に作成します。(<productname>Pgpool-II</productname> 4.0 以前のバージョンでは、デフォルトでは<literal>root</literal>ユーザで<productname>Pgpool-II</productname>を起動する)
</para>
<programlisting>
- [全サーバ]# su - postgres
- [全サーバ]$ echo '任意の文字列' > ~/.pgpoolkey
- [全サーバ]$ chmod 600 ~/.pgpoolkey
+[全サーバ]# su - postgres
+[全サーバ]$ echo '任意の文字列' > ~/.pgpoolkey
+[全サーバ]$ chmod 600 ~/.pgpoolkey
</programlisting>
<para>
「pg_enc -m -k /path/to/.pgpoolkey -u ユーザ名 -p」 コマンドを実行すると、ユーザ名と<literal>AES256</literal>で暗号化したパスワードのエントリが<xref linkend="GUC-POOL-PASSWD">に登録されます。
<xref linkend="GUC-POOL-PASSWD"> がまだ存在しなければ、<filename>pgpool.conf</filename>と同じディレクトリ内に作成されます。
</para>
<programlisting>
- [全サーバ]# su - postgres
- [全サーバ]$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p
- db password: [pgpoolユーザのパスワード]
- [全サーバ]$ pg_enc -m -k ~/.pgpoolkey -u postgres -p
- db password: [postgresユーザのパスワード]
-
- # cat /etc/pgpool-II/pool_passwd
- pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
- postgres:AESHs/pWL5rtXy2IwuzroHfqg==
+[全サーバ]# su - postgres
+[全サーバ]$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p
+db password: [pgpoolユーザのパスワード]
+[全サーバ]$ pg_enc -m -k ~/.pgpoolkey -u postgres -p
+db password: [postgresユーザのパスワード]
+
+# cat /etc/pgpool-II/pool_passwd
+pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
+postgres:AESHs/pWL5rtXy2IwuzroHfqg==
</programlisting>
</sect3>
@@ -600,13 +735,13 @@
デフォルトでは<literal>watchdog</literal>機能が無効のため、<literal>server1</literal>、<literal>server2</literal>及び<literal>server3</literal>で<literal>watchdog</literal>を有効にします。
</para>
<programlisting>
- use_watchdog = on
+use_watchdog = on
</programlisting>
<para>
アクティブ機が立ち上げる仮想IPをdelegate_IPに指定します。仮想 IP はまだ使われていないIPアドレスを指定してください。<literal>server1</literal>、<literal>server2</literal>及び<literal>server3</literal>の共通の設定です。
</para>
<programlisting>
- delegate_IP = '192.168.137.150'
+delegate_IP = '192.168.137.150'
</programlisting>
<para>
@@ -615,7 +750,18 @@
<varname>if_up/down_cmd</varname>や<varname>arping_cmd</varname>を実行するにはroot権限が必要となりますので、
一般ユーザが実行できるように<command>ip/arping</command>コマンドに<literal>setuid</literal>を設定するか、
<productname>Pgpool-II</productname>起動ユーザ、デフォルトでは<literal>postgres</literal>ユーザ (<productname>Pgpool-II</productname> 4.1以降) がパスワードなしに<command>sudo</command>を実行できるように設定する必要があります。
- ここでは、<command>sudo</command>を介して実行するように設定します。RPMからインストールした場合、<literal>postgres</literal>ユーザがパスワードなしに<command>sudo</command>を介して<command>ip/arping</command>を実行できるように設定済みです。
+ </para>
+ <note>
+ <para>
+ RPMからインストールした場合、<literal>postgres</literal>ユーザがパスワードなしに<command>sudo</command>を介して<command>ip/arping</command>を実行できるように設定済みです。
+ <programlisting>
+postgres ALL=NOPASSWD: /sbin/ip
+postgres ALL=NOPASSWD: /usr/sbin/arping
+ </programlisting>
+ </para>
+ </note>
+ <para>
+ ここでは、<command>sudo</command>を介して実行するように設定します。
</para>
<programlisting>
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'
@@ -638,282 +784,109 @@ if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
</programlisting>
<para>
- 各watchdog が稼働するサーバ情報を設定しておきます。
+ 各watchdogが稼働するサーバ情報を設定しておきます。<varname>pgpool_portX</varname>には<xref linkend="example-cluster-pgpool-config-port">の<varname>port</varname>に設定されているポート番号を指定します。
</para>
- <itemizedlist>
- <listitem>
- <para>
- <literal>server1</literal>の場合
- </para>
- <programlisting>
- wd_hostname = 'server1'
- wd_port = 9000
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server2</literal>の場合
- </para>
- <programlisting>
- wd_hostname = 'server2'
- wd_port = 9000
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server3</literal>の場合
- </para>
- <programlisting>
- wd_hostname = 'server3'
- wd_port = 9000
- </programlisting>
- </listitem>
- </itemizedlist>
-
- <para>
- 各監視対象の<productname>Pgpool-II</productname>サーバ情報を設定しておきます。
- </para>
- <itemizedlist>
- <listitem>
- <para>
- <literal>server1</literal>の場合
- </para>
- <programlisting>
- # - Other pgpool Connection Settings -
-
- other_pgpool_hostname0 = 'server2'
- # Host name or IP address to connect to for other pgpool 0
- # (change requires restart)
- other_pgpool_port0 = 9999
- # Port number for other pgpool 0
- # (change requires restart)
- other_wd_port0 = 9000
- # Port number for other watchdog 0
- # (change requires restart)
- other_pgpool_hostname1 = 'server3'
- other_pgpool_port1 = 9999
- other_wd_port1 = 9000
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server2</literal>の場合
- </para>
- <programlisting>
- # - Other pgpool Connection Settings -
-
- other_pgpool_hostname0 = 'server1'
- # Host name or IP address to connect to for other pgpool 0
- # (change requires restart)
- other_pgpool_port0 = 9999
- # Port number for other pgpool 0
- # (change requires restart)
- other_wd_port0 = 9000
- # Port number for other watchdog 0
- # (change requires restart)
- other_pgpool_hostname1 = 'server3'
- other_pgpool_port1 = 9999
- other_wd_port1 = 9000
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server3</literal>の場合
- </para>
- <programlisting>
- # - Other pgpool Connection Settings -
-
- other_pgpool_hostname0 = 'server1'
- # Host name or IP address to connect to for other pgpool 0
- # (change requires restart)
- other_pgpool_port0 = 9999
- # Port number for other pgpool 0
- # (change requires restart)
- other_wd_port0 = 9000
- # Port number for other watchdog 0
- # (change requires restart)
- other_pgpool_hostname1 = 'server2'
- other_pgpool_port1 = 9999
- other_wd_port1 = 9000
- </programlisting>
- </listitem>
- </itemizedlist>
-
- <para>
- ハートビート信号の送信先のホスト名とポート番号を指定します。
- </para>
- <itemizedlist>
- <listitem>
- <para>
- <literal>server1</literal>の場合
- </para>
- <programlisting>
- heartbeat_destination0 = 'server2'
- # Host name or IP address of destination 0
- # for sending heartbeat signal.
- # (change requires restart)
- heartbeat_destination_port0 = 9694
- # Port number of destination 0 for sending
- # heartbeat signal. Usually this is the
- # same as wd_heartbeat_port.
- # (change requires restart)
- heartbeat_device0 = ''
- # Name of NIC device (such like 'eth0')
- # used for sending/receiving heartbeat
- # signal to/from destination 0.
- # This works only when this is not empty
- # and pgpool has root privilege.
- # (change requires restart)
-
- heartbeat_destination1 = 'server3'
- heartbeat_destination_port1 = 9694
- heartbeat_device1 = ''
-
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server2</literal>の場合
- </para>
- <programlisting>
- heartbeat_destination0 = 'server1'
- # Host name or IP address of destination 0
- # for sending heartbeat signal.
- # (change requires restart)
- heartbeat_destination_port0 = 9694
- # Port number of destination 0 for sending
- # heartbeat signal. Usually this is the
- # same as wd_heartbeat_port.
- # (change requires restart)
- heartbeat_device0 = ''
- # Name of NIC device (such like 'eth0')
- # used for sending/receiving heartbeat
- # signal to/from destination 0.
- # This works only when this is not empty
- # and pgpool has root privilege.
- # (change requires restart)
-
- heartbeat_destination1 = 'server3'
- heartbeat_destination_port1 = 9694
- heartbeat_device1 = ''
-
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server3</literal>の場合
- </para>
- <programlisting>
- heartbeat_destination0 = 'server1'
- # Host name or IP address of destination 0
- # for sending heartbeat signal.
- # (change requires restart)
- heartbeat_destination_port0 = 9694
- # Port number of destination 0 for sending
- # heartbeat signal. Usually this is the
- # same as wd_heartbeat_port.
- # (change requires restart)
- heartbeat_device0 = ''
- # Name of NIC device (such like 'eth0')
- # used for sending/receiving heartbeat
- # signal to/from destination 0.
- # This works only when this is not empty
- # and pgpool has root privilege.
- # (change requires restart)
-
- heartbeat_destination1 = 'server2'
- heartbeat_destination_port1 = 9694
- heartbeat_device1 = ''
- </programlisting>
- </listitem>
- </itemizedlist>
- </sect3>
+ <programlisting>
+hostname0 = 'server1'
+ # Host name or IP address of pgpool node
+ # for watchdog connection
+ # (change requires restart)
+wd_port0 = 9000
+ # Port number for watchdog service
+ # (change requires restart)
+pgpool_port0 = 9999
+ # Port number for pgpool
+ # (change requires restart)
+
+hostname1 = 'server2'
+wd_port1 = 9000
+pgpool_port1 = 9999
+
+hostname2 = 'server3'
+wd_port2 = 9000
+pgpool_port2 = 9999
+ </programlisting>
- <sect3 id="example-cluster-pgpool-config-sysconfig">
- <title>/etc/sysconfig/pgpoolの設定</title>
<para>
- <productname>Pgpool-II</productname>を起動時に<filename>pgpool_status</filename>ファイルを無視させたい場合、<filename>/etc/sysconfig/pgpool</filename>の起動オプションOPTSに「-D」を追加します。
+ ハートビート通信用の各<productname>Pgpool-II</productname>サーバ情報を設定しておきます。
</para>
<programlisting>
- [全サーバ]# vi /etc/sysconfig/pgpool
- (...省略...)
- OPTS=" -D -n"
+heartbeat_hostname0 = 'server1'
+ # Host name or IP address used
+ # for sending heartbeat signal.
+ # (change requires restart)
+heartbeat_port0 = 9694
+ # Port number used for receiving/sending heartbeat signal
+ # Usually this is the same as heartbeat_portX.
+ # (change requires restart)
+heartbeat_device0 = ''
+ # Name of NIC device (such like 'eth0')
+ # used for sending/receiving heartbeat
+ # signal to/from destination 0.
+ # This works only when this is not empty
+ # and pgpool has root privilege.
+ # (change requires restart)
+
+heartbeat_hostname1 = 'server2'
+heartbeat_port1 = 9694
+heartbeat_device1 = ''
+heartbeat_hostname2 = 'server3'
+heartbeat_port2 = 9694
+heartbeat_device2 = ''
</programlisting>
+ <note>
+ <para>
+ use_watchdog = onの場合は、pgpoolノード番号を<filename>pgpool_node_id</filename>に設定する必要があります。
+ 詳細は<xref linkend="example-cluster-pgpool-node-id">を参照ください。
+ </para>
+ </note>
</sect3>
<sect3 id="example-cluster-pgpool-config-log">
<title>ログの設定</title>
<para>
- この例では、<productname>Pgpool-II</productname>のログ出力は<literal>syslog</literal>を利用するように設定します。
- </para>
- <programlisting>
- log_destination = 'syslog'
- # Where to log
- # Valid values are combinations of stderr,
- # and syslog. Default to stderr.
-
- syslog_facility = 'LOCAL1'
- # Syslog local facility. Default to LOCAL0
- </programlisting>
- <para>
- 全サーバではログファイルを作成します。
+ <productname>Pgpool-II</productname> 4.2以降、ログ収集プロセスが追加されました。
+ ここでは、ログ収集プロセス(logging_collector)を有効にします。
</para>
<programlisting>
- [全サーバ]# mkdir /var/log/pgpool-II
- [全サーバ]# touch /var/log/pgpool-II/pgpool.log
+log_destination = 'stderr'
+logging_collector = on
+log_directory = '/var/log/pgpool_log'
+log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
+log_truncate_on_rotation = on
+log_rotation_age = 1d
+log_rotation_size = 10MB
</programlisting>
<para>
- 次に<literal>syslog</literal>の設定ファイルを以下のように編集します。
+ すべてのサーバでログファイルを格納するディレクトリを作成します。
</para>
<programlisting>
- [全サーバ]# vi /etc/rsyslog.conf
- ...(省略)...
- *.info;mail.none;authpriv.none;cron.none;LOCAL1.none /var/log/messages
- LOCAL1.* /var/log/pgpool-II/pgpool.log
- </programlisting>
- <para>
- また、<productname>Pgpool-II</productname>に関して<filename>/var/log/messages</filename>と同様のログローテーションを行うように、logrotateの設定を以下のように行います。
- </para>
- <programlisting>
- [全サーバ]# vi /etc/logrotate.d/syslog
- ...(省略)...
- /var/log/messages
- /var/log/pgpool-II/pgpool.log
- /var/log/secure
+[all servers]# mkdir /var/log/pgpool_log/
+[all servers]# chown postgres:postgres /var/log/pgpool_log/
</programlisting>
<para>
- 設定が終わったら、rsyslogサービスを再起動します。
+ ここで、server1の<filename>pgpool.conf</filename>の設定は完了です。
+ server1の<filename>pgpool.conf</filename>を他のノード(server2、server3)にコピーします。
</para>
<programlisting>
- [全サーバ]# systemctl restart rsyslog
- </programlisting>
- </sect3>
-
- <sect3 id="example-cluster-pgpool-config-pcp">
- <title>PCPコマンドの設定</title>
- <para>
- <literal>PCP</literal>コマンドを使用するにはユーザ認証が必要になるので、ユーザ名と<literal>md5</literal>ハッシュに変換されたパスワードを<filename>pcp.conf</filename>ファイルに設定します。
- ここではユーザ名に<literal>pgpool</literal>を使用し、以下のコマンドを実行することで、<ユーザ名:ハッシュ化されたパスワード>が<filename>/etc/pgpool-II/pcp.conf</filename>に追加されます。
- </para>
- <programlisting>
- [全サーバ]# echo 'pgpool:'`pg_md5 PCPコマンドパスワード` >> /etc/pgpool-II/pcp.conf
+[server1]# scp -p /etc/pgpool-II/pgpool.conf root@server2:/etc/pgpool-II/pgpool.conf
+[server1]# scp -p /etc/pgpool-II/pgpool.conf root@server3:/etc/pgpool-II/pgpool.conf
</programlisting>
</sect3>
+ </sect2>
- <sect3 id="example-cluster-pgpool-config-pcppass">
- <title>.pcppassの設定</title>
- <para>
- 前述の<literal>follow_primary_command</literal>のスクリプトでパスワード入力なしで<literal>PCP</literal>コマンドを実行できるように、すべてのサーバで<productname>Pgpool-II</productname>の起動ユーザのホームディレクトリに<filename>.pcppass</filename>を作成します。
- </para>
- <programlisting>
- [全サーバ]# echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass
- [全サーバ]# chmod 600 ~/.pcppass
- </programlisting>
- <para>
- ここで、<productname>Pgpool-II</productname>の設定は完了です。
- </para>
- </sect3>
+ <sect2 id="example-cluster-pgpool-config-sysconfig">
+ <title>/etc/sysconfig/pgpoolの設定</title>
+ <para>
+ <productname>Pgpool-II</productname>起動時に<filename>pgpool_status</filename>ファイルが存在する場合、<productname>Pgpool-II</productname>は<filename>pgpool_status</filename>ファイルからバックエンドの状態(up/down)を読み取ります。
+ <productname>Pgpool-II</productname>起動時に<filename>pgpool_status</filename>ファイルを無視させたい場合、<filename>/etc/sysconfig/pgpool</filename>の起動オプションOPTSに「-D」を追加します。
+ </para>
+ <programlisting>
+[全サーバ]# vi /etc/sysconfig/pgpool
+(...省略...)
+OPTS=" -D -n"
+ </programlisting>
</sect2>
<sect2 id="example-cluster-start-stop">
diff --git a/doc/src/sgml/example-cluster.sgml b/doc/src/sgml/example-cluster.sgml
index 772e50712..2af82df19 100644
--- a/doc/src/sgml/example-cluster.sgml
+++ b/doc/src/sgml/example-cluster.sgml
@@ -7,8 +7,8 @@
servers to create a robust cluster system and avoid the single point of failure or split brain.
</para>
<para>
- <productname>PostgreSQL</productname> 11 is used in this configuration example,
- all scripts have also been tested with <productname>PostgreSQL</productname> 12.
+ <productname>PostgreSQL</productname> 13 is used in this configuration example.
+ All scripts have been tested with <productname>PostgreSQL</productname> 95 and later.
</para>
<sect2 id="example-cluster-requirement">
<title>Requirements</title>
@@ -81,7 +81,7 @@
<tbody>
<row>
<entry>PostgreSQL Version</entry>
- <entry>11.1</entry>
+ <entry>13.0</entry>
<entry>-</entry>
</row>
<row>
@@ -91,7 +91,7 @@
</row>
<row>
<entry>$PGDATA</entry>
- <entry>/var/lib/pgsql/11/data</entry>
+ <entry>/var/lib/pgsql/13/data</entry>
<entry>-</entry>
</row>
<row>
@@ -126,7 +126,7 @@
<tbody>
<row>
<entry>Pgpool-II Version</entry>
- <entry>4.1.0</entry>
+ <entry>4.2.0</entry>
<entry>-</entry>
</row>
<row>
@@ -174,27 +174,63 @@
</tbody>
</tgroup>
</table>
+
+ <table id="example-cluster-table-sample-scripts">
+ <title>Various sample scripts included in rpm package</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Feature</entry>
+ <entry>Script</entry>
+ <entry>Detail</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry morerows='1'>Failover</entry>
+ <entry><ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/failover.sh.sample;hb=refs/heads/V4_2_STABLE">/etc/pgpool-II/failover.sh.sample</ulink></entry>
+ <entry>Run by <xref linkend="GUC-FAILOVER-COMMAND"> to perform failover</entry>
+ </row>
+ <row>
+ <entry><ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/follow_primary.sh.sample;hb=refs/heads/V4_2_STABLE">/etc/pgpool-II/follow_primary.sh.sample</ulink></entry>
+ <entry>Run by <xref linkend="GUC-FOLLOW-PRIMARY-COMMAND"> to synchronize the Standby with the new Primary after failover.</entry>
+ </row>
+ <row>
+ <entry morerows='1'>Online recovery</entry>
+ <entry><ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/recovery_1st_stage.sample;hb=refs/heads/V4_2_STABLE">/etc/pgpool-II/recovery_1st_stage.sample</ulink></entry>
+ <entry>Run by <xref linkend="GUC-RECOVERY-1ST-STAGE-COMMAND"> to recovery a Standby node</entry>
+ </row>
+ <row>
+ <entry><ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/pgpool_remote_start.sample;hb=refs/heads/V4_2_STABLE">/etc/pgpool-II/pgpool_remote_start.sample</ulink></entry>
+ <entry>Run after <xref linkend="GUC-RECOVERY-1ST-STAGE-COMMAND"> to start the Standby node</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ <para>
+ The above scripts are included in the RPM package and can be customized as needed.
+ </para>
</sect2>
<sect2 id="example-cluster-installation">
<title>Installation</title>
<para>
- In this example, we install <productname>Pgpool-II</productname> 4.1 and <productname>PostgreSQL</productname> 11.1 by using RPM packages.
+ In this example, we install <productname>Pgpool-II</productname> 4.2 and <productname>PostgreSQL</productname> 13.0 using RPM packages.
</para>
<para>
- Install <productname>PostgreSQL</productname> by using <productname>PostgreSQL</productname> YUM repository.
+ Install <productname>PostgreSQL</productname> using <productname>PostgreSQL</productname> YUM repository.
</para>
<programlisting>
- # yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
- # yum install postgresql11-server
+ # yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
+ # yum install -y postgresql13-server
</programlisting>
<para>
Install <productname>Pgpool-II</productname> by using Pgpool-II YUM repository.
</para>
<programlisting>
- # yum install http://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm
- # yum install pgpool-II-pg11-*
+ # yum install -y http://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm
+ # yum install -y pgpool-II-pg13-*
</programlisting>
</sect2>
@@ -244,8 +280,8 @@
<listitem>
<para>
Because of the security reasons, we create a user <literal>repl</literal> solely used
- for replication purpose, and a user <literal>pgpool</literal> for streaming
- replication delay check and health check of <productname>Pgpool-II</productname>.
+ for replication purpose, and a user <literal>pgpool</literal> for streaming
+ replication delay check and health check of <productname>Pgpool-II</productname>.
</para>
<table id="example-cluster-user">
@@ -267,7 +303,7 @@
<row>
<entry>pgpool</entry>
<entry>pgpool</entry>
- <entry>Pgpool-II health check and replication delay check user</entry>
+ <entry>Pgpool-II health check (<xref linkend="GUC-HEALTH-CHECK-USER">) and replication delay check (<xref linkend="GUC-SR-CHECK-USER">) user</entry>
</row>
<row>
<entry>postgres</entry>
@@ -380,42 +416,114 @@
</itemizedlist>
</sect2>
+ <sect2 id="example-cluster-pgpool-node-id">
+ <title>Create pgpool_node_id</title>
+ <para>
+ From <productname>Pgpool-II</productname> 4.2, now all configuration parameters are identical on all hosts.
+ If <literal>watchdog</literal> feature is enabled, to distingusish which host is which,
+ a <filename>pgpool_node_id</filename> file is required.
+ You need to create a <filename>pgpool_node_id</filename> file and specify the pgpool (watchdog) node number
+ (e.g. 0, 1, 2 ...) to identify pgpool (watchdog) host.
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>server1</literal>
+ </para>
+ <programlisting>
+[server1]# cat /etc/pgpool-II/pgpool_node_id
+0
+ </programlisting>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>server2</literal>
+ </para>
+ <programlisting>
+[server2]# cat /etc/pgpool-II/pgpool_node_id
+1
+ </programlisting>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>server3</literal>
+ </para>
+ <programlisting>
+[server3]# cat /etc/pgpool-II/pgpool_node_id
+2
+ </programlisting>
+ </listitem>
+ </itemizedlist>
+ </sect2>
+
<sect2 id="example-cluster-pgpool-config">
<title><productname>Pgpool-II</productname> Configuration</title>
- <sect3 id="example-cluster-pgpool-config-common">
- <title>Common Settings</title>
+ <para>
+ Since from <productname>Pgpool-II</productname> 4.2, all configuration parameters are
+ identical on all hosts, you can edit <filename>pgpool.conf</filename> on any pgpool node
+ and copy the edited <filename>pgpool.conf</filename> file to the other pgpool nodes.
+ </para>
+
+ <sect3 id="example-cluster-pgpool-config-config-file">
+ <title>Clustering mode</title>
<para>
- Here are the common settings on <literal>server1</literal>, <literal>server2</literal> and <literal>server3</literal>.
+ <productname>Pgpool-II</productname> has several clustering modes. To set the clustering
+ mode, <xref linkend="GUC-BACKEND-CLUSTERING-MODE"> can be used. In this configuration
+ example, streaming replication mode is used.
</para>
<para>
- When installing <productname>Pgpool-II</productname> from RPM, all the
- <productname>Pgpool-II</productname> configuration files are in <filename>/etc/pgpool-II</filename>.
+ When installing <productname>Pgpool-II</productname> using RPM, all the
+ <productname>Pgpool-II</productname> configuration sample files are in <filename>/etc/pgpool-II</filename>.
In this example, we copy the sample configuration file for streaming replication mode.
</para>
<programlisting>
# cp -p /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf
</programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-listen-addresses">
+ <title>listen_addresses</title>
<para>
To allow Pgpool-II to accept all incoming connections, we set <varname>listen_addresses = '*'</varname>.
</para>
<programlisting>
listen_addresses = '*'
</programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-port">
+ <title>port</title>
+ <para>
+ Specify the port number Pgpool-II listen on.
+ </para>
+ <programlisting>
+port = 9999
+ </programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-sr-check">
+ <title>Streaming Replication Check</title>
<para>
- Specify replication delay check user and password. In this example, we leave
- <xref linkend="GUC-SR-CHECK-USER"> empty, and create the entry in <xref linkend="GUC-POOL-PASSWD">.
- From <productname>Pgpool-II</productname> 4.0, if these parameters are left blank,
- <productname>Pgpool-II</productname> will first try to get the password for that
- specific user from <xref linkend="GUC-SR-CHECK-PASSWORD"> file before using the
- empty password.
+ Specify replication delay check user and password in <xref linkend="GUC-SR-CHECK-USER">
+ and <xref linkend="GUC-SR-CHECK-PASSWORD">. In this example, we leave
+ <xref linkend="GUC-SR-CHECK-PASSWORD"> empty, and create the entry in
+ <xref linkend="GUC-POOL-PASSWD">. See <xref linkend="example-cluster-pgpool-config-auth">
+ for how to create the entry in <xref linkend="GUC-POOL-PASSWD">.
+ From <productname>Pgpool-II</productname> 4.0, if these parameters are left blank,
+ <productname>Pgpool-II</productname> will first try to get the password for that
+ specific user from <xref linkend="GUC-POOL-PASSWD"> file before using the empty password.
</para>
<programlisting>
sr_check_user = 'pgpool'
sr_check_password = ''
</programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-health-check">
+ <title>Health Check</title>
<para>
- Enable health check so that <productname>Pgpool-II</> performs failover. Also, if the network is unstable,
- the health check fails even though the backend is running properly, failover or degenerate operation may occur.
+ Enable health check so that <productname>Pgpool-II</> performs failover. Also, if the network is unstable,
+ the health check fails even though the backend is running properly, failover or degenerate operation may occur.
In order to prevent such incorrect detection of health check, we set <varname>health_check_max_retries = 3</varname>.
Specify <xref linkend="GUC-HEALTH-CHECK-USER"> and <xref linkend="GUC-HEALTH-CHECK-PASSWORD"> in
the same way like <xref linkend="GUC-SR-CHECK-USER"> and <xref linkend="GUC-SR-CHECK-PASSWORD">.
@@ -432,6 +540,10 @@
health_check_max_retries = 3
</programlisting>
+ </sect3>
+
+ <sect3 id="example-cluster-pgpool-config-backend-settings">
+ <title>Backend Settings</title>
<para>
Specify the <productname>PostgreSQL</productname> backend information.
Multiple backends can be specified by adding a number at the end of the parameter name.
@@ -453,13 +565,13 @@
backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
- backend_data_directory1 = '/var/lib/pgsql/11/data'
+ backend_data_directory1 = '/var/lib/pgsql/13/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = 'server3'
backend_port2 = 5432
backend_weight2 = 1
- backend_data_directory2 = '/var/lib/pgsql/11/data'
+ backend_data_directory2 = '/var/lib/pgsql/13/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
</programlisting>
<para>
@@ -506,22 +618,55 @@
are installed in <filename>/etc/pgpool-II/</filename>. Create failover scripts using these sample files.
</para>
<programlisting>
- # cp /etc/pgpool-II/failover.sh{.sample,}
- # cp /etc/pgpool-II/follow_primary.sh{.sample,}
+[all servers]# cp -p /etc/pgpool-II/failover.sh{.sample,}
+[all servers]# cp -p /etc/pgpool-II/follow_primary.sh{.sample,}
+[all servers]# chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}
</programlisting>
<para>
Basically, it should work if you change <emphasis>PGHOME</emphasis> according to PostgreSQL installation directory.
</para>
<programlisting>
- [server1]# vi /etc/pgpool-II/failover.sh
- ...
- PGHOME=/usr/pgsql-11
- ...
+[all servers]# vi /etc/pgpool-II/failover.sh
+...
+PGHOME=/usr/pgsql-13
+...
- [server1]# vi /etc/pgpool-II/follow_primary.sh
- ...
- PGHOME=/usr/pgsql-11
- ...
+[all servers]# vi /etc/pgpool-II/follow_primary.sh
+...
+PGHOME=/usr/pgsql-13
+...
+ </programlisting>
+
+ <para>
+ Since user authentication is required to use the <literal>PCP</literal> command in
+ <varname>follow_primary_command</varname> script,
+ we need to specify user name and md5 encrypted password in <filename>pcp.conf</filename>
+ in format "<literal>username:encrypted password</literal>".
+ </para>
+ <para>
+ if "pgpool" user is specified in <varname>PCP_USER</varname> in <filename>follow_primary.sh</filename>,
+ </para>
+ <programlisting>
+# cat /etc/pgpool-II/follow_primary.sh
+...
+PCP_USER=pgpool
+...
+ </programlisting>
+ <para>
+ then we create the encrypted password entry for <literal>pgpool</literal> user as below:
+ </para>
+ <programlisting>
+[all servers]# echo 'pgpool:'`pg_md5 PCP passowrd` >> /etc/pgpool-II/pcp.conf
+ </programlisting>
+ <para>
+ Since follow_primary_command script has to execute PCP command without entering the
+ password, we need to create <filename>.pcppass</filename> in the home directory of
+ <productname>Pgpool-II</productname> startup user (postgres user) on each server.
+ </para>
+ <programlisting>
+[all servers]# su - postgres
+[all servers]$ echo 'localhost:9898:pgpool:<pgpool user's password>' > ~/.pcppass
+[all servers]$ chmod 600 ~/.pcppass
</programlisting>
</sect3>
@@ -530,7 +675,7 @@
<para>
Next, in order to perform online recovery with <productname>Pgpool-II</productname> we specify
the <productname>PostgreSQL</productname> user name and online recovery command
- <command>recovery_1st_stage</command>.
+ <command>recovery_1st_stage</command>.
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">.
Then, we create <filename>recovery_1st_stage</filename> and <filename>pgpool_remote_start</filename>
@@ -538,36 +683,36 @@
</para>
<programlisting>
- recovery_user = 'postgres'
+recovery_user = 'postgres'
# Online recovery user
- recovery_password = ''
+recovery_password = ''
# Online recovery password
- recovery_1st_stage_command = 'recovery_1st_stage'
+recovery_1st_stage_command = 'recovery_1st_stage'
</programlisting>
<para>
- Online recovery sample scripts<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/recovery_1st_stage.sample;hb=refs/heads/V4_1_STABLE">recovery_1st_stage</ulink>
- and <ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/pgpool_remote_start.sample;hb=refs/heads/V4_1_STABLE">pgpool_remote_start</ulink>
+ Online recovery sample scripts<ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/recovery_1st_stage.sample;hb=refs/heads/V4_2_STABLE">recovery_1st_stage</ulink>
+ and <ulink url="https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/pgpool_remote_start.sample;hb=refs/heads/V4_2_STABLE">pgpool_remote_start</ulink>
are installed in <filename>/etc/pgpool-II/</filename>. Copy these files to the data directory of the primary server (server1).
</para>
<programlisting>
- [server1]# cp /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/11/data/
- [server1]# cp /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/11/data/
- [server1]# chown postgres:postgres /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start}
+[server1]# cp -p /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/13/data/recovery_1st_stage
+[server1]# cp -p /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/13/data/pgpool_remote_start
+[server1]# chown postgres:postgres /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start}
</programlisting>
<para>
Basically, it should work if you change <emphasis>PGHOME</emphasis> according to PostgreSQL installation directory.
</para>
<programlisting>
- [server1]# vi /var/lib/pgsql/11/data/recovery_1st_stage
- ...
- PGHOME=/usr/pgsql-11
- ...
+[server1]# vi /var/lib/pgsql/13/data/recovery_1st_stage
+...
+PGHOME=/usr/pgsql-13
+...
- [server1]# vi /var/lib/pgsql/11/data/pgpool_remote_start
- ...
- PGHOME=/usr/pgsql-11
- ...
+[server1]# vi /var/lib/pgsql/13/data/pgpool_remote_start
+...
+PGHOME=/usr/pgsql-13
+...
</programlisting>
<para>
@@ -578,33 +723,33 @@
<literal>server1</literal>.
</para>
<programlisting>
- [server1]# su - postgres
- [server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
+[server1]# su - postgres
+[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
</programlisting>
</sect3>
<sect3 id="example-cluster-pgpool-config-auth">
<title>Client Authentication Configuration</title>
<para>
- Because in the section <link linkend="EXAMPLE-CLUSTER-PRE-SETUP">Before Starting</link>,
- we already set <productname>PostgreSQL</productname> authentication method to
- <acronym>scram-sha-256</acronym>, it is necessary to set a client authentication by
- <productname>Pgpool-II</productname> to connect to backend nodes.
- When installing with RPM, the <productname>Pgpool-II</productname> configuration file
- <filename>pool_hba.conf</filename> is in <filename>/etc/pgpool-II</filename>.
- By default, pool_hba authentication is disabled, set <varname>enable_pool_hba = on</varname>
+ Because in the section <link linkend="EXAMPLE-CLUSTER-PRE-SETUP">Before Starting</link>,
+ we already set <productname>PostgreSQL</productname> authentication method to
+ <acronym>scram-sha-256</acronym>, it is necessary to set a client authentication by
+ <productname>Pgpool-II</productname> to connect to backend nodes.
+ When installing with RPM, the <productname>Pgpool-II</productname> configuration file
+ <filename>pool_hba.conf</filename> is in <filename>/etc/pgpool-II</filename>.
+ By default, pool_hba authentication is disabled, set <varname>enable_pool_hba = on</varname>
to enable it.
</para>
<programlisting>
- enable_pool_hba = on
+enable_pool_hba = on
</programlisting>
<para>
The format of <filename>pool_hba.conf</filename> file follows very closely PostgreSQL's
<filename>pg_hba.conf</filename> format. Set <literal>pgpool</literal> and <literal>postgres</literal> user's authentication method to <literal>scram-sha-256</literal>.
</para>
<programlisting>
- host all pgpool 0.0.0.0/0 scram-sha-256
- host all postgres 0.0.0.0/0 scram-sha-256
+host all pgpool 0.0.0.0/0 scram-sha-256
+host all postgres 0.0.0.0/0 scram-sha-256
</programlisting>
<note>
<para>
@@ -621,9 +766,9 @@
(<productname>Pgpool-II</productname> 4.0 or before, by default <productname>Pgpool-II</productname>
is started as <literal>root</literal>)
<programlisting>
- [all servers]# su - postgres
- [all servers]$ echo 'some string' > ~/.pgpoolkey
- [all servers]$ chmod 600 ~/.pgpoolkey
+[all servers]# su - postgres
+[all servers]$ echo 'some string' > ~/.pgpoolkey
+[all servers]$ chmod 600 ~/.pgpoolkey
</programlisting>
</para>
<para>
@@ -633,15 +778,15 @@
<filename>pgpool.conf</filename>.
</para>
<programlisting>
- [all servers]# su - postgres
- [all servers]$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p
- db password: [pgpool user's password]
- [all servers]$ pg_enc -m -k ~/.pgpoolkey -u postgres -p
- db password: [postgres user's passowrd]
+[all servers]# su - postgres
+[all servers]$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p
+db password: [pgpool user's password]
+[all servers]$ pg_enc -m -k ~/.pgpoolkey -u postgres -p
+db password: [postgres user's passowrd]
- # cat /etc/pgpool-II/pool_passwd
- pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
- postgres:AESHs/pWL5rtXy2IwuzroHfqg==
+# cat /etc/pgpool-II/pool_passwd
+pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
+postgres:AESHs/pWL5rtXy2IwuzroHfqg==
</programlisting>
</sect3>
@@ -651,7 +796,7 @@
Enable watchdog functionality on <literal>server1</literal>, <literal>server2</literal>, <literal>server3</literal>.
</para>
<programlisting>
- use_watchdog = on
+use_watchdog = on
</programlisting>
<para>
Specify virtual IP address that accepts connections from clients on
@@ -659,17 +804,28 @@
Ensure that the IP address set to virtual IP isn't used yet.
</para>
<programlisting>
- delegate_IP = '192.168.137.150'
+delegate_IP = '192.168.137.150'
</programlisting>
<para>
To bring up/down the virtual IP and send the ARP requests, we set <xref linkend="GUC-IF-UP-CMD">, <xref linkend="GUC-IF-DOWN-CMD"> and <xref linkend="GUC-ARPING-CMD">.
The network interface used in this example is "enp0s8".
Since root privilege is required to execute <varname>if_up/down_cmd</varname> or
- <varname>arping_cmd</varname> command, use setuid on these command or allow
+ <varname>arping_cmd</varname> command, use setuid on these command or allow
<productname>Pgpool-II</productname> startup user, <literal>postgres</literal> user (Pgpool-II 4.1 or later) to run <command>sudo</command> command without a password.
+ </para>
+ <note>
+ <para>
If installed from RPM, the <literal>postgres</literal> user has been configured to run
<command>ip/arping</command> via <command>sudo</command> without a password.
+ <programlisting>
+postgres ALL=NOPASSWD: /sbin/ip
+postgres ALL=NOPASSWD: /usr/sbin/arping
+ </programlisting>
+ </para>
+ </note>
+ <para>
+ Here we configure the following parameters to run <varname>if_up/down_cmd</varname> or <varname>arping_cmd</varname> with sudo.
</para>
<programlisting>
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'
@@ -692,288 +848,118 @@ if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
</programlisting>
<para>
- Specify the hostname and port number of each <productname>Pgpool-II</productname> server.
- </para>
- <itemizedlist>
- <listitem>
- <para>
- <literal>server1</literal>
- </para>
- <programlisting>
- wd_hostname = 'server1'
- wd_port = 9000
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server2</literal>
- </para>
- <programlisting>
- wd_hostname = 'server2'
- wd_port = 9000
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server3</literal>
- </para>
- <programlisting>
- wd_hostname = 'server3'
- wd_port = 9000
- </programlisting>
- </listitem>
- </itemizedlist>
-
- <para>
- Specify the hostname, <productname>Pgpool-II</productname> port number, and watchdog port number of monitored <productname>Pgpool-II</productname> servers on each <productname>Pgpool-II</productname> server.
- </para>
- <itemizedlist>
- <listitem>
- <para>
- <literal>server1</literal>
- </para>
- <programlisting>
- # - Other pgpool Connection Settings -
-
- other_pgpool_hostname0 = 'server2'
- # Host name or IP address to connect to for other pgpool 0
- # (change requires restart)
- other_pgpool_port0 = 9999
- # Port number for other pgpool 0
- # (change requires restart)
- other_wd_port0 = 9000
- # Port number for other watchdog 0
- # (change requires restart)
- other_pgpool_hostname1 = 'server3'
- other_pgpool_port1 = 9999
- other_wd_port1 = 9000
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server2</literal>
- </para>
- <programlisting>
- # - Other pgpool Connection Settings -
-
- other_pgpool_hostname0 = 'server1'
- # Host name or IP address to connect to for other pgpool 0
- # (change requires restart)
- other_pgpool_port0 = 9999
- # Port number for other pgpool 0
- # (change requires restart)
- other_wd_port0 = 9000
- # Port number for other watchdog 0
- # (change requires restart)
- other_pgpool_hostname1 = 'server3'
- other_pgpool_port1 = 9999
- other_wd_port1 = 9000
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server3</literal>
- </para>
- <programlisting>
- # - Other pgpool Connection Settings -
-
- other_pgpool_hostname0 = 'server1'
- # Host name or IP address to connect to for other pgpool 0
- # (change requires restart)
- other_pgpool_port0 = 9999
- # Port number for other pgpool 0
- # (change requires restart)
- other_wd_port0 = 9000
- # Port number for other watchdog 0
- # (change requires restart)
- other_pgpool_hostname1 = 'server2'
- other_pgpool_port1 = 9999
- other_wd_port1 = 9000
- </programlisting>
- </listitem>
- </itemizedlist>
-
- <para>
- Specify the hostname and port number of destination for sending heartbeat signal
- on <literal>server1</literal>, <literal>server2</literal>, <literal>server3</literal>.
+ Specify all <productname>Pgpool-II</productname> nodes information for configuring watchdog.
+ Specify <varname>pgpool_portX</varname> using the port number specified in <varname>port</varname> in
+ <xref linkend="example-cluster-pgpool-config-port">.
</para>
- <itemizedlist>
- <listitem>
- <para>
- <literal>server1</literal>
- </para>
- <programlisting>
- heartbeat_destination0 = 'server2'
- # Host name or IP address of destination 0
- # for sending heartbeat signal.
- # (change requires restart)
- heartbeat_destination_port0 = 9694
- # Port number of destination 0 for sending
- # heartbeat signal. Usually this is the
- # same as wd_heartbeat_port.
- # (change requires restart)
- heartbeat_device0 = ''
- # Name of NIC device (such like 'eth0')
- # used for sending/receiving heartbeat
- # signal to/from destination 0.
- # This works only when this is not empty
- # and pgpool has root privilege.
- # (change requires restart)
-
- heartbeat_destination1 = 'server3'
- heartbeat_destination_port1 = 9694
- heartbeat_device1 = ''
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server2</literal>
- </para>
- <programlisting>
- heartbeat_destination0 = 'server1'
- # Host name or IP address of destination 0
- # for sending heartbeat signal.
- # (change requires restart)
- heartbeat_destination_port0 = 9694
- # Port number of destination 0 for sending
- # heartbeat signal. Usually this is the
- # same as wd_heartbeat_port.
- # (change requires restart)
- heartbeat_device0 = ''
- # Name of NIC device (such like 'eth0')
- # used for sending/receiving heartbeat
- # signal to/from destination 0.
- # This works only when this is not empty
- # and pgpool has root privilege.
- # (change requires restart)
+ <programlisting>
+hostname0 = 'server1'
+ # Host name or IP address of pgpool node
+ # for watchdog connection
+ # (change requires restart)
+wd_port0 = 9000
+ # Port number for watchdog service
+ # (change requires restart)
+pgpool_port0 = 9999
+ # Port number for pgpool
+ # (change requires restart)
- heartbeat_destination1 = 'server3'
- heartbeat_destination_port1 = 9694
- heartbeat_device1 = ''
- </programlisting>
- </listitem>
- <listitem>
- <para>
- <literal>server3</literal>
- </para>
- <programlisting>
- heartbeat_destination0 = 'server1'
- # Host name or IP address of destination 0
- # for sending heartbeat signal.
- # (change requires restart)
- heartbeat_destination_port0 = 9694
- # Port number of destination 0 for sending
- # heartbeat signal. Usually this is the
- # same as wd_heartbeat_port.
- # (change requires restart)
- heartbeat_device0 = ''
- # Name of NIC device (such like 'eth0')
- # used for sending/receiving heartbeat
- # signal to/from destination 0.
- # This works only when this is not empty
- # and pgpool has root privilege.
- # (change requires restart)
+hostname1 = 'server2'
+wd_port1 = 9000
+pgpool_port1 = 9999
- heartbeat_destination1 = 'server2'
- heartbeat_destination_port1 = 9694
- heartbeat_device1 = ''
- </programlisting>
- </listitem>
- </itemizedlist>
- </sect3>
+hostname2 = 'server3'
+wd_port2 = 9000
+pgpool_port2 = 9999
+ </programlisting>
- <sect3 id="example-cluster-pgpool-config-sysconfig">
- <title>/etc/sysconfig/pgpool Configuration</title>
<para>
- If you want to ignore the <filename>pgpool_status</filename> file at startup of
- <productname>Pgpool-II</productname>, add "- D" to the start option OPTS to
- <filename>/etc/sysconfig/pgpool</filename>.
+ Specify all <productname>Pgpool-II</productname> nodes information for sending and receiving heartbeat signal.
</para>
<programlisting>
- [all servers]# vi /etc/sysconfig/pgpool
- ...
- OPTS=" -D -n"
+heartbeat_hostname0 = 'server1'
+ # Host name or IP address used
+ # for sending heartbeat signal.
+ # (change requires restart)
+heartbeat_port0 = 9694
+ # Port number used for receiving/sending heartbeat signal
+ # Usually this is the same as heartbeat_portX.
+ # (change requires restart)
+heartbeat_device0 = ''
+ # Name of NIC device (such like 'eth0')
+ # used for sending/receiving heartbeat
+ # signal to/from destination 0.
+ # This works only when this is not empty
+ # and pgpool has root privilege.
+ # (change requires restart)
+
+heartbeat_hostname1 = 'server2'
+heartbeat_port1 = 9694
+heartbeat_device1 = ''
+heartbeat_hostname2 = 'server3'
+heartbeat_port2 = 9694
+heartbeat_device2 = ''
</programlisting>
+ <note>
+ <para>
+ If use_watchdog = on, please make sure the pgpool node number is specified
+ in <filename>pgpool_node_id</filename> file.
+ See <xref linkend="example-cluster-pgpool-node-id"> for details.
+ </para>
+ </note>
</sect3>
<sect3 id="example-cluster-pgpool-config-log">
<title>Logging</title>
<para>
- In the example, we output <productname>Pgpool-II</productname>'s log to <literal>syslog</literal>.
- </para>
- <programlisting>
- log_destination = 'syslog'
- # Where to log
- # Valid values are combinations of stderr,
- # and syslog. Default to stderr.
-
- syslog_facility = 'LOCAL1'
- # Syslog local facility. Default to LOCAL0
- </programlisting>
- <para>
- Create <productname>Pgpool-II</productname> log file.
- </para>
- <programlisting>
- [all servers]# mkdir /var/log/pgpool-II
- [all servers]# touch /var/log/pgpool-II/pgpool.log
- </programlisting>
- <para>
- Edit config file of syslog <filename>/etc/rsyslog.conf</filename>.
- </para>
- <programlisting>
- [all servers]# vi /etc/rsyslog.conf
- ...
- *.info;mail.none;authpriv.none;cron.none;LOCAL1.none /var/log/messages
- LOCAL1.* /var/log/pgpool-II/pgpool.log
- </programlisting>
- <para>
- Setting logrotate same as <filename>/var/log/messages</filename>.
+ Since Pgpool-II 4.2, the logging collector process has been implemented.
+ In the example, we enable logging collector.
</para>
<programlisting>
- [all servers]# vi /etc/logrotate.d/syslog
- ...
- /var/log/messages
- /var/log/pgpool-II/pgpool.log
- /var/log/secure
+log_destination = 'stderr'
+logging_collector = on
+log_directory = '/var/log/pgpool_log'
+log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
+log_truncate_on_rotation = on
+log_rotation_age = 1d
+log_rotation_size = 10MB
</programlisting>
-
<para>
- Restart rsyslog service.
+ Create the log directory on all servers.
</para>
<programlisting>
- [all servers]# systemctl restart rsyslog
+[all servers]# mkdir /var/log/pgpool_log/
+[all servers]# chown postgres:postgres /var/log/pgpool_log/
</programlisting>
- </sect3>
- <sect3 id="example-cluster-pgpool-config-pcp">
- <title>PCP Command Configuration</title>
- <para>
- Since user authentication is required to use the <literal>PCP</literal> command,
- specify user name and md5 encrypted password in <filename>pcp.conf</filename>.
- Here we create the encrypted password for <literal>pgpool</literal> user, and add
- "<literal>username:encrypted password</literal>" in <filename>/etc/pgpool-II/pcp.conf</filename>.
- </para>
- <programlisting>
- [all servers]# echo 'pgpool:'`pg_md5 PCP passowrd` >> /etc/pgpool-II/pcp.conf
- </programlisting>
- </sect3>
-
- <sect3 id="example-cluster-pgpool-config-pcppass">
- <title>.pcppass</title>
- <para>
- Since follow_primary_command script has to execute PCP command without entering the
- password, we create <filename>.pcppass</filename> in the home directory of
- <productname>Pgpool-II</productname> startup user (root user).
- </para>
- <programlisting>
- [all servers]# echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass
- [all servers]# chmod 600 ~/.pcppass
- </programlisting>
- <para>
- The settings of <productname>Pgpool-II</productname> is completed.
- </para>
+ <para>
+ The configuration of <filename>pgpool.conf</filename> on server1 is completed. Copy the <filename>pgpool.conf</filename>
+ to other <productname>Pgpool-II</productname> nodes (server2 and server3).
+ </para>
+ <programlisting>
+[server1]# scp -p /etc/pgpool-II/pgpool.conf root@server2:/etc/pgpool-II/pgpool.conf
+[server1]# scp -p /etc/pgpool-II/pgpool.conf root@server3:/etc/pgpool-II/pgpool.conf
+ </programlisting>
</sect3>
+ </sect2>
+ <sect2 id="example-cluster-pgpool-config-sysconfig">
+ <title>/etc/sysconfig/pgpool Configuration</title>
+ <para>
+ When starting <productname>Pgpool-II</productname>, if the <filename>pgpool_status</filename>
+ file exists, <productname>Pgpool-II</productname> will read the backend status (up/down) from the
+ <filename>pgpool_status</filename> file.
+ </para>
+ <para>
+ If you want to ignore the <filename>pgpool_status</filename> file at startup of
+ <productname>Pgpool-II</productname>, add "- D" to the start option OPTS to
+ <filename>/etc/sysconfig/pgpool</filename>.
+ </para>
+ <programlisting>
+[all servers]# vi /etc/sysconfig/pgpool
+...
+OPTS=" -D -n"
+ </programlisting>
</sect2>
<sect2 id="example-cluster-start-stop">
diff --git a/src/sample/scripts/failover.sh.sample b/src/sample/scripts/failover.sh.sample
index 8b4f46072..9e0ec0c66 100755
--- a/src/sample/scripts/failover.sh.sample
+++ b/src/sample/scripts/failover.sh.sample
@@ -2,7 +2,7 @@
# This script is run by failover_command.
set -o xtrace
-exec > >(logger -i -p local1.info) 2>&1
+exec > >(logger -i -t pgpool) 2>&1
# Special values:
# %d = failed node id
@@ -32,14 +32,14 @@ NEW_MAIN_NODE_PGDATA="${10}"
OLD_PRIMARY_NODE_HOST="${11}"
OLD_PRIMARY_NODE_PORT="${12}"
-PGHOME=/usr/pgsql-11
+PGHOME=/usr/pgsql-13
-logger -i -p local1.info failover.sh: start: failed_node_id=$FAILED_NODE_ID old_primary_node_id=$OLD_PRIMARY_NODE_ID failed_host=$FAILED_NODE_HOST new_main_host=$NEW_MAIN_NODE_HOST
+echo failover.sh: start: failed_node_id=$FAILED_NODE_ID old_primary_node_id=$OLD_PRIMARY_NODE_ID failed_host=$FAILED_NODE_HOST new_main_host=$NEW_MAIN_NODE_HOST
## If there's no main node anymore, skip failover.
if [ $NEW_MAIN_NODE_ID -lt 0 ]; then
- logger -i -p local1.info failover.sh: All nodes are down. Skipping failover.
+ echo failover.sh: All nodes are down. Skipping failover.
exit 0
fi
@@ -47,36 +47,37 @@ fi
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MAIN_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
if [ $? -ne 0 ]; then
- logger -i -p local1.info failover.sh: passwrodless SSH to postgres@${NEW_MAIN_NODE_HOST} failed. Please setup passwrodless SSH.
+ echo failover.sh: passwrodless SSH to postgres@${NEW_MAIN_NODE_HOST} failed. Please setup passwrodless SSH.
exit 1
fi
## If Standby node is down, skip failover.
if [ $FAILED_NODE_ID -ne $OLD_PRIMARY_NODE_ID ]; then
- logger -i -p local1.info failover.sh: Standby node is down. Skipping failover.
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$OLD_PRIMARY_NODE_HOST -i ~/.ssh/id_rsa_pgpool "
${PGHOME}/bin/psql -p $OLD_PRIMARY_NODE_PORT -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')\"
"
if [ $? -ne 0 ]; then
- logger -i -p local1.error failover.sh: drop replication slot "${FAILED_NODE_HOST}" failed
+ echo failover.sh: Standby node is down. Skipping failover.
+ echo failover.sh: end: drop replication slot "${FAILED_NODE_HOST}" failed
exit 1
fi
+ echo failover.sh: end: Standby node is down. Skipping failover.
exit 0
fi
## Promote Standby node.
-logger -i -p local1.info failover.sh: Primary node is down, promote standby node ${NEW_MAIN_NODE_HOST}.
+echo failover.sh: Primary node is down, promote standby node ${NEW_MAIN_NODE_HOST}.
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
postgres@${NEW_MAIN_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -D ${NEW_MAIN_NODE_PGDATA} -w promote
if [ $? -ne 0 ]; then
- logger -i -p local1.error failover.sh: new_main_host=$NEW_MAIN_NODE_HOST promote failed
+ echo failover.sh: end: failover failed
exit 1
fi
-logger -i -p local1.info failover.sh: end: new_main_node_id=$NEW_MAIN_NODE_ID started as the primary node
+echo failover.sh: end: new_main_node_id=$NEW_MAIN_NODE_ID is promoted to a primary
exit 0
diff --git a/src/sample/scripts/follow_master.sh.sample b/src/sample/scripts/follow_master.sh.sample
deleted file mode 100755
index 6d7c0ab0e..000000000
--- a/src/sample/scripts/follow_master.sh.sample
+++ /dev/null
@@ -1,171 +0,0 @@
-#!/bin/bash
-# This script is run after failover_command to synchronize the Standby with the new Primary.
-# First try pg_rewind. If pg_rewind failed, use pg_basebackup.
-
-set -o xtrace
-exec > >(logger -i -p local1.info) 2>&1
-
-# Special values:
-# %d = failed node id
-# %h = failed node hostname
-# %p = failed node port number
-# %D = failed node database cluster path
-# %m = new master node id
-# %H = new master node hostname
-# %M = old master node id
-# %P = old primary node id
-# %r = new master port number
-# %R = new master database cluster path
-# %N = old primary node hostname
-# %S = old primary node port number
-# %% = '%' character
-
-FAILED_NODE_ID="$1"
-FAILED_NODE_HOST="$2"
-FAILED_NODE_PORT="$3"
-FAILED_NODE_PGDATA="$4"
-NEW_MASTER_NODE_ID="$5"
-NEW_MASTER_NODE_HOST="$6"
-OLD_MASTER_NODE_ID="$7"
-OLD_PRIMARY_NODE_ID="$8"
-NEW_MASTER_NODE_PORT="$9"
-NEW_MASTER_NODE_PGDATA="${10}"
-
-PGHOME=/usr/pgsql-11
-ARCHIVEDIR=/var/lib/pgsql/archivedir
-REPLUSER=repl
-PCP_USER=pgpool
-PGPOOL_PATH=/usr/bin
-PCP_PORT=9898
-
-logger -i -p local1.info follow_master.sh: start: Standby node ${FAILED_NODE_ID}
-
-## Test passwrodless SSH
-ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
-
-if [ $? -ne 0 ]; then
- logger -i -p local1.info follow_master.sh: passwrodless SSH to postgres@${NEW_MASTER_NODE_HOST} failed. Please setup passwrodless SSH.
- exit 1
-fi
-
-## Get PostgreSQL major version
-PGVERSION=`${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'`
-
-if [ $PGVERSION -ge 12 ]; then
-RECOVERYCONF=${FAILED_NODE_PGDATA}/myrecovery.conf
-else
-RECOVERYCONF=${FAILED_NODE_PGDATA}/recovery.conf
-fi
-
-## Check the status of Standby
-ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
-postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -w -D ${FAILED_NODE_PGDATA} status
-
-
-## If Standby is running, synchronize it with the new Primary.
-if [ $? -eq 0 ]; then
-
- logger -i -p local1.info follow_master.sh: pg_rewind for $FAILED_NODE_ID
-
- # Create replication slot "${FAILED_NODE_HOST}"
- ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
- ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c \"SELECT pg_create_physical_replication_slot('${FAILED_NODE_HOST}');\"
- "
-
- ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
-
- set -o errexit
-
- ${PGHOME}/bin/pg_ctl -w -m f -D ${FAILED_NODE_PGDATA} stop
-
- cat > ${RECOVERYCONF} << EOT
-primary_conninfo = 'host=${NEW_MASTER_NODE_HOST} port=${NEW_MASTER_NODE_PORT} user=${REPLUSER} application_name=${FAILED_NODE_HOST} passfile=''/var/lib/pgsql/.pgpass'''
-recovery_target_timeline = 'latest'
-restore_command = 'scp ${NEW_MASTER_NODE_HOST}:${ARCHIVEDIR}/%f %p'
-primary_slot_name = '${FAILED_NODE_HOST}'
-EOT
-
- if [ ${PGVERSION} -ge 12 ]; then
- touch ${FAILED_NODE_PGDATA}/standby.signal
- else
- echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
- fi
-
- ${PGHOME}/bin/pg_rewind -D ${FAILED_NODE_PGDATA} --source-server=\"user=postgres host=${NEW_MASTER_NODE_HOST} port=${NEW_MASTER_NODE_PORT}\"
-
- "
-
- if [ $? -ne 0 ]; then
- logger -i -p local1.error follow_master.sh: end: pg_rewind failed. Try pg_basebackup.
-
- ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
-
- set -o errexit
-
- # Execute pg_basebackup
- rm -rf ${FAILED_NODE_PGDATA}
- rm -rf ${ARCHIVEDIR}/*
- ${PGHOME}/bin/pg_basebackup -h ${NEW_MASTER_NODE_HOST} -U $REPLUSER -p ${NEW_MASTER_NODE_PORT} -D ${FAILED_NODE_PGDATA} -X stream
-
- if [ ${PGVERSION} -ge 12 ]; then
- sed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \
- -e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${FAILED_NODE_PGDATA}/postgresql.conf
- fi
-
- cat > ${RECOVERYCONF} << EOT
-primary_conninfo = 'host=${NEW_MASTER_NODE_HOST} port=${NEW_MASTER_NODE_PORT} user=${REPLUSER} application_name=${FAILED_NODE_HOST} passfile=''/var/lib/pgsql/.pgpass'''
-recovery_target_timeline = 'latest'
-restore_command = 'scp ${NEW_MASTER_NODE_HOST}:${ARCHIVEDIR}/%f %p'
-primary_slot_name = '${FAILED_NODE_HOST}'
-EOT
-
- if [ ${PGVERSION} -ge 12 ]; then
- touch ${FAILED_NODE_PGDATA}/standby.signal
- else
- echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
- fi
- "
-
- if [ $? -ne 0 ]; then
- # drop replication slot
- ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
- ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')\"
- "
-
- logger -i -p local1.error follow_master.sh: end: pg_basebackup failed
- exit 1
- fi
- fi
-
- # start Standby node on ${FAILED_NODE_HOST}
- ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
- postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool $PGHOME/bin/pg_ctl -l /dev/null -w -D ${FAILED_NODE_PGDATA} start
-
- # If start Standby successfully, attach this node
- if [ $? -eq 0 ]; then
-
- # Run pcp_attact_node to attach Standby node to Pgpool-II.
- ${PGPOOL_PATH}/pcp_attach_node -w -h localhost -U $PCP_USER -p ${PCP_PORT} -n ${FAILED_NODE_ID}
-
- if [ $? -ne 0 ]; then
- logger -i -p local1.error follow_master.sh: end: pcp_attach_node failed
- exit 1
- fi
-
- # If start Standby failed, drop replication slot "${FAILED_NODE_HOST}"
- else
-
- ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool \
- ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c "SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')"
-
- logger -i -p local1.error follow_master.sh: end: follow master command failed
- exit 1
- fi
-
-else
- logger -i -p local1.info follow_master.sh: failed_nod_id=${FAILED_NODE_ID} is not running. skipping follow master command
- exit 0
-fi
-
-logger -i -p local1.info follow_master.sh: end: follow master command complete
-exit 0
diff --git a/src/sample/scripts/follow_primary.sh.sample b/src/sample/scripts/follow_primary.sh.sample
index 10939ca59..1ff0c0e69 100755
--- a/src/sample/scripts/follow_primary.sh.sample
+++ b/src/sample/scripts/follow_primary.sh.sample
@@ -3,7 +3,7 @@
# First try pg_rewind. If pg_rewind failed, use pg_basebackup.
set -o xtrace
-exec > >(logger -i -p local1.info) 2>&1
+exec > >(logger -i -t pgpool) 2>&1
# Special values:
# %d = failed node id
@@ -31,20 +31,20 @@ OLD_PRIMARY_NODE_ID="$8"
NEW_MAIN_NODE_PORT="$9"
NEW_MAIN_NODE_PGDATA="${10}"
-PGHOME=/usr/pgsql-11
+PGHOME=/usr/pgsql-13
ARCHIVEDIR=/var/lib/pgsql/archivedir
REPLUSER=repl
PCP_USER=pgpool
PGPOOL_PATH=/usr/bin
PCP_PORT=9898
-logger -i -p local1.info follow_primary.sh: start: Standby node ${FAILED_NODE_ID}
+echo follow_primary.sh: start: Standby node ${FAILED_NODE_ID}
## Test passwrodless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MAIN_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
if [ $? -ne 0 ]; then
- logger -i -p local1.info follow_main.sh: passwrodless SSH to postgres@${NEW_MAIN_NODE_HOST} failed. Please setup passwrodless SSH.
+ echo follow_main.sh: passwrodless SSH to postgres@${NEW_MAIN_NODE_HOST} failed. Please setup passwrodless SSH.
exit 1
fi
@@ -65,7 +65,7 @@ postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -w -D
## If Standby is running, synchronize it with the new Primary.
if [ $? -eq 0 ]; then
- logger -i -p local1.info follow_primary.sh: pg_rewind for $FAILED_NODE_ID
+ echo follow_primary.sh: pg_rewind for $FAILED_NODE_ID
# Create replication slot "${FAILED_NODE_HOST}"
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MAIN_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
@@ -96,7 +96,7 @@ EOT
"
if [ $? -ne 0 ]; then
- logger -i -p local1.error follow_primary.sh: end: pg_rewind failed. Try pg_basebackup.
+ echo follow_primary.sh: end: pg_rewind failed. Try pg_basebackup.
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${FAILED_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
@@ -132,7 +132,7 @@ EOT
${PGHOME}/bin/psql -p ${NEW_MAIN_NODE_PORT} -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')\"
"
- logger -i -p local1.error follow_primary.sh: end: pg_basebackup failed
+ echo follow_primary.sh: end: pg_basebackup failed
exit 1
fi
fi
@@ -148,7 +148,7 @@ EOT
${PGPOOL_PATH}/pcp_attach_node -w -h localhost -U $PCP_USER -p ${PCP_PORT} -n ${FAILED_NODE_ID}
if [ $? -ne 0 ]; then
- logger -i -p local1.error follow_primary.sh: end: pcp_attach_node failed
+ echo follow_primary.sh: end: pcp_attach_node failed
exit 1
fi
@@ -158,14 +158,14 @@ EOT
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MAIN_NODE_HOST} -i ~/.ssh/id_rsa_pgpool \
${PGHOME}/bin/psql -p ${NEW_MAIN_NODE_PORT} -c "SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')"
- logger -i -p local1.error follow_primary.sh: end: follow primary command failed
+ echo follow_primary.sh: end: follow primary command failed
exit 1
fi
else
- logger -i -p local1.info follow_primary.sh: failed_nod_id=${FAILED_NODE_ID} is not running. skipping follow primary command
+ echo follow_primary.sh: failed_nod_id=${FAILED_NODE_ID} is not running. skipping follow primary command
exit 0
fi
-logger -i -p local1.info follow_primary.sh: end: follow primary command complete
+echo follow_primary.sh: end: follow primary command is completed successfully
exit 0
diff --git a/src/sample/scripts/pgpool_remote_start.sample b/src/sample/scripts/pgpool_remote_start.sample
index cb0f7a5ca..ea00d1860 100755
--- a/src/sample/scripts/pgpool_remote_start.sample
+++ b/src/sample/scripts/pgpool_remote_start.sample
@@ -2,20 +2,20 @@
# This script is run after recovery_1st_stage to start Standby node.
set -o xtrace
-exec > >(logger -i -p local1.info) 2>&1
+exec > >(logger -i -t pgpool) 2>&1
DEST_NODE_HOST="$1"
DEST_NODE_PGDATA="$2"
-PGHOME=/usr/pgsql-11
+PGHOME=/usr/pgsql-13
-logger -i -p local1.info pgpool_remote_start: start: remote start Standby node $DEST_NODE_HOST
+echo pgpool_remote_start: start: remote start Standby node $DEST_NODE_HOST
## Test passwrodless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
if [ $? -ne 0 ]; then
- logger -i -p local1.info pgpool_remote_start: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
+ echo pgpool_remote_start: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
exit 1
fi
@@ -25,9 +25,9 @@ ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DES
"
if [ $? -ne 0 ]; then
- logger -i -p local1.error pgpool_remote_start: $DEST_NODE_HOST PostgreSQL start failed.
+ echo pgpool_remote_start: $DEST_NODE_HOST PostgreSQL start failed.
exit 1
fi
-logger -i -p local1.info pgpool_remote_start: end: $DEST_NODE_HOST PostgreSQL started successfully.
+echo pgpool_remote_start: end: PostgreSQL on $DEST_NODE_HOST is started successfully.
exit 0
diff --git a/src/sample/scripts/recovery_1st_stage.sample b/src/sample/scripts/recovery_1st_stage.sample
index 139d1ac74..0693936f8 100755
--- a/src/sample/scripts/recovery_1st_stage.sample
+++ b/src/sample/scripts/recovery_1st_stage.sample
@@ -2,7 +2,7 @@
# This script is executed by "recovery_1st_stage" to recovery a Standby node.
set -o xtrace
-exec > >(logger -i -p local1.info) 2>&1
+exec > >(logger -i -t pgpool) 2>&1
PRIMARY_NODE_PGDATA="$1"
DEST_NODE_HOST="$2"
@@ -12,17 +12,17 @@ DEST_NODE_ID="$5"
DEST_NODE_PORT="$6"
PRIMARY_NODE_HOST=$(hostname)
-PGHOME=/usr/pgsql-11
+PGHOME=/usr/pgsql-13
ARCHIVEDIR=/var/lib/pgsql/archivedir
REPLUSER=repl
-logger -i -p local1.info recovery_1st_stage: start: pg_basebackup for Standby node $DEST_NODE_ID
+echo recovery_1st_stage: start: pg_basebackup for Standby node $DEST_NODE_ID
## Test passwrodless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/null
if [ $? -ne 0 ]; then
- logger -i -p local1.info recovery_1st_stage: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
+ echo recovery_1st_stage: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
exit 1
fi
@@ -76,9 +76,9 @@ if [ $? -ne 0 ]; then
SELECT pg_drop_replication_slot('${DEST_NODE_HOST}');
EOQ
- logger -i -p local1.error recovery_1st_stage: end: pg_basebackup failed. online recovery failed
+ echo recovery_1st_stage: end: pg_basebackup failed. online recovery failed
exit 1
fi
-logger -i -p local1.info recovery_1st_stage: end: recovery_1st_stage complete
+echo recovery_1st_stage: end: recovery_1st_stage is completed successfully
exit 0
[parent: 57d8883d8fe0]