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 davical pg_restore -Fc -d davical davical.pgdump
Note
If you are restoring on a new server you will need to create the database users (usually 'davical_dba' and 'davical_app'), and you will probably also need to alter the pg_hba.conf as for a new installation.
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
Important Notes
- incremental backups backup the entire database cluster, not just the DAViCal database.
- this method does not work if you transition from a 32bit installation to 64bit installation; you will receive an "incorrect checksum in control file" error.
- perform a full backup or use a 'transition' VM to produce a Postgres dump that can be imported in to the 64bit installation
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.
Emergency Restore
A small emergency exporter for caldav data:
#!/usr/bin/php5 -q
<?php
$user = $_SERVER["argv"][1];
$db = pg_connect("dbname=davical");
$result = pg_query_params($db,"SELECT split_part(dav_name,'/',3) as Filename, caldav_data as Content FROM caldav_data WHERE split_part(dav_name,'/',2) = $1 ORDER BY Filename",array($user));
$data = pg_fetch_all($result);
$files = array();
foreach($data as $item) {
if (in_array($item["filename"],array_keys($files))) {
$fp = $files[$item["filename"]];
}
else {
printf("Creating: /tmp/%s-%s.ics\n",$user,$item["filename"]);
$fp = fopen("/tmp/".$user."-".$item["filename"].".ics","w");
fputs($fp,"BEGIN:VCALENDAR\n");
$files[$item["filename"]] = $fp;
}
$data = $item["content"];
$data = str_replace("BEGIN:VCALENDAR\r","",$data);
$data = str_replace("END:VCALENDAR\r","",$data);
fputs($fp,$data);
}
foreach (array_keys($files) as $name) {
$fp = $files[$name];
fputs($fp,"END:VCALENDAR\n");
fclose($fp);
}
?>
Don't hesitate to contact me if you are in that kind of trouble so that you need this script. User:jenszahner
