Backups

From Davical
Revision as of 21:06, 30 November 2012 by Bl1nk (Talk) (corrected format error)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
Help
Available languages

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 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:

#!/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

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:

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