Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres. Do I have to create a new DB on new sever as of name "A" and then restore the Schema "B" on it. pls help with commands.

--- information from comment on Peter's answer:

I want to backup and restore Schema "B " + data. Secondly I forgot to mention that Postgresql 9.1 running on Ubuntu 12.04

share|improve this question

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.

share|improve this answer

You can add parameter -n [schema name] The comment of this parameter is said:

-n schema

Dump only schemas matching schema; this selects both the schema itself, and all its contained objects. When this option is not specified, all non-system schemas in the target database will be dumped.

share|improve this answer
Isn't this what Peter Eisentraut did in his answer? – dezso Feb 17 at 10:09
  • You can use pg_dump tool (see pg_dump doc ) and pg_restore (pg_restore doc)
  • You do not need to create new database name "A" on new server .

*Basic example :

I create "dump.bat" & "restore.bat" files in window to dump/restore

1/ Backup: "C:\Program Files\PostgreSQL\9.1\bin\pg_dump.exe" --host localhost --port 5432 --username "postgres" --role "postgres" --format plain --encoding UTF8 --schema-only --file "dump_resul.sql" --schema "name_schema_B" "name_database_A"


-- PostgreSQL database dump

-- Dumped from database version 9.1.4
-- Dumped by pg_dump version 9.1.4

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

CREATE TABLE abtb_temp (
    id bigint NOT NULL,
    app_code character varying(100)
); ....

*Note: some important options: --data-only, --format=format (ex: format=tar -> if you have a big database), --schema-only, --table=table (ex: --table=schema_name.table_name) ...

2/ Restore: "C:\Program Files\PostgreSQL\9.1\bin\pg_restore.exe" --host localhost --port 5432 --username "postgres" --dbname "any_database" --no-password --no-owner --no-privileges --schema name_schema_B --verbose "C:\dump_resul.sql" (**)

(**) In reality, if your format file is *.sql, you can use pgAdmin (or psql) to restore . You should use pg_restore to restore a file .tar (.bakup ...)

share|improve this answer
in response to your comment: when you don't use --schema-only, the data must be in the dump or you may not have the privillige to select the table contents. – maletin Dec 18 '13 at 11:01

This can be used to take schema backup

pg_dump -U UserName -h HostName -s DBName > "file.sql"

This can be used to restore the DB

psql -d DBName -h HostName -U UserName <  "file.sql"
share|improve this answer
This does not answer the question. The question is about backing up only one schema in a database with multiple schemas, not the entire database. – Mark Sinkinson Feb 4 '15 at 11:58

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.