Tag Archives: Database Backup

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

PostgreSQL Backup & Restore to New Host with WAL Archive files

PostgreSQLArchive WAL, Backup and Recovery of PostgreSQL Using pg_basebackup + Archive Logs (Cross Host), Complete PostgreSQL Recovery with pg_basebackup and WAL Files on Target Server, Database Backup, Database Restore, Disaster Recovery: PostgreSQL Host Migration Using Base Backup and WAL Archives, Disaster Recovery: PostgreSQL Restore with WAL Archives, Full PostgreSQL Recovery Using pg_basebackup and WALs, How to Perform Full PostgreSQL Recovery on a New Server with Base Backup and WAL Files, Move PostgreSQL Database to New Host with pg_basebackup and WAL Replay, pg_basebackup, PostgreSQL Backup, PostgreSQL Backup & Restore to New Host Using pg_basebackup and WAL Archives, PostgreSQL Backup & Restore to New Host with WALs, PostgreSQL disaster recovery, PostgreSQL Host Migration via pg_basebackup and Archive WALs, PostgreSQL Point-in-Time Recovery (PITR) to New Host with pg_basebackup, PostgreSQL Restore, PostgreSQL Server Cloning with pg_basebackup and Archive Logs, Restore PostgreSQL, Restore PostgreSQL on New Server with Base Backup + WAL, Restoring PostgreSQL to a Different Host Using pg_basebackup and Archived WALs, Step-by-Step: PostgreSQL Base Backup and WAL Archive Restore to Another Server, WAL Recovery

How to Backup & Restore + Recover a PostgreSQL to New Host using pg_basebackup+WAL Archives

0. Goal
1. Environment (Source / Target)
2. Verify Existing DB Setup
3. Pre-requisites
4. Take Base Backup and WAL Archive
5. Transfer Base Backup and WAL Archives to Target
6. Check and Prepare Target Environment
7. Restore Backup on Target
8. PostgreSQL Configuration on Target
- 8.1. Update postgresql.conf
- 8.2. Validate PGDATA in systemd and bash shell profile
9. Start PostgreSQL Service
10. Final Validation

0. Goal

Perform a full recovery to the latest consistent state by restoring a complete backup taken with pg_basebackup and applying the WAL archive files.

Note: pg_basebackup does not include WAL archive files (archive log files) in its backup; these must be backed up separately.

We have a base backup taken without using –tablespace-mapping, and the restore needs to be performed on a different host with the following differences:

Tablespace mount point paths are different from the source server.
Archive WAL directory is located in a different path.
WAL file (pg_wal) location differs.
PGDATA directory path is also different.

1. 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	archive_mode=on	archive_mode=on	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)
WAL Archive Directory	/pgArch/pgsql17/arch	/pgarchive/pgsql17/archive	Different path case (W vs w)
Backup Directory	/pgBackup/pgsql17/backup	/pgbackup/pgsql17/backup	Different path case (B vs b)
restore_command	Not Applicable	/pgbackup/pgsql17/backup/archive_bkp11OCT2025	restore_command on target server for full recovery using WAL archive files.
Tablespace & Path	DELL_TB_DATA:/pgTb/pgsql17/tbs/delltbs01 ORCL_TB_DATA:/pgTb/pgsql17/tbs/orcltbs01	DELL_TB_DATA:/pgtsdata01/pgsql17/tbs/delltbs01 ORCL_TB_DATA:/pgtsdata01/pgsql17/tbs/orcltbs01	It will restore using the same tablespace names, but at different locations.
Databases	DELL, ORCL	We will restore both from backup	We will restore both

2. Verify Existing DB 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 |        |           |                       | 7755 kB | dell_tb_data |
 orcl      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7569 kB | orcl_tb_data |
 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
--------------+----------+-----------------------------
 dell_tb_data | postgres | /pgTb/pgsql17/tbs/delltbs01
 orcl_tb_data | postgres | /pgTb/pgsql17/tbs/orcltbs01
 pg_default   | postgres |
 pg_global    | postgres |
(4 rows)

postgres=#

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# CREATE TABLE test.emp (name TEXT, designation TEXT, project TEXT, company TEXT) TABLESPACE DELL_TB_DATA;
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=# CHECKPOINT;
CHECKPOINT
dell=# SELECT * FROM TEST.EMP;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

3. Pre-requisites

3.1. Verify postgresql.conf

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

postgres=# show archive_mode;
 archive_mode
--------------
 on <----
(1 row)

postgres=# show archive_command;
        archive_command
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f
(1 row)

postgres=#

3.2. 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

3.3. Verify User Permissions (REPLICATION or SUPERUSER)

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

postgres=#

3.4. Verify Free Space for Backup

[postgres@lxicbpgdsgv01 ~]$ df -h /pgBackup
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdd1       100G  746M  100G   1% /pgBackup
[postgres@lxicbpgdsgv01 ~]$
# Create directories for base backup and WAL archive log files
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/base_bkp11OCT2025
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/archive_bkp11OCT2025
[postgres@lxicbpgdsgv01 ~]$

4. Take Base Backup and WAL Archive

4.1. Take Base Backup

# Take base backup

[postgres@lxicbpgdsgv01 ~]$ nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/base_bkp11OCT2025 -Ft -Xs -P -v > /pgBackup/pgsql17/backup/base_bkp11OCT2025.log 2>&1 &
[1] 3413
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ cat /pgBackup/pgsql17/backup/base_bkp11OCT2025.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/8C000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3416"
  7754/535855 kB (1%), 0/3 tablespaces (...ckup/base_bkp11OCT2025/16542.tar)
  7754/535855 kB (1%), 1/3 tablespaces (...ckup/base_bkp11OCT2025/16542.tar)
 15474/535855 kB (2%), 1/3 tablespaces (...ckup/base_bkp11OCT2025/16545.tar)
 15474/535855 kB (2%), 2/3 tablespaces (...ckup/base_bkp11OCT2025/16545.tar)
157800/535855 kB (29%), 2/3 tablespaces (...ackup/base_bkp11OCT2025/base.tar)
342056/535855 kB (63%), 2/3 tablespaces (...ackup/base_bkp11OCT2025/base.tar)
496488/535855 kB (92%), 2/3 tablespaces (...ackup/base_bkp11OCT2025/base.tar)
535868/535868 kB (100%), 2/3 tablespaces (...ackup/base_bkp11OCT2025/base.tar)
535868/535868 kB (100%), 3/3 tablespaces
pg_basebackup: write-ahead log end point: 0/8C000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed <-----
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgBackup/pgsql17/backup/base_bkp11OCT2025
540M    /pgBackup/pgsql17/backup/base_bkp11OCT2025
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgBackup/pgsql17/backup/base_bkp11OCT2025
total 552504
-rw-------. 1 postgres postgres   7940096 Oct 12 00:42 16542.tar
-rw-------. 1 postgres postgres   7905792 Oct 12 00:42 16545.tar
-rw-------. 1 postgres postgres 532883456 Oct 12 00:42 base.tar
-rw-------. 1 postgres postgres    243611 Oct 12 00:42 backup_manifest
-rw-------. 1 postgres postgres  16778752 Oct 12 00:42 pg_wal.tar
[postgres@lxicbpgdsgv01 ~]$

