A simple yet effective PostgreSQL backup script.

Many times I have seen administrator creating a backup script to dump their databases that do not address the follow issues :

  • You don’t want to manually specify which database to backup so that every time a new database is created, it will be automatically backed up. Very common mistake.
  • You want to save multiple copies of your database, still you don’t want it to fill your hard disk.

Here is what I use to address that :

#!/bin/bash
# Location to place backups.
backup_dir="/home/postgres-backup/"
#String to append to the name of the backup files
backup_date=`date +%d-%m-%Y`
#Numbers of days you want to keep copie of your databases
number_of_days=30
databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in $databases; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to $backup_dir$i\_$backup_date
    pg_dump -Fc $i > $backup_dir$i\_$backup_date
  fi
done
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;

Obviously, this script run under Linux.

This script need to run under the postgres user.  The best way to do that is to insert it into crontab :

# su – postgres

$vi backup_all_db.sh

(insert above script)

$chmod 700  backup_all_db.sh

$crontab -e

And add a line like this one :

3 0 * * * /var/lib/pgsql/backup_all_db.sh

Enjoy!

backup_all_db.sh
  • Share/Bookmark
Posted in Linux. 16 Comments »

16 Responses to “A simple yet effective PostgreSQL backup script.”

  1. Tim Says:

    Hi Etienne – Thanks for this tutorial!! It has helped me out a great deal with one of my postgres/drupal installs

    If others happen to come across this and want to know a little more about cron, I found this page very useful:

    - https://help.ubuntu.com/community/CronHowto#Crontab%20Example

    Take care
    Tim

  2. Étienne Pouliot Says:

    Hello Tim,
    I’m glad this is helpful to you.

  3. Tousoxeu Says:

    Many thanks, I was hoping to find the perfect script, and I found yours :)
    I’ll check tomorrow if it works, but I guess I now have full backup for my sites&bases.

  4. Adam Says:

    Hi,

    I made a modified version of your script that prints out timestamps and the size of each backup, useful for logging. Thanks for the script!

    #!/bin/bash
    backup_dir= »/var/lib/pgsql/backups/ » # => location to place backups
    backup_date=`date +%F` # => string to append to the name of the backup files
    number_of_days=7 # => number of days you want to keep copies of your databases

    # get a list of databases to back up
    databases=`psql -l -t | egrep -v « ^ ( |template[01]) » | cut -d’|’ -f1 | sed -e ’s/ //g’ -e ‘/^$/d’`

    # starting time
    echo [`date`] START PostgreSQL backups

    # back up each database
    for i in $databases; do
    outfile=$backup_dir$backup_date-$i.dump.gz
    echo -n « –> Dumping $i to $outfile »
    pg_dump -Fc $i | gzip > $outfile
    outfilesize=`ls -lh $outfile | awk ‘{ print $5 }’`
    echo  » ($outfilesize) »
    done

    # clean old backups
    echo « –> Cleaning up backups older than $number_of_days days »
    find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;

    # finish time
    echo [`date`] STOP PostgreSQL backups

  5. Fhillip Says:

    Hello,

    Im having a error on line 12 (but the files is being created):

    postgres@svn-redmine:/etc/cron.daily$ sh backup_all_db.sh
    Dumping postgres to /bkp/redmine/postgres_23-04-2012
    Dumping redmine to /bkp/redmine/redmine_23-04-2012
    Dumping :postgres=CTc/postgres to /bkp/redmine/:postgres=CTc/postgres_23-04-2012
    backup_all_db.sh: line 12: /bkp/redmine/:postgres=CTc/postgres_23-04-2012: File or directory not found
    Dumping :postgres=CTc/postgres to /bkp/redmine/:postgres=CTc/postgres_23-04-2012
    backup_all_db.sh: line 12: /bkp/redmine/:postgres=CTc/postgres_23-04-2012: File or directory not found

    Permission on backup dir:
    drwxrwxrwx 2 postgres root 4096 Abr 23 11:20 redmine
    Tried that too:
    drwxrwxrwx 2 postgres postgres 4096 Abr 23 11:20 redmine

    Thanks.

  6. Adam Says:

    Hi Fhillip,

    Try this line for getting the list of databases:

    databases=$(psql -lt | perl -ane '!/(template[01]|^\s{2,}|^$)/ && print "$F[0]\n"')

    Adam

  7. PostgreSQL: backup script for all databases | bluemind.org Says:

    [...] automatically (delete old backup). This script is based on another one written by Etienne Pouliot: http://www.defitek.com/blog/2010/01/06/a-simple-yet-effective-postgresql-backup-script/ #!/bin/sh   # Posrgres executables CMD_PSQL=/usr/local/pgsql/bin/psql [...]

  8. Ruben Says:

    Hi Etienne

    greetings from spain, nice job, tested on our production environment, worked perfect

    thanks

  9. remd Says:

    Thanks for the helpful script :)
    I added: | sed ‘/:/d’ to the psql… line to get rid of the Error 12 it was generating, as it was also trying to backup some db related infos the psql sed line was capturing (the lines starting with « : »).

    It works great otherwise..

  10. MarbolanGos Says:

    Works great with the sed by remd.
    Here is the script:
    #!/bin/bash
    # Location to place backups.
    backup_dir= »/var/lib/pgsql/backups/ »
    #String to append to the name of the backup files
    backup_date=`date +%d-%m-%Y`
    #Numbers of days you want to keep copie of your databases
    number_of_days=30
    databases=`psql -l -t | cut -d’|’ -f1 | sed -e ’s/ //g’ -e ‘/^$/d’ | sed ‘/:/d’`
    for i in $databases; do
    if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to $backup_dir$i\_$backup_date
    pg_dump -Fc $i > $backup_dir$i\_$backup_date
    fi
    done
    find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;

    Thanks a lot!

  11. Pascal Says:

    Another version to get the databases.
    databases=`psql -lqt | grep -vE ‘^ +(template[0-9]+|postgres)? *\|’ | cut -d’|’ -f1| sed -e ’s/ //g’ -e ‘/^$/d’`

    As my `psql -lqt` output is:
    abcdefghij | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
    | | | | | postgres=CTc/postgres
    template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
    | | | | | postgres=CTc/postgres
    abc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

  12. Kindra Teed Says:

    When I originally commented I clicked the -Notify me when new feedback are added- checkbox and now each time a comment is added I get 4 emails with the same comment. Is there any manner you can take away me from that service? Thanks!

  13. Pedro Says:

    Adam,

    Thank you for the line to get the database list. I used this to clean up our backup script.

    Best,
    Pedro

    databases=$(psql -lt | perl -ane ‘!/(template[01]|^\s{2,}|^$)/ && print « $F[0]\n »‘)

  14. Jimy Says:

    when run the script i got an error

    server version :9.1.8; pg_dumpall version: 8 .4.9

    so please help me what can i do

  15. praveen Says:

    Hi all,
    when i run the script am getting below error

    « pg_dump: server version: 9.2.4; pg_dump version: 8.4.13
    pg_dump: aborting because of server version mismatch »

    any one can help me on this..


Comments are closed.