Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I find it tedious to have to backup databases every week. And I also think weekly backups should be turned into daily backups. If I had to do that, I don't want to do it manually. What's the best way to automate the backing-up of PostgreSQL databases daily?

share|improve this question
Small note: dumping the db can kill the performance, use a cluster and dump on non-active nodes. –  neutrinus Sep 16 at 19:14

5 Answers 5

up vote 33 down vote accepted

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

# by Craig Sanders 
# this script is public domain.  feel free to use or modify as you like.


# directory to save backups in, must be rwx by postgres user
YMD=$(date "+%Y-%m-%d")
mkdir -p $DIR
cd $DIR

# get list of databases in system , exclude the tempate dbs
DBS=$($PSQL -l -t | egrep -v 'template[01]' | awk '{print $1}')

# first dump entire postgres database, including pg_shadow etc.
$DUMPALL -D | gzip -9 > "$DIR/db.out.gz"

# next dump globals (roles and tablespaces) only
$DUMPALL -g | gzip -9 > "$DIR/globals.gz"

# now loop through each individual database and backup the schema and data separately
for database in $DBS; do

    # export data from postgres databases to plain text
    $PGDUMP -C -c -s $database | gzip -9 > $SCHEMA

    # dump data
    $PGDUMP -a $database | gzip -9 > $DATA

# delete backup files older than 30 days
OLD=$(find $BASE_DIR -type d -mtime +30)
if [ -n "$OLD" ] ; then
        echo deleting old backup files: $OLD
        echo $OLD | xargs rm -rfv
share|improve this answer
+1 for a great script –  rkthkr Aug 28 '09 at 5:57
I use something very similar as a PreDumpCmd for backuppc, with the exception that I don't encode the date into the path, as backuppc deals with keeping multiple copies. –  David Pashley Aug 28 '09 at 6:03
Great script, but I found I needed to tweak the regex such that it didn't include pipes and blank lines as database names. DBS=$($PSQL -l -t | egrep -v 'template[01]' | awk '{print $1}' | egrep -v '^\|' | egrep -v '^$') –  s29 Oct 9 '13 at 23:45
pg_dump dbname | gzip > filename.gz

Reload with

createdb dbname
gunzip -c filename.gz | psql dbname


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

share|improve this answer
Splitting the file is a great idea. It is better to split the dump, using split -C, so that a line is never split. The debugging of a failed restore is easier. –  Gianluca Della Vedova Sep 11 '14 at 7:12

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:

/usr/local/pgsql/bin/pg_dumpall -U postgres -f /var/backups/backup.dump

Save it as /home/randell/bin/, add to cron:

0 0 * * 0 /home/randell/bin/
share|improve this answer

If you want to backup an entire cluster with minimal system load, you can simply tar the root directory of the postgresql cluster. for example:

echo "select pg_start_backup('full backup - `date`');" | psql
/usr/bin/rdiff-backup --force --remove-older-than 7D $BACKUP_TARGET
/usr/bin/rdiff-backup --include '/etc/postgresql' --include $PGDATA --exclude '/*' / $BACKUP_TARGET
/bin/tar -cjf /mnt/tmp/$SERVER_NAME.tbz2 $BACKUP_TARGET 2>&1
echo "select pg_stop_backup();" | psql

that's the bulk of my backup script.

share|improve this answer
No, this doesn't work if you don't also have WAL archiving enabled. –  Peter Eisentraut Jan 8 '10 at 22:01

in case anyone has to backup their postgres on a windows machine without the aid of cygwin etc I have a batch file which does the job quite well.

this will backup the databases into individual files in it's own directory every day

set dtnm=%date:~-4,4%%date:~-7,2%%date:~0,2%
set bdir=D:\backup\%dtnm%
mkdir %bdir%

FOR /F "tokens=1,2 delims=|" %%a IN ('psql -l -t -A -U postgres') DO (
    IF %%b EQU postgres pg_dump -U postgres -f %bdir%\%%a.sql.gz -Z 9 -i %%a
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.