PostgreSQL Schema Level Backup & Restore

Environment	UAT (Source)	SIT (Target)
Database	gebua	gebta
Server/Host	GEBUA / 192.168.2.23	GEBTA / 192.168.2.21
Schema 1	geb (tables: emp, dept,hr,pgbench_accounts,pgbench_branches,pgbench_history,pgbench_tellers)	geb (to be populated)
Schema 2	trd (table: orders)	trd (optional)
Backup Location	/pgBackup/gebua/backup	/pgBackup/gebta/backup

Schema Backup Flags Reference

Flag	Description	Example
`-n schema`	Include specific schema only	pg_dump -n geb -d gebua
`-n schema1 -n schema2`	Include multiple schemas	pg_dump -n geb -n sales -d gebua
`-N schema`	Exclude specific schema	pg_dump -N pg_catalog -d gebua
`-s`	Structure only (no data)	pg_dump -n geb -s -d gebua
`-a`	Data only (no structure)	pg_dump -n geb -a -d gebua
`-Fc`	Custom format (recommended)	pg_dump -n geb -Fc -f backup.dmp
`-v`	Verbose output	pg_dump -n geb -v -d gebua
`-O`	Skip object ownership	pg_dump -n geb -O -d gebua

1. Single Schema Backup/Restore

2. Single Schema Backup/Restore(STRUCTURE ONLY + DATA BACKUP)

3. Multiple Schemas Backup/Restore

4. Restore Single Schema from Multi-Schema Dump

5. Restore Single Schema from Full Database Dump

6. List Dump Contents

Important: Do not use the -n option during restore unless it is absolutely necessary.

When the -n option is used, PostgreSQL restores only the specified schema. However, if that schema does not already exist in the target database, the restore will fail even if the schema is present in the backup dump file.

If you decide to use the -n option to restore a single schema from either:

a multi-schema backup dump, or
a full database backup dump,

make sure you create the target schema before starting the restore. Otherwise, the restore will fail because pg_restore -n does not create the schema automatically.

0. Sample Data on Source


SCHEMA 1: GEB

[postgres@pgdb02 backup]$ psql -d gebua -c "SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL SELECT 'dept', COUNT(*) FROM geb.dept UNION ALL SELECT 'pgbench_accounts', COUNT(*) FROM geb.pgbench_accounts UNION ALL SELECT 'pgbench_branches', COUNT(*) FROM geb.pgbench_branches UNION ALL SELECT 'pgbench_history', COUNT(*) FROM geb.pgbench_history UNION ALL SELECT 'pgbench_tellers', COUNT(*) FROM geb.pgbench_tellers ORDER BY table_name;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@pgdb02 backup]$

SCHEMA 2: TRD
[postgres@pgdb02 backup]$ psql -d gebua -c "SELECT COUNT(*) FROM TRD.ORDERS;"
  count
---------
 1000001
(1 row)

[postgres@pgdb02 backup]$

1. Single Schema Backup/Restore

BACKUP on UAT:

[postgres@pgdb02 ~]$ cd /pgBackup/gebua/backup
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -n geb -Fc -v -f 01_task_gebua_geb_backup_binary.dmp > 01_task_gebua_geb_backup_binary.log 2>&1 &
[1] 4312
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 01_task_gebua_geb_backup_binary.log
nohup: ignoring input
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading subscription membership of tables
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
pg_dump: dumping contents of table "geb.dept"
pg_dump: dumping contents of table "geb.emp"
pg_dump: dumping contents of table "geb.pgbench_accounts"
pg_dump: dumping contents of table "geb.pgbench_branches"
pg_dump: dumping contents of table "geb.pgbench_history"
pg_dump: dumping contents of table "geb.pgbench_tellers"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

[postgres@pgdb02 backup]$ scp 01_task_gebua_geb_backup_binary.dmp lxceftsgvdb01:/pgBackup/gebta/backup
01_task_gebua_geb_backup_binary.dmp 100% 54MB 62.4MB/s 00:00
[postgres@pgdb02 backup]$

RESTORE on SIT:

# Without Error

Note: Please don't use the '-n' option during restore. The restore will fail if the specified schema does not already exist in the target database, even if that schema is present in the backup dump file.

psql -d gebta -c "DROP SCHEMA geb CASCADE;"   
nohup pg_restore -d gebta -Fc -v 01_task_gebua_geb_backup_binary.dmp > 01_task_gebua_geb_backup_binary_restore.log 2>&1 &

