From 819980b71c37b8429c0e6a7522b2581ace817b81 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 17 Mar 2010 17:57:36 +0000 Subject: [PATCH] adding db backup scripts, and an overview README of how to use them git-svn-id: svn://svn.open-ils.org/ILS-Contrib@835 6d9bc8c9-1ec2-4278-b937-99fde70a366f --- ESI-Examples/sys/scripts/README.db-backup | 67 ++++++++++++++++++ ESI-Examples/sys/scripts/eg-db-backup.sh | 108 +++++++++++++++++++++++++++++ ESI-Examples/sys/scripts/eg-wal-archive.sh | 92 ++++++++++++++++++++++++ 3 files changed, 267 insertions(+) create mode 100644 ESI-Examples/sys/scripts/README.db-backup create mode 100755 ESI-Examples/sys/scripts/eg-db-backup.sh create mode 100755 ESI-Examples/sys/scripts/eg-wal-archive.sh diff --git a/ESI-Examples/sys/scripts/README.db-backup b/ESI-Examples/sys/scripts/README.db-backup new file mode 100644 index 0000000..1be6e8d --- /dev/null +++ b/ESI-Examples/sys/scripts/README.db-backup @@ -0,0 +1,67 @@ +README.db-backup +---------------- + +Evergreen uses the PostgreSQL database for it's backend data store. In order +to correctly back up this database, specific steps must be taken. An rsync +or tar/cpio archive of the data directory is not sufficient! + +For more information about why and how PostgreSQL backups work, see: + +http://www.postgresql.org/docs/8.3/static/continuous-archiving.html + + + +------------ Overview + +Included in this directory are two scripts: + * eg-db-backup.sh + * eg-wal-archive.sh + +These scripts facilitate the correct backup procedures for PostgreSQL in an +Evergreen environment. Both must be edited to configure them for use in +a new setup. + + + +------------- eg-db-backup.sh + +The eg-db-backup.sh script should be run on a regular basis, normally +nightly, from the postgres user's crontab file. For example: + +0 6 * * * /home/postgres/eg-db-backup.sh + + +This will create a base backup every morning at 6AM. + + + +------------- eg-wal-archive.sh + +The eg-wal-archive.sh is a PostgreSQL WAL archive_command-compatable script +which will continuously archive the transaction files (WAL) that PostgreSQL +generates as Evergreen transactions are processed. To use this script, +set the archive_command variable in your production postgresql.conf file to: + +archive_command = '/home/postgres/eg-wal-archive.sh %p %f' + +(Assuming, of course, that /home/postgres/eg-wal-archive.sh is the path to the +script on your system). Some versions of PostgreSQL will require a separate +archive_mode setting be enabled in order to begin archiving WAL files. + + + +------------- Cleaning up + +PostgreSQL backups are relatively large, and you only need to keep the most +recent base backup plus and WAL generated after the start of the backup in +order to recover. To remove older backups, the following crontab entries are +useful: + + +0 5 * * * for i in `find /path/to/backup/directory/ -ctime +2`; do rm $i 2>/dev/null; done + +The path in the above 'find' command should match the directory into which you +are archiving both base backups and WAL files. This should be added to the +postgres user's crontab on the database server, and the crontab of the +configured ARCHIVE_USER on any remote archive server. + diff --git a/ESI-Examples/sys/scripts/eg-db-backup.sh b/ESI-Examples/sys/scripts/eg-db-backup.sh new file mode 100755 index 0000000..f796fc4 --- /dev/null +++ b/ESI-Examples/sys/scripts/eg-db-backup.sh @@ -0,0 +1,108 @@ +#!/bin/bash +# Evergreen database snapshot creation and archiving script +# Copyright (C) 2008-2010 Equinox Software Inc. +# Mike Rylander +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 2 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA +# +# +# You will need to edit the variables below to configure this script for +# use in your production environment. +# +# This script must be run as the postgres user, and if snapshot shipping +# is enabled then the postgres user should be able to log into the remote +# host as the ARCHIVE_USER over ssh and scp with a passphraseless ssh key. +# + + +#--------------------- CONFIGURATION BEGIN ------------------------ +# Remove the following line once you have adjusted the configuration +# below to match your environment. +echo "Configuration not complete!" && exit 1; + + +# Where the postgres binaries are installed, particularly psql +PGBIN=/usr/local/bin/ + + +# Where the database cluster lives +PGDATA=/usr/local/pgsql/data; + + +# How to name the database snapshot files. Adjust to taste. +ARCHIVE_LABEL=`date +MyEvergreen-production-postgres-backup-%FT%T` + + +# Local snapshot archiving directory +ARCHIVE_DIR="/var/backup/$HOSTNAME/evergreen/database/snapshot/" + + +# Remote host (IP or resolvable name) to which snapshots should be shipped. +# Leave empty to disable snapshot shipping. +ARCHIVE_HOST= + + +# User on the remote snapshot-receiving host. +ARCHIVE_USER= + +# Snapshot archiving directory on the remote host, if snapshot shipping is +# enabled. +ARCHIVE_DST="/var/backup/$HOSTNAME/evergreen/database/snapshot/" + +#--------------------- CONFIGURATION END ------------------------ + + + + +ARCHIVE_FILE=$ARCHIVE_LABEL.cpio.gz +# Make sure we're not overwriting an existing backup +if [ -e $ARCHIVE_DIR/$ARCHIVE_FILE ]; then + echo "Cannot create backup: $ARCHIVE_DIR/$ARCHIVE_FILE exists"; + exit; +fi + + +# Tell PG we're starting the backup +START_RESULT=`$PGBIN/psql -tc "SELECT pg_start_backup('$ARCHIVE_LABEL') IS NOT NULL;"|grep t` +if [ "_" == "_$START_RESULT" ]; then + echo "Could not start backup labeled $ARCHIVE_LABEL"; + exit; +fi + + +# Grab the data we need (just copy it locally) ... +(cd $PGDATA && find . -depth -print | grep -v pg_xlog | cpio -o | gzip > $ARCHIVE_DIR/$ARCHIVE_FILE) + + +# ... tell PG we're done ... +STOP_RESULT=`$PGBIN/psql -tc "SELECT pg_stop_backup() IS NOT NULL;"|grep t` +if [ "_" == "_$STOP_RESULT" ]; then + echo "Could not stop backup labeled $ARCHIVE_LABEL"; + exit; +fi + +echo "Backup of database on $HOSTNAME complete. Archive label: $ARCHIVE_LABEL" + +if [ "_$ARCHIVE_HOST" != "_" ]; then + # ... then push it over to the backup host + scp -q $ARCHIVE_DIR/$ARCHIVE_FILE $ARCHIVE_USER@$ARCHIVE_HOST:$ARCHIVE_DST + SCP_RES=$? + if [ "$SCP_RES" != "0" ]; then + echo "Unable to archive $ARCHIVE_DIR/$ARCHIVE_FILE to $ARCHIVE_USER@$ARCHIVE_HOST:$ARCHIVE_DST!!" + exit; + fi + echo "Remote backup: $ARCHIVE_HOST:$ARCHIVE_DST" +fi + diff --git a/ESI-Examples/sys/scripts/eg-wal-archive.sh b/ESI-Examples/sys/scripts/eg-wal-archive.sh new file mode 100755 index 0000000..e828ab5 --- /dev/null +++ b/ESI-Examples/sys/scripts/eg-wal-archive.sh @@ -0,0 +1,92 @@ +#!/bin/bash +#!/bin/bash +# Evergreen WAL archiving script +# Copyright (C) 2008-2010 Equinox Software Inc. +# Mike Rylander +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 2 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA +# +# +# You will need to edit the variables below to configure this script for +# use in your production environment. +# +# If WAL file shipping is enabled then the postgres user should be able to +# log into the remote host as the ARCHIVE_USER over ssh and scp with a +# passphraseless ssh key. +# +# In your postgresql.conf, turn on archive_mode (if applicable) and adjust +# the archive_command thusly: +# +# archive_command = '/location/of/this/script/eg-wal-archive.sh %p %f' +# + + +#--------------------- CONFIGURATION BEGIN ------------------------ +# Remove the following line once you have adjusted the configuration +# below to match your environment. +echo "Configuration not complete!" && exit 1; + + +# File which, if it exists, pauses WAL archiving +PAUSE_FILE=/tmp/wal-pause + +# Local WAL archiving directory +ARCHIVE_DIR="/var/backup/$HOSTNAME/evergreen/database/wal/" + + +# Remote host (IP or resolvable name) to which WAL files should be shipped. +# Leave empty to disable WAL file shipping. +ARCHIVE_HOST= + + +# User on the remote WAL-receiving host. +ARCHIVE_USER= + +# Snapshot archiving directory on the remote host, if WAL file shipping is +# enabled. +ARCHIVE_DST="/var/backup/$HOSTNAME/evergreen/database/wal/" + +#--------------------- CONFIGURATION END ------------------------ + + + + +while [ -e $PAUSE_FILE ]; do sleep 1; done + +P=$1 +F=$2 + +if [ -e $ARCHIVE_DIR/$F.bz2 ]; then + echo "Cannot archive: $ARCHIVE_DIR/$F.bz2 already exists" + logger -p local3.info "Cannot archive: $ARCHIVE_DIR/$F.bz2 already exists" + exit 0; +fi + +cp $P $ARCHIVE_DIR/$F +CP_RES=$? + +if [ "$CP_RES" != "0" ]; then + echo "Cannot archive: unable to copy WAL file $P to $ARCHIVE_DIR/$F, cp exit code = $CP_RES" + logger -p local3.info "Cannot archive: unable to copy WAL file $P to $ARCHIVE_DIR/$F, cp exit code = $CP_RES" + exit 1; +fi + +/bin/bzip2 $ARCHIVE_DIR/$F +if [ "_$ARCHIVE_HOST" != "_" ]; then + scp -q $ARCHIVE_DIR/$F.bz2 $ARCHIVE_USER@$ARCHIVE_HOST:$ARCHIVE_DST +fi + +exit 0 + -- 2.11.0