current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
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. It's 100% free, no registration required.
up vote 3 down vote favorite

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

2 Answers 2

up vote 2 down vote

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 -f 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
    
Thanks Peter for your response. I was able to backup and restore Schema. but there was no data. to be clear I want to backup and restore ScemaB+data. I will appreciate your help. secondly forgot to metion that Postgresql 9.1 running on Ubuntu 12.04. –  user30422 Nov 30 '13 at 2:23
up vote 0 down vote
  • 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"

Results:

-- 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
1  
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

Your Answer

 
discard

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.