<h1><a name="Database_Backup_and_Restore_proc"></a> Database Backup and Restore procedures </h1> <p /> GNUmed takes the utmost care to safeguard your medical data during upgrade and operation: <p /> <ul> <li> upgrades are done non-destructively by cloning databases </li> <li> a slew of foreign keys, triggers, rules and constraints ensures data integrity </li> <li> most database access is read-only </li> <li> full transaction semantics are used (serializable) </li> <li> conflicting concurrent data modifications are detected </li> <li> data modifications are audit logged </li></ul> <p /> 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: <p /> <ul> <li> use <a href="http://en.wikipedia.org/wiki/RAID" rel="nofollow" target="_top">RAID</a> for database drives </li> <li> use <a href="http://en.wikipedia.org/wiki/S.M.A.R.T." rel="nofollow" target="_top">S.M.A.R.T.</a> monitoring on database drives </li> <li> <a href="http://en.wikipedia.org/wiki/Replication_(computer_science)" rel="nofollow" target="_top">re</a><a href="http://rsync.samba.org/" rel="nofollow" target="_top">pli</a><a href="http://slony.info/" rel="nofollow" target="_top">cate</a> to a backup machine </li> <li> consider <a href="http://en.wikipedia.org/wiki/Disk_cloning" rel="nofollow" target="_top">cloning</a> the physical machine into a virtual machine (e.g. with <a href="http://www.vmware.com/products/converter/get.html" rel="nofollow" target="_top">VMware Converter</a>) </li> <li> take <a href="http://en.wikipedia.org/wiki/Backup" rel="nofollow" target="_top">backups</a> </li> <li> <a href="http://en.wikipedia.org/wiki/Cron" rel="nofollow" target="_top">automate</a> taking backups </li> <li> keep several generations of backups </li> <li> keep duplicates of backups both on- and <a href="http://en.wikipedia.org/wiki/Rsync" rel="nofollow" target="_top">offsite</a> </li> <li> <strong>Test</strong> the backups ! </li> <li> <strong>Test</strong> the restore procedures ! </li></ul> <p /> If you follow the above advice you have done quite a bit to be on the safe side. The <a href="http://www.postgresql.org/docs/8.3/static/admin.html" rel="nofollow" target="_top">PostgreSQL Manual</a> has excellent chapters on <a href="http://www.postgresql.org/docs/8.4/static/backup.html" rel="nofollow" target="_top">backup and restore</a> as well as <a href="http://www.postgresql.org/docs/8.4/static/high-availability.html" rel="nofollow" target="_top">replication and availability</a>. Be sure to read it. <p /> GNUmed provides <a href="http://gitorious.org/gnumed/gnumed/trees/master/gnumed/gnumed/server" rel="nofollow" target="_top">a few scripts</a> to help with the above on U*ix. They are ready for integration with cron/anacron. A brief outline is given in <ul> <li> an <a href="http://lists.gnu.org/archive/html/gnumed-devel/2008-10/msg00001.html" rel="nofollow" target="_top">archived but still valid email</a> </li> <li> a <a href="http://lists.gnu.org/archive/html/gnumed-devel/2009-07/msg00371.html" rel="nofollow" target="_top">recent (troubleshooting) email</a>. </li></ul> <p /> <p /> <hr /> <h3><a name="Additional_arcane_and_rarely_nee"></a> Additional arcane and rarely needed information </h3> <p /> Below find an outdated example of how to script a GNUmed backup: <p /> <h4><a name="Backup"></a> Backup </h4> <p /> pg_dump and pg_dumpall are the command line programs to use. <p /> 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 <p /> <p /> <strong>pg_dumpall -g</strong> 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. <p /> <strong>pg_dump</strong> is needed to dump the rest. <p /> Prerequisites are a <a href="ConfigurePostgreSQL.html" class="twikiLink">properly configured</a> <code>pg_hba.conf</code> to allow access from the machine running the backup command to the server. <p /> e.g. if gm-dbo has administrative access <p /> <strong>pg_dumpall -g -h 192.168.1.6 -U gm-dbo > roles.sql</strong> <p /> dumps the roles to roles.sql <p /> <strong>echo enter backup password ;read y;echo $y> /tmp/pass.txt</strong> <p /> <strong>pg_dump -h 192.168.1.6 -U gm-dbo gnumed_v2 -Fc | openssl enc -bf -p <a href="file:/tmp/pass.txt" target="_top">file:/tmp/pass.txt</a> > gnumed_v2.dump.bf</strong> <p /> <strong>shred -u -z /tmp/pass.txt</strong> <p /> here pg_dump is using the parameter F(ormat) c(ustom). Custom format is usually compressed and must be restored using pg_restore. <p /> openssl enc is used with the -bf (blowfish) algorithm to encrypt using a password found in the file /tmp/pass.txt . <p /> The encrypted output is then piped to gnumed_v2.dump.bf <p /> <p /> If the data fits in 4G, then it can be transferred to a media for backup. <p /> <strong>growisofs -Z /dev/dvd gnumed_v2.dump.bf</strong> <p /> <p /> otherwise , the unix cmd "split" could be used. <p /> cat gnumed_v2.dump.bf | split -b 3900m gnumed_v2.dump <p /> but this would require double the space for gnumed_v2.dump.bf <p /> A less space consuming method would be to output to standard output from offsets of multiples of 3.9G , and piping to a backup , <p /> <a href="http://ozdocit.org/tiki-index.php?page=computer+tips+and+tricks" rel="nofollow" target="_top">ozdocit.org</a> has a backup script available. <p /> the debian dvd-backup package has the very simple command growisofs. <p /> growisofs -Z /dev/dvd -R -J filename <p /> means to initialize and store filename to the dvd with Rockridge and Joliet extensions. <p /> growisofs -M /dev/dvd -R -J filename2 <p /> means to add another file as a multisession dvd to a non-empty dvd. <p /> The backup <p /> <h4><a name="Restoring_to_a_blank_gnumed_v2_d"></a> Restoring to a blank gnumed_v2 database </h4> <p /> if the dvd was remounted for reading at /media/cdrom0 <p /> make sure pg_hba.conf allows access. <p /> create the database <p /> <strong>createdb gnumed_v2 -O gm-dbo</strong> <p /> add the roles <p /> <strong>su postgres</strong> <p /> <strong>psql -f roles.sql</strong> <p /> <p /> alternatively, have a net enabled admin user, e.g. gm-dbo <p /> <strong>psql -f roles -U gm-dbo -h 192.168.1.7</strong> <p /> restore <p /> <strong>cat /media/cdrom0/gnumed_v2.dump.bf | openssl enc -bf -d -p <a href="file:/tmp/pass.txt" target="_top">file:/tmp/pass.txt</a> | pg_restore -h 192.168.1.7 gnumed_v2 -U gm-dbo -Fc</strong> <p /> <strong>shred -u -z /tmp/pass.txt</strong> <p /> 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 <hr /> <p /> <hr />
