pgpool online recovery scripts

pgpool_recovery_1st_stage.sh

#!/bin/bash

set -e

SSH=/usr/bin/ssh
PSQL=/usr/bin/psql
PGBASEBACKUP=/usr/bin/pg_basebackup
REPLICATION_USER=replication

if [ -z "$MASTER_HOST" ]; then
    MASTER_HOST=db01
fi
if [ -z "$REPLICATION_USER" ]; then
    REPLICATION_USER=replication
fi


master_cluster_path=$1
recovery_host=$2
slave_cluster_path=$3

# start basebackup
prefix=$(date +%Y%m%d-%H%M%S)
$SSH -T $recovery_host "if [ -d $slave_cluster_path ]; then mv $slave_cluster_path $slave_cluster_path.$prefix; fi"
$SSH -T $recovery_host "$PGBASEBACKUP -h $MASTER_HOST -U $REPLICATION_USER -D $slave_cluster_path --progress --xlog"

# copy recovery.conf
$SSH -T $recovery_host "cp -p ~/{recovery.conf,recovery-replication.conf} $slave_cluster_path/"

echo "recovery 1st stage. done"

pgpool_recovery_2nd_stage.sh

#!/bin/bash

set -e

SSH=/usr/bin/ssh
PSQL=/usr/bin/psql
RSYNC=/usr/bin/rsync
ARCHIVE_DIR=/data/pgsql/archlog

master_cluster_path=$1
recovery_host=$2
slave_cluster_path=$3

$PSQL -c 'SELECT pg_switch_xlog()' postgres

$RSYNC -avx --delete $ARCHIVE_DIR/ $recovery_host:$ARCHIVE_DIR/
#$SSH -T $recovery_host "rm -fvr $ARCHIVE_DIR/*"
#$SSH -T $recovery_host "$RSYNC -avx --delete --exclude=pg_xlog --exclude=postmaster.pid $slave_cluster_path/ /data/pgsql/data/"

echo "recovery 2nd stage. done"

pgpool_remote_start

#!/bin/bash

PGCTL=/usr/bin/pg_ctl
PGVERSION=9.3
POSTGRES_CONF=/etc/postgresql/$PGVERSION/main/postgresql.conf
DEVNULL=/dev/null

if [ $# -ne 2 ]; then
    echo "pgpool_remote_start remote_host remote_datadir"
    exit 1
fi

recovery_host=$1
slave_cluster_path=$2

ssh -T $recovery_host "$PGCTL start -w -o \"-c 'config_file=$POSTGRES_CONF'\" -D $slave_cluster_path >$DEVNULL 2>&1 <$DEVNULL"
echo "$recovery_host postgresql start"