Tag Archives: PostgreSQL backup best practices

Schema Backup and Restore Using pg_dump in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

PostgreSQL Schema Level Backup & Restore

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

Schema Backup Flags Reference

FlagDescriptionExample
-n schemaInclude specific schema onlypg_dump -n geb -d gebua
-n schema1 -n schema2Include multiple schemaspg_dump -n geb -n sales -d gebua
-N schemaExclude specific schemapg_dump -N pg_catalog -d gebua
-sStructure only (no data)pg_dump -n geb -s -d gebua
-aData only (no structure)pg_dump -n geb -a -d gebua
-FcCustom format (recommended)pg_dump -n geb -Fc -f backup.dmp
-vVerbose outputpg_dump -n geb -v -d gebua
-OSkip object ownershippg_dump -n geb -O -d gebua

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

Backup & Restore using PostgreSQL COPY

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Backup & Restore using PostgreSQL COPY

Table of Contents

1. Understanding the COPY Command

PostgreSQL’s COPY command is one of the most efficient ways to export and import data to/from files. Unlike traditional INSERT statements, COPY can transfer large amounts of data in seconds, making it ideal for backups, data migrations, and bulk operations. Remember to always test restore procedures in non-production environments and maintain multiple backups in different formats for maximum data safety.

  • COPY TO: Exports data from a PostgreSQL table to a file
  • COPY FROM: Imports data from a file into a PostgreSQL table
  • Why use it: 10-100x faster than individual INSERT statements for bulk operations
  • Flexible formatting: Support for tab-delimited, comma-delimited, CSV, and custom formats
  • Query support: Can export results of complex queries, not just entire tables
  • Selective import: Import only specific columns; others receive NULL or DEFAULT values

Key Point: COPY is not meant to replace full backups using pg_dump, but it’s perfect for selective data export/import, data migrations, and quick backups of specific tables.

2. Setup: Create Schema & Test Data

First, create a schema and sample table with test data:

postgres=# \c
You are now connected to database "postgres" as user "postgres".

-- Create user 

postgres=# create user edbadm password 'edbadm';
CREATE ROLE
postgres=# 

-- Create Database 

postgres=# CREATE DATABASE edbua;
CREATE DATABASE
postgres=# 

-- Create schema and insert some data.
postgres=# \c edbua
You are now connected to database "edbua" as user "postgres".
edbua=#
edbua=# CREATE SCHEMA edb AUTHORIZATION edbadm;
CREATE SCHEMA
edbua=#

