PostgreSQL Automatic Backups with Redundant Storage

We recently installed Canvas, a LMS system by Instructure, for a local area school. It was a suprisingly painless setup process for the application itself using the Production Start Guide. Because we’re on a VPS though, we needed a way to have a backup of the user files and the databases. AWS S3 took care of the files. However, the database was on the VPS. We view our VPS (or EC2 instances) as temporary so storing information that needs to be durable is risky. To mitigate this risk, we’ve implemented the following solution:


Create a backup daily of the database and transfer the files via SSH to an external server which provides redundant file storage.

How to Setup the Solution

Step #1: Create a SSH Key for the user that will run the crons

Follow these steps to setup the SSH key. You will NOT want a password so that it can be done automatically by the server. After creating the SSH keys, you will want to copy the contents of the “.pub” key file and move that to the authorized_keys of the external server you are using.

Step #2: Create a .pgpass file

In order for the database backup to run without asking a user for the password you need to provide it within the user that will run the scripts home directory. First, SSH into the server with this user. Then follow the instructions below.

cd ~
touch .pgpass
vi .pgpass

Insert the following into the .pgpass file:


After you are back to the bash, type the following command.

chmod 0600 ~/.pgpass

Here is some more info on the topic:

Step 3: Create Backups Directories

We want to create both a regular directory and a directory we copy files to after transfers have been made. This way we can delete backups in the moved directory on a cron as well.

cd /var
mkdir backups
cd backups
mkdir database
cd /var
mkdir moved-backups
cd moved-backups
mkdir database

Step 4: Create Scripts Directory & Files

cd /var
mkdir scripts
cd scripts
touch pg_backup.config

I got the “pg” files from this link: The contents of all these files can be found below. Add them with a text editor (like vim).

Step 5: Test Scripts

In order to perform the backup and transfer the backup files to the remote server run the following command.

/var/scripts/ && /var/scripts/

Step 6: Setup Cron Job for Automated Backups

Almost there! Just need to setup the cron job to run our scripts. Remember, this should be the same user that you used to setup everything else.

crontab -e

That command will open up the default text editor. Insert the following below the comments.

0 3 * * * /var/scripts/ && /var/scripts/ > /dev/null 2>&1

There we go! We are all set. Just make sure that the cron job daemon is running.

service cron status

That should output something like “cron start/running, process #”

That’s it! Hope it helps



Leave a Reply

Your email address will not be published. Required fields are marked *