Backups
From DAViCal Wiki
Contents |
Full backup
- The DAViCal data is stored in the PostgreSQL database. You should back this up periodically using the PostgreSQL pg_dump command:
pg_dump -Fc davical >davical.pgdump
- To restore the database use the PostgreSQL command pg_restore:
createdb --owner davical_dba --encoding UTF8 --template template0 pg_restore -Fc -d davical davical.pgdump
Incremental backup
PostgreSQL supports automatic incremental backups.
What does this mean?
Instead of backing up the entire database - and having to remember to do it - PostgreSQL will store the changes in 'increment files'.
Why should you care?
You should always keep multiple backups of any database you have.
If, for example, you only have one backup and you accidentally delete an event and overwrite your previous backup, you cannot recover that event.
The trouble with keeping multiple backups is that you use a lot of disk space very quickly. So instead of having multiple complete backups, it makes sense to have a complete backup and store incremental changes from that complete backup.
Having incremental backups has the following benefits:
- Point-in-time restorations
- Automatic backups
- Space efficient backups
It is important to note that incremental backups backup the entire database cluster, not just the DAViCal database.
How to set up incremental backups
The PostgreSQL manual has a page on continuous archiving, which gives you a complete guide on how to do incremental backups and should be read to understand what is going on.
Here is a basic guide to setting up incremental backups:
Create a backup directory and set permissions
mkdir /var/backups/postgres/ chown postgres:postgres /var/backups/postgres/ chmod 750 /var/backups/postgres/
Create a backup script
Here is an example I store in /usr/local/bin/postgres-backup.
#!/bin/bash
BACKUP_DIR="/var/backups/postgres"
POSTGRES_MANUAL="http://www.postgresql.org/docs/8.3/static/continuous-archiving.html"
NOTIFY_EMAIL="root"
NOTIFY_SUBJECT="Postgres backup fail - $0"
# Check if the correct number of arguments have been given
if test ${BASH_ARGC} -ne 2;
then
echo "The incorrect number of arguments were supplied"
exit 1;
fi
# Copy arguments into friendlier variable names
SRC_FILE=${1}
DST_FILENAME=${2}
# Test if backup directory exists
if test ! -d ${BACKUP_DIR};
then
echo "The usual postgres incremental backup has failed, the ${BACKUP_DIR} directory does not exist. Look at ${POSTGRES_MANUAL}" | mail -s "${NOTIFY_SUBJECT}" ${NOTIFY_EMAIL};
exit 1;
fi;
# Test if the source file exists
if test ! -f ${SRC_FILE};
then
echo "The usual postgres incremental backup has failed, the source file (${SRC_FILE}) does not exist. Look at ${POSTGRES_MANUAL}" | mail -s "${NOTIFY_SUBJECT}" ${NOTIFY_EMAIL};
exit 1;
fi;
# Test if backup increment already exists
if test ! -f ${BACKUP_DIR}/${DST_FILENAME}.gz;
then
# Make a compressed copy of the increment to the backup directory
gzip -c ${SRC_FILE} > ${BACKUP_DIR}/${DST_FILENAME}.gz &&
# If you have included the postgres user in the backup group, uncomment the line below
# Make the copy readable by the backup group
# chgrp backup /var/backups/postgres/${DST_FILENAME}.gz &&
# Make the copy readable by the owner and group only
chmod 440 ${BACKUP_DIR}/${DST_FILENAME}.gz;
else
echo "The usual postgres incremental backup has failed, there is a filename conflict (${DST_FILENAME}). Look at ${POSTGRES_MANUAL}" | mail -s "${NOTIFY_SUBJECT}" ${NOTIFY_EMAIL};
exit 1;
fi
Make the script executable
chown postgres:postgres /usr/local/bin/postgres-backup chmod 550 /usr/local/bin/postgres-backup
Modify PostgreSQL to do archiving
You need to modify your postgresql.conf, which, on Debian, is stored in /etc/postgresql/8.3/main/
These options need to be changed.
archive_mode = on # Turn on archiving archive_command = '/usr/local/bin/postgres-backup %p %f' # What command to run archive_timeout = 86400 # Do a forced backup every 7 days
Reload PostgreSQL
/etc/init.d/postgresql-8.3 reload
Start a full database cluster backup
sudo -u postgres psql
postgres=# SELECT pg_start_backup('/var/backups/postgres/');
postgres=# \q
Backup the full database cluster
tar czvf /var/backups/postgres/fullbackup-$(date +%Y%m%d).tar.gz /var/lib/postgresql/8.3/main/base/
Stop the full database cluster backup
sudo -u postgres psql postgres=# SELECT pg_stop_backup(); postgres=# \q
You are done!
How to restore incremental backups
The PostgreSQL manual has a page on continuous archiving, which also guides you on how to do recover a database using incremental backups.
