32

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

29

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.

0
13

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.

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

-1

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)
1
  • What does your script do that the other answers don't? – Colin 't Hart Jul 7 '20 at 16:40

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.