Tag Archives: postgresql backup and restore

Schema Backup and Restore Using pg_dump in PostgreSQL

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

Clone PostgreSQL Cluster from One Host to Another Using pg_basebackup (No Archive Mode)

How To Backup and Restore PostgreSQL DB Cluster from One Host to Another Using pg_basebackup (No Archive Mode)

1. Goal
2. Environment (Source / Target)
3. Verify Existing Setup
4. Verify postgresql.conf
5. Verify pg_hba.conf
6. Verify Users
7. Directory Permissions
8. Take Backup
9. Verify Backup
10. Transfer Backup to Target
11. Prepare Target & Restore

11.1 Backup Target (Optional)
11.2 Stop PostgreSQL and Clean Directories
11.3 Restore Data and WAL
11.4 Set Permissions
11.5 Verify Restored Files
11.6 Create Symbolic Link for WAL

12. Start PostgreSQL on Target
13. Final Verification

In PostgreSQL, a cluster is a group of databases managed by one PostgreSQL server. This is just a standalone server, not like an Oracle RAC cluster.

1. Goal

1. Perform a consistent backup using pg_basebackup from lxicbpgdsgv01.
2. Restore the backup on lxicbpgdsgv02 without applying archived WALs.

2. Environment (Source / Target)

Aspect	Source	Target	Difference
Hostname	lxicbpgdsgv01	lxicbpgdsgv02	Different hostnames
IP Address	192.168.2.51	192.168.2.52	Different IPs
OS	RHEL 9	RHEL 9	Same
DB Version	PostgreSQL v17.6	PostgreSQL v17.6	Same
Archive mode	No Archivelog	No Archivelog	Same
PGDATA	/pgData/pgsql17/data	/pgdata/pgsql17/data	Different path case (D vs d)
WAL Directory	/pgWal/pgsql17/wal	/pgwal/pgsql17/wal	Different path case (W vs w)
Tablespace	pg_default	pg_default	Same
Databases	DELL, ORCL	No Databases	Need to clone

3. Verify Existing Setup

