Table Backup and Recovery Using pg_dump in PostgreSQL

Environment	UAT (Source)	SIT (Target)	SIT (Table Test)
Database	gebua	gebta	gebsit
Server/Host	GEBUA / 192.168.2.23	GEBTA / 192.168.2.21	GEBSIT / 192.168.2.22
Schema/Tables	geb (emp, dept) / trd (orders)	geb.emp, geb.dept (refreshed)	trd.orders (refreshed)
Backup Location	/pgBackup/gebua/backup	/pgBackup/gebta/backup	/pgBackup/gebsit/backup

Table Backup Flags Reference

Flag	Description	Example
`-t table`	Include specific table only	pg_dump -t geb.emp -d gebua
`-t t1 -t t2`	Include multiple tables	pg_dump -t geb.emp -t geb.dept -d gebua
`-T table`	Exclude specific table	pg_dump -T geb.pgbench_accounts -d gebua
`-s`	Structure only (no data)	pg_dump -t geb.dept -s -d gebua
`-a`	Data only (no structure)	pg_dump -t geb.dept -a -d gebua
`-Fc`	Custom format (recommended)	pg_dump -t geb.emp -Fc -f backup.dmp
`-v`	Verbose output	pg_dump -t geb.emp -v -d gebua
`-c`	Restore: drop object before recreating	pg_restore -t geb.emp -c -d gebta
`-l`	List dump contents (TOC)	pg_restore -l backup.dmp

1. Backup a Single Table & Restore

2. Backup Multiple Tables

3. Backup Table Structure Only & Table Data Only

4. Restore Single Table from Multiple Table Backup Dump

5. Restore Single Table from Full Database Backup Dump

6. Drop and Recreate the Table During Restore

7. Restore Single table from Single schema dump

8. Restore Single table from Multi schema dump

9. View Backup Contents

When pg_restore is used with -t (table), PostgreSQL implicitly performs a data-only restore for that table. It does not restore the table definition (DDL). Therefore:

If geb.dept already exists, its data is restored.
If geb.dept does not exist, nothing is restored because there is no table to load data into.
Separated Schema and Table (-n geb -t dept): Using the explicit schema flag (-n geb) along with the table flag (-t dept) prevents pg_restore from getting confused by the dot-notation (geb.dept) filter logic.

pg_restore -d gebta -t dept -n geb -Fc -v 02_task_gebua_geb_emp_dept_backup_binary.dmp > 02_task_gebua_geb_emp_dept_backup_binary_restore_single_table.log 2>&1 &

0. Sample Data on Source