backup_manifest is a JSON file included inside the tar backup.
It contains metadata about the backup, like:
--> List of all files included in the backup
--> Their sizes and checksums
--> Backup timestamps
--> Backup type (full, incremental, differential)
--> Other metadata needed to verify and restore the backup properly

4.2. Insert new row on table test.emp after backup

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# INSERT INTO test.emp VALUES ('KK', 'DBA LEAD', 'RCM', 'iGATE');
INSERT 0 1
dell=# CHECKPOINT;
CHECKPOINT
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
 KK   | DBA LEAD    | RCM     | iGATE <---- New row
(4 rows)

# Switch Archivelog (WAL Archive logs)
dell=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/8D000328
(1 row)

dell=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/8E000000
(1 row)

dell=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/8E000000
(1 row)

dell=#

4.3. Copy WAL Archive Files Separately

[postgres@lxicbpgdsgv01 ~]$ cp /pgArch/pgsql17/arch/* /pgBackup/pgsql17/backup/archive_bkp11OCT2025
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgBackup/pgsql17/backup/archive_bkp11OCT2025
total 65540
-rw-------. 1 postgres postgres 16777216 Oct 12 00:53 00000001000000000000008A
-rw-------. 1 postgres postgres 16777216 Oct 12 00:53 00000001000000000000008B
-rw-------. 1 postgres postgres      341 Oct 12 00:53 00000001000000000000008C.00000028.backup
-rw-------. 1 postgres postgres 16777216 Oct 12 00:53 00000001000000000000008C
-rw-------. 1 postgres postgres 16777216 Oct 12 00:53 00000001000000000000008D
[postgres@lxicbpgdsgv01 ~]$

5. Transfer Base Backup and WAL Archives to Target

[postgres@lxicbpgdsgv01 ~]$ cd /pgBackup/pgsql17/backup/
[postgres@lxicbpgdsgv01 backup]$ ls -ltr
total 4
drwxr-xr-x. 2 postgres postgres   97 Oct 12 00:42 base_bkp11OCT2025
-rw-r--r--. 1 postgres postgres 1286 Oct 12 00:42 base_bkp11OCT2025.log
drwxr-xr-x. 2 postgres postgres  182 Oct 12 00:56 archive_bkp11OCT2025
[postgres@lxicbpgdsgv01 backup]$
[postgres@lxicbpgdsgv01 backup]$ tar -cvf base_bkp11OCT2025.tar base_bkp11OCT2025
base_bkp11OCT2025/
base_bkp11OCT2025/16542.tar
base_bkp11OCT2025/pg_wal.tar
base_bkp11OCT2025/16545.tar
base_bkp11OCT2025/base.tar
base_bkp11OCT2025/backup_manifest
[postgres@lxicbpgdsgv01 backup]$
[postgres@lxicbpgdsgv01 backup]$ tar -cvf archive_bkp11OCT2025.tar archive_bkp11OCT2025
archive_bkp11OCT2025/
archive_bkp11OCT2025/00000001000000000000008A
archive_bkp11OCT2025/00000001000000000000008B
archive_bkp11OCT2025/00000001000000000000008C
archive_bkp11OCT2025/00000001000000000000008C.00000028.backup
archive_bkp11OCT2025/00000001000000000000008D
[postgres@lxicbpgdsgv01 backup]$
[postgres@lxicbpgdsgv01 backup]$ ls -ltr
total 618056
drwxr-xr-x. 2 postgres postgres        97 Oct 12 00:42 base_bkp11OCT2025
-rw-r--r--. 1 postgres postgres      1286 Oct 12 00:42 base_bkp11OCT2025.log
drwxr-xr-x. 2 postgres postgres       182 Oct 12 00:56 archive_bkp11OCT2025
-rw-r--r--. 1 postgres postgres 565760000 Oct 12 01:00 base_bkp11OCT2025.tar
-rw-r--r--. 1 postgres postgres  67123200 Oct 12 01:01 archive_bkp11OCT2025.tar
[postgres@lxicbpgdsgv01 backup]$

[postgres@lxicbpgdsgv01 backup]$ scp *.tar 192.168.2.52:/pgbackup/pgsql17/backup
postgres@192.168.2.52's password:
archive_bkp11OCT2025.tar                      100%   64MB  54.6MB/s   00:01
base_bkp11OCT2025.tar                         100%  540MB  58.3MB/s   00:09
[postgres@lxicbpgdsgv01 backup]$

6. Check and Prepare Target Environment

6.1. Check Existing symbolic link and Mounts

[postgres@lxicbpgdsgv02 ~]$ find /pgdata/pgsql17/data -type l -ls
 67117239      0 lrwxrwxrwx   1 postgres postgres       18 Oct 10 19:43 /pgdata/pgsql17/data/pg_wal -> /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$

6.2. Create Tablespace Directories

[postgres@lxicbpgdsgv02 ~]$ mkdir -p /pgtsdata01/pgsql17/tbs/delltbs01
[postgres@lxicbpgdsgv02 ~]$ mkdir -p /pgtsdata01/pgsql17/tbs/orcltbs01
[postgres@lxicbpgdsgv02 ~]$ chown -R postgres:postgres /pgtsdata01/pgsql17/tbs/delltbs01
[postgres@lxicbpgdsgv02 ~]$ chown -R postgres:postgres /pgtsdata01/pgsql17/tbs/orcltbs01
[postgres@lxicbpgdsgv02 ~]$ chmod 700 /pgtsdata01/pgsql17/tbs/delltbs01
[postgres@lxicbpgdsgv02 ~]$ chmod 700 /pgtsdata01/pgsql17/tbs/orcltbs01
[postgres@lxicbpgdsgv02 ~]$

6.3. Stop PostgreSQL

[root@lxicbpgdsgv02 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv02 ~]# ps -ef | grep postgres
root        4269    3222  0 01:14 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv02 ~]#

6.4. Clean Data, WAL, and Archive Directories

# Please ensure a backup is taken, if needed, before deleting the contents.
# Remove $PGDATA contents
[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgdata/pgsql17/data/*
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data
total 0
[postgres@lxicbpgdsgv02 ~]$

# Remove WAL log files (Redo log files)
[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgwal/pgsql17/wal/*
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal
total 0
[postgres@lxicbpgdsgv02 ~]$

# Remove WAL Archive log files 
[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgarchive/pgsql17/archive/*
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgarchive/pgsql17/archive
total 0
[postgres@lxicbpgdsgv02 ~]$

7. Restore Backup on Target

7.1. Extract Backup Files

[postgres@lxicbpgdsgv02 ~]$ cd /pgbackup/pgsql17/backup
[postgres@lxicbpgdsgv02 backup]$ ls -ltr
total 618052
-rw-r--r--. 1 postgres postgres  67123200 Oct 12 01:04 archive_bkp11OCT2025.tar
-rw-r--r--. 1 postgres postgres 565760000 Oct 12 01:04 base_bkp11OCT2025.tar
[postgres@lxicbpgdsgv02 backup]$
[postgres@lxicbpgdsgv02 backup]$ tar -xvf base_bkp11OCT2025.tar
base_bkp11OCT2025/
base_bkp11OCT2025/16542.tar
base_bkp11OCT2025/pg_wal.tar
base_bkp11OCT2025/16545.tar
base_bkp11OCT2025/base.tar
base_bkp11OCT2025/backup_manifest
[postgres@lxicbpgdsgv02 backup]$
[postgres@lxicbpgdsgv02 backup]$ tar -xvf archive_bkp11OCT2025.tar
archive_bkp11OCT2025/
archive_bkp11OCT2025/00000001000000000000008A
archive_bkp11OCT2025/00000001000000000000008B
archive_bkp11OCT2025/00000001000000000000008C
archive_bkp11OCT2025/00000001000000000000008C.00000028.backup
archive_bkp11OCT2025/00000001000000000000008D
[postgres@lxicbpgdsgv02 backup]$
[postgres@lxicbpgdsgv02 backup]$ ls -ltr
total 618052
drwxr-xr-x. 2 postgres postgres        97 Oct 12 00:42 base_bkp11OCT2025
drwxr-xr-x. 2 postgres postgres       182 Oct 12 00:56 archive_bkp11OCT2025
-rw-r--r--. 1 postgres postgres  67123200 Oct 12 01:04 archive_bkp11OCT2025.tar
-rw-r--r--. 1 postgres postgres 565760000 Oct 12 01:04 base_bkp11OCT2025.tar
[postgres@lxicbpgdsgv02 backup]$
[postgres@lxicbpgdsgv02 backup]$ cd base_bkp11OCT2025
[postgres@lxicbpgdsgv02 base_bkp11OCT2025]$ ls -ltr
total 552504
-rw-------. 1 postgres postgres   7940096 Oct 12 00:42 16542.tar
-rw-------. 1 postgres postgres   7905792 Oct 12 00:42 16545.tar
-rw-------. 1 postgres postgres  16778752 Oct 12 00:42 pg_wal.tar
-rw-------. 1 postgres postgres 532883456 Oct 12 00:42 base.tar
-rw-------. 1 postgres postgres    243611 Oct 12 00:42 backup_manifest
[postgres@lxicbpgdsgv02 base_bkp11OCT2025]$

7.2. Restore Base Data Directory

# Restore base backup to $PGDATA

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/
total 0
[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/base_bkp11OCT2025/base.tar -C /pgdata/pgsql17/data > /pgbackup/pgsql17/backup/base_restore.log 2>&1 &
[1] 4482
[postgres@lxicbpgdsgv02 ~]$

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/
total 80
-rw-------. 1 postgres postgres   227 Oct 10 18:41 backup_label.old
-rw-------. 1 postgres postgres  1169 Oct 10 18:41 postgresql.conf.bkp_10sep2025
-rw-------. 1 postgres postgres 30702 Oct 10 18:41 postgresql.conf.bkp
-rw-------. 1 postgres postgres    88 Oct 10 18:41 postgresql.auto.conf
drwx------. 2 postgres postgres    18 Oct 10 18:41 pg_xact
-rw-------. 1 postgres postgres     3 Oct 10 18:41 PG_VERSION
drwx------. 2 postgres postgres     6 Oct 10 18:41 pg_twophase
drwx------. 2 postgres postgres     6 Oct 10 18:41 pg_stat_tmp
drwx------. 2 postgres postgres     6 Oct 10 18:41 pg_snapshots
drwx------. 2 postgres postgres     6 Oct 10 18:41 pg_serial
drwx------. 2 postgres postgres     6 Oct 10 18:41 pg_notify
drwx------. 4 postgres postgres    36 Oct 10 18:41 pg_multixact
-rw-------. 1 postgres postgres  2640 Oct 10 18:41 pg_ident.conf
-rw-------. 1 postgres postgres  5600 Oct 10 18:41 pg_hba.conf
drwx------. 2 postgres postgres     6 Oct 10 18:41 pg_dynshmem
drwx------. 2 postgres postgres     6 Oct 10 18:41 pg_commit_ts
drwx------. 2 postgres postgres     6 Oct 10 19:45 pg_subtrans
-rw-------. 1 postgres postgres  1169 Oct 11 02:42 postgresql.conf
drwx------. 2 postgres postgres     6 Oct 11 02:57 pg_tblspc
drwx------. 5 postgres postgres    33 Oct 11 03:01 base
drwx------. 2 postgres postgres     6 Oct 12 00:42 pg_replslot
drwx------. 4 postgres postgres    68 Oct 12 00:42 pg_logical
-rw-------. 1 postgres postgres    68 Oct 12 00:42 tablespace_map
-rw-------. 1 postgres postgres   227 Oct 12 00:42 backup_label
drwx------. 4 postgres postgres    45 Oct 12 01:29 pg_wal <-- it created as directory.
drwx------. 2 postgres postgres  4096 Oct 12 01:29 global
drwx------. 2 postgres postgres     6 Oct 12  2025 pg_stat
drwx------. 2 postgres postgres   162 Oct 12  2025 log
-rw-------. 1 postgres postgres    30 Oct 12  2025 current_logfiles
[postgres@lxicbpgdsgv02 ~]$

7.3. Restore WAL Directory

# Restore WAL Files to New WAL location 

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal/
total 0
[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/base_bkp11OCT2025/pg_wal.tar -C /pgwal/pgsql17/wal > /pgbackup/pgsql17/backup/wal_restore.log 2>&1 &
[1] 4495
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal/
total 16384
-rw-------. 1 postgres postgres 16777216 Oct 12 00:42 00000001000000000000008C
[postgres@lxicbpgdsgv02 ~]$

7.4. Remove and Symlink pg_wal

# Remove default pg_wal directory from $PGDATA
# Create symbolic link to new WAL file location 
[postgres@lxicbpgdsgv02 ~]$ rm -rf /pgdata/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv02 ~]$
[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 12 01:50 /pgdata/pgsql17/data/pg_wal -> /pgwal/pgsql17/wal
[postgres@lxicbpgdsgv02 ~]$

7.5. Update $PGDATA/tablespace_map

[postgres@lxicbpgdsgv02 ~]$ cat /pgdata/pgsql17/data/tablespace_map
16542 /pgTb/pgsql17/tbs/delltbs01
16545 /pgTb/pgsql17/tbs/orcltbs01
[postgres@lxicbpgdsgv02 ~]$

# Update to new location
[postgres@lxicbpgdsgv02 ~]$ sed -i 's|/pgTb/|/pgtsdata01/|g' /pgdata/pgsql17/data/tablespace_map
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ cat /pgdata/pgsql17/data/tablespace_map
16542 /pgtsdata01/pgsql17/tbs/delltbs01
16545 /pgtsdata01/pgsql17/tbs/orcltbs01
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgtsdata01/pgsql17/tbs/delltbs01
total 0
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgtsdata01/pgsql17/tbs/orcltbs01
total 0
[postgres@lxicbpgdsgv02 ~]$

7.6. Restore Tablespace Data

# Restore Tablespace DELL_TB_DATA
[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/base_bkp11OCT2025/16542.tar -C /pgtsdata01/pgsql17/tbs/delltbs01 > /pgbackup/pgsql17/backup/16542_restore.log 2>&1 &
[1] 4593
[postgres@lxicbpgdsgv02 ~]$

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgtsdata01/pgsql17/tbs/delltbs01
total 0
drwx------. 3 postgres postgres 19 Oct 11 02:55 PG_17_202406281 <---
[postgres@lxicbpgdsgv02 ~]$

# Restore Tablespace ORCL_TB_DATA
[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/base_bkp11OCT2025/16545.tar -C /pgtsdata01/pgsql17/tbs/orcltbs01 > /pgbackup/pgsql17/backup/16545_restore.log 2>&1 &
[1] 4594
[postgres@lxicbpgdsgv02 ~]$

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgtsdata01/pgsql17/tbs/orcltbs01
total 0
drwx------. 3 postgres postgres 19 Oct 11 02:58 PG_17_202406281 <---
[postgres@lxicbpgdsgv02 ~]$

7.7. Restore WAL Archive files to backup location

# I have already extracted Archivelog files in the step number 7.1
# Please use this location for restore_command in postgresql.conf file

[postgres@lxicbpgdsgv02 ~]$ cd /pgbackup/pgsql17/backup/archive_bkp11OCT2025/
[postgres@lxicbpgdsgv02 archive_bkp11OCT2025]$ ls -ltr
total 65540
-rw-------. 1 postgres postgres 16777216 Oct 12 00:53 00000001000000000000008D
-rw-------. 1 postgres postgres      341 Oct 12 00:53 00000001000000000000008C.00000028.backup
-rw-------. 1 postgres postgres 16777216 Oct 12 00:53 00000001000000000000008C
-rw-------. 1 postgres postgres 16777216 Oct 12 00:53 00000001000000000000008B
-rw-------. 1 postgres postgres 16777216 Oct 12 00:53 00000001000000000000008A
[postgres@lxicbpgdsgv02 archive_bkp11OCT2025]$

7.8. Create $PGDATA/recovery.signal file

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/recovery.signal
ls: cannot access '/pgdata/pgsql17/data/recovery.signal': No such file or directory
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ touch /pgdata/pgsql17/data/recovery.signal
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/recovery.signal
-rw-r--r--. 1 postgres postgres 0 Oct 12 02:06 /pgdata/pgsql17/data/recovery.signal
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ chmod 700 /pgdata/pgsql17/data/recovery.signal
[postgres@lxicbpgdsgv02 ~]$

7.9. Set Permissions

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

8. PostgreSQL Configuration on Target

8.1. Update postgresql.conf

# • restore_command
# • archive_mode (Optional)
# • archive_command (Optional)

[postgres@lxicbpgdsgv02 ~]$ cat /pgdata/pgsql17/data/postgresql.conf | egrep -i 'archive_|command'
archive_mode = on
archive_command = 'cp %p /pgArch/pgsql17/arch/%f'
[postgres@lxicbpgdsgv02 ~]$

# Update archive_mode=on (Optional)
# Update archive_command='cp %p /pgarchive/pgsql17/archive/%f' (Optional)
# Update restore_command = 'cp /pgbackup/pgsql17/backup/archive_bkp11OCT2025/%f %p'  <--- Must required for full recovery using WAL Archive log files.

[postgres@lxicbpgdsgv02 ~]$ cat /pgdata/pgsql17/data/postgresql.conf | egrep -i 'archive_|command'
archive_mode = on
archive_command='cp %p /pgarchive/pgsql17/archive/%f'
restore_command = 'cp /pgbackup/pgsql17/backup/archive_bkp11OCT2025/%f %p' <---- must be set correctly
[postgres@lxicbpgdsgv02 ~]$

8.2. Validate PGDATA in systemd and bash shell profile

[postgres@lxicbpgdsgv02 ~]$ cat /usr/lib/systemd/system/postgresql-17.service | grep -i "Environment=PGDATA"
Environment=PGDATA=/pgdata/pgsql17/data
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ cat .bash_profile | grep -i PGDATA=
PGDATA=/pgdata/pgsql17/data
[postgres@lxicbpgdsgv02 ~]$

9. Start PostgreSQL Service

[root@lxicbpgdsgv02 ~]# systemctl start postgresql-17.service
[root@lxicbpgdsgv02 ~]#
[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 Sun 2025-10-12 02:23:36 +08; 1min 17s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 4887 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 4892 (postgres)
      Tasks: 8 (limit: 20496)
     Memory: 66.3M
        CPU: 200ms
     CGroup: /system.slice/postgresql-17.service
             ├─4892 /usr/pgsql-17/bin/postgres -D /pgdata/pgsql17/data
             ├─4893 "postgres: logger "
             ├─4894 "postgres: checkpointer "
             ├─4895 "postgres: background writer "
             ├─4905 "postgres: walwriter "
             ├─4906 "postgres: autovacuum launcher "
             ├─4907 "postgres: archiver last was 00000002.history"
             └─4908 "postgres: logical replication launcher "

Oct 12 02:23:35 lxicbpgdsgv02.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 12 02:23:35 lxicbpgdsgv02.rajasekhar.com postgres[4892]: 2025-10-12 02:23:35.389 +08 [4892] LOG:  redirecting log output to logging collector process
Oct 12 02:23:35 lxicbpgdsgv02.rajasekhar.com postgres[4892]: 2025-10-12 02:23:35.389 +08 [4892] HINT:  Future log output will appear in directory "log".
Oct 12 02:23:36 lxicbpgdsgv02.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv02 ~]#

10. Final Validation

10.1. Check Database and Tablespace Listing

[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 |        |           |                       | 7755 kB | dell_tb_data |
 orcl      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7569 kB | orcl_tb_data |
 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
--------------+----------+-----------------------------------
 dell_tb_data | postgres | /pgtsdata01/pgsql17/tbs/delltbs01
 orcl_tb_data | postgres | /pgtsdata01/pgsql17/tbs/orcltbs01
 pg_default   | postgres |
 pg_global    | postgres |
(4 rows)

postgres=#

10.2. Verify Application Data

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

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
 KK   | DBA LEAD    | RCM     | iGATE <----
(4 rows)

dell=# exit
[postgres@lxicbpgdsgv02 ~]$

10.3. Validate Symbolic Links for Tablespaces

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/pg_tblspc/
total 0
lrwxrwxrwx. 1 postgres postgres 33 Oct 12 02:23 16545 -> /pgtsdata01/pgsql17/tbs/orcltbs01
lrwxrwxrwx. 1 postgres postgres 33 Oct 12 02:23 16542 -> /pgtsdata01/pgsql17/tbs/delltbs01
[postgres@lxicbpgdsgv02 ~]$

10.4. Confirm recovery.signal Auto-deletion

# File should be gone after successful recovery.

[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/recovery.signal
ls: cannot access '/pgdata/pgsql17/data/recovery.signal': No such file or directory
[postgres@lxicbpgdsgv02 ~]$

10.5. Review PostgreSQL Logs

[postgres@lxicbpgdsgv02 ~]$ cat /pgdata/pgsql17/data/log/postgresql-Sun.log
...
...
2025-10-12 02:23:35.389 +08 [4892] LOG:  starting PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
2025-10-12 02:23:35.391 +08 [4892] LOG:  listening on IPv6 address "::1", port 5432
2025-10-12 02:23:35.392 +08 [4892] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-10-12 02:23:35.409 +08 [4892] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2025-10-12 02:23:35.418 +08 [4892] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-10-12 02:23:35.424 +08 [4896] LOG:  database system was interrupted; last known up at 2025-10-12 00:42:13 +08
2025-10-12 02:23:35.424 +08 [4896] LOG:  creating missing WAL directory "pg_wal/archive_status"
2025-10-12 02:23:35.424 +08 [4896] LOG:  creating missing WAL directory "pg_wal/summaries"
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp11OCT2025/00000002.history': No such file or directory
2025-10-12 02:23:36.418 +08 [4896] LOG:  starting backup recovery with redo LSN 0/8C000028, checkpoint LSN 0/8C000080, on timeline ID 1
2025-10-12 02:23:36.440 +08 [4896] LOG:  restored log file "00000001000000000000008C" from archive
2025-10-12 02:23:36.505 +08 [4896] LOG:  starting archive recovery
2025-10-12 02:23:36.516 +08 [4896] LOG:  redo starts at 0/8C000028
2025-10-12 02:23:36.539 +08 [4896] LOG:  restored log file "00000001000000000000008D" from archive
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp11OCT2025/00000001000000000000008E': No such file or directory
2025-10-12 02:23:36.597 +08 [4896] LOG:  completed backup recovery with redo LSN 0/8C000028 and end LSN 0/8C000120
2025-10-12 02:23:36.597 +08 [4896] LOG:  consistent recovery state reached at 0/8C000120
2025-10-12 02:23:36.599 +08 [4892] LOG:  database system is ready to accept read-only connections
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp11OCT2025/00000001000000000000008E': No such file or directory
2025-10-12 02:23:36.612 +08 [4896] LOG:  redo done at 0/8D000310 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.09 s
2025-10-12 02:23:36.612 +08 [4896] LOG:  last completed transaction was at log time 2025-10-12 00:52:29.813112+08
2025-10-12 02:23:36.639 +08 [4896] LOG:  restored log file "00000001000000000000008D" from archive
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp11OCT2025/00000002.history': No such file or directory
2025-10-12 02:23:36.700 +08 [4896] LOG:  selected new timeline ID: 2
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp11OCT2025/00000001.history': No such file or directory
2025-10-12 02:23:36.779 +08 [4896] LOG:  archive recovery complete
2025-10-12 02:23:36.782 +08 [4894] LOG:  checkpoint starting: end-of-recovery immediate wait
2025-10-12 02:23:36.810 +08 [4894] LOG:  checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.002 s, sync=0.008 s, total=0.031 s; sync files=3, longest=0.003 s, average=0.003 s; distance=32768 kB, estimate=32768 kB; lsn=0/8E000028, redo lsn=0/8E000028
2025-10-12 02:23:36.823 +08 [4892] LOG:  database system is ready to accept connections
[postgres@lxicbpgdsgv02 ~]$

10.6. Check DB recovery status

[postgres@lxicbpgdsgv02 ~]$ psql -c "SELECT pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f  <--- means recovery is completed.
(1 row)

[postgres@lxicbpgdsgv02 ~]$

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

Backup and Restore on Same Host Using pg_basebackup

How to Backup and Restore PostgreSQL DB Cluster on the Same Host Using pg_basebackup

1. Environment
2. Verify Existing DB Setup
3. Pre-requisites
4. Take Backup
5. Prepare & Restore
5.1 Stop PostgreSQL and Clean Directories
5.2 Remove all from Data & WAL directories
5.3 Restore Data and WAL
A. Copy Backup files to $PGDATA
B. Copy WAL files from $PGDATA/pg_wal to /pgWal/pgsql17/wal/
C. Remove the existing $PGDATA/pg_wal directory
D. Create a symbolic link pointing $PGDATA/pg_wal to a separate WAL directory
E. Set Permissions
6. Start PostgreSQL
7. Final Verification

1. Environment

ASPECT	Env
Hostname	lxicbpgdsgv01
IP Address	192.168.2.51
OS	RHEL 9
DB Version	PostgreSQL v17.6
Archive mode	archive_mode=off
pgData	/pgData/pgsql17/data
WAL Directory	/pgWal/pgsql17/wal
Tablespace	pg_default
Databases	DELL, ORCL

2. Verify Existing DB 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=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

3. Pre-requisites

Verify postgresql.conf

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

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

postgres=# SHOW archive_mode;
 archive_mode
--------------
 off
(1 row)

postgres=#

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

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

Verify free space for backup

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgData/pgsql17/data/
524M    /pgData/pgsql17/data/
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ du -sh /pgWal/pgsql17/wal/
801M    /pgWal/pgsql17/wal/
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ df -h /pgBackup/
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdd1       100G  746M  100G   1% /pgBackup <----
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/basebackup_10OCT2025
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/log/
[postgres@lxicbpgdsgv01 ~]$

4. Take Backup

[postgres@lxicbpgdsgv01 ~]$ ls -ltrh /pgWal/pgsql17/wal | wc -l
53 <---- 
[postgres@lxicbpgdsgv01 ~]$

pg_basebackup does not include all files from the pg_wal directory in the backup. Instead, it selectively includes only the WAL files required to make the base backup consistent at the point in time the backup was taken.

-- If you want to store the base backup and WAL files in separate backup directories. 

nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/basebackup_10OCT2025 --waldir=/pgBackup/pgsql17/backup/wal_backup -Fp -Xs -P -v > /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log 2>&1 &

The --waldir option in pg_basebackup is supported only when using the plain format (-Fp), not with the tar format (-Ft).

If we want symlinks preserved → use (both -Fp & --waldir use together) -Fp --waldir=/pgWal/pgsql17/wal

-- OR --

The directory mush be empty: /pgBackup/pgsql17/backup/basebackup_10OCT2025

[postgres@lxicbpgdsgv01 ~]$ nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/basebackup_10OCT2025 -Fp -Xs -P -v > /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log 2>&1 &
[1] 4973
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/85000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_4975"
279413/536037 kB (52%), 0/1 tablespace (...asebackup_10OCT2025/base/5/16533)
536047/536047 kB (100%), 0/1 tablespace (...ckup_10OCT2025/global/pg_control)
536047/536047 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/85000158
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed  <-----
[postgres@lxicbpgdsgv01 ~]$

5. Prepare & Restore

5.1 Stop PostgreSQL and Clean Directories

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]# 
[root@lxicbpgdsgv01 ~]# ps -ef | grep postgres
root        5057    3151  0 18:57 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv01 ~]#

5.2 Remove all from Data & WAL directories

-- Remove all from PGDATA directory

[root@lxicbpgdsgv01 ~]# rm -rf /pgData/pgsql17/data/*
[root@lxicbpgdsgv01 ~]# ls -ltr /pgData/pgsql17/data/
total 0
[root@lxicbpgdsgv01 ~]#

-- Remove all from WAL directory 

[root@lxicbpgdsgv01 ~]# rm -rf /pgWal/pgsql17/wal/*
[root@lxicbpgdsgv01 ~]# ls -ltr /pgWal/pgsql17/wal/
total 0
[root@lxicbpgdsgv01 ~]#

5.3 Restore Data and WAL

A. Copy Backup files to $PGDATA

Since we did not use the -Fp format with the --waldir option, all required WAL files will be included in the backup under the default path: PGDATA/pg_wal.

[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgData/pgsql17/data/
total 0
[postgres@lxicbpgdsgv01 ~]$ cd /pgBackup/pgsql17/backup/basebackup_10OCT2025/
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$ cp -Rp * /pgData/pgsql17/data/
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$ ls -ltr /pgData/pgsql17/data/
total 296
-rw-------. 1 postgres postgres    227 Oct 10 18:41 backup_label
drwx------. 4 postgres postgres     77 Oct 10 18:41 pg_wal
drwx------. 7 postgres postgres     59 Oct 10 18:41 base
drwx------. 4 postgres postgres     68 Oct 10 18:41 pg_logical
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_dynshmem
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_commit_ts
drwx------. 2 postgres postgres    110 Oct 10 18:41 log
-rw-------. 1 postgres postgres   1169 Oct 10 18:41 postgresql.conf.bkp_10sep2025
-rw-------. 1 postgres postgres  30702 Oct 10 18:41 postgresql.conf.bkp
-rw-------. 1 postgres postgres     88 Oct 10 18:41 postgresql.auto.conf
drwx------. 2 postgres postgres     18 Oct 10 18:41 pg_xact
-rw-------. 1 postgres postgres      3 Oct 10 18:41 PG_VERSION
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_twophase
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_tblspc
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_subtrans
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_stat_tmp
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_stat
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_snapshots
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_serial
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_replslot
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_notify
drwx------. 4 postgres postgres     36 Oct 10 18:41 pg_multixact
-rw-------. 1 postgres postgres   2640 Oct 10 18:41 pg_ident.conf
-rw-------. 1 postgres postgres   5600 Oct 10 18:41 pg_hba.conf
-rw-------. 1 postgres postgres   1171 Oct 10 18:41 postgresql.conf
drwx------. 2 postgres postgres   4096 Oct 10 18:41 global
-rw-------. 1 postgres postgres     30 Oct 10 18:41 current_logfiles
-rw-------. 1 postgres postgres 227736 Oct 10 18:41 backup_manifest
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$

B. Copy WAL files from $PGDATA/pg_wal to /pgWal/pgsql17/wal/

[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgWal/pgsql17/wal/
total 0
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ cd /pgData/pgsql17/data/pg_wal/
[postgres@lxicbpgdsgv01 pg_wal]$ cp -Rp * /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 pg_wal]$
[postgres@lxicbpgdsgv01 pg_wal]$ ls -ltr /pgWal/pgsql17/wal
total 16384
drwx------. 2 postgres postgres        6 Oct 10 18:41 summaries
drwx------. 2 postgres postgres        6 Oct 10 18:41 archive_status
-rw-------. 1 postgres postgres 16777216 Oct 10 18:41 000000010000000000000085
[postgres@lxicbpgdsgv01 pg_wal]$ cd
[postgres@lxicbpgdsgv01 ~]$

C. Remove the existing $PGDATA/pg_wal directory

[postgres@lxicbpgdsgv01 ~]$ rm -rf /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$

D. Create a symbolic link pointing $PGDATA/pg_wal to a separate WAL directory

[postgres@lxicbpgdsgv01 ~]$ ln -s /pgWal/pgsql17/wal /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct 10 19:43 /pgData/pgsql17/data/pg_wal -> /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$

E. Set Permissions

[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgData/pgsql17/data
[postgres@lxicbpgdsgv01 ~]$ chmod 700 /pgData/pgsql17/data
[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$ chmod 700 /pgWal/pgsql17/wal

6. Start PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl start postgresql-17.service
[root@lxicbpgdsgv01 ~]# 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 Fri 2025-10-10 19:45:21 +08; 5s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 5230 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 5235 (postgres)
      Tasks: 7 (limit: 15835)
     Memory: 34.2M
        CPU: 94ms
     CGroup: /system.slice/postgresql-17.service
             ├─5235 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─5236 "postgres: logger "
             ├─5237 "postgres: checkpointer "
             ├─5238 "postgres: background writer "
             ├─5240 "postgres: walwriter "
             ├─5241 "postgres: autovacuum launcher "
             └─5242 "postgres: logical replication launcher "

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

7. Final Verification

[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 |        |           |                       | 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=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

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

Disable ARCHIVELOG Mode

PostgreSQLarchive_mode, Database Backup, pg_checkpoint, pg_switch_wal, PostgreSQL, PostgreSQL administration, PostgreSQL archiving, PostgreSQL checkpoint, PostgreSQL Configuration, PostgreSQL DBA, PostgreSQL performance tuning, PostgreSQL recovery, PostgreSQL Tips, WAL files, write ahead log

How to Disable ARCHIVELOG Mode

1. Verify Existing Archive Mode
2. Edit the archive settings
3. Restart PostgreSQL
4. Verify Current Mode
5. Verify WAL Archiving Behavior

1. Verify Existing Archive Mode

postgres=# SHOW archive_mode;
 archive_mode
--------------
 on  <------ 
(1 row)

postgres=#

postgres=# SHOW archive_command;
        archive_command
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f  <----- 
(1 row)

postgres=#

2. Edit the archive settings


[postgres@lxicbpgdsgv01 ~]$ cp /pgData/pgsql17/data/postgresql.conf /pgData/pgsql17/data/postgresql.conf.bkp_10sep2025
[postgres@lxicbpgdsgv01 ~]$ vi /pgData/pgsql17/data/postgresql.conf

#archive_mode = on
#archive_command = 'cp %p /pgArch/pgsql17/arch/%f'

3. Restart PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl start  postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# 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 16:34:01 +08; 3s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 3492 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 3497 (postgres)
      Tasks: 7 (limit: 15835)
     Memory: 17.6M
        CPU: 92ms
     CGroup: /system.slice/postgresql-17.service
             ├─3497 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─3498 "postgres: logger "
             ├─3499 "postgres: checkpointer "
             ├─3500 "postgres: background writer "
             ├─3502 "postgres: walwriter "
             ├─3503 "postgres: autovacuum launcher "
             └─3504 "postgres: logical replication launcher "

Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com postgres[3497]: 2025-10-09 16:34:01.929 +08 [3497] LOG:  redirecting log output to logging collector process
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com postgres[3497]: 2025-10-09 16:34:01.929 +08 [3497] HINT:  Future log output will appear in directory "log".
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

4. Verify Current Mode

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

postgres=# SHOW archive_mode;
 archive_mode
--------------
 off  <------ it's disabled
(1 row)

postgres=# SHOW archive_command;
 archive_command
-----------------
 (disabled) <-------
(1 row)

postgres=#

5. Verify WAL Archiving Behavior


postgres=# CHECKPOINT;
CHECKPOINT
postgres=#
postgres=# CHECKPOINT;
CHECKPOINT
postgres=# CHECKPOINT;
CHECKPOINT
postgres=#
postgres=# exit
postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=#
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgArch/pgsql17/arch/
total 0  <---- Archivelogs not generating
[postgres@lxicbpgdsgv01 ~]$

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

PostgreSQL Backup and Restore Using pg_dumpall

PostgreSQLBackup Strategies, Database Automation, Database Backup, Database Management, Database Migration, Database Restore, DevOps, Disaster Recovery, Environment Replication, Full Database Backup, Linux PostgreSQL, Open Source Database, pg_dump vs pg_dumpall, pg_dumpall, PostgreSQL, PostgreSQL 15, PostgreSQL administration, PostgreSQL Backup, PostgreSQL Cluster Backup, PostgreSQL Restore, PostgreSQL Tablespaces, PostgreSQL Utilities, psql, SQL Scripting

PostgreSQL Backup and Restore Using pg_dumpall and psql

Table of Contents

0. Introduction

Backup:

1. Backup All Databases
2. Backup Users/Roles Definition
3. Backup Tablespaces Definition
4. Backup Schema Only (No Data)
5. Backup Data Only (No Schema)
6. Backup Data as INSERT Commands
7. Backup Global Objects Only (No Databases)

Restore:

8. Restore on Same Host

A. Drop Existing Databases (Optional)
B. Restore Full Backup
C. Verification

9. Restore to Another Host (Different Tablespace Paths)

A. Copy Backup File
B. Extract Tablespace/Database Info
C. Create Tablespace Directories on Target
D. Create Tablespaces in PostgreSQL
E. Optional: Backup on Target Host
F. Restore on Target Host
G. Final Verification

Click to expand pg_dumpall –help

[postgres@lxtrdpgdsgv01 ~]$ pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
  pg_dumpall [OPTION]...

General options:
  -f, --file=FILENAME          output file name
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -g, --globals-only           dump only global objects, no databases
  -O, --no-owner               skip restoration of object ownership
  -r, --roles-only             dump only roles, no databases or tablespaces
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in the dump
  -t, --tablespaces-only       dump only tablespaces, no databases or roles
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --exclude-database=PATTERN   exclude databases whose name matches PATTERN
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-role-passwords          do not dump passwords for roles
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --no-table-access-method     do not dump table access methods
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words
  --restrict-key=RESTRICT_KEY  use provided string as psql \restrict key
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If -f/--file is not used, then the SQL script will be written to the standard
output.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
[postgres@lxtrdpgdsgv01 ~]$

0. Introduction

Note: pg_dumpall does not support custom format backups.

The pg_dumpall utility is used to back up an entire PostgreSQL environment, including:

* Roles and users
* Tablespaces
* All databases (schemas and data)

It is especially useful for:

* Full cluster migrations
* Disaster recovery
* Environment replication across dev, QA, and prod

When restoring to a different host, you'll need to:

* Update tablespace paths (using tools like sed)
* Pre-create required tablespace directories

This ensures compatibility and successful restoration across different environments.

Backup

1. Backup ALL databases

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.log 2>&1 &
[1] 5610
[postgres@lxtrdpgdsgv01 ~]$ 
[postgres@lxtrdpgdsgv01 ~]$ cat /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.log | grep -i /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql  -Fa 'user=postgres dbname=template1'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=edpua'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=gebua'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=orcl'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql  -Fa 'user=postgres dbname=postgres'"
[postgres@lxtrdpgdsgv01 ~]$

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# SELECT
orcl-#     schemaname || '.' || relname AS table_name,
orcl-#     pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
orcl-#     pg_size_pretty(pg_relation_size(relid)) AS table_size,
orcl-#     pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
orcl-# FROM
orcl-#     pg_catalog.pg_statio_user_tables
orcl-# ORDER BY
orcl-#     pg_total_relation_size(relid) DESC;
    table_name    | total_size | table_size | index_size
------------------+------------+------------+------------
 trd.metrics_high | 799 MB     | 498 MB     | 301 MB
 trd.metrics_mid  | 638 MB     | 398 MB     | 240 MB
 trd.employees    | 493 MB     | 322 MB     | 172 MB
 trd.sales_2023   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2024   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2022   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2021   | 398 MB     | 248 MB     | 150 MB
 trd.metrics_low  | 159 MB     | 99 MB      | 60 MB
 trd.test_data    | 71 MB      | 50 MB      | 21 MB
 trd.emp_summary  | 24 kB      | 8192 bytes | 16 kB
 trd.metrics_rest | 8192 bytes | 0 bytes    | 8192 bytes
(11 rows)

orcl=#

2. Backup users/roles definition

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --roles-only -v -f /pgBackup/pgsql15/backup/roles.sql > /pgBackup/pgsql15/backup/roles.log 2>&1 &
[1] 5205
[postgres@lxtrdpgdsgv01 ~]$

3. Backup tablespaces definition

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --tablespaces-only -v -f /pgBackup/pgsql15/backup/tablespaces.sql > /pgBackup/pgsql15/backup/tablespaces.log 2>&1 &
[1] 5183
[postgres@lxtrdpgdsgv01 ~]$

4. Backup dump only the schema, no data

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --schema-only -v -f /pgBackup/pgsql15/backup/schemas.sql > /pgBackup/pgsql15/backup/schemas.log 2>&1 &
[1] 4890
[postgres@lxtrdpgdsgv01 ~]$

5. Backup dump only the data, not the schema

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --data-only -v -f /pgBackup/pgsql15/backup/dataonly.sql > /pgBackup/pgsql15/backup/dataonly.log 2>&1 &
[1] 5233
[postgres@lxtrdpgdsgv01 ~]$

6. Backup dump data as INSERT commands, rather than COPY

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --inserts -v -f /pgBackup/pgsql15/backup/inserts.sql > /pgBackup/pgsql15/backup/inserts.log 2>&1 &
[1] 5274
[postgres@lxtrdpgdsgv01 ~]$

7. Backup dump only global objects, no databases

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --globals-only -v -f /pgBackup/pgsql15/backup/globals.sql > /pgBackup/pgsql15/backup/globals.log 2>&1 &
[1] 5331
[postgres@lxtrdpgdsgv01 ~]$

Restore

8. Restore on same host

A. Drop Existing Databases (Optional)

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 gebua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 orcl      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(6 rows)

postgres=# drop database edpua;
DROP DATABASE
postgres=# drop database gebua;
DROP DATABASE
postgres=# drop database orcl;
DROP DATABASE
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(3 rows)

postgres=#

postgres=# \db
             List of tablespaces
    Name    |  Owner   |      Location
------------+----------+---------------------
 edpua_ix   | postgres | /pgIx/pgsql15/edpua
 edpua_tb   | postgres | /pgTb/pgsql15/edpua
 gebua_ix   | postgres | /pgIx/pgsql15/gebua
 gebua_tb   | postgres | /pgTb/pgsql15/gebua
 orcl_ix    | postgres | /pgIx/pgsql15/orcl
 orcl_tb    | postgres | /pgTb/pgsql15/orcl
 pg_default | postgres |
 pg_global  | postgres |
(8 rows)

postgres=#

B. Restore Full Backup

[postgres@lxtrdpgdsgv01 ~]$ nohup psql -U postgres -X -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql -d postgres > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore.log
[1] 4132
[postgres@lxtrdpgdsgv01 ~]$ 

-- OR --

nohup psql -U postgres -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore.log 2>&1 &

-- Log: backup_all_databases_10OCT2025_restore.log

C. Verification

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 gebua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 orcl      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(6 rows)

postgres=#

9. Restore to Another Host (Different Tablespace Paths)

A. Copy Backup File

[postgres@lxtrdpgdsgv01 backup]$ scp backup_all_databases_10OCT2025.sql 192.168.2.31:/pgBackup/pgsql15/backup/
postgres@192.168.2.31's password:
backup_all_databases_10OCT2025.sql                 100% 3556MB  51.5MB/s   01:09
[postgres@lxtrdpgdsgv01 backup]$ 

[postgres@pg17 backup]$ ls -ltr backup_all_databases_10OCT2025.sql
-rw-r--r--. 1 postgres postgres 3729147646 Oct  6 06:17 backup_all_databases_10OCT2025.sql
[postgres@pg17 backup]$

B. Extract Tablespace/Database Info

[postgres@lxtrdpgdsgv01 backup]$ grep -i "CREATE TABLESPACE" /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
CREATE TABLESPACE orcl_tb OWNER postgres LOCATION '/pgTb/pgsql15/orcl';
CREATE TABLESPACE orcl_ix OWNER postgres LOCATION '/pgIx/pgsql15/orcl';
CREATE TABLESPACE gebua_tb OWNER postgres LOCATION '/pgTb/pgsql15/gebua';
CREATE TABLESPACE gebua_ix OWNER postgres LOCATION '/pgIx/pgsql15/gebua';
CREATE TABLESPACE edpua_tb OWNER postgres LOCATION '/pgTb/pgsql15/edpua';
CREATE TABLESPACE edpua_ix OWNER postgres LOCATION '/pgIx/pgsql15/edpua';
[postgres@lxtrdpgdsgv01 backup]$
[postgres@lxtrdpgdsgv01 backup]$ grep -i "CREATE DATABASE" /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
CREATE DATABASE edpua WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = edpua_tb;
CREATE DATABASE gebua WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = gebua_tb;
CREATE DATABASE orcl WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = orcl_tb;
[postgres@lxtrdpgdsgv01 backup]$

C. Create Tablespace Directories on Target

[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(3 rows)

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

postgres=#


# Create Directory 

[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/orcl_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/orcl_ix
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/gebua_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/gebua_ix
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/edpua_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/edpua_ix

D. Create Tablespaces in PostgreSQL

postgres=# CREATE TABLESPACE orcl_tb OWNER postgres LOCATION  '/pgData/pgsql15/orcl_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE orcl_ix OWNER postgres LOCATION  '/pgData/pgsql15/orcl_ix';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE gebua_tb OWNER postgres LOCATION '/pgData/pgsql15/gebua_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE gebua_ix OWNER postgres LOCATION '/pgData/pgsql15/gebua_ix';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edpua_tb OWNER postgres LOCATION '/pgData/pgsql15/edpua_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edpua_ix OWNER postgres LOCATION '/pgData/pgsql15/edpua_ix';
CREATE TABLESPACE
postgres=#


-- OR --- 
 You can modify the tablespace paths directly in the backup file; however, this approach is not recommended for large backup files due to  reliability concerns.

cd /pgBackup/pgsql15/backup/
# Replace ORCL paths
sed -i 's|/pgTb/pgsql15/orcl|/pgData/pgsql15/orcl_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/orcl|/pgData/pgsql15/orcl_ix|g' backup_all_databases_10OCT2025.sql

# Replace GEBUA paths
sed -i 's|/pgTb/pgsql15/gebua|/pgData/pgsql15/gebua_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/gebua|/pgData/pgsql15/gebua_ix|g' backup_all_databases_10OCT2025.sql

# Replace EDPUA paths
sed -i 's|/pgTb/pgsql15/edpua|/pgData/pgsql15/edpua_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/edpua|/pgData/pgsql15/edpua_ix|g' backup_all_databases_10OCT2025.sql


[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \db
               List of tablespaces
    Name    |  Owner   |         Location
------------+----------+--------------------------
 edpua_ix   | postgres | /pgData/pgsql15/edpua_ix
 edpua_tb   | postgres | /pgData/pgsql15/edpua_tb
 gebua_ix   | postgres | /pgData/pgsql15/gebua_ix
 gebua_tb   | postgres | /pgData/pgsql15/gebua_tb
 orcl_ix    | postgres | /pgData/pgsql15/orcl_ix
 orcl_tb    | postgres | /pgData/pgsql15/orcl_tb
 pg_default | postgres |
 pg_global  | postgres |
(8 rows)

postgres=#

E. Optional: Backup on Target Host

[postgres@pg17 ~]$ nohup pg_dumpall -U postgres -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_pg17.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_pg17.log 2>&1 &
[1] 4246
[postgres@pg17 ~]$

F. Restore on Target Host

[postgres@pg17 ~]$ nohup psql -U postgres -X -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql -d postgres > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore_pg17.log 2>&1 &
[1] 5213
[postgres@pg17 ~]$ 

-- OR --

nohup psql -U postgres -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore_pg17.log 2>&1 &

-- Log: backup_all_databases_10OCT2025_restore_pg17.log

G. Final Verification

[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
gebua | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
orcl | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | postgres=CTc/postgres+
| | | | | | | =c/postgres
(6 rows)

postgres=#

[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# SELECT
orcl-# schemaname || '.' || relname AS table_name,
orcl-# pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
orcl-# pg_size_pretty(pg_relation_size(relid)) AS table_size,
orcl-# pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
orcl-# FROM
orcl-# pg_catalog.pg_statio_user_tables
orcl-# ORDER BY
orcl-# pg_total_relation_size(relid) DESC;
 table_name | total_size | table_size | index_size
------------------+------------+------------+------------
trd.metrics_high | 799 MB | 498 MB | 301 MB
trd.metrics_mid | 638 MB | 398 MB | 240 MB
trd.employees | 493 MB | 322 MB | 172 MB
trd.sales_2023 | 399 MB | 249 MB | 150 MB
trd.sales_2024 | 399 MB | 249 MB | 150 MB
trd.sales_2022 | 399 MB | 249 MB | 150 MB
trd.sales_2021 | 398 MB | 248 MB | 150 MB
trd.metrics_low | 159 MB | 99 MB | 60 MB
trd.test_data | 71 MB | 50 MB | 21 MB
trd.emp_summary | 24 kB | 8192 bytes | 16 kB
trd.metrics_rest | 8192 bytes | 0 bytes | 8192 bytes
(11 rows)

orcl=#

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