PostgreSQL: How to backup only One Schema from a database and restore it on another server

Question

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

Answer 1

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.

Answer 2

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.

Answer 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 ...)

Answer 4

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

Answer 5

To backup only one scheme from multiple schema, you can use the following script:

1. Create a dump of database with specific schema.
2. Connect to server and create a new database.
3. Restore the dump of the database to the newly created database.

from subprocess import PIPE,Popen import subprocess

username = user_name password = password host = localhost port= 5432 database = dummy_test schema = 'public' dest_file = /tmp/test.sql

#Dump of specific schema

try:

        process = subprocess.Popen(
                ['pg_dump',
                 '--dbname=postgresql://{}:{}@{}:{}/{}'.format(username, password, host, port, database),
                 '-n', schema,
                 '-Fc',
                 '-f', dest_file,
                 '-v'],
                stdout=subprocess.PIPE
            )
        output = process.communicate()[0]
    except Exception as e:
        print(e)

#connection to the database

    try:
        con = psycopg2.connect(dbname='postgres', port=port,
                           user=username, host=host,
                           password=password)

    except Exception as e:
        print(e)
    
    new_database = 'test_new'
    con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    cur = con.cursor()
    
    try:
        cur.execute("DROP DATABASE {} ;".format(new_database))
    except Exception as e:
        print('DB does not exist, nothing to drop')
    
    #create new database
    cur.execute("CREATE DATABASE {} ;".format(new_database))
    cur.execute("GRANT ALL PRIVILEGES ON DATABASE {} TO {} ;".format(new_database, username))
    
    #restore backup dump into new database
    try:
        backup_file = dest_file
        create_process = subprocess.Popen(
                ['pg_restore',
                 '--no-owner',
                 '--dbname=postgresql://{}:{}@{}:{}/{}'.format(username,
                                                               password,
                                                               host,
                                                               port, new_database),
                 '-v',
                 backup_file],
                stdout=subprocess.PIPE
            )
        output = create_process.communicate()[0]
    except Exception as e:
        print(e)

Answer 6

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"