I’ve just started using PostgreSQL as my database of choice for a few projects, and needed some way of scheduling backups. As a result I’ve created the following small bash script that will backup a given database and put it in a gzipped tar archive.
You can find the script in my svn repository here.
Usage is pretty simple:
backup_pgsql dbname
where dbname is the name of the database you want to backup.
By default, the script tries to put your backup in /tmp/ – you can change this behaviour by modifying the BACKUP_DIR variable in the script – make sure to include the trailing / though.
Now, of course this is only the backup part. To schedule this, you can edit your crontab file (usually found in /etc/crontab) and include a line like this:
1 3 * * * root backup_pgsql dbname
This will backup the database, dbname, at 03.01am every day. Assuming your backup_pgsql is in your PATH. I put mine in /usr/local/bin/ and chmod +x it. If you don’t want to do that, simply provide the full path to the script.
