Re: [GENERAL] WAL Archive command.

pg_xlog/
----------------------------------
-rw------- 1 postgres postgres 16777216 Sep 27 23:30 000000010000013300000082
-rw------- 1 postgres postgres 16777216 Sep 27 23:31 000000010000013300000083
-rw------- 1 postgres postgres 16777216 Sep 27 23:31 000000010000013300000084
-rw------- 1 postgres postgres 16777216 Sep 27 23:31 000000010000013300000085
-rw------- 1 postgres postgres 16777216 Sep 27 23:51 000000010000013300000086
-rw------- 1 postgres postgres 16777216 Sep 28 00:47 000000010000013300000087
-rw------- 1 postgres postgres 16777216 Sep 28 01:55 000000010000013300000088
-rw------- 1 postgres postgres 16777216 Sep 28 02:47 000000010000013300000089
-rw------- 1 postgres postgres 16777216 Sep 27 18:04 00000001000001330000008A
-rw------- 1 postgres postgres 16777216 Sep 27 18:02 00000001000001330000008B
-rw------- 1 postgres postgres 16777216 Sep 27 18:02 00000001000001330000008C
-rw------- 1 postgres postgres 16777216 Sep 27 18:05 00000001000001330000008D
-rw------- 1 postgres postgres 16777216 Sep 27 18:03 00000001000001330000008E
-rw------- 1 postgres postgres 16777216 Sep 27 18:02 00000001000001330000008F
-rw------- 1 postgres postgres 16777216 Sep 27 18:02 000000010000013300000090
-rw------- 1 postgres postgres 16777216 Sep 27 18:37 000000010000013300000091
-rw------- 1 postgres postgres 16777216 Sep 27 20:21 000000010000013300000092
-rw------- 1 postgres postgres 16777216 Sep 27 21:00 000000010000013300000093

pg_xlog/archive_status
----------------------------------
-rw------- 1 postgres postgres 0 Sep 27 23:30 000000010000013300000081.done
-rw------- 1 postgres postgres 0 Sep 27 23:30 000000010000013300000082.done
-rw------- 1 postgres postgres 0 Sep 27 23:31 000000010000013300000083.done
-rw------- 1 postgres postgres 0 Sep 27 23:31 000000010000013300000084.done
-rw------- 1 postgres postgres 0 Sep 27 23:31 000000010000013300000085.done
-rw------- 1 postgres postgres 0 Sep 27 23:51 000000010000013300000086.done
-rw------- 1 postgres postgres 0 Sep 28 00:47 000000010000013300000087.done
-rw------- 1 postgres postgres 0 Sep 28 01:55 000000010000013300000088.done

postgresql.conf
#----------------------------------
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = '/opt/wal_archive.sh "%p" "%f" > archive_command.log 2>&1'

wal_archive.sh
------------------------------------
#!/bin/bash -xv
PG_XLOG="$1"
PG_XLOG_FILENAME="$2"
HOST=hostname
ARCHIVE_DIR="/pg_archive/master"
ARCHIVE_TO_KEEP="3"     #No of days of archive logs to keep
EMAIL="a@b.com"
ERROR_COUNT_FILE="/tmp/replication_archive_error_count.txt"

OLD_COUNT=`cat ${ERROR_COUNT_FILE}`
DNOW=`date +%u`
hour=$(date +%H)
D=`date`

#Do the cleanup if the day is Monday or Thursday and time is between 11 p.m. UTC and 22 hrs UTC
if [ "$DNOW" -eq "1" -o "$DNOW" -eq "4" -a "$hour" -ge 11 -a "$hour" -lt 22 ]; then
    find "${ARCHIVE_DIR}"/ -type f -mtime +"${ARCHIVE_TO_KEEP}" -exec rm -f {} +
    if [ "$?" -eq "1" ]; then
        echo "The wal_archive script could not cleanup the archive directory of $HOST" | mail -s "ERROR - WAL Archive for $HOST" "$EMAIL"
    fi
fi

if [ ! -f "${ARCHIVE_DIR}"/"${PG_XLOG_FILENAME}" ]; then
    cp "${PG_XLOG}" "${ARCHIVE_DIR}"/"${PG_XLOG_FILENAME}"
    /usr/bin/rsync -W -az "${PG_XLOG}" postgres@standby:"${ARCHIVE_DIR}"/"${PG_XLOG_FILENAME}"
    if [ "$?" -ne "0" ]; then
        #If rsync fails, then remove the copied file from master, increase the error count, and retry.
        rm -rf "${ARCHIVE_DIR}"/"${PG_XLOG_FILENAME}"
        NEW_COUNT=`expr $OLD_COUNT + 1`
        if [ "$NEW_COUNT" -ge "100" ]; then
            echo -e "${D}""\n""Rsync could not transfer the WAL file from Master to slave." | mail -s "ALERT - WAL Archive for $HOST" "$EMAIL"
            echo "0" > $ERROR_COUNT_FILE
        else
            echo "$NEW_COUNT" > $ERROR_COUNT_FILE
        fi
        exit 1
    else
        echo "0" > $ERROR_COUNT_FILE
        exit 0
    fi
else
    exit 0
fi