22

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

16

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. Note that with -b all blobs are added not just for a single schema. – Nux Oct 17 '19 at 11:00
8

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

-n schema
--schema=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.

  • 2
    Isn't this what Peter Eisentraut did in his answer? – dezso Feb 17 '16 at 10:09
3
  • 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 ...)

  • 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
0

I wrote a script that makes this trivial. Easy exporting of SQL functions and table schemas into individual *.sql files. Makes it easy to manage, backup, and source control:

#!/bin/sh

# Export all table schemas and functions into individual *.sql files for easy management and source control.

TEMP_WORK_PATH="/tmp/postgres"
BACKUP_ROOT_PATH="/vagrant/backup" # This is where your *.sql files will be exported at
DATABASE_NAME="myapp_db"
DATABASE_TABLES_PREFIX="myapp_"
POSTGRES_USER="postgres"
POSTGRES_PASSWORD="postgres"


[ -d $TEMP_WORK_PATH ] || mkdir -p $TEMP_WORK_PATH
rm -rf $TEMP_WORK_PATH/*

[ -d $BACKUP_ROOT_PATH ] || mkdir -p $BACKUP_ROOT_PATH
rm -rf $BACKUP_ROOT_PATH/*

mkdir $BACKUP_ROOT_PATH/tables
mkdir $BACKUP_ROOT_PATH/routines

export PGPASSWORD=$POSTGRES_PASSWORD

cd $TEMP_WORK_PATH

# Get all table schemas and write to individual files
echo "Export table schemas..."
for table in $(psql -U $POSTGRES_USER -d $DATABASE_NAME -t -c "Select table_name From information_schema.tables Where table_type='BASE TABLE' and table_name like '$DATABASE_TABLES_PREFIX%'");
do pg_dump -st $table -U $POSTGRES_USER $DATABASE_NAME > $BACKUP_ROOT_PATH/tables/$table.sql;
done;

# Get all functions in db and output to one file
echo "Getting stored functions..."
psql -U $POSTGRES_USER -At $DATABASE_NAME > $TEMP_WORK_PATH/db_functions.sql <<EOF
SELECT pg_get_functiondef(f.oid)
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public';
EOF

# Split function file into separate files per function
echo "Exporting stored functions..."
csplit -f function -b '%d.sql' db_functions.sql '/FUNCTION/' '{*}'

# Rename the function files to match the function name in the file
# then place the files into the target backup directory
counter=1
while [ -f $TEMP_WORK_PATH/function$counter.sql ]
do
  name=$(head -1 function$counter.sql | awk {'print $5'})
  name=$(echo $name | cut -d "." --f 2 | cut -d "(" --f 1)
  mv function$counter.sql $BACKUP_ROOT_PATH/routines/$name.sql
  counter=$((counter+1))
done

echo "done"

https://gist.github.com/dantheman213/aff70ee42a11f2d1fa46983878cd62e1

-3

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"
  • 5
    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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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