[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# \l+
                                                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 dell      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7763 kB | pg_default |
 orcl      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7907 kB | pg_default |
 postgres  | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 492 MB  | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7545 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7723 kB | pg_default | default template for new databases
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
(5 rows)

postgres=#

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

dell=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

dell=# 
dell=# \dn test
 List of schemas
 Name |  Owner
------+----------
 test | postgres
(1 row)

dell=#
dell=# \echo :AUTOCOMMIT
on
dell=#

dell=# CREATE TABLE test.emp ( name TEXT, designation TEXT, project TEXT, company TEXT);
CREATE TABLE
dell=# INSERT INTO test.emp VALUES ('Sugi', 'DBA', 'Jetstar', 'iGATE');
INSERT 0 1
dell=# INSERT INTO test.emp VALUES ('Teja', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
dell=# INSERT INTO test.emp VALUES ('RAJ', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
dell=#
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

dell=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=#
orcl=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | sample_data | table | postgres
(1 row)

orcl=#
orcl=# select count(*) from sample_data;
 count
-------
  1000  <----- 
(1 row)

orcl=#

4. Verify postgresql.conf


postgres=# SHOW wal_level;
 wal_level
-----------
 replica  <---- Should be replica
(1 row)

postgres=#
postgres=# SHOW max_wal_senders;
 max_wal_senders
-----------------
 10 <----- 
(1 row)

postgres=# SHOW archive_mode;
 archive_mode
--------------
 off  <----- No archive log mode
(1 row)

postgres=# show archive_command;
 archive_command
-----------------
 (disabled)
(1 row)

postgres=#

5. Verify pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS             METHOD
# Local connections for replication (for pg_basebackup run locally)
local   replication     all                                 trust

# Remote connections for replication (for pg_basebackup run remotely)
#host    replication     repl_user       192.168.2.52/32     scram-sha-256

6. Verify user permissions:REPLICATION or SUPERUSER required

postgres=# \du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=#

7. Directory Permissions for Backup

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgBackup/pgsql17/backup/
drwx------. 2 postgres postgres 6 Oct  9 18:46 /pgBackup/pgsql17/backup/
[postgres@lxicbpgdsgv01 ~]$

8. Take Backup

Want symlinks preserved → use (both -Fp & –waldir)-Fp --waldir=/pgWal/pgsql17/wal
Want single tar archive → use -Ft, but recreate symlinks after restore, for pg_wal
The –waldir option in pg_basebackup is supported only when using the plain format (-Fp), not with the tar format (-Ft).

[postgres@lxicbpgdsgv01 ~]$ nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025 -Ft -Xs -P > /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025.log 2>&1 &
[1] 4438
[postgres@lxicbpgdsgv01 ~]$

**** I have forgot use -v (verbose option to get extra output)
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025 -Ft -Xs -P -v > /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025.log 2>&1 &

9. Verify Backup


[postgres@lxicbpgdsgv01 ~]$ cat /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025.log
nohup: ignoring input
waiting for checkpoint
133816/536026 kB (24%), 0/1 tablespace
299960/536026 kB (55%), 0/1 tablespace
447864/536026 kB (83%), 0/1 tablespace
536037/536037 kB (100%), 0/1 tablespace
536037/536037 kB (100%), 1/1 tablespace
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgBackup/pgsql17/backup/pg_basebackup_lxicbpgdsgv01_10sep2025
total 540M
-rw-------. 1 postgres postgres 524M Oct  9 18:56 base.tar  
-rw-------. 1 postgres postgres 223K Oct  9 18:56 backup_manifest
-rw-------. 1 postgres postgres  17M Oct  9 18:56 pg_wal.tar 
[postgres@lxicbpgdsgv01 ~]$

10. Transfer Backup to Target


[postgres@lxicbpgdsgv01 pg_basebackup_lxicbpgdsgv01_10sep2025]$ scp * 192.168.2.52:/pgbackup/pgsql17/backup/
postgres@192.168.2.52's password:  
backup_manifest                   100%  222KB  30.7MB/s   00:00
base.tar                          100%  523MB  74.0MB/s   00:07
pg_wal.tar                        100%   16MB  43.5MB/s   00:00
[postgres@lxicbpgdsgv01 pg_basebackup_lxicbpgdsgv01_10sep2025]$

11. Prepare Target & Restore

-- List Source Backup files 
[postgres@lxicbpgdsgv02 ~]$ ls -lrth /pgbackup/pgsql17/backup
total 540M
-rw-------. 1 postgres postgres 223K Oct  9 19:08 backup_manifest
-rw-------. 1 postgres postgres 524M Oct  9 19:08 base.tar
-rw-------. 1 postgres postgres  17M Oct  9 19:08 pg_wal.tar
[postgres@lxicbpgdsgv02 ~]$

11.1 Backup Target (Optional)


nohup pg_basebackup -U postgres -D /pgbackup/pgsql17/pg_basebackup_lxicbpgdsgv02_10sep2025 -Ft -Xs -P > /pgbackup/pgsql17/pg_basebackup_lxicbpgdsgv02_10sep2025.log 2>&1 &

-- Verify Tablespaces path -- It's pg_default in our case
-- Verify WAL File (Redo log files) location 

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct  9 17:37 /pgdata/pgsql17/data/pg_wal -> /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$

11.2 Stop PostgreSQL and Clean Directories

-- Stop PostgreSQL DB Cluster
[root@lxicbpgdsgv02 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv02 ~]#
[root@lxicbpgdsgv02 ~]# ps -ef | grep postgres
root       10259    7832  0 19:20 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv02 ~]#

-- Remove all from Data directory 

