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?

  • Small note: dumping the db can kill the performance, use a cluster and dump on non-active nodes. – neutrinus Sep 16 '15 at 19:14
  • You can make scheduled backups with the help of this free tool postgresql-backup.com – Olek Nilson May 5 '16 at 10:16
  • Next question: How large are you, what general backup mechanism do you have in place. For example, I am not ever backing up anything manually. Install agent of my backup system, select elements to be backed up in UI, schedule backup (in my case for databases: every 5 minutes).... finished. But that assumes having enough that it makes sense to install a proper system. – TomTom Jun 6 '16 at 18:43
  • I wouldn't call postgresql-backup.com a "free" solution. It is only free for the first 2 databases... @OlekNilson – Aidan Melen Apr 24 '17 at 16:49

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 <cas@taz.net.au>
# 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 --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

    # 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"


# 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

pg_dumpall -D switch (line 27) is deprecated, now replaced with --column-inserts

  • 11
    +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
  • 2
    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
  • 1
    @s29 I think its better to use a direct query instead of all that grep hackery, like: DBS=( $(${PSQL} -t -A -c "select datname from pg_database where datname not in ('template0', 'template1')") ) – PolyTekPatrick Nov 10 '17 at 13:37
  • Awesome script - so how does this compare to services like ClusterControl? – karns Aug 27 '19 at 19:18
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 http://www.postgresql.org/docs/8.1/interactive/backup.html#BACKUP-DUMP-ALL

  • 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/backup.sh, add to cron:

0 0 * * 0 /home/randell/bin/backup.sh
  • IF pg_dumpall is used is it possible to restore single table from it or it will restore all at once? can you please share script for restore single table created by using dumpall – Ashish Karpe Nov 26 '19 at 5:10

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.

  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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