Good morning,
I am attempting to set up cascading replication on a hot standby setup. We are running postgres 9.4 on all servers. I can ship logs from the master to the first standby but cannot get logs to ship from the first standby to the second standby. I see the cascading replication in the streaming replication documentation. Do I need to set up streaming replication in order for this to work? I have sent my postgresql.conf file for the first standby as reference as well as the copy statement.
Thank you
--
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com
#!/bin/sh
############################################################################
#
############################################################################
# Copyright (C) 2007 Dominion Enterprises All Rights Reserved
############################################################################
## This script is invoked by the ARCHIVE_COMMAND parameter in the postgres
## config file. It is run on server inddbatest01.SERVER.INT.
## It will do the following:
##
## 1. Copy the informational .backup and .history files to staging library
## 2. Gzip the actual xlogs to the staging library
## 3. Ping inddbatest02.SERVER.INT to test for network connectivity
## 4. scp the following to DR-legacy-PSQL.SERVER.INT
## - informational log files
## - the straight unzipped pg_log files for use in hot standby
##
## The staging library on PROD-LEGACY-PSQL.SERVER.INT is:
## /mnt/dbdumps/postgres-legacy/archlog
##
## Copied to CVS FROM folder /MavMail/mm/InternalTools
## /database_replication/ar-db-2desq
##
## MES- 3/18/2015: if first testping successful, failover after 10 minutes
## of unsuccessful scp
###########################################################################
REQ_FILE=$1
DEST=$2
GZDEST="${DEST}.gz"
LF="${REQ_FILE}.lock"
SUFFIX=${REQ_FILE##*.}
DATETIME=`date +"%Y%m%d-%H%M%S"`
PING='/bin/ping -c 1 -W 1'
##/usr/bin/lockfile "${LF}" # lockfile also on receiving server
#####################################################################
## logic to gzip xlogs to staging
####################################################################
if [ "${SUFFIX}" != 'history' ] && [ "${SUFFIX}" != 'backup' ]; then
/bin/cat "${REQ_FILE}" | gzip > /postgres_data/backups/archlog/${GZDEST}
if [ "$?" -gt "0" ] ; then
echo 'unsuccessful gzip of archlog file' >> /tmp/archlog.inddbatest02.log
echo 'the unsuccessful gzip exit code is ' "$?" >> /tmp/archlog.inddbatest02.log
fi
else
#####################################################################
## logic to copy informational files to staging
####################################################################
cp "${REQ_FILE}" "/postgres_data/backups/archlog/${DEST}"
if [ "$?" -gt "0" ] ; then
echo " unsuccessful unzipped copy of backup info file" >> /tmp/archlog.inddbatest02.log
echo 'the unsuccessful backup file copy is ' "$?" >> /tmp/archlog.inddbatest02.log
fi
fi
#
badping=0
badscp=0
echo "this is the echo before the ping to inddbatest02 " >> /tmp/archlog.inddbatest02.log
${PING} 10.201.117.119
##########################################################################
## upon successful ping of inddbatest03, scp to
## /postgres_data/backups/archlogfromtest02
##
## Postgres should now will handle outages up to 30 minutes
## if greater than 30 minutes dba should be called
#####################################################################
## 01/15/2015 - logic to scp current log directly to dr server - for hotstandby
##########################################################################
if [ "$?" = "0" ] ; then
echo "the datetime before the scp to (inddbatest03) /postgres_data/backups/archlogfromtest02 is " >> /tmp/archlog.inddbatest02.log
/bin/date >> /tmp/archlog.inddbatest02.log
echo "this is the echo after the check of the ping to inddbatest03 " >> /tmp/archlog.inddbatest02.log
scp "${REQ_FILE}" "pguser@inddbatest03:/postgres_data/backups/archlogfromtest02/${DEST}"
if [ "$?" != "0" ] ; then
echo "first log scp to inddbatest03 not good - trying again after sleeping 5 minutes. time is ${DATETIME} " >> /tmp/archlog.inddbatest02.log
sleep 300
badscp=1
else
echo " the datetime after the scp to inddbatest03 /postgres_data/backups/archlogfromtest02 folder is " >> /tmp/archlog.inddbatest02.log
/bin/date >> /tmp/archlog.inddbatest02.log
badscp=0
exit 0
fi
scp "${REQ_FILE}" "pguser@inddbatest03:/postgres_data/backups/archlogfromtest02/${DEST}"
if [ "$?" != "0" ] ; then
echo "second log scp to inddbatest03 not good - trying again after sleeping 5 minutes. time is ${DATETIME} " >> /tmp/archlog.inddbatest02.log
sleep 300
badscp=1
else
echo " the datetime after the scp to inddbatest03 /postgres_data/backups/archlogfromtest02 folder is " >> /tmp/archlog.inddbatest02.log
/bin/date >> /tmp/archlog.inddbatest02.log
badscp=0
exit 0
fi
scp "${REQ_FILE}" "pguser@inddbatest03:/postgres_data/backups/archlogfromtest02/${DEST}"
if [ "$?" != "0" ] ; then
echo "third attempt at log scp to inddbatest03 failed - calling dba time is ${DATETIME} " >> /tmp/archlog.inddbatest02.log
else
echo " the datetime after the scp to inddbatest03 /postgres_data/backups/archlogfromtest02 folder is " >> /tmp/archlog.inddbatest02.log
/bin/date >> /tmp/archlog.inddbatest02.log
badscp=0
exit 0
fi
echo " the log just copied is ${DEST} " >> /tmp/archlog.inddbatest02.log
echo " the datetime after the scp to inddbatest03 /postgres_data/backups/archlogfromtest02 folder is " >> /tmp/archlog.inddbatest02.log
/bin/date >> /tmp/archlog.inddbatest02.log
else
number=0
badping=1
echo "unsuccessful ping of inddbatest03 - first time - sleeping for 1 minute. time is ${DATETIME} " >> /tmp/archlog.inddbatest02.log
while [ "$badping" = '1' ] ; do
echo "copying current archive log ${GZDEST} to holdingrep directory. time is ${DATETIME} " >> /tmp/archlog.inddbatest02.log
/bin/cp -u "/postgres_data/backups/archlog/${GZDEST}" "/home/pguser/holdingrep/${GZDEST}"
sleep 60
number=$((number + 60))
${PING} 10.201.117.119
if [ "$?" = "0" ] ;
then
echo " successful ping of inddbatest03 after $number seconds: sleeping for 20 seconds then continuing with scp copy from holdingrep. time is ${DATETIME} " >> /tmp/archlog.inddbatest02.log
sleep 20
badping=0
echo "the datetime before the scp to (inddbatest03)/postgres_data/backups/archlogfromtest02/ from the staging library is " >> /tmp/archlog.inddbatest02.log
/bin/date >> /tmp/archlog.inddbatest02.log
cd /home/pguser/holdingrep/
gunzip *
scp * pguser@inddbatest03:/postgres_data/backups/archlogfromtest02/ >> /tmp/archlog.inddbatest02.log
if [ "$?" = "0" ] ;
then
echo "the datetime after the successful scp from the staging library is " >> /tmp/archlog.inddbatest02.log
/bin/date >> /tmp/archlog.inddbatest02.log
/bin/rm /home/pguser/holdingrep/* -f
else
echo "unsuccessful scp to inddbatest03 after successful ping - sleeping for another minute " >> /tmp/archlog.inddbatest02.log
fi
else
echo " unsuccessful ping of inddbatest03 after $number seconds - sleeping for another minute. time is ${DATETIME} " >> /tmp/archlog.inddbatest02.log
fi
if [ "$number" -ge "3600" ] ; then
echo " ping has been unsuccessful for more than 60 minutes - calling dba. time is ${DATETIME} " >> /tmp/archlog.inddbatest02.log
fi
done
fi
#
Attachment:
postgresql.conf
Description: Binary data
