Backup Procedure
From DocMGR
Notice!!!
Be sure to try these methods out for yourself on a working database, and verify that you can successfully backup and restore your data before waiting until you have an actual crash. With any backup, you should always verify your backup/restoration procedure works!!! Quick Solution
If you have compiled postgresql from source and installed it to it's own directory, you may simply make a copy of the pgsql directory and the DocMGR directory. If you store files/ in a non-standard location, be sure to back that up as well. If your server crashes, simply restore those directories and all should be fine. This is as long as you are running a compatible glibc version on the replacement server. Using SQL Dumps (Non-tsearch2 users)
* First, make a copy of the DocMGR installation directory. If files/ is stored in a non-standard place, copy it as well.
* Backup your DocMGR database by performing the following steps:
o su postgres
o /path/to/pgsql/bin/pg_dump -d docmgr -f /path/to/backup/file.sql
* To restore the database perform the following steps:
o su postgres
o /path/to/pgsql/bin/createdb docmgr
o /path/to/pgsql/bin/psql -d docmgr -f /path/to/backup/file.sql
Using Tsearch2 & SQL Dumps
* First, make a copy of the DocMGR installation directory. If files/ is stored in a non-standard place, copy it as well.
* If you are running a 7.4.x version of postgresql, then be sure to run the regprocedure_update.sql file in the scripts/ directory on your database. You can do that by running "su postgres; /path/to/pgsql/bin/pg_dump -d docmgr -f /path/to/docmgr/scripts/regprocedure_update.sql"
* Backup your DocMGR database by performing the following steps:
o su postgres
o /path/to/pgsql/bin/pg_dump -d docmgr -f /path/to/backup/file.sql
* To restore the database perform the following steps:
o su postgres
o /path/to/pgsql/bin/createdb docmgr
o /path/to/pgsql/bin/psql -d docmgr -f /path/to/docmgr/scripts/docmgr-XXX.sql
o /path/to/pgsql/bin/psql -d docmgr -f /path/to/backup/file.sql. Ignore "Function already exists" errors.
postgres backup script
Following is a backup script (UNIX shell) for postgres databases. It used to be available on http://database.sourceforge.net/. Do not forget to change the required customization parameters at the top of the script. Although not mentioned, it also works well for pgsql 8.x Credit and copyright go to the author mentioned in the script.
#!/bin/sh
#
# Postgresql backup script
#
# Copyright 2001 Author
#
# 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., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# Author
# |
# +-- speedboy_420 at hotmail dot com
#
# Last modified
# |
# +-- 16-10-2001
#
# Version
# |
# +-- 1.1.2
#
# Description
# |
# +-- A bourne shell script that automates the backup, vacuum and
# analyze of databases running on a postgresql server.
#
# Tested on
# |
# +-- Postgresql
# | |
# | +-- 7.1.3
# | +-- 7.1.2
# | +-- 7.1
# | +-- 7.0
# |
# +-- Operating systems
# | |
# | +-- Linux Redhat 6.2
# | +-- Linux Mandrake 7.2
# | +-- FreeBSD 4.3
# | +-- Cygwin (omit the column ":" because NT doesn't quite digest it)
# |
# +-- Shells:
# |
# +-- sh
# +-- bash
# +-- ksh
#
# Requirements
# |
# +-- grep, awk, sed, echo, gzip, chmod, touch, mkdir, date, psql,
# test, expr, dirname, find, tail, du, pg_dump, vacuum_db
#
# Installation
# |
# +-- Set the path and shell you wish to use for this script on the
# | first line of this file. Keep in mind this script has only been
# | tested on the shells above in the "Tested on" section.
# |
# +-- Set the configuration variables below in the configuration
# | section to appropriate values.
# |
# +-- Remove the line at the end of the configuration section so that
# | the script will run.
# |
# +-- Now save the script and perform the following command:
# |
# | chmod +x ./pg_backup.sh
# |
# +-- Now run the configuration test:
# |
# | ./pg_backup.sh configtest
# |
# | This will test the configuration details.
# |
# +-- Once you have done that add similiar entries given as examples
# below to the crontab (`crontab -e`) without the the _first_ #
# characters.
#
# # Run a backup, vacuum and analyze every morning at 03:00
# 00 03 * * * /server/postgres/pg_backup.sh bva > /dev/null 2>&1
# # Run a backup at 09:00, 12:00, 15:00, 18:00 everyday
# 00 09 * * * /server/postgres/pg_backup.sh b > /dev/null 2>&1
# 00 12 * * * /server/postgres/pg_backup.sh b > /dev/null 2>&1
# 00 15 * * * /server/postgres/pg_backup.sh b > /dev/null 2>&1
# 00 18 * * * /server/postgres/pg_backup.sh b > /dev/null 2>&1
#
# Restoration
# |
# +-- Restoration can be performed by using psql or pg_restore.
# | Here are two examples:
# |
# | a) If the backup is plain text:
# |
# | Firstly gunzip your backup file (if it was gzipped).
# |
# | gunzip backup_file.gz
# | psql -U postgres database < backup_file
# |
# | b) If the backup is not plain text:
# |
# | Firstly gunzip your backup file (if it was gzipped).
# |
# | gunzip backup_file
# | pg_restore -d database -F {c|t} backup_file
# |
# | Note: {c|t} is the format the database was backed up as.
# |
# | pg_restore -d database -F t backup_file_tar
# |
# +-- Refer to the following url for more pg_restore help:
#
# http://www.postgresql.org/idocs/index.php?app-pgrestore.html
#
# To Do List
# 1. db_connectivity() is BROKEN, if the script can not create a
# connection to postmaster it should die and write to the logfile
# that it could not connect.
# 2. make configtest check for every required binary
#
########################################################################
# Start configuration #
########################################################################
#
##################
# Authentication #
##################
#
# Postgresql username to perform backups under.
postgresql_username="postgres"
# Postgresql password for the Postgresql username (if required).
postgresql_password="<password>"
# Postgresql hostname to connect to.
postgresql_hostname="localhost"
##################
# Locations #
##################
#
# Location to place backups.
location_backup_dir="<path to backup dir>"
# Location to place the pg_backup.sh logfile.
location_logfile="<path to backup log>"
# Location of the psql binaries.
location_binaries="<path to pgsql binaries"
##################
# Permissions #
##################
#
# Permissions for the backup location.
permissions_backup_dir="0755"
# Permissions for the backup files.
permissions_backup_file="0644"
# Permissions for the backup logfile.
permissions_backup_log="0644"
##################
# Other options #
##################
#
# Databases to exclude from the backup process (separated by a space)
exclusions="template"
# Backup format
# |
# +-- p = plain text : psql database < backup_file
# +-- t = tar : pg_restore -F t -d database backup_file
# +-- c = custom : pg_restore -F c -d database backup_file
#
backup_format="p"
# Backup large objects
backup_large_objects="yes"
# Gzip the backups
backup_gzip="no"
# Date format for the backup
# |
# +-- %d-%m-%Y = DD-MM-YYYY
# +-- %Y-%m-%d = YYYY-MM-DD
# +-- %A-%b-%Y = Tuesday-Sep-2001
# +-- %A-%Y-%d-%m-%Y = Tuesday-2001-18-09-2001
# |
# +-- For more date formats type:
#
# date --help
#
backup_date_format=""
########################################################################
# End configuration #
########################################################################
#
#################
# Variables #
#################
#
version="1.1.2"
current_time=`date +%H:%M`
date_info=`date +$backup_date_format`
PGUSER="$postgresql_username"
PGPASSWORD="$postgresql_password"
PATH="$PATH:/bin:/usr/bin"
# Export the variables
export PGUSER PGPASSWORD PATH
#################
# Checking #
#################
#
# Check the backup format
if [ "$backup_format" = "p" ]; then
backup_type="Plain text SQL"
backup_args="-F $backup_format"
elif [ "$backup_format" = "t" ]; then
backup_type="Tar"
if [ "$backup_large_objects" = "yes" ]; then
backup_args="-b -F $backup_format"
else
backup_args="-F $backup_format"
fi
elif [ "$backup_format" = "c" ]; then
backup_type="Custom"
if [ "$backup_large_objects" = "yes" ]; then
backup_args="-b -F $backup_format"
else
backup_args="-F $backup_format"
fi
else
backup_format="c"
backup_args="-F $backup_format"
backup_type="Custom"
fi
#################
# Functions #
#################
#
# Obtain a list of available databases with reference to the user
# defined exclusions
db_connectivity() {
tmp=`echo -n '(';echo -n $exclusions | sed 's/\ /\|/g'; echo -n ')'`
if [ "$exclusions" = "" ]; then
databases=`$location_binaries/psql -h $postgresql_hostname -U $postgresql_username -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $location_logfile`
else
databases=`$location_binaries/psql -h $postgresql_hostname -U $postgresql_username -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $location_logfile`
fi
}
# Setup the permissions according to the Permissions section
set_permissions() {
# Make the backup directories and secure them.
mkdir -m $permissions_backup_dir -p "$location_backup_dir/`date +%B-%Y`/$date_info"
# Touch the log file
touch "$location_logfile"
# Make the backup tree
chmod -f $permissions_backup_log "$location_logfile"
chmod -f $permissions_backup_dir "$location_backup_dir"
chmod -f $permissions_backup_dir "$location_backup_dir/`date +%B-%Y`"
chmod -f $permissions_backup_dir "$location_backup_dir/`date +%B-%Y`/$date_info"
}
# Run backup
run_b() {
for i in $databases; do
start_time=`date '+%s'`
timeinfo=`date '+%T %x'`
"$location_binaries/pg_dump" $backup_args -h $postgresql_hostname $i > "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
if [ "$backup_gzip" = "yes" ]; then
gzip "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup.gz"
else
chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
fi
finish_time=`date '+%s'`
duration=`expr $finish_time - $start_time`
echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $location_logfile
done
}
# Run backup and vacuum
run_bv() {
for i in $databases; do
start_time=`date '+%s'`
timeinfo=`date '+%T %x'`
"$location_binaries/vacuumdb" -h $postgresql_hostname -U $postgresql_username $i >/dev/null 2>&1
"$location_binaries/pg_dump" $backup_args -h $postgresql_hostname $i > "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
if [ "$backup_gzip" = "yes" ]; then
gzip "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup.gz"
else
chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
fi
finish_time=`date '+%s'`
duration=`expr $finish_time - $start_time`
echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $location_logfile
done
}
# Run backup, vacuum and analyze
run_bva() {
for i in $databases; do
start_time=`date '+%s'`
timeinfo=`date '+%T %x'`
"$location_binaries/vacuumdb" -z -h $postgresql_hostname -U $postgresql_username $i >/dev/null 2>&1
"$location_binaries/pg_dump" $backup_args -h $postgresql_hostname $i > "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
if [ "$backup_gzip" = "yes" ]; then
gzip "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup.gz"
else
chmod $permissions_backup_file "$location_backup_dir/`date +%B-%Y`/$date_info/$current_time-postgresql_database-$i-backup"
fi
finish_time=`date '+%s'`
duration=`expr $finish_time - $start_time`
echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $location_logfile
done
}
# Run vacuum
run_v() {
for i in $databases; do
start_time=`date '+%s'`
timeinfo=`date '+%T %x'`
"$location_binaries/vacuumdb" -h $postgresql_hostname -U $postgresql_username $i >/dev/null 2>&1
finish_time=`date '+%s'`
duration=`expr $finish_time - $start_time`
echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $location_logfile
done
}
# Run vacuum and analyze
run_va() {
for i in $databases; do
start_time=`date '+%s'`
timeinfo=`date '+%T %x'`
"$location_binaries/vacuumdb" -z -h $postgresql_hostname -U $postgresql_username $i >/dev/null 2>&1
finish_time=`date '+%s'`
duration=`expr $finish_time - $start_time`
echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $location_logfile
done
}
# Print information regarding available backups
print_info() {
echo "Postgresql backup script version $version"
echo ""
echo "Available backups:"
echo ""
if [ ! -d $location_backup_dir ] ; then
echo "There are currently no available backups"
echo ""
exit 0
else
for i in `find "$location_backup_dir" -type d -maxdepth 2`; do
echo "$i `du -h \"$i\" | tail -n 1 | awk {'print $1'}`"
#for j in `ls "$location_backup_dir/$i"`; do
# echo " $j `du -h \"$location_backup_dir/$i/$j\" | tail -n 1 | awk {'print $1'}`"
#done
done
echo ""
fi
}
# Print configuration test
print_configtest() {
echo "Postgresql backup script version $version"
echo ""
echo "Configuration test..."
echo ""
# Check database connectivity information
echo -n "Database hostname : "
echo "$postgresql_hostname"
echo -n "Database username : "
echo "$postgresql_username"
echo -n "Database connectivity : "
$location_binaries/psql -h $postgresql_hostname -U $postgresql_username -q -c "select now()" template1 > /dev/null 2>&1 && echo "Yes" || echo "Connection could not be established..."
# Backup information
echo ""
echo "Backup information:"
echo ""
echo -n "Backup format : "
if [ "$backup_format" = "p" ]; then
echo "Plain text SQL"
elif [ "$backup_format" = "t" ]; then
echo "Tar"
else
echo "Custom"
fi
echo -n "Backup large objects : "
if [ "$backup_large_objects" = "yes" ]; then
echo "Yes"
else
echo "No"
fi
echo -n "Gzip backups : "
if [ "$backup_gzip" = "yes" ]; then
echo "Yes"
else
echo "No"
fi
echo -n "Backup date format : $date_info"
echo ""
# File locations
echo -n "Backup directory : "
echo "$location_backup_dir"
echo -n "Backup logfile : "
echo "$location_logfile"
echo -n "Postgresql binaries : "
echo "$location_binaries"
# Backup file permissions
echo -n "Backup directory permissions : "
echo "$permissions_backup_dir"
echo -n "Backup file permissions : "
echo "$permissions_backup_file"
echo -n "Backup log permissions : "
echo "$permissions_backup_log"
# Databases that will be backed up
echo -n "Databases that will be backed up : "
echo ""
for i in $databases; do
echo " $i"
done
# Databases that will not be backed up
echo -n "Databases that will not be backed up :"
echo ""
if [ "$exclusions" = "" ]; then
echo " none"
else
for i in $exclusions; do
echo " $i"
done
echo ""
fi
# Check if the backups location is writable
echo "Checking permissions:"
echo ""
echo -n "Write access : $location_backup_dir: "
# Needed to create/write to the dump location"
test -w "$location_backup_dir" && echo "Yes" || echo "No"
# Check if the logfile location is writable
echo -n "Write access : $location_logfile: "
# Needed to create/write to this scripts logfile"
if [ ! -x $location_logfile ] ; then
test -w `dirname "$location_logfile"` && echo "Yes" || echo "No"
else
test -w "$location_logfile" && echo "Yes" || echo "No"
fi
# Check if the binaries are executable
echo -n "Execute access : $location_binaries/psql: "
test -x $location_binaries/psql && echo "Yes" || echo "No"
echo -n "Execute access : $location_binaries/pg_dump: "
test -x $location_binaries/pg_dump && echo "Yes" || echo "No"
echo -n "Execute access : $location_binaries/vacuumdb: "
test -x $location_binaries/vacuumdb && echo "Yes" || echo "No"
echo ""
}
# Print help
print_help() {
echo "Postgresql backup script version $version"
echo ""
echo "Usage: $0 [options]"
echo ""
echo "Options:"
echo " b, Backup ALL databases"
echo " bv, Backup and Vacuum ALL databases"
echo " bva, Backup, Vacuum and Analyze ALL databases"
echo " v, Vacuum ALL databases"
echo " va, Vacuum and Analyze ALL databases"
echo " info, Information regarding all available backups"
echo " configtest, Configuration test"
echo " help, This message"
echo ""
echo "Report bugs to <speedboy_420 at hotmail dot com >."
exit 0
}
case "$1" in
# Run backup
b)
db_connectivity
set_permissions
run_b
exit 0
;;
# Run backup and vacuum
bv)
db_connectivity
set_permissions
run_bv
exit 0
;;
# Run backup, vacuum and analyze
bva)
db_connectivity
set_permissions
run_bva
exit 0
;;
# Run vacuum
v)
db_connectivity
set_permissions
run_v
exit 0
;;
# Run vacuum and analyze
va)
db_connectivity
set_permissions
run_va
exit 0
;;
# Print info
info)
set_permissions
print_info
exit 0
;;
# Print configtest
configtest)
db_connectivity
set_permissions
print_configtest
exit 0
;;
# Default
*)
print_help
exit 0
;;
esac
