Skip to content

Database dump and restore

Jump to bottom
Paul Schwartz edited this page Jun 2, 2015 · 4 revisions

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.

##pgAdminIII

Before you start make sure you have SU permissions or you will not be able to restore the database

###Database Dump

Right click on the database you want to backup and select the backup option

Enter the name and location of the backup file

Accept the defaults and click OK

###Preparing the target database to restore

As with the command line we only want to dump the schema, not the whole database. Right click on the schema 'clinlims' and select "Drop cascade from the menu.

###Restore Database

To restore the database right click on the database and select "Restore" If you do not see "Restore" then you do not have the proper permissions. You will need to connect to the database as a role with super user permissions

Select the file name you want to restore from.

If the OK button is not enabled that means that the backup file is not in the correct format. It must be restored in the same format as it was dumped. You can try to uncheck the compressed check box, that sometimes fixes the problem

The restore will exit with a code of 1 because it was not able to restore the public schema. This is expected. Select "Cancel" to finish.

Clone this wiki locally