Backup and restore of OpenClinica

Backing up your OpenClinca setup is a must. And not only that: you must practice restoring your backup. You know why, so do it.
Having said these grave warnings: backing up your OpenClinica is simple! There are only two things to backup: your database and your study-files.

Backup of the database

Postgres comes with a number of commandline utilities and one of them is pg_dump. This utility makes a (huge) text-file with commands to recreate your openclinica database, plus inserts into all the tables.

Let's say you your database is called "openclinica" and you are making your backup on 14 Juli 2010. First you login to your server and then you type:

sudo -u postgres /usr/bin/pg_dump openclinica > pg_dump_openclinica_20100714

The syntax is straightforward: pg_dump, followed by the name of the database and then the name of the dumpfile. Make the name of this file as descriptive as possible, so do not use just "OC" or "backup", and always add a date to it. As a last step you must take your dump-file of the server and store it somewhere safe. And I repeat: do not leave it on your server!

Backup of the studyfiles

All your CRF's in XL-format, plus XML-rules files, plus CRF-attached files and also your generated datasets and Metadata-sets are stored in the directory openclinica.data. This directory is located in /usr/local/tomcat/. For a complete backup of OpenClinica you can put all these files in a tar and then take that to a safe location anywhere, but not on the same server.
You can do this in one line with:

tar -cvvf oc_data_20100714.tar /usr/local/tomcat/openclinica.data

To take things one step further, include your datainfo.properties. Usually the information in this file does not change much after the installation of OpenClinica. But in case of a crash, it will save you a lot of time if you have settings like ports and mail-configuration, so type:

tar -rf oc_data_20100714.tar /usr/local/tomcat/webapps/OpenClinica/WEB-INF/classes/datainfo.properties

Restore of the database

To restore the database you will have to delete the existing openclinica database, recreate it and "run" the dump-file:

/usr/bin/psql
drop database openclinica
create database openclinica with encoding='UTF-8' owner=clinica;

Then exit psql (\q) and go to the directory where your dumpfile is located and type (as user postgres):

/usr/bin/psql openclinica < pg_dump_openclinica_20100714

This will restore your database.

a script, maybe?

The above is all very fine, but most of us want a script that we can use in crontab. Of course we can combine the above lines in a script but let's go a step further. We make a directory structure where we can put all the backup-files per month.
It's difficult to give a "one-size-fits-all" solution, but you can use this script as a starting point:

#!/bin/sh
# use date as extension and find the year and month for the folders
date_ext=$(date +%Y%m%d)
yearmonth=$(date +%Y%m)

# create the folders, if they do not exist already
cd /home/gerben/bu_files
mkdir $yearmonth
cd $yearmonth

# start with making a dump of the database
sudo -u postgres /usr/bin/pg_dump openclinica > pg_dump_openclinica_$date_ext

# put all Study files in one big tar
tar -cf openclinica_$date_ext.tar /usr/local/tomcat/openclinica.data
# add datainfo.properties
tar -rf openclinica_$date_ext.tar /usr/local/tomcat/webapps/OpenClinica/WEB-INF/classes/datainfo.properties
# add the database dump file
tar -rf openclinica_$date_ext.tar pg_dump_openclinica_$date_ext
# zip it all
gzip -9 -f openclinica_$date_ext.tar
# remove the original dump file
rm -f pg_dump_openclinica_$date_ext

# do the same for the tomcat logs
tar -cf tomcat_logs_$date_ext.tar /usr/local/tomcat/logs
gzip -9f tomcat_logs_$date_ext.tar

restoring from linux to windows

You may want to restore a dump-file that was created on Linux to a Windows-machine with pgAdmin. Unfortunately this can not be done using the graphical interface, but must be done using a prompt.

cd C:\Program Files (x86)\PostgreSQL\8.4\bin
psql -U postgres openclinica < c:\oc\diversen\pg_backups\pg_dump_openclinica_20150801

Other how-to-pages can be found here.

this page was last reviewed August 2015