[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 'orders', COUNT(*) FROM trd.orders ORDER BY table_name;"
 table_name | row_count
------------+-----------
 dept       |         5
 emp        |         3
 orders     |   1000001
(3 rows)

[postgres@pgdb02 backup]$

1. Backup a Single Table & Restore

Table-level backups are useful when you only need to refresh or recover one table without touching the rest of the schema.

BACKUP on UAT:

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

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -t geb.emp -Fc -v -f 01_task_gebua_geb_emp_backup_binary.dmp > 01_task_gebua_geb_emp_backup_binary.log 2>&1 &
[1] 4101
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 01_task_gebua_geb_emp_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.emp"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

[postgres@pgdb02 backup]$ scp 01_task_gebua_geb_emp_backup_binary.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/
01_task_gebua_geb_emp_backup_binary.dmp 100% 1367 934.7KB/s 00:00
[postgres@pgdb02 backup]$

RESTORE on SIT:

# Make sure schema should exist
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "CREATE SCHEMA GEB;"
CREATE SCHEMA
[postgres@lxceftsgvdb01 backup]$

# Drop if exits
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "DROP TABLE geb.emp;"
DROP TABLE
[postgres@lxceftsgvdb01 backup]$

# Create SCHEMA IF NOT EXIST
[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fc -v 01_task_gebua_geb_emp_backup_binary.dmp > 01_task_gebua_geb_emp_backup_binary_restore.log 2>&1 &
[1] 4218
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 01_task_gebua_geb_emp_backup_binary_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating TABLE "geb.emp"
pg_restore: processing data for table "geb.emp"
[postgres@lxceftsgvdb01 backup]$

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

[postgres@lxceftsgvdb01 backup]$

2. Backup Multiple Tables

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -t geb.emp -t geb.dept -Fc -v -f 02_task_gebua_geb_emp_dept_backup_binary.dmp > 02_task_gebua_geb_emp_dept_backup_binary.log 2>&1 &
[1] 5210
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 02_task_gebua_geb_emp_dept_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"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

[postgres@pgdb02 backup]$ scp 02_task_gebua_geb_emp_dept_backup_binary.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/
02_task_gebua_geb_emp_dept_backup_binary.dmp 100% 2324 1.4MB/s 00:00
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ psql -d gebta -c "DROP TABLE geb.emp; DROP TABLE geb.dept;"
DROP TABLE
DROP TABLE
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ nohup pg_restore -d gebta -Fc -v 02_task_gebua_geb_emp_dept_backup_binary.dmp > 02_task_gebua_geb_emp_dept_backup_binary_restore.log 2>&1 &
[1] 5233
[postgres@pgdb02 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 02_task_gebua_geb_emp_dept_backup_binary_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating TABLE "geb.dept"
pg_restore: creating TABLE "geb.emp"
pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT 'emp' AS table_name, COUNT(*) FROM geb.emp UNION ALL SELECT 'dept', COUNT(*) FROM geb.dept;"
 table_name | count
------------+-------
 emp        |     3
 dept       |     5
(2 rows)

[postgres@lxceftsgvdb01 backup]$

3. Backup Table Structure Only & Table Data Only

Splitting structure and data into two dumps lets you version/restore DDL independently from the data load — restore structure first, then data.

BACKUP on UAT:

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -t geb.dept -Fc -s -v -f 03_task_gebua_geb_dept_backup_structure.dmp > 03_task_gebua_geb_dept_backup_structure.log 2>&1 &
[1] 5301
[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -t geb.dept -Fc -a -v -f 04_task_gebua_geb_dept_backup_data.dmp > 04_task_gebua_geb_dept_backup_data.log 2>&1 &
[2] 5304
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 03_task_gebua_geb_dept_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]$
[postgres@pgdb02 backup]$
[postgres@pgdb02 backup]$ cat 04_task_gebua_geb_dept_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"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

[postgres@pgdb02 backup]$ scp 03_task_gebua_geb_dept_backup_structure.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/
03_task_gebua_geb_dept_backup_structure.dmp 100% 1461 1.1MB/s 00:00
[postgres@pgdb02 backup]$ scp 04_task_gebua_geb_dept_backup_data.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/
04_task_gebua_geb_dept_backup_data.dmp 100% 1206 625.9KB/s 00:00
[postgres@pgdb02 backup]$

RESTORE on SIT (structure first, then data):

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "DROP TABLE geb.dept;"
DROP TABLE
[postgres@lxceftsgvdb01 backup]$


[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fc -v 03_task_gebua_geb_dept_backup_structure.dmp > 03_task_gebua_geb_dept_backup_structure_restore.log 2>&1 &
[1] 4302
[postgres@lxceftsgvdb01 backup]$ cat 03_task_gebua_geb_dept_backup_structure_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating TABLE "geb.dept"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fc -v 04_task_gebua_geb_dept_backup_data.dmp > 04_task_gebua_geb_dept_backup_data_restore.log 2>&1 &
[1] 4315
[postgres@lxceftsgvdb01 backup]$ cat 04_task_gebua_geb_dept_backup_data_restore.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: implied data-only restore
pg_restore: processing data for table "geb.dept"
[postgres@lxceftsgvdb01 backup]$

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

[postgres@lxceftsgvdb01 backup]$

4. Restore Single Table from Multiple Table Backup Dump

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "DROP TABLE geb.dept;"
DROP TABLE
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ pg_restore -d gebta -n geb -t dept -Fc -v 02_task_gebua_geb_emp_dept_backup_binary.dmp > 02_task_gebua_geb_emp_dept_backup_binary_restore_single_table.log 2>&1 &
[1] 4422
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 02_task_gebua_geb_emp_dept_backup_binary_restore_single_table.log
pg_restore: connecting to database for restore
pg_restore: creating TABLE "geb.dept"
pg_restore: processing data for table "geb.dept"
[postgres@lxceftsgvdb01 backup]$

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

[postgres@lxceftsgvdb01 backup]$

5. Restore Single Table from Full Database Backup Dump

BACKUP (full database) on UAT:

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -Fc -v -f 05_task_gebua_full_backup_binary.dmp > 05_task_gebua_full_backup_binary.log 2>&1 &
[1] 5410
[postgres@pgdb02 backup]$ cat 05_task_gebua_full_backup_binary.log
nohup: ignoring input pg_dump: last built-in OID is 16383
pg_dump: reading schemas
pg_dump: reading user-defined tables
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 "trd.orders"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

[postgres@pgdb02 backup]$ scp 05_task_gebua_full_backup_binary.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/
05_task_gebua_full_backup_binary.dmp 100% 34MB 41.7MB/s 00:00
[postgres@pgdb02 backup]$

RESTORE single table on SIT:

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

[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "DROP TABLE trd.orders;"
DROP TABLE
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ pg_restore -d gebta -n trd -t orders -Fc -v 05_task_gebua_full_backup_binary.dmp > 05_task_gebua_full_backup_binary_restore_single_table.log 2>&1 &
[1] 4509
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ cat 05_task_gebua_full_backup_binary_restore_single_table.log
pg_restore: connecting to database for restore
pg_restore: creating TABLE "trd.orders"
pg_restore: processing data for table "trd.orders"
[postgres@lxceftsgvdb01 backup]$

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

[postgres@lxceftsgvdb01 backup]$

6. Drop and Recreate the Table During Restore

[postgres@lxceftsgvdb01 backup]$ pg_restore -d gebta -n trd -t orders -Fc -c -v 05_task_gebua_full_backup_binary.dmp > 05_task_gebua_full_backup_binary_restore_single_table_c.log 2>&1 &
[1] 4525
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ cat 05_task_gebua_full_backup_binary_restore_single_table_c.log
pg_restore: connecting to database for restore
pg_restore: dropping TABLE orders
pg_restore: creating TABLE "trd.orders"
pg_restore: processing data for table "trd.orders"
[postgres@lxceftsgvdb01 backup]$

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

[postgres@lxceftsgvdb01 backup]$

7. Restore Single table from Single schema dump

BACKUP on UAT:

[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] 4349
[postgres@pgdb02 backup]$

TRANSFER to SIT:

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

RESTORE on SIT:

# Without -n 
[postgres@lxceftsgvdb01 backup]$ pg_restore -d gebta -t dept -c -Fc -v 01_task_gebua_geb_backup_binary.dmp
pg_restore: connecting to database for restore
pg_restore: dropping TABLE dept
pg_restore: creating TABLE "geb.dept"
pg_restore: processing data for table "geb.dept"
[postgres@lxceftsgvdb01 backup]$

-- OR --

# With -n -t
[postgres@lxceftsgvdb01 backup]$ pg_restore -d gebta -n geb -t dept -c -Fc -v 01_task_gebua_geb_backup_binary.dmp
pg_restore: connecting to database for restore
pg_restore: dropping TABLE dept
pg_restore: creating TABLE "geb.dept"
pg_restore: processing data for table "geb.dept"
[postgres@lxceftsgvdb01 backup]$

8. Restore Single table from Multi schema dump

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] 4328
[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]$
[postgres@pgdb02 backup]$ scp 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%   34MB  59.9MB/s   00:00
[postgres@pgdb02 backup]$

RESTORE on SIT:

[postgres@lxceftsgvdb01 backup]$ pg_restore -d gebta -n geb -t dept -Fc -c -v 04_task_gebua_geb_sales_backup_multi_schema.dmp
pg_restore: connecting to database for restore
pg_restore: dropping TABLE dept
pg_restore: creating TABLE "geb.dept"
pg_restore: processing data for table "geb.dept"
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT COUNT(*) FROM geb.dept;"
 count
-------
     5
(1 row)

[postgres@lxceftsgvdb01 backup]$

9. View Backup Contents

# List dump contents
pg_restore -l 01_task_gebua_geb_emp_backup_binary.dmp

[postgres@lxceftsgvdb01 backup]$ pg_restore -l 01_task_gebua_geb_emp_backup_binary.dmp
;
; Archive created at 2026-07-16 19:50:13 +08
; dbname: gebua
; TOC Entries: 6
; 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:
;
219; 1259 32844 TABLE geb emp gebadm
4354; 0 32844 TABLE DATA geb emp gebadm
[postgres@lxceftsgvdb01 backup]$

# Extract only object definitions (Table Only)
[postgres@lxceftsgvdb01 backup]$ pg_restore -s -f emp_table_metadata.sql 01_task_gebua_geb_emp_backup_binary.dmp
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ cat emp_table_metadata.sql
--
-- PostgreSQL database dump
--

\restrict de2CWMbMHynBifQUiksm12g6tnrI5bb0441VuCUrKEkAOPy97yAFZStx9E95REZ

-- Dumped from database version 17.10
-- Dumped by pg_dump version 17.10

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: emp; Type: TABLE; Schema: geb; Owner: gebadm
--

CREATE TABLE geb.emp (
    name text,
    designation text,
    project text,
    company text
);


ALTER TABLE geb.emp OWNER TO gebadm;

--
-- PostgreSQL database dump complete
--

\unrestrict de2CWMbMHynBifQUiksm12g6tnrI5bb0441VuCUrKEkAOPy97yAFZStx9E95REZ

[postgres@lxceftsgvdb01 backup]$


# Extract the dump into SQL (extract DDL and data)
[postgres@lxceftsgvdb01 backup]$ pg_restore -f emp_table_data_only.sql 01_task_gebua_geb_emp_backup_binary.dmp
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ cat emp_table_data_only.sql
--
-- PostgreSQL database dump
--

\restrict Y08cgQwrYR77M0cDFpfm0kElelenUf9hXTzya2BOG9ZVZ2tK1MrqAYWIJdJMKHN

-- Dumped from database version 17.10
-- Dumped by pg_dump version 17.10

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: emp; Type: TABLE; Schema: geb; Owner: gebadm
--

CREATE TABLE geb.emp (
    name text,
    designation text,
    project text,
    company text
);


ALTER TABLE geb.emp OWNER TO gebadm;

--
-- Data for Name: emp; Type: TABLE DATA; Schema: geb; Owner: gebadm
--

COPY geb.emp (name, designation, project, company) FROM stdin;
Sugi    DBA     Jetstar iGATE
Teja    DBA     RCM     iGATE
RAJ     DBA     RCM     iGATE
\.


--
-- PostgreSQL database dump complete
--

\unrestrict Y08cgQwrYR77M0cDFpfm0kElelenUf9hXTzya2BOG9ZVZ2tK1MrqAYWIJdJMKHN

[postgres@lxceftsgvdb01 backup]$

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

Table Backup and Recovery Using pg_dump in PostgreSQL

Table Backup and Recovery Using pg_dump in PostgreSQL

Table Backup Flags Reference

Contents

0. Sample Data on Source

1. Backup a Single Table & Restore

2. Backup Multiple Tables

3. Backup Table Structure Only & Table Data Only

4. Restore Single Table from Multiple Table Backup Dump

5. Restore Single Table from Full Database Backup Dump

6. Drop and Recreate the Table During Restore

7. Restore Single table from Single schema dump

8. Restore Single table from Multi schema dump

9. View Backup Contents