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

A quick and dirty way:

1) rename default schema:

alter schema public rename to public_save; 

2) create new schema as default schema:

create schema public; 

3) restore data

pg_restore -f pub.backup db_temp [and whatever other options] 

4) rename schemas according to need:

alter schema public rename to temp_schema; alter schema public_save rename to public;