postgres=# \c edbua edbadm
You are now connected to database "edbua" as user "edbadm".
edbua=>
edbua=> CREATE TABLE edb.emp (id SERIAL PRIMARY KEY, name TEXT NOT NULL, designation TEXT, project TEXT,company TEXT);
CREATE TABLE
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('Sugi', 'DBA', 'Jetstar', 'iGATE');
INSERT 0 1
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('Teja', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('RAJ', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('Arun', 'Developer', 'Jetstar', 'TCS');
INSERT 0 1
edbua=> INSERT INTO edb.emp (name, designation, project, company) VALUES ('Priya', 'QA', 'RCM', 'Wipro');
INSERT 0 1
edbua=> SELECT * FROM edb.emp;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=> SELECT COUNT(*) FROM edb.emp;
 count
-------
     5 <-----
(1 row)

edbua=>

Backup directory setup (execute from terminal as root):

[root@pgdb02 ~]# mkdir -p /pgBackup/edbua/backup/
[root@pgdb02 ~]# chown postgres:postgres /pgBackup/edbua/backup/
[root@pgdb02 ~]#

3. Basic Export & Import (Tab-Delimited)

Export: Export all data from a table to a file using the default tab-delimited format:

-- Basic COPY: Exports all data (tab-delimited by default)

postgres=# \c edbua
You are now connected to database "edbua" as user "postgres".
edbua=#
edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt';
COPY 5
edbua=#

This creates a simple text file with each record on one line, columns separated by tabs. To view the exported file from terminal:

[postgres@pgdb02 ~]$ ls -ltr /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
-rw-r--r--. 1 postgres postgres 116 Jul 10 15:17 /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ wc -l /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
5 /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt
1           Sugi            DBA         Jetstar          iGATE
2           Teja            DBA         RCM              iGATE
3           RAJ             DBA         RCM              iGATE
4           Arun            Developer   Jetstar           TCS
5           Priya           QA          RCM               Wipro
[postgres@pgdb02 ~]$

The output shows each employee record with columns separated by tabs. This format is useful for simple, fast backups.

Import: Restore data from the backup file:

--- Simulate data loss - truncate the table

edbua=# TRUNCATE TABLE edb.emp CASCADE;
TRUNCATE TABLE
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     0
(1 row)

edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp.txt';
COPY 5
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     5  <----
(1 row)

edbua=# SELECT * FROM edb.emp;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=#

4. Custom Delimiters – Export & Import

Use different delimiters to format data for different systems and tools. Choose the delimiter that best matches your target system or data import tool. When importing, ensure the DELIMITER option matches the file format.

Export with Pipe Delimiter (|):

--- Export

edbua=# \c
You are now connected to database "edbua" as user "postgres".
edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_pipe.txt' WITH DELIMITER '|';
COPY 5
edbua=#

edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp_pipe.txt
1|Sugi|DBA|Jetstar|iGATE
2|Teja|DBA|RCM|iGATE
3|RAJ|DBA|RCM|iGATE
4|Arun|Developer|Jetstar|TCS
5|Priya|QA|RCM|Wipro
edbua=#


----- IMPORT 

--- Simulate data loss - truncate the table
edbua=# TRUNCATE TABLE edb.emp CASCADE;
TRUNCATE TABLE
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     0
(1 row)

edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_pipe.txt' WITH DELIMITER '|';
COPY 5
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     5  <----
(1 row)

edbua=# SELECT * FROM edb.emp;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=#

Export with Comma Delimiter:

-- EXPORT

edbua=# \c
You are now connected to database "edbua" as user "postgres".
edbua=#
edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_comma.txt' WITH DELIMITER ',';
COPY 5
edbua=#
edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp_comma.txt
1,Sugi,DBA,Jetstar,iGATE
2,Teja,DBA,RCM,iGATE
3,RAJ,DBA,RCM,iGATE
4,Arun,Developer,Jetstar,TCS
5,Priya,QA,RCM,Wipro
edbua=#

-- IMPORT

--- Simulate data loss - truncate the table
edbua=# TRUNCATE TABLE edb.emp CASCADE;
TRUNCATE TABLE
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     0
(1 row)

edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_comma.txt' WITH DELIMITER ',';
COPY 5
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     5   <-------
(1 row)

edbua=# SELECT * FROM edb.emp;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=#

Export with Semicolon Delimiter:

-- EXPORT

edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_semicolon.txt' WITH DELIMITER ';';
COPY 5
edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp_semicolon.txt
1;Sugi;DBA;Jetstar;iGATE
2;Teja;DBA;RCM;iGATE
3;RAJ;DBA;RCM;iGATE
4;Arun;Developer;Jetstar;TCS
5;Priya;QA;RCM;Wipro
edbua=#

-- IMPORT
--- Simulate data loss - truncate the table
edbua=# TRUNCATE TABLE edb.emp CASCADE;
TRUNCATE TABLE
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     0
(1 row)

edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_semicolon.txt' WITH DELIMITER ';';
COPY 5
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     5  <-----------------
(1 row)

edbua=# SELECT * FROM edb.emp;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=#

5. CSV Format – Export & Import with Headers

CSV (Comma-Separated Values) format is the most portable and human-readable format. Include headers for better clarity.

Export as CSV with column headers & Import

-- EXPORT

edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv' WITH CSV HEADER;
COPY 5
edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv
id,name,designation,project,company
1,Sugi,DBA,Jetstar,iGATE
2,Teja,DBA,RCM,iGATE
3,RAJ,DBA,RCM,iGATE
4,Arun,Developer,Jetstar,TCS
5,Priya,QA,RCM,Wipro
edbua=#
edbua=# \! ls -l /pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv
-rw-r--r--. 1 postgres postgres 152 Jul 10 15:41 /pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv
edbua=#

---- IMPORT
--- Simulate data loss - truncate the table
edbua=# TRUNCATE TABLE edb.emp CASCADE;
TRUNCATE TABLE
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     0
(1 row)

edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp.csv' WITH CSV HEADER;
COPY 5
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     5  <------
(1 row)

edbua=# SELECT * FROM edb.emp;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=#

Export as CSV with custom delimiter and header & Import

edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_custom.csv' WITH CSV HEADER DELIMITER '|';
COPY 5
edbua=# \! cat /pgBackup/edbua/backup/table_backup_edbua_edb_emp_custom.csv
id|name|designation|project|company
1|Sugi|DBA|Jetstar|iGATE
2|Teja|DBA|RCM|iGATE
3|RAJ|DBA|RCM|iGATE
4|Arun|Developer|Jetstar|TCS
5|Priya|QA|RCM|Wipro
edbua=#

--- IMPORT 
--- Simulate data loss - truncate the table
edbua=# TRUNCATE TABLE edb.emp CASCADE;
TRUNCATE TABLE
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     0
(1 row)

edbua=# COPY edb.emp FROM '/pgBackup/edbua/backup/table_backup_edbua_edb_emp_custom.csv' WITH CSV HEADER DELIMITER '|';
COPY 5
edbua=# SELECT COUNT(*) FROM edb.emp;
 count
-------
     5 <-------------------
(1 row)

edbua=# SELECT * FROM edb.emp;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=#

We will see the first line contains column names: id,name,designation,project,company, followed by the data. This format is ideal for importing into Excel, Google Sheets, or other database systems.

6. Filtered Data – Export & Import Specific Queries

Export and import only specific data based on conditions using queries.

Export only DBAs from iGATE:

edbua=# SELECT id, name, designation, project, company FROM edb.emp WHERE designation = 'DBA' AND company = 'iGATE';
 id | name | designation | project | company
----+------+-------------+---------+---------
  1 | Sugi | DBA         | Jetstar | iGATE
  2 | Teja | DBA         | RCM     | iGATE
  3 | RAJ  | DBA         | RCM     | iGATE
(3 rows)

edbua=# COPY ( SELECT id, name, designation, project, company FROM edb.emp WHERE designation = 'DBA' AND company = 'iGATE' ) TO '/pgBackup/edbua/backup/edb_emp_dbas.txt' WITH DELIMITER '|';
COPY 3
edbua=#
edbua=# \! cat /pgBackup/edbua/backup/edb_emp_dbas.txt
1|Sugi|DBA|Jetstar|iGATE
2|Teja|DBA|RCM|iGATE
3|RAJ|DBA|RCM|iGATE
edbua=#

Export specific columns only:

edbua=# SELECT name, designation, company FROM edb.emp WHERE company IN ('iGATE', 'TCS');
 name | designation | company
------+-------------+---------
 Sugi | DBA         | iGATE
 Teja | DBA         | iGATE
 RAJ  | DBA         | iGATE
 Arun | Developer   | TCS
(4 rows)

edbua=# COPY ( SELECT name, designation, company FROM edb.emp WHERE company IN ('iGATE', 'TCS') ) TO '/pgBackup/edbua/backup/edb_emp_selected.txt' WITH CSV HEADER;
COPY 4
edbua=#
edbua=# \! cat /pgBackup/edbua/backup/edb_emp_selected.txt
name,designation,company
Sugi,DBA,iGATE
Teja,DBA,iGATE
RAJ,DBA,iGATE
Arun,Developer,TCS
edbua=#


---- Import with column mapping:

-- Create a staging table with different column structure and Import specific columns mapped to different table
edbua=# CREATE TABLE edb.emp_staging ( full_name TEXT, job_title TEXT, employer TEXT );
CREATE TABLE
edbua=# COPY edb.emp_staging (full_name, job_title, employer) FROM '/pgBackup/edbua/backup/edb_emp_selected.txt' WITH CSV HEADER;
COPY 4
edbua=# SELECT * FROM edb.emp_staging;
 full_name | job_title | employer
-----------+-----------+----------
 Sugi      | DBA       | iGATE
 Teja      | DBA       | iGATE
 RAJ       | DBA       | iGATE
 Arun      | Developer | TCS
(4 rows)

edbua=#

Export with row numbering:

edbua=# SELECT ROW_NUMBER() OVER () as row_num, name, designation, project FROM edb.emp ORDER BY name;
 row_num | name  | designation | project
---------+-------+-------------+---------
       4 | Arun  | Developer   | Jetstar
       5 | Priya | QA          | RCM
       3 | RAJ   | DBA         | RCM
       1 | Sugi  | DBA         | Jetstar
       2 | Teja  | DBA         | RCM
(5 rows)

edbua=# COPY ( SELECT ROW_NUMBER() OVER () as row_num, name, designation, project FROM edb.emp ORDER BY name ) TO '/pgBackup/edbua/backup/edb_emp_numbered.csv' WITH CSV HEADER;
COPY 5
edbua=#
edbua=# \! cat /pgBackup/edbua/backup/edb_emp_numbered.csv
row_num,name,designation,project
4,Arun,Developer,Jetstar
5,Priya,QA,RCM
3,RAJ,DBA,RCM
1,Sugi,DBA,Jetstar
2,Teja,DBA,RCM
edbua=#

This approach is perfect for creating targeted backups, archiving old data, generating reports, and selectively importing data into different table structures.

7. Handling Special Cases (NULLs, Special Characters, Duplicates)

A. Handling NULL Values During Export & Import:

-- Create table with nullable columns
edbua=# CREATE TABLE edb.emp_nullable ( id SERIAL PRIMARY KEY, name TEXT, designation TEXT, project TEXT, company TEXT );
CREATE TABLE
edbua=#

-- Insert data with some NULL values
edbua=# INSERT INTO edb.emp_nullable (name, designation, project, company) VALUES ('Sugi', 'DBA', 'Jetstar', NULL), ('Teja', 'DBA', NULL, 'iGATE'), ('RAJ', NULL, 'RCM', 'iGATE');
INSERT 0 3
edbua=#

-- Export (NULLs appear as empty fields)
edbua=# COPY edb.emp_nullable TO '/pgBackup/edbua/backup/edb_emp_nulls.csv' WITH CSV HEADER;
COPY 3
edbua=#
edbua=# SELECT * FROM edb.emp_nullable;
 id | name | designation | project | company
----+------+-------------+---------+---------
  1 | Sugi | DBA         | Jetstar |
  2 | Teja | DBA         |         | iGATE
  3 | RAJ  |             | RCM     | iGATE
(3 rows)

edbua=#
-- Import back (empty fields become NULL)
edbua=# TRUNCATE TABLE edb.emp_nullable;
TRUNCATE TABLE
edbua=# COPY edb.emp_nullable FROM '/pgBackup/edbua/backup/edb_emp_nulls.csv' WITH CSV HEADER;
COPY 3
edbua=# SELECT * FROM edb.emp_nullable;  -- Notice NULL values preserved
 id | name | designation | project | company
----+------+-------------+---------+---------
  1 | Sugi | DBA         | Jetstar |
  2 | Teja | DBA         |         | iGATE
  3 | RAJ  |             | RCM     | iGATE
(3 rows)

edbua=#

B. Exporting & Importing Data with Special Characters:

-- Create table with special characters
edbua=# CREATE TABLE edb.emp_special ( id SERIAL PRIMARY KEY, name TEXT, description TEXT );
CREATE TABLE
edbua=# 

-- Insert data with quotes and commas
edbua=# INSERT INTO edb.emp_special (name, description) VALUES ('John "The Expert" Doe', 'Senior DBA, 10 years experience'), ('Mary O''Brien', 'Works with Oracle, PostgreSQL, MySQL');
INSERT 0 2
edbua=# 

-- Export with proper escaping (CSV handles this automatically)
edbua=# COPY edb.emp_special TO '/pgBackup/edbua/backup/edb_emp_special.csv' WITH CSV HEADER QUOTE '"' ESCAPE '"';
COPY 2
edbua=#
edbua=# SELECT * FROM edb.emp_special;
 id |         name          |             description
----+-----------------------+--------------------------------------
  1 | John "The Expert" Doe | Senior DBA, 10 years experience
  2 | Mary O'Brien          | Works with Oracle, PostgreSQL, MySQL
(2 rows)

edbua=#

-- Import from the file (escaping automatically handled)

edbua=# TRUNCATE TABLE edb.emp_special;
TRUNCATE TABLE
edbua=# COPY edb.emp_special FROM '/pgBackup/edbua/backup/edb_emp_special.csv' WITH CSV HEADER QUOTE '"' ESCAPE '"';
COPY 2
edbua=# SELECT * FROM edb.emp_special;
 id |         name          |             description
----+-----------------------+--------------------------------------
  1 | John "The Expert" Doe | Senior DBA, 10 years experience
  2 | Mary O'Brien          | Works with Oracle, PostgreSQL, MySQL
(2 rows)

edbua=#

C. Handling Duplicate Keys:

-- If importing data might have duplicate primary keys:

-- Option 1: Truncate first (removes all data)
TRUNCATE TABLE edb.emp CASCADE;

-- Option 2: Import to staging table first, then validate
CREATE TABLE edb.emp_staging (LIKE edb.emp);
COPY edb.emp_staging FROM '/pgBackup/edbua/backup/edb_emp.csv' WITH CSV HEADER;

-- Check for issues, then copy valid data
SELECT COUNT(*) FROM edb.emp_staging;
INSERT INTO edb.emp SELECT * FROM edb.emp_staging WHERE id NOT IN (SELECT id FROM edb.emp);

8. Copying Data Between Tables (emp to emp02)

Transfer data from one table to another table.

-- Step 1: Export emp data to file
edbua=# select * from edb.emp;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=# COPY edb.emp TO '/pgBackup/edbua/backup/emp_to_emp02.csv' WITH CSV HEADER;
COPY 5
edbua=#

-- Step 2: Create emp02 table 
edbua=# CREATE TABLE edb.emp02 ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, designation TEXT, project TEXT, company TEXT );
CREATE TABLE
edbua=#

-- Step 3: Import data from file into emp02
edbua=# select * from edb.emp02;
 id | name | designation | project | company
----+------+-------------+---------+---------
(0 rows)

edbua=# COPY edb.emp02 FROM '/pgBackup/edbua/backup/emp_to_emp02.csv' WITH CSV HEADER;
COPY 5
edbua=# select * from edb.emp02;
 id | name  | designation | project | company
----+-------+-------------+---------+---------
  1 | Sugi  | DBA         | Jetstar | iGATE
  2 | Teja  | DBA         | RCM     | iGATE
  3 | RAJ   | DBA         | RCM     | iGATE
  4 | Arun  | Developer   | Jetstar | TCS
  5 | Priya | QA          | RCM     | Wipro
(5 rows)

edbua=#
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