You can use the linux scheduler cron to automate backups of your postgres database. First as root, you’ll want to log in as the postgres user since we’ll be doing a number of things in that account:

su - postgres

Assuming you have a database named “mydb”, lets set up a small script in the postgres account. On Ubuntu & Linux Mint the account is in /var/lib/postgresql, and on Red Hat & CentOS you’ll find it in /var/lib/pgsql. For now, lets assume Ubuntu. Here is the script:

1
2
3
4
5
6
7
8
9
10
11

#!/bin/sh
cd /var/lib/postgresql
prefix=mydb_
CURRENT=$(date "+%y%m%d-%H%M%S")
suffix=.sql
pg_dump -U postgres -w mydb > ${prefix}${CURRENT}${suffix}
chown postgres:postgres ${prefix}${CURRENT}${suffix}
gzip -9 ${prefix}${CURRENT}${suffix}
chown postgres:postgres ${prefix}${CURRENT}${suffix}.gz
chmod 644 ${prefix}${CURRENT}${suffix}.gz
mv ${prefix}${CURRENT}${suffix}.gz /var/lib/postgresql/backups

Make sure you give your script execute permissions:

chmod 755 db_backup.sh

You also want to create the backups directory where your sql dump will be archived:

mkdir backups

Next you’ll need to set up our postgres credentials in a .pgpass file:

1

localhost:5432:mydb:postgres:dbPa$$w0Rd

You will need to set the parmissions for .pgpass to 600.

chmod 600 .pgpass

Also, since cron will run as root, you’ll need to copy the .pgpass file over to root’s home directory:

sudo cp .pgpass ~root

Finally, you’ll want to run “crontab -e” as root to schedule when your new backup script will run. This entry will have the system run the script at 00:00:00 every day:

@daily /var/lib/postgresql/db_backup.sh