[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgdata/pgsql17/data/*
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/
total 0
[postgres@lxicbpgdsgv02 ~]$


-- Remove all from WAL directory 

[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgwal/pgsql17/wal/*
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal/
total 0
[postgres@lxicbpgdsgv02 ~]$

11.3 Restore Data and WAL

-- List source backup files

[postgres@lxicbpgdsgv02 ~]$ cd /pgbackup/pgsql17/backup/
[postgres@lxicbpgdsgv02 backup]$ ls -ltr
total 552652
-rw-------. 1 postgres postgres    227724 Oct  9 19:08 backup_manifest
-rw-------. 1 postgres postgres 548901888 Oct  9 19:08 base.tar
-rw-------. 1 postgres postgres  16778752 Oct  9 19:08 pg_wal.tar
drwxr-xr-x. 3 postgres postgres        18 Oct  9 19:32 data_lxicbpgdsgv02_old
[postgres@lxicbpgdsgv02 backup]$

# Restore DATA Directory 
[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/base.tar -C /pgdata/pgsql17/data > /pgbackup/pgsql17/backup/base_restore.log 2>&1 &
[1] 11289
[postgres@lxicbpgdsgv02 ~]$


# Restore WAL Directory 

[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/pg_wal.tar -C /pgwal/pgsql17/wal > /pgbackup/pgsql17/backup/pg_wal_restore.log 2>&1 &
[1] 11304
[postgres@lxicbpgdsgv02 ~]$

-- OR -- 

# Restore base and WAL archives sequentially in a single command

nohup bash -c "tar -xvf /pgbackup/pgsql17/backup/base.tar -C /pgdata/pgsql17/data && tar -xvf /pgbackup/pgsql17/backup/pg_wal.tar -C /pgwal/pgsql17/wal" > /pgbackup/pgsql17/backup/fulltar_restore.log 2>&1 &

11.4 Set Permissions

[postgres@lxicbpgdsgv02 ~]$ chown -R postgres:postgres /pgdata/pgsql17/data
[postgres@lxicbpgdsgv02 ~]$ chmod 700 /pgdata/pgsql17/data
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ chown -R postgres:postgres /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$ chmod 700 /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$

11.5 Verify Restored Files

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data
total 72
-rw-------. 1 postgres postgres    88 Sep 30 21:50 postgresql.auto.conf
drwx------. 2 postgres postgres    18 Sep 30 21:50 pg_xact
-rw-------. 1 postgres postgres     3 Sep 30 21:50 PG_VERSION
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_twophase
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_tblspc
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_subtrans
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_stat_tmp
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_snapshots
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_serial
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_notify
drwx------. 4 postgres postgres    36 Sep 30 21:50 pg_multixact
-rw-------. 1 postgres postgres  2640 Sep 30 21:50 pg_ident.conf
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_dynshmem
drwx------. 2 postgres postgres     6 Sep 30 21:50 pg_commit_ts
-rw-------. 1 postgres postgres 30702 Oct  7 18:20 postgresql.conf.bkp
drwx------. 2 postgres postgres    84 Oct  9 00:01 log
-rw-------. 1 postgres postgres  1169 Oct  9 16:31 postgresql.conf.bkp_10sep2025
-rw-------. 1 postgres postgres  1171 Oct  9 16:32 postgresql.conf
drwx------. 7 postgres postgres    59 Oct  9 18:08 base
drwx------. 2 postgres postgres     6 Oct  9 18:44 pg_stat
-rw-------. 1 postgres postgres    30 Oct  9 18:44 current_logfiles
-rw-------. 1 postgres postgres  5600 Oct  9 18:53 pg_hba.conf
-rw-------. 1 postgres postgres     0 Oct  9 18:56 tablespace_map
drwx------. 2 postgres postgres     6 Oct  9 18:56 pg_replslot
drwx------. 4 postgres postgres    68 Oct  9 18:56 pg_logical
-rw-------. 1 postgres postgres   227 Oct  9 18:56 backup_label
drwx------. 4 postgres postgres    45 Oct  9 20:06 pg_wal <--- Created as Directory, instead of symbolic link 
drwx------. 2 postgres postgres  4096 Oct  9 20:06 global
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal
total 16384
-rw-------. 1 postgres postgres 16777216 Oct  9 18:56 000000010000000000000045
[postgres@lxicbpgdsgv02 ~]$

Please note:

--- Want symlinks preserved → use -Fp
--- Want single tar archive → use -Ft, but recreate symlinks after restore, for pg_wal

11.6 Create Symbolic Link for WAL

[postgres@lxicbpgdsgv02 ~]$ ls -ld /pgdata/pgsql17/data/pg_wal
drwx------. 4 postgres postgres 45 Oct  9 20:06 /pgdata/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgdata/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv02 ~]$ ln -s /pgwal/pgsql17/wal /pgdata/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ ls -ld /pgdata/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct  9 21:04 /pgdata/pgsql17/data/pg_wal -> /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$ 
[postgres@lxicbpgdsgv02 ~]$ cd /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 wal]$ ll
total 16384
-rw-------. 1 postgres postgres 16777216 Oct  9 18:56 000000010000000000000045
[postgres@lxicbpgdsgv02 wal]$

12. Start PostgreSQL on Target — no recovery needed.

[root@lxicbpgdsgv02 ~]# systemctl start postgresql-17.service
[root@lxicbpgdsgv02 ~]# systemctl status postgresql-17.service
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Thu 2025-10-09 21:06:08 +08; 5s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 12375 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 12380 (postgres)
      Tasks: 7 (limit: 20496)
     Memory: 33.6M
        CPU: 114ms
     CGroup: /system.slice/postgresql-17.service
             ├─12380 /usr/pgsql-17/bin/postgres -D /pgdata/pgsql17/data
             ├─12381 "postgres: logger "
             ├─12382 "postgres: checkpointer "
             ├─12383 "postgres: background writer "
             ├─12385 "postgres: walwriter "
             ├─12386 "postgres: autovacuum launcher "
             └─12387 "postgres: logical replication launcher "

Oct 09 21:06:06 lxicbpgdsgv02.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 09 21:06:06 lxicbpgdsgv02.rajasekhar.com postgres[12380]: 2025-10-09 21:06:06.781 +08 [12380] LOG:  redirecting log output to logging collector process
Oct 09 21:06:06 lxicbpgdsgv02.rajasekhar.com postgres[12380]: 2025-10-09 21:06:06.781 +08 [12380] HINT:  Future log output will appear in directory "log".
Oct 09 21:06:08 lxicbpgdsgv02.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv02 ~]#

13. Final Verification

[postgres@lxicbpgdsgv02 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# \l+
                                                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 dell      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7609 kB | pg_default |
 orcl      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7753 kB | pg_default |
 postgres  | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 492 MB  | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7545 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7569 kB | pg_default | default template for new databases
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
(5 rows)

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

postgres=#
postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# \dt test.*
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test   | emp  | table | postgres
(1 row)

dell=#

dell=# select * from test.emp;  <---- We can see Data 
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#
dell=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | sample_data | table | postgres
(1 row)

orcl=#
orcl=# select count(*) from sample_data;
 count
-------
  1000 <----
(1 row)

orcl=#

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

BRIGHT DBA

ORACLE DBA – Tips and Techniques

Tag Archives: postgresql backup and restore

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

Clone PostgreSQL Cluster from One Host to Another Using pg_basebackup (No Archive Mode)

How To Backup and Restore PostgreSQL DB Cluster from One Host to Another Using pg_basebackup (No Archive Mode)

Table of Contents

In PostgreSQL, a cluster is a group of databases managed by one PostgreSQL server. This is just a standalone server, not like an Oracle RAC cluster.

1. Goal

2. Environment (Source / Target)

3. Verify Existing Setup

4. Verify postgresql.conf

5. Verify pg_hba.conf

6. Verify user permissions:REPLICATION or SUPERUSER required

7. Directory Permissions for Backup

8. Take Backup

9. Verify Backup

10. Transfer Backup to Target

11. Prepare Target & Restore

11.1 Backup Target (Optional)

11.2 Stop PostgreSQL and Clean Directories

11.3 Restore Data and WAL

11.4 Set Permissions

11.5 Verify Restored Files

11.6 Create Symbolic Link for WAL

12. Start PostgreSQL on Target — no recovery needed.

13. Final Verification