What's the best way to automate backing-up of PostgreSQL databases?
Solution 1:
the same as you do for any other repetitive task that can be automated - you write a script to do the backup, and then set up a cron job to run it.
a script like the following, for instance:
(Note: it has to be run as the postgres user, or any other user with the same privs)
#! /bin/bash
# backup-postgresql.sh
# by Craig Sanders <[email protected]>
# This script is public domain. feel free to use or modify
# as you like.
DUMPALL='/usr/bin/pg_dumpall'
PGDUMP='/usr/bin/pg_dump'
PSQL='/usr/bin/psql'
# directory to save backups in, must be rwx by postgres user
BASE_DIR='/var/backups/postgres'
YMD=$(date "+%Y-%m-%d")
DIR="$BASE_DIR/$YMD"
mkdir -p "$DIR"
cd "$DIR"
# get list of databases in system , exclude the tempate dbs
DBS=( $($PSQL --list --tuples-only |
awk '!/template[01]/ && $1 != "|" {print $1}') )
# first dump entire postgres database, including pg_shadow etc.
$DUMPALL --column-inserts | gzip -9 > "$DIR/db.out.gz"
# next dump globals (roles and tablespaces) only
$DUMPALL --globals-only | gzip -9 > "$DIR/globals.gz"
# now loop through each individual database and backup the
# schema and data separately
for database in "${DBS[@]}" ; do
SCHEMA="$DIR/$database.schema.gz"
DATA="$DIR/$database.data.gz"
INSERTS="$DIR/$database.inserts.gz"
# export data from postgres databases to plain text:
# dump schema
$PGDUMP --create --clean --schema-only "$database" |
gzip -9 > "$SCHEMA"
# dump data
$PGDUMP --disable-triggers --data-only "$database" |
gzip -9 > "$DATA"
# dump data as column inserts for a last resort backup
$PGDUMP --disable-triggers --data-only --column-inserts \
"$database" | gzip -9 > "$INSERTS"
done
# delete backup files older than 30 days
echo deleting old backup files:
find "$BASE_DIR/" -mindepth 1 -type d -mtime +30 -print0 |
xargs -0r rm -rfv
EDIT :
pg_dumpall -D
switch (line 27) is deprecated, now replaced with --column-inserts
https://wiki.postgresql.org/wiki/Deprecated_Features
Solution 2:
pg_dump dbname | gzip > filename.gz
Reload with
createdb dbname
gunzip -c filename.gz | psql dbname
or
cat filename.gz | gunzip | psql dbname
Use split
. The split
command allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:
pg_dump dbname | split -b 1m - filename
Reload with
createdb dbname
cat filename* | psql dbname
Your could toss one of those in /etc/cron.hourly
Sourced from http://www.postgresql.org/docs/8.1/interactive/backup.html#BACKUP-DUMP-ALL
Solution 3:
Whatever commands you issue "by hand", - write them to script, and put call to this script in cron or whatever scheduler you use.
You can of course make the script more fancy, but generally, I think that you'll get there - start simple, and later refine.
Simplest possible script:
#!/bin/bash
/usr/local/pgsql/bin/pg_dumpall -U postgres -f /var/backups/backup.dump
Save it as /home/randell/bin/backup.sh, add to cron:
0 0 * * 0 /home/randell/bin/backup.sh