Re: Wal archive way behind in streaming replication

I haven't been following this thread, but FWIW, here is a simple bash
script I use to rebuild a hot standby when I'm testing.
It assumes that the master is correctly set up for replication, that the
port is 5432. and that the replication user is named "replication".
Runs on CentOS, haven't tried it on any other distro.

This is a script I call /root/recreate_standby.sh, and gets run as root.
This is optional, if you run it as postgres. remove the "sudo su - postgres
-c" sections.

Watch out for word-wrapping from the email client.
------------

#!/bin/bash

## This script runs on the standby.
## Executed as root, else remove the "sudo - postgres -c" commands.
## Assumes you have a valid recovery.conf saved at
## $PGDATA/../recovery.conf.bkp

export PGDATA=/path/to/data/dir ## Must be set correctly
export PGPORT=5432
export MASTER=192.168.x.x ## IP or host entry for the master
Postgresql server
export PGBIN=/usr/pgsql-9.3/bin

service postgresql-9.3 stop -m immediate

if [ $? != 0 ]; then
service postgresql-9.3 start
echo "Could not shut down PostgreSQL. Aborting."
exit 1
fi

rm -rf $PGDATA

if [ $? != 0 ]; then
echo "Could not remove the PostgreSQL $PGDATA dir. Aborting."
exit 1
fi

## If the replication role is not set to "trust" in the master's
## pg_hba.conf file, the password will need to be passed into the command
below,
## and "--no-password" will need to be removed or revised to be "--password"
su - postgres -c "$PGBIN/pg_basebackup --pgdata=$PGDATA --host=$MASTER
--port=$PGPORT --username=replication --no-password --xlog-method=stream
--format=plain --progress --verbose"

su - postgres -c "cp -p $PGDATA/../recovery.conf.bkp $PGDATA/recovery.conf"

service postgresql-9.3 start

su - postgres -c "$PGBIN/pg_isready -U postgres -p $PGPORT -t2"

while [ $? != 0 ]; do
echo "Sleep 1 second, check if slave is up yet. If not, sleep again."
sleep 1;
su - postgres -c "$PGBIN/pg_isready -U postgres -p $PGPORT -t2"
done

su - postgres -c "$PGBIN/psql -d postgres -U postgres -qXc 'select
pg_is_in_recovery() as is_pg_in_recovery'"

exit 0