Entries tagged [postgresql]

Backing up your data

Feb 14, 2015

Backing up your data is important because it is always possible for you to lose your data. It can be due to you accidentally deleting it, your hard drive crashing or because you lost access to your server. In any case, it is a good idea to set up automated backups both on your server and on another machine that is not in the same physical location as your server.

Here are some resources I used to setup backups on this server which is running Debian Wheezy:

Backing up Postgresql

First create a read only user to perform the back ups as described here. If you don’t want to create a linux user for the read only user, you may need to edit your pg_hba.conf file to change the auth-method to md5 instead of peer which is the default.

Then use this backup script which can make daily, weekly and monthly backups. The script contains the documentation on how to use it.

Backup config and application data files

Next backup any config files that you have modified. Most of your configuration should be in the /etc directory. So you can just backup the whole directory. Then you need to back up your web applications data files as well - files like images, videos etc.. that aren’t stored in your database but are stored on the file system.

To automate the backup of these files, I created a simple shell script with cp commands to copy files to a backup directory. I then dropped this script into /etc/cron.daily so that the script runs daily. If you don’t know how to use cron, this is a good place to start.

Copying your backups to another machine

To automate copying your backups to another machine, you can use rsync. The process is a bit involved. I referred to these guides to setup my rsync backups:

  • A general backup guide that also explains how to use rsync.
  • To automate rsync backups to another machine, we need to use ssh keys and when using those, it is a good idea to create user just to run rsync and prevent that user from running anything else. This explains how to do that.