From a6a15205e41ed40acc0f9b813813c31367081242 Mon Sep 17 00:00:00 2001 From: Bo Peng Date: Tue, 11 Aug 2020 23:18:39 +0900 Subject: [PATCH] Doc: add sample script links in configuration example. --- doc.ja/src/sgml/example-cluster.sgml | 592 +++++---------------------- doc/src/sgml/example-cluster.sgml | 592 +++++---------------------- 2 files changed, 215 insertions(+), 969 deletions(-) diff --git a/doc.ja/src/sgml/example-cluster.sgml b/doc.ja/src/sgml/example-cluster.sgml index 33333ece4..96de84b7e 100644 --- a/doc.ja/src/sgml/example-cluster.sgml +++ b/doc.ja/src/sgml/example-cluster.sgml @@ -399,11 +399,11 @@ health_check_period = 5 - # Health check period - # Disabled (0) by default + # Health check period + # Disabled (0) by default health_check_timeout = 30 - # Health check timeout - # 0 means no timeout + # Health check timeout + # 0 means no timeout health_check_user = 'pgpool' health_check_password = '' @@ -416,16 +416,16 @@ # - Backend Connection Settings - backend_hostname0 = 'server1' - # Host name or IP address to connect to for backend 0 + # Host name or IP address to connect to for backend 0 backend_port0 = 5432 - # Port number for backend 0 + # Port number for backend 0 backend_weight0 = 1 - # Weight for backend 0 (only in load balancing mode) + # Weight for backend 0 (only in load balancing mode) backend_data_directory0 = '/var/lib/pgsql/11/data' - # Data directory for backend 0 + # Data directory for backend 0 backend_flag0 = 'ALLOW_TO_FAILOVER' - # Controls various backend behavior - # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER + # Controls various backend behavior + # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER backend_hostname1 = 'server2' backend_port1 = 5432 backend_weight1 = 1 @@ -472,287 +472,27 @@ - /etc/pgpool-II/failover.sh及び/etc/pgpool-II/follow_master.shを作成し、実行権限を与えておきます。 + サンプルスクリプトfailover.sh及びfollow_master.shは + /etc/pgpool-II/配下にインストールされていますので、これらのファイルをコピーして作成します。 - # vi /etc/pgpool-II/failover.sh - # vi /etc/pgpool-II/follow_master.sh - # chmod +x /etc/pgpool-II/{failover.sh,follow_master.sh} + # cp /etc/pgpool-II/failover.sh{.sample,} + # cp /etc/pgpool-II/follow_master.sh{.sample,} + + + 基本的にはPGHOMEを環境に合わせて変更すれば、動作します。 + + + [server1]# vi /etc/pgpool-II/failover.sh + ... + PGHOME=/usr/pgsql-11 + ... + + [server1]# vi /etc/pgpool-II/follow_master.sh + ... + PGHOME=/usr/pgsql-11 + ... - - - - - /etc/pgpool-II/failover.sh - - -#!/bin/bash -# This script is run by failover_command. - -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}" -OLD_PRIMARY_NODE_HOST="${11}" -OLD_PRIMARY_NODE_PORT="${12}" - -PGHOME=/usr/pgsql-11 - - -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_master_host=$NEW_MASTER_NODE_HOST - -## If there's no master node anymore, skip failover. -if [ $NEW_MASTER_NODE_ID -lt 0 ]; then - logger -i -p local1.info failover.sh: All nodes are down. Skipping failover. - exit 0 -fi - -## 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 failover.sh: passwrodless SSH to postgres@${NEW_MASTER_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 - exit 1 - fi - - exit 0 -fi - -## Promote Standby node. -logger -i -p local1.info failover.sh: Primary node is down, promote standby node ${NEW_MASTER_NODE_HOST}. - -ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \ - postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -D ${NEW_MASTER_NODE_PGDATA} -w promote - -if [ $? -ne 0 ]; then - logger -i -p local1.error failover.sh: new_master_host=$NEW_MASTER_NODE_HOST promote failed - exit 1 -fi - -logger -i -p local1.info failover.sh: end: new_master_node_id=$NEW_MASTER_NODE_ID started as the primary node -exit 0 - - - - - - - - /etc/pgpool-II/follow_master.sh - - -#!/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 - - - - @@ -760,7 +500,6 @@ exit 0 続いて、オンラインリカバリを行うためのPostgreSQLのユーザ名およびオンラインリカバリ時に呼び出されるコマンドrecovery_1st_stageを設定します。 オンラインリカバリで実行されるpgpool_recovery関数はPostgreSQLのスーパーユーザ権限が必要なため、recovery_userにスーパーユーザを指定しなければなりません。ここでは、postrgesユーザを指定します。 - オンラインリカバリ用のスクリプトrecovery_1st_stage、pgpool_remote_startをプライマリサーバ(server1)のデータベースクラスタ配下に配置し、実行権限を与えておきます。 recovery_user = 'postgres' @@ -770,147 +509,30 @@ exit 0 recovery_1st_stage_command = 'recovery_1st_stage' + + オンラインリカバリ用のサンプルスクリプトrecovery_1st_stage + 及びpgpool_remote_startは + /etc/pgpool-II/配下にインストールされていますので、これらのファイルをプライマリサーバ(server1)のデータベースクラスタ配下に配置します。 + - [server1]# su - postgres - [server1]$ vi /var/lib/pgsql/11/data/recovery_1st_stage - [server1]$ vi /var/lib/pgsql/11/data/pgpool_remote_start - [server1]$ chmod +x /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start} + [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} + + + 基本的にはPGHOMEを環境に合わせて変更すれば、動作します。 + + + [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 + ... - - - - - /var/lib/pgsql/11/data/recovery_1st_stage - - -#!/bin/bash -# This script is executed by "recovery_1st_stage" to recovery a Standby node. - -set -o xtrace -exec > >(logger -i -p local1.info) 2>&1 - -PRIMARY_NODE_PGDATA="$1" -DEST_NODE_HOST="$2" -DEST_NODE_PGDATA="$3" -PRIMARY_NODE_PORT="$4" -DEST_NODE_ID="$5" -DEST_NODE_PORT="$6" - -PRIMARY_NODE_HOST=$(hostname) -PGHOME=/usr/pgsql-11 -ARCHIVEDIR=/var/lib/pgsql/archivedir -REPLUSER=repl - -logger -i -p local1.info 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. - 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=${DEST_NODE_PGDATA}/myrecovery.conf -else - RECOVERYCONF=${DEST_NODE_PGDATA}/recovery.conf -fi - -## Create replication slot "${DEST_NODE_HOST}" -${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ -SELECT pg_create_physical_replication_slot('${DEST_NODE_HOST}'); -EOQ - -## Execute pg_basebackup to recovery Standby node -ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool " - - set -o errexit - - rm -rf $DEST_NODE_PGDATA - rm -rf $ARCHIVEDIR/* - - ${PGHOME}/bin/pg_basebackup -h $PRIMARY_NODE_HOST -U $REPLUSER -p $PRIMARY_NODE_PORT -D $DEST_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\" ${DEST_NODE_PGDATA}/postgresql.conf - fi - - cat > ${RECOVERYCONF} << EOT -primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${DEST_NODE_HOST} passfile=''/var/lib/pgsql/.pgpass''' -recovery_target_timeline = 'latest' -restore_command = 'scp ${PRIMARY_NODE_HOST}:${ARCHIVEDIR}/%f %p' -primary_slot_name = '${DEST_NODE_HOST}' -EOT - - if [ ${PGVERSION} -ge 12 ]; then - touch ${DEST_NODE_PGDATA}/standby.signal - else - echo \"standby_mode = 'on'\" >> ${RECOVERYCONF} - fi - - sed -i \"s/#*port = .*/port = ${DEST_NODE_PORT}/\" ${DEST_NODE_PGDATA}/postgresql.conf -" - -if [ $? -ne 0 ]; then - - ${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ -SELECT pg_drop_replication_slot('${DEST_NODE_HOST}'); -EOQ - - logger -i -p local1.error 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 -exit 0 - - - - - - /var/lib/pgsql/11/data/pgpool_remote_start - - -#!/bin/bash -# This script is run after recovery_1st_stage to start Standby node. - -set -o xtrace -exec > >(logger -i -p local1.info) 2>&1 - -PGHOME=/usr/pgsql-11 -DEST_NODE_HOST="$1" -DEST_NODE_PGDATA="$2" - - -logger -i -p local1.info 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. - exit 1 -fi - -## Start Standby node -ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool " - $PGHOME/bin/pg_ctl -l /dev/null -w -D $DEST_NODE_PGDATA start -" - -if [ $? -ne 0 ]; then - logger -i -p local1.error 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. -exit 0 - - - また、オンラインリカバリ機能を使用するには、pgpool_recovery、pgpool_remote_start、pgpool_switch_xlogという関数が必要になるので、server1のtemplate1にpgpool_recoveryをインストールしておきます。 @@ -1060,14 +682,14 @@ arping_path = '/usr/sbin' # - Other pgpool Connection Settings - other_pgpool_hostname0 = 'server2' - # Host name or IP address to connect to for other pgpool 0 - # (change requires restart) + # 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) + # Port number for other pgpool 0 + # (change requires restart) other_wd_port0 = 9000 - # Port number for other watchdog 0 - # (change requires restart) + # Port number for other watchdog 0 + # (change requires restart) other_pgpool_hostname1 = 'server3' other_pgpool_port1 = 9999 other_wd_port1 = 9000 @@ -1081,14 +703,14 @@ arping_path = '/usr/sbin' # - Other pgpool Connection Settings - other_pgpool_hostname0 = 'server1' - # Host name or IP address to connect to for other pgpool 0 - # (change requires restart) + # 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) + # Port number for other pgpool 0 + # (change requires restart) other_wd_port0 = 9000 - # Port number for other watchdog 0 - # (change requires restart) + # Port number for other watchdog 0 + # (change requires restart) other_pgpool_hostname1 = 'server3' other_pgpool_port1 = 9999 other_wd_port1 = 9000 @@ -1102,14 +724,14 @@ arping_path = '/usr/sbin' # - Other pgpool Connection Settings - other_pgpool_hostname0 = 'server1' - # Host name or IP address to connect to for other pgpool 0 - # (change requires restart) + # 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) + # Port number for other pgpool 0 + # (change requires restart) other_wd_port0 = 9000 - # Port number for other watchdog 0 - # (change requires restart) + # Port number for other watchdog 0 + # (change requires restart) other_pgpool_hostname1 = 'server2' other_pgpool_port1 = 9999 other_wd_port1 = 9000 @@ -1127,21 +749,21 @@ arping_path = '/usr/sbin' heartbeat_destination0 = 'server2' - # Host name or IP address of destination 0 - # for sending heartbeat signal. - # (change requires restart) + # 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) + # 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) + # 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 @@ -1155,21 +777,21 @@ arping_path = '/usr/sbin' heartbeat_destination0 = 'server1' - # Host name or IP address of destination 0 - # for sending heartbeat signal. - # (change requires restart) + # 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) + # 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) + # 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 @@ -1183,21 +805,21 @@ arping_path = '/usr/sbin' heartbeat_destination0 = 'server1' - # Host name or IP address of destination 0 - # for sending heartbeat signal. - # (change requires restart) + # 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) + # 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) + # 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 diff --git a/doc/src/sgml/example-cluster.sgml b/doc/src/sgml/example-cluster.sgml index b70899e73..374f6187c 100644 --- a/doc/src/sgml/example-cluster.sgml +++ b/doc/src/sgml/example-cluster.sgml @@ -422,11 +422,11 @@ health_check_period = 5 - # Health check period - # Disabled (0) by default + # Health check period + # Disabled (0) by default health_check_timeout = 30 - # Health check timeout - # 0 means no timeout + # Health check timeout + # 0 means no timeout health_check_user = 'pgpool' health_check_password = '' @@ -440,16 +440,16 @@ # - Backend Connection Settings - backend_hostname0 = 'server1' - # Host name or IP address to connect to for backend 0 + # Host name or IP address to connect to for backend 0 backend_port0 = 5432 - # Port number for backend 0 + # Port number for backend 0 backend_weight0 = 1 - # Weight for backend 0 (only in load balancing mode) + # Weight for backend 0 (only in load balancing mode) backend_data_directory0 = '/var/lib/pgsql/11/data' - # Data directory for backend 0 + # Data directory for backend 0 backend_flag0 = 'ALLOW_TO_FAILOVER' - # Controls various backend behavior - # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER + # Controls various backend behavior + # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER backend_hostname1 = 'server2' backend_port1 = 5432 backend_weight1 = 1 @@ -501,287 +501,28 @@ - Create /etc/pgpool-II/failover.sh, and add execute permission. + Sample scripts failover.sh + and follow_master.sh + are installed in /etc/pgpool-II/. Create failover scripts using these sample files. - # vi /etc/pgpool-II/failover.sh - # vi /etc/pgpool-II/follow_master.sh - # chmod +x /etc/pgpool-II/{failover.sh,follow_master.sh} + # cp /etc/pgpool-II/failover.sh{.sample,} + # cp /etc/pgpool-II/follow_master.sh{.sample,} + + Basically, it should work if you change PGHOME according to PostgreSQL installation directory. + + + [server1]# vi /etc/pgpool-II/failover.sh + ... + PGHOME=/usr/pgsql-11 + ... - - - - /etc/pgpool-II/failover.sh - - -#!/bin/bash -# This script is run by failover_command. - -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}" -OLD_PRIMARY_NODE_HOST="${11}" -OLD_PRIMARY_NODE_PORT="${12}" - -PGHOME=/usr/pgsql-11 - - -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_master_host=$NEW_MASTER_NODE_HOST - -## If there's no master node anymore, skip failover. -if [ $NEW_MASTER_NODE_ID -lt 0 ]; then - logger -i -p local1.info failover.sh: All nodes are down. Skipping failover. - exit 0 -fi - -## 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 failover.sh: passwrodless SSH to postgres@${NEW_MASTER_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 - exit 1 - fi - - exit 0 -fi - -## Promote Standby node. -logger -i -p local1.info failover.sh: Primary node is down, promote standby node ${NEW_MASTER_NODE_HOST}. - -ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \ - postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -D ${NEW_MASTER_NODE_PGDATA} -w promote - -if [ $? -ne 0 ]; then - logger -i -p local1.error failover.sh: new_master_host=$NEW_MASTER_NODE_HOST promote failed - exit 1 -fi - -logger -i -p local1.info failover.sh: end: new_master_node_id=$NEW_MASTER_NODE_ID started as the primary node -exit 0 - - - - - - - - /etc/pgpool-II/follow_master.sh - - -#!/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 - - - - + [server1]# vi /etc/pgpool-II/follow_master.sh + ... + PGHOME=/usr/pgsql-11 + ... + @@ -798,153 +539,36 @@ exit 0 recovery_user = 'postgres' - # Online recovery user + # Online recovery user recovery_password = '' - # Online recovery password + # Online recovery password recovery_1st_stage_command = 'recovery_1st_stage' + + Online recovery sample scriptsrecovery_1st_stage + and pgpool_remote_start + are installed in /etc/pgpool-II/. Copy these files to the data directory of the primary server (server1). + - [server1]# su - postgres - [server1]$ vi /var/lib/pgsql/11/data/recovery_1st_stage - [server1]$ vi /var/lib/pgsql/11/data/pgpool_remote_start - [server1]$ chmod +x /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start} + [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} + + Basically, it should work if you change PGHOME according to PostgreSQL installation directory. + + + [server1]# vi /var/lib/pgsql/11/data/recovery_1st_stage + ... + PGHOME=/usr/pgsql-11 + ... - - - - /var/lib/pgsql/11/data/recovery_1st_stage - - -#!/bin/bash -# This script is executed by "recovery_1st_stage" to recovery a Standby node. - -set -o xtrace -exec > >(logger -i -p local1.info) 2>&1 - -PRIMARY_NODE_PGDATA="$1" -DEST_NODE_HOST="$2" -DEST_NODE_PGDATA="$3" -PRIMARY_NODE_PORT="$4" -DEST_NODE_ID="$5" -DEST_NODE_PORT="$6" - -PRIMARY_NODE_HOST=$(hostname) -PGHOME=/usr/pgsql-11 -ARCHIVEDIR=/var/lib/pgsql/archivedir -REPLUSER=repl - -logger -i -p local1.info 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. - 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=${DEST_NODE_PGDATA}/myrecovery.conf -else - RECOVERYCONF=${DEST_NODE_PGDATA}/recovery.conf -fi - -## Create replication slot "${DEST_NODE_HOST}" -${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ -SELECT pg_create_physical_replication_slot('${DEST_NODE_HOST}'); -EOQ - -## Execute pg_basebackup to recovery Standby node -ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool " - - set -o errexit - - rm -rf $DEST_NODE_PGDATA - rm -rf $ARCHIVEDIR/* - - ${PGHOME}/bin/pg_basebackup -h $PRIMARY_NODE_HOST -U $REPLUSER -p $PRIMARY_NODE_PORT -D $DEST_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\" ${DEST_NODE_PGDATA}/postgresql.conf - fi - - cat > ${RECOVERYCONF} << EOT -primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${DEST_NODE_HOST} passfile=''/var/lib/pgsql/.pgpass''' -recovery_target_timeline = 'latest' -restore_command = 'scp ${PRIMARY_NODE_HOST}:${ARCHIVEDIR}/%f %p' -primary_slot_name = '${DEST_NODE_HOST}' -EOT - - if [ ${PGVERSION} -ge 12 ]; then - touch ${DEST_NODE_PGDATA}/standby.signal - else - echo \"standby_mode = 'on'\" >> ${RECOVERYCONF} - fi - - sed -i \"s/#*port = .*/port = ${DEST_NODE_PORT}/\" ${DEST_NODE_PGDATA}/postgresql.conf -" - -if [ $? -ne 0 ]; then - - ${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ -SELECT pg_drop_replication_slot('${DEST_NODE_HOST}'); -EOQ - - logger -i -p local1.error 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 -exit 0 - - - - - - /var/lib/pgsql/11/data/pgpool_remote_start - - -#!/bin/bash -# This script is run after recovery_1st_stage to start Standby node. - -set -o xtrace -exec > >(logger -i -p local1.info) 2>&1 - -PGHOME=/usr/pgsql-11 -DEST_NODE_HOST="$1" -DEST_NODE_PGDATA="$2" - - -logger -i -p local1.info 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. - exit 1 -fi - -## Start Standby node -ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool " - $PGHOME/bin/pg_ctl -l /dev/null -w -D $DEST_NODE_PGDATA start -" - -if [ $? -ne 0 ]; then - logger -i -p local1.error 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. -exit 0 - - - + [server1]# vi /var/lib/pgsql/11/data/pgpool_remote_start + ... + PGHOME=/usr/pgsql-11 + ... + In order to use the online recovery functionality, the functions of @@ -1112,14 +736,14 @@ arping_path = '/usr/sbin' # - Other pgpool Connection Settings - other_pgpool_hostname0 = 'server2' - # Host name or IP address to connect to for other pgpool 0 - # (change requires restart) + # 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) + # Port number for other pgpool 0 + # (change requires restart) other_wd_port0 = 9000 - # Port number for other watchdog 0 - # (change requires restart) + # Port number for other watchdog 0 + # (change requires restart) other_pgpool_hostname1 = 'server3' other_pgpool_port1 = 9999 other_wd_port1 = 9000 @@ -1133,14 +757,14 @@ arping_path = '/usr/sbin' # - Other pgpool Connection Settings - other_pgpool_hostname0 = 'server1' - # Host name or IP address to connect to for other pgpool 0 - # (change requires restart) + # 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) + # Port number for other pgpool 0 + # (change requires restart) other_wd_port0 = 9000 - # Port number for other watchdog 0 - # (change requires restart) + # Port number for other watchdog 0 + # (change requires restart) other_pgpool_hostname1 = 'server3' other_pgpool_port1 = 9999 other_wd_port1 = 9000 @@ -1154,14 +778,14 @@ arping_path = '/usr/sbin' # - Other pgpool Connection Settings - other_pgpool_hostname0 = 'server1' - # Host name or IP address to connect to for other pgpool 0 - # (change requires restart) + # 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) + # Port number for other pgpool 0 + # (change requires restart) other_wd_port0 = 9000 - # Port number for other watchdog 0 - # (change requires restart) + # Port number for other watchdog 0 + # (change requires restart) other_pgpool_hostname1 = 'server2' other_pgpool_port1 = 9999 other_wd_port1 = 9000 @@ -1180,21 +804,21 @@ arping_path = '/usr/sbin' heartbeat_destination0 = 'server2' - # Host name or IP address of destination 0 - # for sending heartbeat signal. - # (change requires restart) + # 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) + # 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) + # 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 @@ -1207,21 +831,21 @@ arping_path = '/usr/sbin' heartbeat_destination0 = 'server1' - # Host name or IP address of destination 0 - # for sending heartbeat signal. - # (change requires restart) + # 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) + # 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) + # 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 @@ -1234,21 +858,21 @@ arping_path = '/usr/sbin' heartbeat_destination0 = 'server1' - # Host name or IP address of destination 0 - # for sending heartbeat signal. - # (change requires restart) + # 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) + # 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) + # 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 -- 2.39.5