Database dump and restore

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

##Command line

###Database Dump

Postgres is installed with utilities to make it easier to dump a database.

pg_dump -h localhost  -U [user name] [db_name of origin] > [filename]

Example

pg_dump -h localhost  -U clinlims cdielis > cdielisdump.backup

If you want to restore this to a database using pgAdminIII then it will need to be compressed and formated. The following is an example:

pg_dump -h localhost  -U clinlims  -Fc  cdielis > cdielisdump.backup

###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;

The reason we are only dropping the schema and not the whole database is to save the step of recreating a new database

##Restore Database

pg_restore -h localhost-U [user name] -d [db_name of destination] [filename from previous step]

Alternatively in psql you can read in the file. Often you have logged in to psql to drop the old schema and it is just as easy to stay there to import the new database

psql -h localhost -U clinlims -W cdielis
psql> \i cdielisdump.backup

You will see the following warning

WARNING:  no privileges were granted for "public" GRANT

That is perfectly fine. It is because of the use of the clinlims Schema/namespace rather than the public schema.