Backups

Full backup
pg_dump -Fc davical >davical.pgdump
 * The DAViCal data is stored in the PostgreSQL database. You should back this up periodically using the PostgreSQL pg_dump command:

createdb --owner davical_dba --encoding UTF8 --template template0 davical pg_restore -Fc -d davical davical.pgdump
 * To restore the database use the PostgreSQL command pg_restore:

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.
 * 1) !/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"

if test ${BASH_ARGC} -ne 2; then echo "The incorrect number of arguments were supplied" exit 1; fi
 * 1) Check if the correct number of arguments have been given

SRC_FILE=${1} DST_FILENAME=${2}
 * 1) Copy arguments into friendlier variable names

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;
 * 1) Test if backup directory 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;
 * 1) Test if the source file 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 # 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
 * 1) Test if backup increment already exists
 * 1)   chgrp backup /var/backups/postgres/${DST_FILENAME}.gz &&

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 day

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: <?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);
 * 1) !/usr/bin/php5 -q

$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

Restoring database on a new installation
In order to restore the database to a newly installation you should check if the database (default database is davical) is empty. If not you should remove it and re-create it: su postgres -c 'dropdb davical' Then you should continue with:
 * Restoring the database.
 * Upgrading the database structure.

Notice : Don't forget to run: /usr/share/davical/dba/update-davical-database

Per user ics/vcard export
In order to export user collections (calendar and/or contacts), use the following command:

psql database -Atc "SELECT array_to_string(array(SELECT caldav_data FROM caldav_data WHERE dav_name LIKE '/username/collection/%'),'');" > export_file


 * username: the user that we want to export data from
 * collection: can be either addresses for contacts entries or calendar for calendar entries.

Example: addressbook export (database_name: "davical", user_name: "peter", addressbook_name: "addresses"):

psql davical -Atc "SELECT array_to_string(array(SELECT caldav_data FROM caldav_data WHERE dav_name LIKE '/peter/addresses/%'),'');" > exported_addressbook.vcf

Example: calendar export (database_name: "davical", user_name: "mark", calendar_name: "calendar"):

psql davical -Atc "SELECT array_to_string(array(SELECT caldav_data FROM caldav_data WHERE dav_name LIKE '/mark/calendar/%'),'');" > exported_calendar.ics

Notice : Be sure to run as user postgres (su postgres) or have appropriate permissions.

Thanks to Jan.Mate from DAViCal's Mailing List