Database dump and restore

You can backup and restore the database either using the command line or pgAdminIII

##Command line

###Preparing the target database to restore

If you need to create a database in which to restore to you can create it with the following:

'createdb -E UTF8 -O [owner] [db_name]'

If you just need to clean out an existing database (cdielis in this example) use the psql command

> psql -h localhost -U clinlims -W cdielis
psql> DROP SCHEMA clinlims CASCADE;

You should specify the host when starting {{{psql}}}. The clinlims user owns the cdielis database.* In the above command line cdielis is the target database, the schema will be created by the back script. In the default system is called clinlims (same as the user).

== Database Dump==

There are utitilities to make it easier to dump a database.

{{{ pg_dump -FC -E UTF8 [db_name of origin] > [filename]

pg_dump -h localhost -U clinlimsSU -W -Fc -E UTF8 cdielis > cdielisdump.backup }}}

NOTE: You probably need to add {{{-h localhost}}} to the command, because most of our DB servers are NOT setup to answer remote connections.

This file can be used in pgadmin. PGADMINIII only accepts compressed files so when you run pg_dump you have to add {{{-Fc}}} to the command ( F = format, c = compressed).

== Restore Database==

You can use PGadmin to restore using a file generated in the above step.

{{{ pg_restore -d [db_name of destination] [filename from previous step] }}}

Command Line Options {{{ -W = force password prompt -U = username -d = database }}}

Alternatively in {{{psql}}} you can read in the file in assuming there is not already a schema by the same name.

{{{

psql -h localhost -U clinlims -W cdielis

psql> \i cdielisdump.backup }}}