Skip to content

Instantly share code, notes, and snippets.

@jjuarez

jjuarez/postgresql_backup.sh

Created December 3, 2015 11:01
Download ZIP

Revisions

  1. jjuarez created this gist Dec 3, 2015.
    31 changes: 31 additions & 0 deletions postgresql_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,31 @@
    #!/bin/bash

    DEST_HOST=${1}
    DATA_DIRECTORY=${2:-"/srv/data/postgresql-data"}
    ARCHIVE_DIRECTORY=${3:-"/srv/data/postgresql-archives"}

    [[ -n "${DEST_HOST}" ]] || exit 1

    ##
    # Prepare for a local backup
    /usr/bin/psql --command "SELECT pg_start_backup('Streaming Replication', true)" --dbname postgres

    ##
    # Prepare remote postgresql server for slave-ification
    /usr/bin/ssh postgres@${DEST_HOST} "/etc/init.d/postgresql stop; rm ${DATA_DIRECTORY}/FAILOVER; mv ${DATA_DIRECTORY}/recovery.done ${DATA_DIRECTORY}/recovery.conf"

    ##
    # Copy local data to remove postgres server
    /usr/bin/rsync -C -a --delete -e ssh --exclude pg_log --exclude pg_xlog --exclude recovery.conf --exclude recovery.done ${DATA_DIRECTORY}/ ${DEST_HOST}:${DATA_DIRECTORY}/

    ##
    # This archives the the WAL log (ends writing to it and moves it to the $archive dir
    /usr/bin/psql --command "SELECT pg_stop_backup()" --dbname postgres

    ##
    # this rsyncs the WAL archives that are written after pg_stop_backup is called.
    /usr/bin/rsync -C -a --delete -e ssh ${ARCHIVE_DIRECTORY}/ ${DEST_HOST}:${ARCHIVE_DIRECTORY}/

    ##
    # Start postgres (and replication) on the remote slave database
    /usr/bin/ssh postgres@${DEST_HOST} "/etc/init.d/postgresql start"