I want to restore the database with a different schema

Question

I have taken a dump of a database named temp1, by using the follwing command

$  pg_dump -i -h localhost  -U postgres -F c -b -v -f pub.backup temp1 

Now I want to restore the dump in a different database called "db_temp" , but in that I just want that all the tables should be created in a "temp_schema" ( not the default schema which is in the fms temp1 database ) which is in the "db_temp" database.

Is there any way to do this using pg_restore command?

Any other method also be appreciated!

Answer 1

There is a simple solution:

• Create your backup dump in plain SQL format (format "p" using the parameter --format=p or -F p)
• Edit your pub.backup.sql dump with your favorite editor and add the following two lines at the top of your file:

create schema myschema;

SET search_path TO myschema;

Now you can restore your backup dump with the command

psql -f pub.backup.sql

The set search_path to <schema> command will set myschema as the default, so that new tables and other objects are created in this schema, independently of the "default" schema where they lived before.