Re: Data Replication

We've done warm standby as you indicate, and we've not needed anything
special.

On the primary's postgresql.conf we use:

archive_command = '~/postgresql/bin/copyWAL "%p" "%f"'

Our copyWAL script is just a wrapper for 'scp' since we want to copy the
data encrypted over the network:

#!/bin/bash

WALPATH=$1
WALFILE=$2
WALCOPYLOG=~/postgresql/logs/WALcopy.log

echo $0 - $(date) - Copy WAL received PATH: $WALPATH and FILE: $WALFILE
>> $WALCOPYLOG
echo $0 - $(date) - $(ls -l $PGDATA/$WALPATH) >> $WALCOPYLOG

scp -B -C -p "$WALPATH"
user(at)otherhost(dot)com:postgresql/recoveryWALs/"$WALFILE"
RET=$?
if [ $RET -ne 0 ]; then
echo $0 - $(date) - Copy WAL PATH: $WALPATH - failed to copy to backup
system, exit code: $RET >> $WALCOPYLOG
exit RET
fi

# 0 exit status means successfully copied
exit 0;

On the warm standby, our recovery.conf uses pg_standby, which is part of
the contrib code:

restore_command = '~/postgresql/bin/pg_standby -l -d -t
~/postgresql/recoveryWALs/STOP_RECOVERY ~/postgresql/recoveryWALs %f %p
%r 2>> ~/po
stgresql/logs/pg_standby.log'

We have a script that puts the STOP_RECOVERY file in the specified
folder when we want it to go into live mode.

Hope this helps....

David

Rutherdale, Will wrote:
> Thanks, Joshua.
>
> As I mentioned to Steve, warm standby / log shipping seems to be the
> main feature I'm looking for.
>
> The PITR solution you mention: is that an improvement over regular log
> shipping? Or do I misunderstand where that fits into the system?
>
> -Will
>