[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fc -c -v 01_task_gebua_geb_backup_binary.dmp > 01_task_gebua_geb_backup_binary_restore.log 2>&1 &
[postgres@lxceftsgvdb01 backup]$ cat 01_task_gebua_geb_backup_binary_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey
pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey
pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey
pg_restore: dropping CONSTRAINT dept dept_pkey
pg_restore: dropping TABLE pgbench_tellers
pg_restore: dropping TABLE pgbench_history
pg_restore: dropping TABLE pgbench_branches
pg_restore: dropping TABLE pgbench_accounts
pg_restore: dropping TABLE emp
pg_restore: dropping TABLE dept
pg_restore: dropping SCHEMA geb
pg_restore: creating SCHEMA "geb"
pg_restore: creating TABLE "geb.dept"
pg_restore: creating TABLE "geb.emp"
pg_restore: creating TABLE "geb.pgbench_accounts"
pg_restore: creating TABLE "geb.pgbench_branches"
pg_restore: creating TABLE "geb.pgbench_history"
pg_restore: creating TABLE "geb.pgbench_tellers"
pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: processing data for table "geb.pgbench_accounts"
pg_restore: processing data for table "geb.pgbench_branches"
pg_restore: processing data for table "geb.pgbench_history"
pg_restore: processing data for table "geb.pgbench_tellers"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "\dn;"
      List of schemas
  Name  |       Owner
--------+-------------------
 geb    | gebadm
 public | pg_database_owner
(2 rows)

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL SELECT 'dept', COUNT(*) FROM geb.dept UNION ALL SELECT 'pgbench_accounts', COUNT(*) FROM geb.pgbench_accounts UNION ALL SELECT 'pgbench_branches', COUNT(*) FROM geb.pgbench_branches UNION ALL SELECT 'pgbench_history', COUNT(*) FROM geb.pgbench_history UNION ALL SELECT 'pgbench_tellers', COUNT(*) FROM geb.pgbench_tellers ORDER BY table_name;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@lxceftsgvdb01 backup]$

2. Single Schema Backup/Restore(STRUCTURE ONLY + DATA BACKUP)

BACKUP on UAT:(BOTH STRUCTURE + DATA)

