Postgres WAL

postgres_wal_backup.sh

PG_VERSION="9.5"
ETC_PG="/etc/postgresql/$PG_VERSION"

cd $ETC_PG/backups
rm -rf *
pg_basebackup --xlog -U postgres --format=t -D $ETC_PG/backups/

postgres_wal_restore.sh

service postgresql stop

PG_VERSION="9.5"
ETC_PG="/etc/postgresql/$PG_VERSION"
VAR_PG="/var/lib/postgresql/$PG_VERSION"
FROMDIR="$ETC_PG/archives"
TODIR="$VAR_PG/tmp/pg_xlog"

cd $VAR_PG

if [ ! -d "$VAR_PG/tmp/" ]
then
  mkdir tmp
else
  rm -rf tmp
  mkdir tmp
fi

mv $VAR_PG/main/* $VAR_PG/tmp/

cd $VAR_PG/main/
rm -rf *

cd $ETC_PG/backups

if [ ! -f "base.tar" ]
then
  echo "Base tar does not exist !"
  exit
else
  tar -xf base.tar -C $VAR_PG/main/
fi

cd $VAR_PG/main/

if [ ! -d "$FROMDIR" ]
then
  echo "Directory $FROMDIR does not exist!!"
  exit
fi

if [ ! -d "$TODIR" ]
then
  echo "Directory $TODIR does not exist!!"
  exit
fi

cd $FROMDIR

for i in `find . -type f`
  do
    if [ ! -f $TODIR/$i ]
    then
      echo "copying file $i"
      cp $i $TODIR/$i
    fi
done

cd $TODIR/

cd $VAR_PG/main/
FILE="recovery.done"

if [ -f $FILE ]
then
  mv $FILE recovery.conf
else
  echo "restore_command = 'cp $ETC_PG/archives/%f %p'" >> recovery.conf
fi

chown -R postgres:postgres *

service postgresql start

exit

postgres_wal_settings.sh

service postgresql stop

PG_VERSION="9.5"
ETC_PG="/etc/postgresql/$PG_VERSION"
VAR_PG="/var/lib/postgresql/$PG_VERSION"
FROMDIR="$ETC_PG/archives/"
TODIR="$VAR_PG/tmp/pg_xlog/"

cd $ETC_PG

mkdir archives
mkdir backups

chown postgres:postgres archives
chown postgres:postgres backups

cd $ETC_PG/main/
echo 'max_wal_senders=1' >> postgresql.conf
echo 'max_wal_size=1GB' >> postgresql.conf
echo 'archive_timeout=600' >> postgresql.conf
echo 'wal_keep_segments=10' >> postgresql.conf
echo 'wal_level=hot_standby' >> postgresql.conf
echo 'archive_mode=on' >> postgresql.conf
echo "archive_command='test ! -f $ETC_PG/archives/%f && cp %p $ETC_PG/archives/%f'" >> postgresql.conf
echo 'local replication postgres trust' >> pg_hba.conf

service postgresql restart

exit