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 a few scripts to help with the above on U*ix. They are ready for integration with cron/anacron. A brief outline is given in
Additional arcane and rarely needed information
Below find an outdated example of how to script a GNUmed backup:
Backup
pg_dump and pg_dumpall are the command line programs to use.
the parameters in command for these commands are:
-h hostname, can be a 4 dot inet number
-p the port to use, if it is not the default 5432; this might happen if running a different pg_cluster for test purposes.
-U the username to login as , usually an administrator
pg_dumpall -g is needed to dump the globals , which are the user roles for gnumed. The password is one-way md5 hash
encrypted, so there should be no security risk from seeing the file.
pg_dump is needed to dump the rest.
Prerequisites are a properly configured pg_hba.conf
to allow access from the machine running the backup command
to the server.
e.g.
if gm-dbo has administrative access
pg_dumpall -g -h 192.168.1.6 -U gm-dbo > roles.sql
dumps the roles to roles.sql
echo enter backup password ;read y;echo $y> /tmp/pass.txt
pg_dump -h 192.168.1.6 -U gm-dbo gnumed_v2 -Fc | openssl enc -bf -p file:/tmp/pass.txt > gnumed_v2.dump.bf
shred -u -z /tmp/pass.txt
here pg_dump is using the parameter F(ormat) c(ustom). Custom format is usually compressed and must be restored using pg_restore.
openssl enc is used with the -bf (blowfish) algorithm to encrypt using a password found in the file /tmp/pass.txt .
The encrypted output is then piped to gnumed_v2.dump.bf
If the data fits in 4G, then it can be transferred to a media for backup.
growisofs -Z /dev/dvd gnumed_v2.dump.bf
otherwise , the unix cmd "split" could be used.
cat gnumed_v2.dump.bf | split -b 3900m gnumed_v2.dump
but this would require double the space for gnumed_v2.dump.bf
A less space consuming method would be to output to standard output
from offsets of multiples of 3.9G , and piping to a backup ,
ozdocit.org has a backup script available.
the debian dvd-backup package has the very simple command growisofs.
growisofs -Z /dev/dvd -R -J filename
means to initialize and store filename to the dvd with Rockridge and Joliet extensions.
growisofs -M /dev/dvd -R -J filename2
means to add another file as a multisession dvd to a non-empty dvd.
The backup
Restoring to a blank gnumed_v2 database
if the dvd was remounted for reading at /media/cdrom0
make sure pg_hba.conf allows access.
create the database
createdb gnumed_v2 -O gm-dbo
add the roles
su postgres
psql -f roles.sql
alternatively, have a net enabled admin user, e.g. gm-dbo
psql -f roles -U gm-dbo -h 192.168.1.7
restore
cat /media/cdrom0/gnumed_v2.dump.bf | openssl enc -bf -d -p file:/tmp/pass.txt | pg_restore -h 192.168.1.7 gnumed_v2 -U gm-dbo -Fc
shred -u -z /tmp/pass.txt
means output the contents of the dvd file gnumed_v2.dump.bf to openssl enc in -d(ecrypt) mode, using -bf (blowfish) algorithm ,
where password has been stored again in /tmp/pass.txt , and pipe to pg_restore , which is expecting -F(ormat) c(ustom),
the compressed format