which uses the pg_dump and pg_dumpall.
[bash]
#!/bin/bash
## This scripts dumps all the databases in a Postgres 8.1 server, localhost
## 2 dump files are made for each database. One with Inserts another without.
## some variables, change them to fit yours
LOGFILE="/var/lib/pgsql/data/pg_log/pgsql_dump.log"
HOSTNAME=`hostname`
MAILLIST="name1@something.com,name2@something.com"
BACKUP_DIR="/backup/postgresql_dumps"
# Date and time variables
DATE=`date +%Y-%m-%d`
TIME=`date +%k:%M:%S`
TODAY=$DATE"T"$TIME
# only postgres can run this script!
if [ `whoami` != "postgres" ]; then
echo "pgsql_dump tried to run, but user is not postgres!" >> $LOGFILE
echo "You are not postgres, can not run."
echo "Try: su -c ./pgsql_dump.sh postgres"
exit;
fi
# clean up old dumps! (find all types which are files, with the name that ends with .sql,
# their date older than 7 days, and execute the command "rm" (remove) )
find $BACKUP_DIR -type f -name ‘*.sql’ -mtime +7 -exec rm {} ;
# Check if there any backup files.
# Action: find in folder BACKUP_DIR all files with file-extension .sql.
# Count the number of files with wc (word count, option -l, which counts the numbers of lines.
# If this number is 0, then there are no files, and clearly something is wrong,
# because you don’t have any backups!
if [ `find $BACKUP_DIR -type f -name ‘*.sql’ | wc -l` -eq 0 ]; then
echo "There are no pgsql dumps for the last 2 days at $HOSTNAME. Something is wrong!" | mail -s "[PGSQLDUMP ERROR] $HOSTNAME" $MAILLIST
fi
# Create the log-file if it doesn’t exist
if [ ! -e $LOGFILE ]; then
touch $LOGFILE
fi
# Find which databases you have in your Postgresql server
# Action: list out all the databases, remove unwanted lines and characters, extract wanted line with awk (line 1),
# strip away white empty lines with the command ‘sed’:
DATABASES=`psql -q -c "l" | sed -n 4,/eof/p | grep -v rows | grep -v template0 | awk {‘print $1’} | sed -e ‘/^$/d’`
# Dump the databases in individual files
for i in $DATABASES; do
FILENAME="$i-$TODAY.sql"
pg_dump -D $i > /backup/postgresql_dumps/"$FILENAME"
done
# we also like a dump with copy statements
for i in $DATABASES; do
FILENAME="$i-cp-$TODAY.sql"
pg_dump $i > /backup/postgresql_dumps/"$FILENAME"
done
# full backup is also necessary
pg_dumpall -D > /backup/postgresql_dumps/"all-databases $TODAY".sql
pg_dumpall > /backup/postgresql_dumps/"all-databases $TODAY".sql
# Finally vacuum the database
vacuumdb -a -f -z -q
[/bash]