Additional arcane and rarely needed information

For most users, the information on the parent of this page, GmManualDatabaseBackupRestore, should suffice.

A quick overview of postgres' backup and restore functions are posted at this blog.

Below find an outdated example of how to script a GNUmed 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 -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 -U gm-dbo gnumed_v2 -Fc | openssl enc -bf -p file:/tmp/pass.txt >

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

If the data fits in 4G, then it can be transferred to a media for backup.

growisofs -Z /dev/dvd

otherwise , the unix cmd "split" could be used.

cat | split -b 3900m gnumed_v2.dump

but this would require double the space for

A less space consuming method would be to output to standard output from offsets of multiples of 3.9G , and piping to a backup , 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


cat /media/cdrom0/ | openssl enc -bf -d -p file:/tmp/pass.txt | pg_restore -h gnumed_v2 -U gm-dbo -Fc

shred -u -z /tmp/pass.txt

means output the contents of the dvd file 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
Topic revision: 26 Jan 2015, JamesBusser
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback
Powered by Olark