# Backup Structure
[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -n geb -Fc -s -v -f 02_task_gebua_geb_backup_structure.dmp > 02_task_gebua_geb_backup_structure.log 2>&1 &

[postgres@pgdb02 backup]$ cat 02_task_gebua_geb_backup_structure.log
nohup: ignoring input
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading subscription membership of tables
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
[postgres@pgdb02 backup]$

# Backup Data only 
[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -n geb -Fc -a -v -f 03_task_gebua_geb_backup_data.dmp > 03_task_gebua_geb_backup_data.log 2>&1 &

[postgres@pgdb02 backup]$ cat 03_task_gebua_geb_backup_data.log
nohup: ignoring input
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading subscription membership of tables
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
pg_dump: dumping contents of table "geb.dept"
pg_dump: dumping contents of table "geb.emp"
pg_dump: dumping contents of table "geb.pgbench_accounts"
pg_dump: dumping contents of table "geb.pgbench_branches"
pg_dump: dumping contents of table "geb.pgbench_history"
pg_dump: dumping contents of table "geb.pgbench_tellers"
[postgres@pgdb02 backup]$

TRANSFER to SIT:(BOTH STRUCTURE + DATA)

scp /pgBackup/gebua/backup/02_task_gebua_geb_backup_structure.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/
scp /pgBackup/gebua/backup/03_task_gebua_geb_backup_data.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/

RESTORE on SIT: (BOTH STRUCTURE + DATA)

psql -d gebua -c "DROP SCHEMA geb CASCADE;"

# Restore Structure
[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fc -c -v 02_task_gebua_geb_backup_structure.dmp > 02_task_gebua_geb_backup_structure_restore.log 2>&1 &

[postgres@lxceftsgvdb01 backup]$ cat 02_task_gebua_geb_backup_structure_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey
pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey
pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey
pg_restore: dropping CONSTRAINT dept dept_pkey
pg_restore: dropping TABLE pgbench_tellers
pg_restore: dropping TABLE pgbench_history
pg_restore: dropping TABLE pgbench_branches
pg_restore: dropping TABLE pgbench_accounts
pg_restore: dropping TABLE emp
pg_restore: dropping TABLE dept
pg_restore: creating TABLE "geb.dept"
pg_restore: creating TABLE "geb.emp"
pg_restore: creating TABLE "geb.pgbench_accounts"
pg_restore: creating TABLE "geb.pgbench_branches"
pg_restore: creating TABLE "geb.pgbench_history"
pg_restore: creating TABLE "geb.pgbench_tellers"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$

# Restore Data
[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fc -a -v 03_task_gebua_geb_backup_data.dmp > 03_task_gebua_geb_backup_data_restore.log 2>&1 &

[postgres@lxceftsgvdb01 backup]$ cat 03_task_gebua_geb_backup_data_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: processing data for table "geb.pgbench_accounts"
pg_restore: processing data for table "geb.pgbench_branches"
pg_restore: processing data for table "geb.pgbench_history"
pg_restore: processing data for table "geb.pgbench_tellers"
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "\dn;"
      List of schemas
  Name  |       Owner
--------+-------------------
 geb    | gebadm
 public | pg_database_owner
(2 rows)

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL SELECT 'dept', COUNT(*) FROM geb.dept UNION ALL SELECT 'pgbench_accounts', COUNT(*) FROM geb.pgbench_accounts UNION ALL SELECT 'pgbench_branches', COUNT(*) FROM geb.pgbench_branches UNION ALL SELECT 'pgbench_history', COUNT(*) FROM geb.pgbench_history UNION ALL SELECT 'pgbench_tellers', COUNT(*) FROM geb.pgbench_tellers ORDER BY table_name;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@lxceftsgvdb01 backup]$

3. Multiple Schemas Backup/Restore

BACKUP on UAT:

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -n geb -n trd -Fc -v -f 04_task_gebua_geb_sales_backup_multi_schema.dmp > 04_task_gebua_geb_sales_backup_multi_schema.log 2>&1 &
[1] 4688
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 04_task_gebua_geb_sales_backup_multi_schema.log
nohup: ignoring input
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding table default expressions
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading subscription membership of tables
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
pg_dump: dumping contents of table "geb.dept"
pg_dump: dumping contents of table "geb.emp"
pg_dump: dumping contents of table "geb.pgbench_accounts"
pg_dump: dumping contents of table "geb.pgbench_branches"
pg_dump: dumping contents of table "geb.pgbench_history"
pg_dump: dumping contents of table "geb.pgbench_tellers"
pg_dump: dumping contents of table "trd.orders"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

[postgres@pgdb02 backup]$ scp /pgBackup/gebua/backup/04_task_gebua_geb_sales_backup_multi_schema.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/
04_task_gebua_geb_sales_backup_multi_schema.dmp 100% 27MB 55.1MB/s 00:00
[postgres@pgdb02 backup]$

RESTORE on SIT:

# Without errors -- Manually 
psql -d gebua -c "DROP SCHEMA geb CASCADE; DROP SCHEMA sales CASCADE;"
nohup pg_restore -d gebta -Fc -v 04_task_gebua_geb_sales_backup_multi_schema.dmp > 04_task_gebua_geb_sales_backup_multi_schema_restore.log 2>&1 &

# Drop and recreate
[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fc -c -v 04_task_gebua_geb_sales_backup_multi_schema.dmp > 04_task_gebua_geb_sales_backup_multi_schema_restore.log 2>&1 &
[1] 4305
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 04_task_gebua_geb_sales_backup_multi_schema_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT orders orders_pkey
pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey
pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey
pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey
pg_restore: dropping CONSTRAINT dept dept_pkey
pg_restore: dropping DEFAULT orders order_id
pg_restore: dropping SEQUENCE orders_order_id_seq
pg_restore: dropping TABLE orders
pg_restore: dropping TABLE pgbench_tellers
pg_restore: dropping TABLE pgbench_history
pg_restore: dropping TABLE pgbench_branches
pg_restore: dropping TABLE pgbench_accounts
pg_restore: dropping TABLE emp
pg_restore: dropping TABLE dept
pg_restore: dropping SCHEMA trd
pg_restore: dropping SCHEMA geb
pg_restore: creating SCHEMA "geb"
pg_restore: creating SCHEMA "trd"
pg_restore: creating TABLE "geb.dept"
pg_restore: creating TABLE "geb.emp"
pg_restore: creating TABLE "geb.pgbench_accounts"
pg_restore: creating TABLE "geb.pgbench_branches"
pg_restore: creating TABLE "geb.pgbench_history"
pg_restore: creating TABLE "geb.pgbench_tellers"
pg_restore: creating TABLE "trd.orders"
pg_restore: creating SEQUENCE "trd.orders_order_id_seq"
pg_restore: creating SEQUENCE OWNED BY "trd.orders_order_id_seq"
pg_restore: creating DEFAULT "trd.orders order_id"
pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: processing data for table "geb.pgbench_accounts"
pg_restore: processing data for table "geb.pgbench_branches"
pg_restore: processing data for table "geb.pgbench_history"
pg_restore: processing data for table "geb.pgbench_tellers"
pg_restore: processing data for table "trd.orders"
pg_restore: executing SEQUENCE SET orders_order_id_seq
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
pg_restore: creating CONSTRAINT "trd.orders orders_pkey"
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "\dn;"
      List of schemas
  Name  |       Owner
--------+-------------------
 geb    | gebadm
 public | pg_database_owner
 trd    | trdadm
(3 rows)

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL SELECT 'dept', COUNT(*) FROM geb.dept UNION ALL SELECT 'pgbench_accounts', COUNT(*) FROM geb.pgbench_accounts UNION ALL SELECT 'pgbench_branches', COUNT(*) FROM geb.pgbench_branches UNION ALL SELECT 'pgbench_history', COUNT(*) FROM geb.pgbench_history UNION ALL SELECT 'pgbench_tellers', COUNT(*) FROM geb.pgbench_tellers ORDER BY table_name;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT COUNT(*) FROM TRD.ORDERS;"
  count
---------
 1000001
(1 row)

[postgres@lxceftsgvdb01 backup]$

4. Restore Single Schema from Multi-Schema Dump

[postgres@lxceftsgvdb01 ~]$ psql -d gebta
psql (17.10)
Type "help" for help.

gebta=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 geb    | gebadm
 public | pg_database_owner
 trd    | trdadm
(3 rows)

gebta=# drop schema geb cascade;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to table geb.dept
drop cascades to table geb.emp
drop cascades to table geb.pgbench_accounts
drop cascades to table geb.pgbench_branches
drop cascades to table geb.pgbench_history
drop cascades to table geb.pgbench_tellers
DROP SCHEMA
gebta=#
gebta=# drop schema trd cascade;
NOTICE:  drop cascades to table trd.orders
DROP SCHEMA
gebta=#
gebta=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

gebta=#


[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -n trd -Fc -v 04_task_gebua_geb_sales_backup_multi_schema.dmp > 04_task_gebua_sales_restore_single_from_multi.log 2>&1 &
[1] 3725
[postgres@lxceftsgvdb01 backup]$
[1]+ Exit 1 nohup pg_restore -d gebta -n trd -Fc -v 04_task_gebua_geb_sales_backup_multi_schema.dmp > 04_task_gebua_sales_restore_single_from_multi.log 2>&1
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 04_task_gebua_sales_restore_single_from_multi.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating TABLE "trd.orders"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 222; 1259 41085 TABLE orders trdadm
pg_restore: error: could not execute query: ERROR:  schema "trd" does not exist
LINE 1: CREATE TABLE trd.orders (
                     ^
Command was: CREATE TABLE trd.orders (
    order_id integer NOT NULL,
    product character varying(100),
    amount numeric(10,2)
);


pg_restore: error: could not execute query: ERROR:  schema "trd" does not exist
Command was: ALTER TABLE trd.orders OWNER TO trdadm;

pg_restore: creating SEQUENCE "trd.orders_order_id_seq"
pg_restore: from TOC entry 221; 1259 41084 SEQUENCE orders_order_id_seq trdadm
pg_restore: error: could not execute query: ERROR:  schema "trd" does not exist
Command was: CREATE SEQUENCE trd.orders_order_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


pg_restore: error: could not execute query: ERROR:  schema "trd" does not exist
Command was: ALTER SEQUENCE trd.orders_order_id_seq OWNER TO trdadm;

pg_restore: creating SEQUENCE OWNED BY "trd.orders_order_id_seq"
pg_restore: from TOC entry 4386; 0 0 SEQUENCE OWNED BY orders_order_id_seq trdadm
pg_restore: error: could not execute query: ERROR:  schema "trd" does not exist
Command was: ALTER SEQUENCE trd.orders_order_id_seq OWNED BY trd.orders.order_id;


pg_restore: creating DEFAULT "trd.orders order_id"
pg_restore: from TOC entry 4216; 2604 41088 DEFAULT orders order_id trdadm
pg_restore: error: could not execute query: ERROR:  schema "trd" does not exist
Command was: ALTER TABLE ONLY trd.orders ALTER COLUMN order_id SET DEFAULT nextval('trd.orders_order_id_seq'::regclass);


pg_restore: processing data for table "trd.orders"
pg_restore: from TOC entry 4375; 0 41085 TABLE DATA orders trdadm
pg_restore: error: could not execute query: ERROR:  schema "trd" does not exist
Command was: COPY trd.orders (order_id, product, amount) FROM stdin;
pg_restore: executing SEQUENCE SET orders_order_id_seq
pg_restore: from TOC entry 4387; 0 0 SEQUENCE SET orders_order_id_seq trdadm
pg_restore: error: could not execute query: ERROR:  relation "trd.orders_order_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('trd.orders_order_id_seq', 1000001,...
                                 ^
Command was: SELECT pg_catalog.setval('trd.orders_order_id_seq', 1000001, true);


pg_restore: creating CONSTRAINT "trd.orders orders_pkey"
pg_restore: from TOC entry 4220; 2606 41090 CONSTRAINT orders orders_pkey trdadm
pg_restore: error: could not execute query: ERROR:  schema "trd" does not exist
Command was: ALTER TABLE ONLY trd.orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (order_id);


pg_restore: warning: errors ignored on restore: 9
[postgres@lxceftsgvdb01 backup]$



Issue : If the target schema does not already exist in your destination database, pg_restore will throw an error because it does not automatically create the schema container when filtering with the -n flag.

Solution: # CREATE SCHEMA FIRST THEN TRIGGER RESTORE

# CREATE SCHEMA 
gebta=# CREATE SCHEMA TRD;
CREATE SCHEMA
gebta=#

gebta=# ALTER SCHEMA TRD OWNER TO TRDADM; 
ALTER SCHEMA 
gebta=# 

# Restore singel schema 'trd' from multi schema dump file

[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -n trd -Fc -v 04_task_gebua_geb_sales_backup_multi_schema.dmp > 04_task_gebua_sales_restore_single_from_multi.log 2>&1 &
[1] 3752
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 04_task_gebua_sales_restore_single_from_multi.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating TABLE "trd.orders"
pg_restore: creating SEQUENCE "trd.orders_order_id_seq"
pg_restore: creating SEQUENCE OWNED BY "trd.orders_order_id_seq"
pg_restore: creating DEFAULT "trd.orders order_id"
pg_restore: processing data for table "trd.orders"
pg_restore: executing SEQUENCE SET orders_order_id_seq
pg_restore: creating CONSTRAINT "trd.orders orders_pkey"
[postgres@lxceftsgvdb01 backup]$

Note: ALTER SCHEMA OWNER is optional. If the original schema owner does not exist on the target database, you can use the --no-owner option during restore to skip restoring object ownership.

5. Restore Single Schema from Full Database Dump

BACKUP on UAT:

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -Fc -v -f 02_task_gebua_full_backup_binary.dmp > 02_task_gebua_full_backup_binary.log 2>&1 &
[1] 4737
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 02_task_gebua_full_backup_binary.log
nohup: ignoring input
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding table default expressions
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading subscription membership of tables
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
pg_dump: dumping contents of table "geb.dept"
pg_dump: dumping contents of table "geb.emp"
pg_dump: dumping contents of table "geb.pgbench_accounts"
pg_dump: dumping contents of table "geb.pgbench_branches"
pg_dump: dumping contents of table "geb.pgbench_history"
pg_dump: dumping contents of table "geb.pgbench_tellers"
pg_dump: dumping contents of table "trd.orders"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

scp /pgBackup/gebua/backup/02_task_gebua_full_backup_binary.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/

RESTORE on SIT:

If the target schema does not already exist in your destination database, pg_restore will throw an error because it does not automatically create the schema container when filtering with the -n flag.

# Cleanup

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "DROP SCHEMA trd CASCADE;"
NOTICE: drop cascades to table trd.orders
DROP SCHEMA
[postgres@lxceftsgvdb01 backup]$

# Restore
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "CREATE SCHEMA trd;"
CREATE SCHEMA
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "ALTER SCHEMA TRD OWNER TO TRDADM;"
ALTER SCHEMA
[postgres@lxceftsgvdb01 backup]$

# I will remove '-c' flag
[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -n trd -Fc -v 02_task_gebua_full_backup_binary.dmp > 02_task_gebua_full_backup_binary_restore.log 2>&1 &
[1] 3814
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 02_task_gebua_full_backup_binary_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating TABLE "trd.orders"
pg_restore: creating SEQUENCE "trd.orders_order_id_seq"
pg_restore: creating SEQUENCE OWNED BY "trd.orders_order_id_seq"
pg_restore: creating DEFAULT "trd.orders order_id"
pg_restore: processing data for table "trd.orders"
pg_restore: executing SEQUENCE SET orders_order_id_seq
pg_restore: creating CONSTRAINT "trd.orders orders_pkey"
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "\dn;"
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
 trd    | trdadm
(2 rows)

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "select count(*) from trd.orders;"
  count
---------
 1000001  <--------------
(1 row)

[postgres@lxceftsgvdb01 backup]$

6. List Dump Contents

#  List dumps
pg_restore -l 02_task_gebua_full_backup_binary.dmp

[postgres@lxceftsgvdb01 backup]$ pg_restore -l 02_task_gebua_full_backup_binary.dmp
;
; Archive created at 2026-07-15 20:46:43 +08
;     dbname: gebua
;     TOC Entries: 29
;     Compression: gzip
;     Dump Version: 1.16-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 17.10
;     Dumped by pg_dump version: 17.10
;
;
; Selected TOC Entries:
;
6; 2615 32843 SCHEMA - geb gebadm
7; 2615 41083 SCHEMA - trd trdadm
220; 1259 32849 TABLE geb dept gebadm
219; 1259 32844 TABLE geb emp gebadm
225; 1259 41097 TABLE geb pgbench_accounts gebadm
226; 1259 41100 TABLE geb pgbench_branches gebadm
223; 1259 41091 TABLE geb pgbench_history gebadm
224; 1259 41094 TABLE geb pgbench_tellers gebadm
222; 1259 41085 TABLE trd orders trdadm
221; 1259 41084 SEQUENCE trd orders_order_id_seq trdadm
4386; 0 0 SEQUENCE OWNED BY trd orders_order_id_seq trdadm
4216; 2604 41088 DEFAULT trd orders order_id trdadm
4373; 0 32849 TABLE DATA geb dept gebadm
4372; 0 32844 TABLE DATA geb emp gebadm
4378; 0 41097 TABLE DATA geb pgbench_accounts gebadm
4379; 0 41100 TABLE DATA geb pgbench_branches gebadm
4376; 0 41091 TABLE DATA geb pgbench_history gebadm
4377; 0 41094 TABLE DATA geb pgbench_tellers gebadm
4375; 0 41085 TABLE DATA trd orders trdadm
4387; 0 0 SEQUENCE SET trd orders_order_id_seq trdadm
4218; 2606 32853 CONSTRAINT geb dept dept_pkey gebadm
4224; 2606 41112 CONSTRAINT geb pgbench_accounts pgbench_accounts_pkey gebadm
4226; 2606 41108 CONSTRAINT geb pgbench_branches pgbench_branches_pkey gebadm
4222; 2606 41110 CONSTRAINT geb pgbench_tellers pgbench_tellers_pkey gebadm
4220; 2606 41090 CONSTRAINT trd orders orders_pkey trdadm
[postgres@lxceftsgvdb01 backup]$

# Extract only object definitions (Schema Only)
pg_restore -s -f schema.sql 02_task_gebua_full_backup_binary.dmp

# Extract the dump into SQL (extract DDL and data)
pg_restore -f backup.sql 02_task_gebua_full_backup_binary.dmp

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you

Rajasekhar Amudala

Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala

BRIGHT DBA

ORACLE DBA – Tips and Techniques

Schema Backup and Restore Using pg_dump in PostgreSQL

PostgreSQL Schema Level Backup & Restore

Schema Backup Flags Reference

Contents

0. Sample Data on Source

1. Single Schema Backup/Restore

2. Single Schema Backup/Restore(STRUCTURE ONLY + DATA BACKUP)

3. Multiple Schemas Backup/Restore

4. Restore Single Schema from Multi-Schema Dump

5. Restore Single Schema from Full Database Dump

6. List Dump Contents