Database Backup and Restore procedures

GNUmed takes the utmost care to safeguard your medical data during upgrade and operation:

• upgrades are done non-destructively by cloning databases
• a slew of foreign keys, triggers, rules and constraints ensures data integrity
• most database access is read-only
• full transaction semantics are used (serializable)
• conflicting concurrent data modifications are detected
• data modifications are audit logged

However, disaster strikes when it is least expected and there is a whole world of trouble GNUmed cannot do anything about. There are a few rules prudent to follow:

• use RAID for database drives
• use S.M.A.R.T. monitoring on database drives
• replicate to a backup machine
• consider cloning the physical machine into a virtual machine (e.g. with VMware Converter)
• take backups
• automate taking backups
• keep several generations of backups
• keep duplicates of backups both on- and offsite
• Test the backups !
• Test the restore procedures !

If you follow the above advice you have done quite a bit to be on the safe side. The PostgreSQL Manual has excellent chapters on backup and restore as well as replication and availability. Be sure to read it.

GNUmed provides some scripts and configuration files which, under *nix, are ready for integration with cron/anacron. They can be found:

• in server releases, available here, where the scripts reside at the same level as the directory bootstrap and the example .conf files, which need renaming and configuring, reside in the server release subdirectory /etc/gnumed at the same level
• in the GNUmed git repository with the scripts at this level and the .conf files at this level.

The next sections provide an overview of the configuration and script files. For more information, consult the in-line comments within the respective files.

Configuration (.conf) files setup

The location and names of the .conf files need to be correctly-referenced in the dependent script files. By default, the scripts expect to find the .conf files in the directory /etc/gnumed with names gnumed-backup.conf and gnumed-restore.conf.

In addition, each .conf file contains a series of options which should nearly always be changed. These include

• some suitable name representing the praxis, which will be the INSTANCE_OWNER
• where to eventually store the backup which, by default, is BACKUP_DIR="${HOME}/.gnumed/backup/" and which, if unchanged, will result in the backup being written into a directory owned by root. Under Mac OS 10.x this would be /var/root/.gnumed/backup
• which database to backup e.g. GM_DATABASE="gnumed_v15"
• the user/group the backup is eventually owned by BACKUP_OWNER="$USER:$USER"
• more options, for example for offsiting and for using gnotary

Script (.sh) files setup

The scripts gm-backup_database.sh and gm-restore_database.sh can be run unchanged except for the appended Mac OS constraints.

Note also that when running the backup or restore manually – where you had not further configured pg_hba.conf to allow root to access the GNUmed database as "gm-dbo", and where you had not set up a .pgpass file – what works is to

• sudo -i to root
• cd to the appropriate directory where the shell scripts are found
• execute the script sh gm-backup_database.sh
• provide the password for the postgres database user (twice) and the password for gm-dbo (twice)
• take care whether any database newer than the database specified in your conf file should perhaps be the one that actually needs backing up. This will depend on whether you had since upgraded your production database or whether the newer versions represent purely tests of newer versions.

Mac OS constraints:

• on account of trouble in Mac OS to support the command line (or shell script) su -c, it is necessary under Mac OS to comment-out with # the backup script 'sanity check' which under linux can test for whether or not the database exists.
• since Mac OS does not support -W (verify) Mac users should set VERIFY_TAR to an empty string "" in gnumed-backup.conf which will then do what is needed

Archival emails:

• a (troubleshooting) email.

Additional arcane and rarely needed information can be found on this page

Caveats about backing up and restoring

• the backup script …
  • is cron-ready, and can also be run by hand
  • should be run as root, which must be able to connect to the gnumed database as gm-dbo
  • creates an archive whose name fits the form backup-gnumed_vNN-INSTANCE_OWNER-hostname-datetimestamp.tar
  • issues the tar command from inside the directory containing the …-database.sql and …-role,sql files such that the command tar tf …, when applied to the archive, will return only filenames without the names of any enclosing directories

• the restore script …
  • expects the sql files contained within the archive to conform to the naming convention above, and
  • requires the archive to contain only the .sql files – no prepended directory name(s)!
  • may not smoothly restore a database when the destination is running a different version of PostgreSQL or, especially, a different operating system
    • as one example (see posting), the CREATE statement exported under Mac OS, Enterprise Postgres 9.3 was not acceptable to a Debian Postgres 9.4 until the options LC_COLLATE = 'en_CA' LC_CTYPE = 'en_CA' were each made into 'en_CA.UTF-8'