• Simple Postgresql Cron Backup

    Here is a very simple method to backup your postgresql DB using a cron job and the pg_dump command. The main problem people encounter when trying to automate the pg_dump command is the password input. Now you could easily create a local user with no password, but why would you want to do that.

    The first thing you will want to do is login as the postgresql user that was created during setup, you want to do this to circumvent the interactive password of pg_dump. You will do the rest of the instructions as this user.

    su postgres

    Now the process is rather straightforward from here. I like to make a bash script, then run that daily with cron. The script I like usually looks something like this:

    cd /path/to/backup/
    pg_dump dbname > dbname.$(date.%F).dat.sql
    tar -cf dbname.$(date.%F).tar dbname.$(date.%F).dat.sql
    gzip dbname.$(date.%F).tar
    rm -f dbname.$(date.%F).dat.sql

    This creates the dump, tar and zips the file then removes the source.
    Now launch the crontab editor and have it run your script.

    crontab -e

    I use the following to run it at 1am everyday:

    0 1 * * *      /path/to/script.sh

    It doesn’t get much easier than that. Now exit the postgres user and that simple backup job will continue to run daily until you stop it.


    1 responses to “Simple Postgresql Cron Backup” RSS icon

    Leave a reply