Backup only one schema and restore it on another server

I have a database named "A" which has two schemas "B" and "C".

I want to backup and restore Schema "B" (including data) on a different server. Not sure how to do this as I am new to Postgres.

Do I have to create a new database on the new sever as of name "A" and then restore the Schema "B" on it?

Please help with the necessary commands.

PostgreSQL 9.1 running on Ubuntu 12.04

You can select which schemas to dump with the -n option of pg_dump. Create a dump of schema B:

pg_dump ...other...options... -Fc -n B >dump.dmp

Restore the dump file:

pg_restore -d somedb dump.dmp

The target database does not have to have the same name as the original one.

Note that you will have problems if schema B has dependencies on schema C. Then you won't be able to restore it separately.

Also note that when dumping one schema, you will not dump blobs (LOB type). Use -b option to dump with blobs. With -b all blobs are added, not just for a single schema.

StackExchange Database Administrators Q#53185, answer score: 45