Tag Archives: pg_basebackup

PostgreSQL

PostgreSQL DBA Step by Step Learning

#	PostgreSQL DBA Topics
1	How to Install PostgreSQL ON Linux?
2	How to Install PostgreSQL on Linux 7 using source code?
3	How to START/STOP PostgreSQL ON Linux?
4	How to Create Database in PostgreSQL?
5	PostgreSQL User Management
6	PostgreSQL pg_hba.conf Guide
7	PostgreSQL Change Data Directory
8	Understanding WAL Files in PostgreSQL – For Oracle DBAs
9	Change PostgreSQL WAL Directory Path (pg_wal)
10	Enable Archive Mode in PostgreSQL 17
11	How to Disable ARCHIVELOG Mode
12	PostgreSQL Tablespace Management
13	PostgreSQL pg_dump and pg_restore Guide
14	PostgreSQL Backup and Restore Using pg_dumpall and psql
15	pg_basebackup – Backup, Restore, and Recovery
16	Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17	Backup & Restore PostgreSQL DB Cluster on Same Host
18	Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19	PostgreSQL PITR – Point in Time Recovery
20	Configure Streaming Replication in PostgreSQL
21	Manual Failover in PostgreSQL Streaming Replication
22	Convert Asynchronous Replication to Synchronous Replication

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.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 ~]$

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: 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/

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

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

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

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

12. Start PostgreSQL on Target
13. Final Verification

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

1. Goal

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

2. Environment (Source / Target)

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

3. Verify Existing Setup

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

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

postgres=#

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

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

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

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

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

dell=#

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

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

orcl=#

4. Verify postgresql.conf


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

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

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

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

postgres=#

5. Verify pg_hba.conf

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

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

6. Verify user permissions:REPLICATION or SUPERUSER required

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

postgres=#

7. Directory Permissions for Backup

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

8. Take Backup

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

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

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

9. Verify Backup


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

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

10. Transfer Backup to Target


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

11. Prepare Target & Restore

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

11.1 Backup Target (Optional)


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

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

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

11.2 Stop PostgreSQL and Clean Directories

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

-- Remove all from Data directory 

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


-- Remove all from WAL directory 

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

11.3 Restore Data and WAL

-- List source backup files

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

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


# Restore WAL Directory 

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

-- OR -- 

# Restore base and WAL archives sequentially in a single command

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

11.4 Set Permissions

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

11.5 Verify Restored Files

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

Please note:

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

11.6 Create Symbolic Link for WAL

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

12. Start PostgreSQL on Target — no recovery needed.

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

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

13. Final Verification

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

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

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

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

dell=#

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

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

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

orcl=#

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

pg_basebackup

PostgreSQLBackup PostgreSQL Database, base backup, compressed backup, How to Backup Postgresql, pg_basebackup, pg_basebackup examples, pg_basebackup gzip, pg_basebackup restore, pg_basebackup tar, pg_hba.conf, pg_wal, physical backup, PITR, point in time recovery, postgres disaster recovery, postgres replication, postgresql 17 backup, PostgreSQL archive, PostgreSQL Backup, PostgreSQL recovery, PostgreSQL Restore, recovery.signal, replication role, tar backup

pg_basebackup – Backup, Restore, and Recovery

What is pg_basebackup?

pg_basebackup is a utility provided by PostgreSQL to take a physical base backup of the entire database cluster.

Common Use Cases:

Setting up standby servers for streaming replication
Creating physical backups for disaster recovery
Performing Point-In-Time Recovery (PITR)
Performing Incremental Backups (PostgreSQL v17 feature)

It connects to a running PostgreSQL server and copies all necessary data files and WAL segments, producing a consistent and restorable backup.

———- Backup ———-

Pre-requisites parameter and config:

1. User : Requires a user with replication role or superuser privileges

CREATE ROLE repl_user WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'replpass';

2. postgresql.conf

wal_level=replica (Mandatory)
max_wal_senders >=1 (Mandatory)
archive_mode=on (Optional if Database in NO Archivelog mode)
archive_command = ‘cp %p /pgArch/pgsql17/arch/%f’ (Optional if Database in NO Archivelog mode)

3. pg_hba.conf

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

# or you can use peer (if same OS user postgres is used)
# local   replication     all                                 peer

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

How pg_basebackup Works Internally:

Feature	`pg_basebackup` Supports?
Backup of data directory	Yes
Include enough WALs to make backup consistent	Yes
Backup of WAL archives (even with archive mode=on & archive_command configured)	No
Works without `archive_mode`	Yes

Performs a physical file-level backup by copying the full data directory
Uses PostgreSQL’s streaming replication protocol
Requires a user with replication role or superuser privileges
Can be used while the server is running (online backup)
Ensures a transactionally consistent snapshot of the database
Even with archive_mode=on and archive_command configured, pg_basebackup does not back up WAL archive logs from the archive location. It only includes enough live WAL files to make the backup consistent, streamed from pg_wal/.
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.

What It Includes:

All essential data files of the cluster
Necessary WAL (Write-Ahead Log) segments for recovery
Custom tablespaces, replication slots, and large objects

WAL Handling Options:

Default: WAL files included after backup (-X fetch)
Streaming: WAL files streamed live during backup (-X stream)

How to take Backup using pg_basebackup ?

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

-- Do not use -R here since it's not a replica (No standby).

Typically Backup using: Plain format, Tar format & Tar format (gzip)

1. Take backup in Plain format
nohup pg_basebackup -U postgres -D /pgBackups/pgsql17/demo_restore -Fp -Xs -P -v > pg_basebackup_demo_restore.log 2>&1 &

2. Take backup in Tar format
nohup pg_basebackup -U postgres -D /pgBackups/pgsql17/demo_tar_backup -Ft -Xs -P -v > pg_basebackup_tar.log 2>&1 &

3. Take backup in Compressed Tar format (gzip)
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/ -Ft -z -Xs -P -v > pg_basebackup_tar.log 2>&1 &

4. Take compressed Tar backup with transfer rate limit
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/ -Ft -z -X stream -P --max-rate=5M -v > pg_basebackup_tar.log 2>&1 &

5. Take compressed Tar backup with server-side gzip compression at max level (9)
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/ -Ft --compress=server-gzip:9 -Xs -P -v > pg_basebackup.log 2>&1 &

6. Take Tar backup from a remote host
nohup pg_basebackup -h remote_host -p port -U postgres -D /pgBackup/remote_tar_backup -Ft -Xs -P -v > pg_basebackup_remote_tar.log 2>&1 &

PG BASE BACKUP Flag	Description
`-U <username>`	Specifies the PostgreSQL user to connect as.
`-D <directory>`	Specifies the target directory for the backup.
`-F p`	Takes the backup in Plain format (file system copy).
`-F t`	Takes the backup in Tar archive format.
`-z`	Compresses the backup using gzip compression (only valid with tar format).
`--compress=server-gzip:<level>`	Enables server-side gzip compression with specified compression level (1-9).
`-X s`	Includes the Write-Ahead Log (WAL) files by copying the WAL segment files.
`-X stream`	Streams the WAL files while taking the backup for continuous consistency.
`-P`	Shows progress information during the backup.
`--max-rate=<rate>`	Limits the maximum transfer rate during the backup (e.g., 5M for 5 megabytes/sec).
`-v`	–verbose, extra output

———- Restore ———-

1. Restore is a File-Level Operation

A base backup is a physical copy of the database files — including system catalogs, user data, WAL files, and optionally config files.
Restoring is done by simply copying or extracting the backup files into a valid PostgreSQL data directory (PGDATA) on the target system.

Restore Steps:

Stop PostgreSQL (if running)
Copy or extract the backup into a clean data directory (PGDATA)
Place a file named recovery.signal in the data directory
Start PostgreSQL to begin recovery

2. Custom Tablespaces Outside `PGDATA`

Backups include symlinks to external tablespace locations
On restore:
- Ensure original paths exist and are accessible
- Or remap symlinks to new locations
- Check ownership and permissions (postgres:postgres, 0700)

3. Restoring on a Different Host

Ensure matching directory structure or adjust accordingly
PostgreSQL version and architecture must match
Update postgresql.conf and pg_hba.conf as needed

4. Restoring Across PostgreSQL Versions

pg_basebackup is version-specific.

Not allowed: PostgreSQL 14 → PostgreSQL 15 or 17 (Lower to Higher)
Not allowed: PostgreSQL 17 → PostgreSQL 14 (Higher to Lower)
Use pg_dump/pg_restore or pg_upgrade for version upgrades

———- Recovery ———-

How Recovery Works:

PostgreSQL detects recovery.signal at startup
WAL files (in pg_wal/ or archive) are replayed (Redo apply / Archive apply from restore_command location )
When recovery completes:
- PostgreSQL automatically removes recovery.signal
- The server becomes a primary (read/write)
Recovery stops when:
- All available WALs are applied, or
- A recovery target (e.g., timestamp, transaction ID) is reached

What is recovery.signal ?

File	Purpose
`recovery.signal`	Tells PostgreSQL to enter recovery mode during startup
Automatically removed?	Yes, after recovery completes
Required for standalone restore?	Yes, otherwise WAL replay is skipped

Summary

1. Take a base backup on the source server using ‘pg_basebackup’.
2. Take Backup of WAL Archive files separately on Source (Manually, because pg_basebackup does NOT take backup of WAL archive files)
3. Transfer both the base backup and WAL archive logs to the target server.
4. Restore the base backup to the PostgreSQL data directory on the target.
5. Copy the WAL files to the dedicated WAL location (e.g., ‘/pgwal/pgsql17/wals’).
6. Remove ‘$PGDATA/pg_wal’ and create a symbolic link to the WAL location:
‘ln -s /pgwal/pgsql17/wals /pgdata/pgsql17/data/pg_wal’
7. Create an empty ‘recovery.signal‘ file in ‘$PGDATA’.
8. Set ‘restore_command‘ in ‘postgresql.conf’ to point to WAL archive backup path (e.g., ‘/pgbackup/pgsql17/backup/wal_archive_bkp’).
9. Ensure WAL archive files are restored to ‘/pgbackup/pgsql17/backup/wal_archive_bkp’ for ‘restore_command’ to access.
10. Update ‘tablespace_map‘ to reflect correct paths if using custom tablespaces.
11. Start PostgreSQL on the target; recovery will complete and automatically remove ‘recovery.signal’.
12. Tablespace symbolic links will create automatically by PostgreSQL.

Operation	Key Point
Backup	File-level copy using replication protocol
Restore	Place files into `PGDATA`, handle symlinks, configs, and versions
Recovery	Triggered by `recovery.signal`, replays WAL, auto-removes signal file

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

pg_dump & pg_restore

PostgreSQLBinary Backup, Custom Format Backup, Database Best Practices, Database Migration, Database Restore, Directory Format Backup, Disaster Recovery, Full Backup, Object Level Backup, Parallel Backup, pg_basebackup, pg_dump, pg_dump examples, pg_dump options, pg_dumpall, pg_restore, pg_restore examples, pg_restore options, Plain Text Backup, PostgreSQL, PostgreSQL 15, PostgreSQL 16, PostgreSQL 17, PostgreSQL Admin, PostgreSQL Backup, PostgreSQL backup syntax, PostgreSQL CLI, PostgreSQL Commands, PostgreSQL Database, PostgreSQL Maintenance, PostgreSQL Restore, PostgreSQL restore syntax, PostgreSQL Tips, PostgreSQL Tutorials, psql, Restore from dump, Restore using psql, Schema Level Backup, Schema Restore, SQL Backup, Table Level Backup, Table Restore, Tar Format Backup

PostgreSQL pg_dump and pg_restore Guide

Table of Contents
___________________________________________________________________________________________________

Backup:

0. pg_dump / pg_restore –help
1. Backup Output Formats
2. Full Database Backup

A. Plain Text Format
B. Tar Format
C. Directory Format
D. Custom Format

3. Schema Level Backup
4. Table Level Backup
5. Object Level Backup (PostgreSQL v17 Feature)

Restore:

6. Pre-requisites for Restore
7. Full Database Restore
8. Schema Level Restore

A. Restore from Own Schema’s Backup
B. Restore Specific Schema from Full Backup

9. Table Level Restore

A. Restore from Own Table’s Backup
B. Restore Table Data-Only from Schema Backup
C. Restore Table from Schema Backup
D. Restore Table with -n and -t Options
E. Restore Using Plain SQL File (COPY-based)

___________________________________________________________________________________________________

0. pg_dump/pg_restore –help

Click to expand pg_dump –help

[postgres@pg17 ~]$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -e, --extension=PATTERN      dump the specified extension(s) only
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=PATTERN         dump the specified schema(s) only
  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=PATTERN          dump the specified table(s) only
  -T, --exclude-table=PATTERN  do NOT dump the specified table(s)
  -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
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --include-foreign-data=PATTERN
                               include data of foreign tables on foreign
                               servers matching PATTERN
  --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-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --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
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -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 no database name is supplied, then the PGDATABASE environment
variable value is used.

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

Click to expand pg_restore –help

[postgres@pg17 ~]$ pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name (- for stdout)
  -F, --format=c|d|t       backup file format (should be automatic)
  -l, --list               print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

Options controlling the restore:
  -a, --data-only              restore only the data, no schema
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 create the target database
  -e, --exit-on-error          exit on error, default is to continue
  -I, --index=NAME             restore named index
  -j, --jobs=NUM               use this many parallel jobs to restore
  -L, --use-list=FILENAME      use table of contents from this file for
                               selecting/ordering output
  -n, --schema=NAME            restore only objects in this schema
  -N, --exclude-schema=NAME    do not restore objects in this schema
  -O, --no-owner               skip restoration of object ownership
  -P, --function=NAME(args)    restore named function
  -s, --schema-only            restore only the schema, no data
  -S, --superuser=NAME         superuser user name to use for disabling triggers
  -t, --table=NAME             restore named relation (table, view, etc.)
  -T, --trigger=NAME           restore named trigger
  -x, --no-privileges          skip restoration of access privileges (grant/revoke)
  -1, --single-transaction     restore as a single transaction
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security
  --if-exists                  use IF EXISTS when dropping objects
  --no-comments                do not restore comments
  --no-data-for-failed-tables  do not restore data of tables that could not be
                               created
  --no-publications            do not restore publications
  --no-security-labels         do not restore security labels
  --no-subscriptions           do not restore subscriptions
  --no-table-access-method     do not restore table access methods
  --no-tablespaces             do not restore tablespace assignments
  --section=SECTION            restore named section (pre-data, data, or post-data)
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -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 restore

The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.

If no input file name is supplied, then standard input is used.

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

1. Backup Output Formats

A. Plain Text Format
# Using -Fp explicitly:
pg_dump -U postgres -d your_db_name -Fp -f /path/to/your_db.sql

# Shell redirect
pg_dump -U postgres -d your_db_name > /path/to/your_db.sql

B. Tar Format
pg_dump -U postgres -d your_db_name -Ft -f /path/to/your_db.tar

C. Directory Format and Parallel Backup
-- Parallel backup is only supported with -Fd
-- pg_dump will create the directory if it doesn't exist.

# Without parallelism
pg_dump -U postgres -d your_db_name -Fd -f /path/to/backup_dir/

# With parallelism
pg_dump -U postgres -d your_db_name -Fd -j8 -f /path/to/backup_dir/

D. Custom Format (Compressed binary format by default)
pg_dump -U postgres -d your_db_name -Fc -f /path/to/your_db.dump

2. Full Database Backup

A. Plain Text Format

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v > /pgBackup/pgsql15/backup/blpua/database_blpua_full.sql 2> /pgBackup/pgsql15/backup/blpua/log/database_blpua_full.log &

-- OR --

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v -Fp -f /pgBackup/pgsql15/backup/blpua/database_blpua_full1.sql > /pgBackup/pgsql15/backup/blpua/log/database_blpua_full1.log 2>&1 &

-- OR -- 

[postgres@pg17 ~]$ pg_dump -U postgres -d blpua | split -b 1G - /pgBackup/pgsql15/backup/blpua/database_blpua_split.dmp
[postgres@pg17 ~]$
[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/database_blpua_s*
-rw-r--r--. 1 postgres postgres 1.0G Sep 30 11:03 /pgBackup/pgsql15/backup/blpua/database_blpua_split.dmpaa
-rw-r--r--. 1 postgres postgres 650M Sep 30 11:03 /pgBackup/pgsql15/backup/blpua/database_blpua_split.dmpab
[postgres@pg17 ~]$

-- OR --

[postgres@pg17 ~]$ nohup sh -c "pg_dump -U postgres -d blpua -v | gzip > /pgBackup/pgsql15/backup/blpua/database_blpua.gz" > /pgBackup/pgsql15/backup/blpua/log/database_blpua.log 2>&1 &

-- OR --

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v > /pgBackup/pgsql15/backup/blpua/database_blpua.sql 2> /pgBackup/pgsql15/backup/blpua/log/database_blpua.log & gzip /pgBackup/pgsql15/backup/blpua/database_blpua.sql

B. Tar Format

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v -Ft -f /pgBackup/pgsql15/backup/blpua/database_blpua.tar > /pgBackup/pgsql15/backup/blpua/log/blpua_tar_dump.log 2>&1 &

C. Directory Format

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v -Fd -j4 -f /pgBackup/pgsql15/backup/blpua_dir > /pgBackup/pgsql15/backup/blpua/log/blpua_dump.log 2>&1 &

D. Custome Format

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -Fc -v -f /pgBackup/pgsql15/backup/blpua/database_blpua_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/database_blpua_$(date +%Y_%m_%d).log 2>&1 &
[postgres@pg17 ~]$ 
[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/database_blpua*.dmp
-rw-r--r--. 1 postgres postgres 402M Sep 29 04:58 /pgBackup/pgsql15/backup/blpua/database_blpua_2025_09_29.dmp

[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/log/database_blpua*.log
-rw-r--r--. 1 postgres postgres 2.5K Sep 29 04:58 /pgBackup/pgsql15/backup/blpua/log/database_blpua_2025_09_29.log
[postgres@pg17 ~]$

3. Schema Level Backup

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -n blp -Fc -v -f /pgBackup/pgsql15/backup/blpua/schema_blp_blpua_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua_$(date +%Y_%m_%d).log 2>&1 &
[1] 5472
[postgres@pg17 ~]$

[postgres@pg17 ~]$ jobs -l
[1]+  5472 Running                 nohup pg_dump -U postgres -d blpua -n blp -Fc -v -f /pgBackup/pgsql15/backup/blpua/schema_blp_blpua_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua_$(date +%Y_%m_%d).log 2>&1 &
[postgres@pg17 ~]$

[postgres@pg17 ~]$ jobs -l
[1]+  5472 Done                    nohup pg_dump -U postgres -d blpua -n blp -Fc -v -f /pgBackup/pgsql15/backup/blpua/schema_blp_blpua_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua_$(date +%Y_%m_%d).log 2>&1
[postgres@pg17 ~]$


[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/schema_blp_blpua*
-rw-r--r--. 1 postgres postgres 402M Sep 30 02:09 /pgBackup/pgsql15/backup/blpua/schema_blp_blpua_2025_09_30.dmp
[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua*
-rw-r--r--. 1 postgres postgres 2.5K Sep 30 02:09 /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua_2025_09_30.log
[postgres@pg17 ~]$

4. Table Level Backup

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -t blp.employees -Fc -v -f /pgBackup/pgsql15/backup/blpua/table_blp_employees_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/table_blp_employees_$(date +%Y_%m_%d).log 2>&1 &
[1] 5652
[postgres@pg17 ~]$ 

[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/table_blp_employees_*
-rw-r--r--. 1 postgres postgres 62M Sep 30 02:22 /pgBackup/pgsql15/backup/blpua/table_blp_employees_2025_09_30.dmp
[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/log/table_blp_employees_*
-rw-r--r--. 1 postgres postgres 2.0K Sep 30 02:22 /pgBackup/pgsql15/backup/blpua/log/table_blp_employees_2025_09_30.log
[postgres@pg17 ~]$

5. Object Level Backup (PostgreSQL v17 Feature)

++ filter is only for pg_dump, not pg_restore

[postgres@lxicbpgdsgv01 ~]$ cat include_tables.par
include table demo.table_1
include table demo.table_2
include table demo.table_3
[postgres@lxicbpgdsgv01 ~]$


[postgres@lxicbpgdsgv01 ~]$ pg_dump -d testdb_source --filter=include_tables.par > include_tables.sql

[postgres@lxicbpgdsgv01 ~]$ ls -lrth include_tables.sql
-rw-r--r--. 1 postgres postgres 4.5K Sep 30 22:19 include_tables.sql
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat include_tables.sql | grep -i "CREATE TABLE"
CREATE TABLE demo.table_1 (
CREATE TABLE demo.table_2 (
CREATE TABLE demo.table_3 (
[postgres@lxicbpgdsgv01 ~]$


[postgres@lxicbpgdsgv01 ~]$ cat exclude_tables.par
exclude table demo.table_1
exclude table demo.table_2
exclude table demo.table_3
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ pg_dump -d testdb_source --filter=exclude_tables.par > exclude_tables.sql
[postgres@lxicbpgdsgv01 ~]$ ls -ltr exclude_tables.sql
-rw-r--r--. 1 postgres postgres 10931 Sep 30 22:23 exclude_tables.sql
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ cat exclude_tables.sql | grep -i "CREATE TABLE"
CREATE TABLE demo.table_10 (
CREATE TABLE demo.table_4 (
CREATE TABLE demo.table_5 (
CREATE TABLE demo.table_6 (
CREATE TABLE demo.table_7 (
CREATE TABLE demo.table_8 (
CREATE TABLE demo.table_9 (
[postgres@lxicbpgdsgv01 ~]$

6. Pre-requisites for restore

postgres=# CREATE TABLESPACE TEST_TB LOCATION '/pgTb/pgsql15/test';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE TEST_IX LOCATION '/pgIx/pgsql15/test';
CREATE TABLESPACE
postgres=#
postgres=# CREATE DATABASE TEST TABLESPACE TEST_TB;
CREATE DATABASE
postgres=#

[postgres@pg17 blpua]$ scp *.dmp postgres@192.168.2.32:/pgBackup/pgsql15/backup/test
postgres@192.168.2.32's password:
database_blpua_2025_09_29.dmp                           100%  402MB  39.9MB/s   00:10
schema_blp_blpua_2025_09_30.dmp                         100%  402MB  43.8MB/s   00:09
table_blp_employees_2025_09_30.dmp                      100%   61MB  47.0MB/s   00:01
[postgres@pg17 blpua]$

7. Full Database Restore

++ The source database uses two custom tablespaces: blpua_tbs01 and blpua_ixtbs01.
++ On the target system, I have two different tablespaces: test_tb and test_ix.
++ Unlike Oracle, PostgreSQL does not provide a direct equivalent of REMAP_TABLESPACE.
++ Therefore, to simplify the restore and ensure all objects are placed into the default tablespace (test_tb).
++ Using --no-owner --no-tablespaces in pg_restore — even if not specified, data still gets written to the default database tablespace. These options just help avoid errors in the log.
++ This ensures that all objects are restored to the default tablespace of the target database, regardless of their original tablespace assignments.
++ Later, we can move all indexes to the desired target tablespace using the ALTER INDEX ... SET TABLESPACE command.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# ALTER INDEX blp.idx_employee_name SET TABLESPACE test_ix;  <--  This command physically relocates the index to the test_ix tablespace.
ALTER INDEX
test=#


[postgres@lxtrdpgdsgv01 ~]$ nohup pg_restore -U postgres -d test --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/database_blpua_2025_09_29.dmp > /pgBackup/pgsql15/backup/test/log/restore_blpua_test_$(date +%Y_%m_%d).log 2>&1 &
[1] 5300
[postgres@lxtrdpgdsgv01 ~]$

[postgres@lxtrdpgdsgv01 ~]$ cat /pgBackup/pgsql15/backup/test/log/restore_blpua_test_2025_09_30.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "blp"
pg_restore: creating TYPE "blp.address"
pg_restore: creating TYPE "blp.employment_status"
pg_restore: creating DOMAIN "blp.positive_integer"
pg_restore: creating FUNCTION "blp.get_salary(integer)"
pg_restore: creating FUNCTION "blp.log_update()"
pg_restore: creating PROCEDURE "blp.raise_salary(integer, numeric)"
pg_restore: creating SEQUENCE "blp.emp_id_seq"
pg_restore: creating TABLE "blp.employees"
pg_restore: creating MATERIALIZED VIEW "blp.emp_summary"
pg_restore: creating SEQUENCE "blp.employees_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.employees_id_seq"
pg_restore: creating VIEW "blp.high_paid_employees"
pg_restore: creating TABLE "blp.metrics"
pg_restore: creating TABLE "blp.metrics_high"
pg_restore: creating SEQUENCE "blp.metrics_id_seq"
pg_restore: creating TABLE "blp.metrics_low"
pg_restore: creating TABLE "blp.metrics_mid"
pg_restore: creating TABLE "blp.metrics_rest"
pg_restore: creating TABLE "blp.sales"
pg_restore: creating TABLE "blp.sales_2021"
pg_restore: creating TABLE "blp.sales_2022"
pg_restore: creating TABLE "blp.sales_2023"
pg_restore: creating TABLE "blp.sales_2024"
pg_restore: creating SEQUENCE "blp.sales_id_seq"
pg_restore: creating TABLE "blp.test_data"
pg_restore: creating SEQUENCE "blp.test_data_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.test_data_id_seq"
pg_restore: creating TABLE ATTACH "blp.metrics_high"
pg_restore: creating TABLE ATTACH "blp.metrics_low"
pg_restore: creating TABLE ATTACH "blp.metrics_mid"
pg_restore: creating TABLE ATTACH "blp.metrics_rest"
pg_restore: creating TABLE ATTACH "blp.sales_2021"
pg_restore: creating TABLE ATTACH "blp.sales_2022"
pg_restore: creating TABLE ATTACH "blp.sales_2023"
pg_restore: creating TABLE ATTACH "blp.sales_2024"
pg_restore: creating DEFAULT "blp.employees id"
pg_restore: creating DEFAULT "blp.test_data id"
pg_restore: processing data for table "blp.employees"
pg_restore: processing data for table "blp.metrics_high"
pg_restore: processing data for table "blp.metrics_low"
pg_restore: processing data for table "blp.metrics_mid"
pg_restore: processing data for table "blp.metrics_rest"
pg_restore: processing data for table "blp.sales_2021"
pg_restore: processing data for table "blp.sales_2022"
pg_restore: processing data for table "blp.sales_2023"
pg_restore: processing data for table "blp.sales_2024"
pg_restore: processing data for table "blp.test_data"
pg_restore: executing SEQUENCE SET emp_id_seq
pg_restore: executing SEQUENCE SET employees_id_seq
pg_restore: executing SEQUENCE SET metrics_id_seq
pg_restore: executing SEQUENCE SET sales_id_seq
pg_restore: executing SEQUENCE SET test_data_id_seq
pg_restore: creating CONSTRAINT "blp.employees employees_pkey"
pg_restore: creating CONSTRAINT "blp.metrics metrics_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_high metrics_high_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_low metrics_low_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_mid metrics_mid_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_rest metrics_rest_pkey"
pg_restore: creating CONSTRAINT "blp.sales sales_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2021 sales_2021_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2022 sales_2022_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2023 sales_2023_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2024 sales_2024_pkey"
pg_restore: creating CONSTRAINT "blp.test_data test_data_pkey"
pg_restore: creating INDEX "blp.idx_employee_name"
pg_restore: creating INDEX ATTACH "blp.metrics_high_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_low_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_mid_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_rest_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2021_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2022_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2023_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2024_pkey"
pg_restore: creating TRIGGER "blp.employees employee_update_trigger"
pg_restore: creating MATERIALIZED VIEW DATA "blp.emp_summary"
[postgres@lxtrdpgdsgv01 ~]$

8. Schema Level Restore

++ pg_restore does not support schema remapping like Oracle’s REMAP_SCHEMA.
++ I want to restore schema blp --> trd. 
++ Option A: Restore Dump as it is, Then Rename <--- Best for simplicity
++ Option B: Split Dump into Schema + Data, Modify Both <--- This is more complex, but possible if you must use schema remapping.
++ This is schema dump file : /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
++ Using --no-owner --no-tablespaces in pg_restore — even if not specified, data still gets written to the default database tablespace. These options just help avoid errors in the log.

A. To Restore schema from own schema’s backup

postgres=# CREATE TABLESPACE ORCL_TB LOCATION '/pgTb/pgsql15/orcl';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE ORCL_IX LOCATION '/pgIx/pgsql15/orcl';
CREATE TABLESPACE
postgres=#
postgres=# CREATE DATABASE ORCL TABLESPACE ORCL_TB;
CREATE DATABASE
postgres=#

-- With NO parallelism


[postgres@lxtrdpgdsgv01 ~]$ nohup pg_restore -U postgres -d orcl --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp > /pgBackup/pgsql15/backup/test/log/restore_blpua_test_$(date +%Y_%m_%d).log 2>&1 &
[1] 7344
[postgres@lxtrdpgdsgv01 ~]$

postgres=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# ALTER SCHEMA blp RENAME TO trd;
ALTER SCHEMA
orcl=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
 trd    | postgres
(2 rows)

orcl=#


[postgres@lxtrdpgdsgv01 ~]$ cat /pgBackup/pgsql15/backup/test/log/restore_blpua_test_2025_09_30.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "blp"
pg_restore: creating TYPE "blp.address"
pg_restore: creating TYPE "blp.employment_status"
pg_restore: creating DOMAIN "blp.positive_integer"
pg_restore: creating FUNCTION "blp.get_salary(integer)"
pg_restore: creating FUNCTION "blp.log_update()"
pg_restore: creating PROCEDURE "blp.raise_salary(integer, numeric)"
pg_restore: creating SEQUENCE "blp.emp_id_seq"
pg_restore: creating TABLE "blp.employees"
pg_restore: creating MATERIALIZED VIEW "blp.emp_summary"
pg_restore: creating SEQUENCE "blp.employees_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.employees_id_seq"
pg_restore: creating VIEW "blp.high_paid_employees"
pg_restore: creating TABLE "blp.metrics"
pg_restore: creating TABLE "blp.metrics_high"
pg_restore: creating SEQUENCE "blp.metrics_id_seq"
pg_restore: creating TABLE "blp.metrics_low"
pg_restore: creating TABLE "blp.metrics_mid"
pg_restore: creating TABLE "blp.metrics_rest"
pg_restore: creating TABLE "blp.sales"
pg_restore: creating TABLE "blp.sales_2021"
pg_restore: creating TABLE "blp.sales_2022"
pg_restore: creating TABLE "blp.sales_2023"
pg_restore: creating TABLE "blp.sales_2024"
pg_restore: creating SEQUENCE "blp.sales_id_seq"
pg_restore: creating TABLE "blp.test_data"
pg_restore: creating SEQUENCE "blp.test_data_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.test_data_id_seq"
pg_restore: creating TABLE ATTACH "blp.metrics_high"
pg_restore: creating TABLE ATTACH "blp.metrics_low"
pg_restore: creating TABLE ATTACH "blp.metrics_mid"
pg_restore: creating TABLE ATTACH "blp.metrics_rest"
pg_restore: creating TABLE ATTACH "blp.sales_2021"
pg_restore: creating TABLE ATTACH "blp.sales_2022"
pg_restore: creating TABLE ATTACH "blp.sales_2023"
pg_restore: creating TABLE ATTACH "blp.sales_2024"
pg_restore: creating DEFAULT "blp.employees id"
pg_restore: creating DEFAULT "blp.test_data id"
pg_restore: processing data for table "blp.employees"
pg_restore: processing data for table "blp.metrics_high"
pg_restore: processing data for table "blp.metrics_low"
pg_restore: processing data for table "blp.metrics_mid"
pg_restore: processing data for table "blp.metrics_rest"
pg_restore: processing data for table "blp.sales_2021"
pg_restore: processing data for table "blp.sales_2022"
pg_restore: processing data for table "blp.sales_2023"
pg_restore: processing data for table "blp.sales_2024"
pg_restore: processing data for table "blp.test_data"
pg_restore: executing SEQUENCE SET emp_id_seq
pg_restore: executing SEQUENCE SET employees_id_seq
pg_restore: executing SEQUENCE SET metrics_id_seq
pg_restore: executing SEQUENCE SET sales_id_seq
pg_restore: executing SEQUENCE SET test_data_id_seq
pg_restore: creating CONSTRAINT "blp.employees employees_pkey"
pg_restore: creating CONSTRAINT "blp.metrics metrics_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_high metrics_high_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_low metrics_low_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_mid metrics_mid_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_rest metrics_rest_pkey"
pg_restore: creating CONSTRAINT "blp.sales sales_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2021 sales_2021_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2022 sales_2022_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2023 sales_2023_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2024 sales_2024_pkey"
pg_restore: creating CONSTRAINT "blp.test_data test_data_pkey"
pg_restore: creating INDEX "blp.idx_employee_name"
pg_restore: creating INDEX ATTACH "blp.metrics_high_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_low_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_mid_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_rest_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2021_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2022_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2023_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2024_pkey"
pg_restore: creating TRIGGER "blp.employees employee_update_trigger"
pg_restore: creating MATERIALIZED VIEW DATA "blp.emp_summary"
[postgres@lxtrdpgdsgv01 ~]$


--- OR ---

-- With parallelism 4

nohup pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp > /pgBackup/pgsql15/backup/test/log/restore_blpua_test_$(date +%Y_%m_%d).log1 2>&1 &

[postgres@lxtrdpgdsgv01 ~]$ ps -ef | grep pg_restore
postgres    7089    5102  0 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7091    7089  2 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7092    7089  3 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7093    7089  2 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7094    7089  2 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7126    5736  0 05:49 pts/1    00:00:00 grep --color=auto pg_restore
[postgres@lxtrdpgdsgv01 ~]$

B. To Restore only a specific schema from Full Database Backup

Step 1: Create schema 

gebua=# CREATE SCHEMA IF NOT EXISTS blp;

Step 2: Restore Objects and Data

nohup pg_restore -U postgres -d gebua -n blp --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/database_blpua_2025_09_29.dmp > /pgBackup/pgsql15/backup/test/log/restore_blpua_test_$(date +%Y_%m_%d).log_full 2>&1 &

9. Table Level Restore

A. Restore Table from own table’s backup

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -l /pgBackup/pgsql15/backup/test/table_blp_employees_2025_09_30.dmp
;
; Archive created at 2025-09-30 02:22:21 EDT
;     dbname: blpua
;     TOC Entries: 13
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.13
;     Dumped by pg_dump version: 15.13
;
;
; Selected TOC Entries:
;
229; 1259 92805 TABLE blp employees postgres
228; 1259 92804 SEQUENCE blp employees_id_seq postgres
4338; 0 0 SEQUENCE OWNED BY blp employees_id_seq postgres
4181; 2604 92808 DEFAULT blp employees id postgres
4331; 0 92805 TABLE DATA blp employees postgres
4339; 0 0 SEQUENCE SET blp employees_id_seq postgres
4183; 2606 92812 CONSTRAINT blp employees employees_pkey postgres
4184; 1259 92824 INDEX blp idx_employee_name postgres
4185; 2620 92829 TRIGGER blp employees employee_update_trigger postgres
[postgres@lxtrdpgdsgv01 ~]$


Step 1: Create schema 

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=# CREATE SCHEMA IF NOT EXISTS blp;
CREATE SCHEMA
edpua=#


Step 2: Restore Objects and Data

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -d edpua --no-tablespaces -v /pgBackup/pgsql15/backup/test/table_blp_employees_2025_09_30.dmp
pg_restore: connecting to database for restore
pg_restore: creating TABLE "blp.employees"
pg_restore: creating SEQUENCE "blp.employees_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.employees_id_seq"
pg_restore: creating DEFAULT "blp.employees id"
pg_restore: processing data for table "blp.employees"
pg_restore: executing SEQUENCE SET employees_id_seq
pg_restore: creating CONSTRAINT "blp.employees employees_pkey"
pg_restore: creating INDEX "blp.idx_employee_name"
pg_restore: creating TRIGGER "blp.employees employee_update_trigger"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4185; 2620 92829 TRIGGER employees employee_update_trigger postgres
pg_restore: error: could not execute query: ERROR:  function blp.log_update() does not exist
Command was: CREATE TRIGGER employee_update_trigger AFTER UPDATE ON blp.employees FOR EACH ROW EXECUTE FUNCTION blp.log_update();


pg_restore: warning: errors ignored on restore: 1
[postgres@lxtrdpgdsgv01 ~]$

++ PostgreSQL does not have a --ignore-errors flag, The above erros ignorable.

B. Restore Single Table data-only from Schema backup

edpua=# TRUNCATE TABLE blp.employees;
TRUNCATE TABLE
edpua=#

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -d edpua --data-only -t employees -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
pg_restore: connecting to database for restore
pg_restore: processing data for table "blp.employees"
[postgres@lxtrdpgdsgv01 ~]$

edpua=# select count(*) from blp.employees;
 5000000  <-----

edpua=#

C. Restore Single Table from Schema backup

Step 1: Create schema 

edpua=# CREATE SCHEMA blp;

Step 2: Restore Objects and Data

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -d edpua --clean -t employees -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
pg_restore: connecting to database for restore
pg_restore: dropping TABLE employees
pg_restore: creating TABLE "blp.employees"
pg_restore: processing data for table "blp.employees"
[postgres@lxtrdpgdsgv01 ~]$
[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=# select count(*) from blp.employees;
  count
---------
 5000000 <----
(1 row)

edpua=#

D. Restore Single Table from Schema backup (add both -n and -t)

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -d edpua --clean -n blp -t employees -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
pg_restore: connecting to database for restore
pg_restore: dropping TABLE employees
pg_restore: creating TABLE "blp.employees"
pg_restore: processing data for table "blp.employees"
[postgres@lxtrdpgdsgv01 ~]$
[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=# select count(*) from blp.employees;
  count
---------
 5000000 <-----
(1 row)

edpua=#

E. Restoring Using a Plain SQL File (COPY-based)

Step 1: Extracted table data from custom-format dump

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -n blp -t employees -f /pgBackup/pgsql15/backup/test/blp_employees.sql /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
[postgres@lxtrdpgdsgv01 ~]$
[postgres@lxtrdpgdsgv01 ~]$ ls -lrth /pgBackup/pgsql15/backup/test/blp_employees.sql
-rw-r--r--. 1 postgres postgres 210M Sep 30 09:17 /pgBackup/pgsql15/backup/test/blp_employees.sql
[postgres@lxtrdpgdsgv01 ~]$

[postgres@lxtrdpgdsgv01 ~]$ head -50 /pgBackup/pgsql15/backup/test/blp_employees.sql
--
-- PostgreSQL database dump
--

\restrict TthhVsS8JvVxrplYNApzHUalZpygXUqFg2pIvbSH8GTkbJ6WBNyW0JLnuuvkPdt

-- Dumped from database version 15.13
-- Dumped by pg_dump version 15.13

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

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: employees; Type: TABLE; Schema: blp; Owner: postgres
--

CREATE TABLE blp.employees (
    id integer NOT NULL,
    name text,
    salary numeric(10,2),
    hired_on date
);


ALTER TABLE blp.employees OWNER TO postgres;

--
-- Data for Name: employees; Type: TABLE DATA; Schema: blp; Owner: postgres
--

COPY blp.employees (id, name, salary, hired_on) FROM stdin;
1       Employee_1      70969.47        2005-06-13
2       Employee_2      94341.15        2006-12-09
3       Employee_3      101657.57       2012-07-05
4       Employee_4      54701.92        2010-01-05
5       Employee_5      104229.43       2001-06-28
6       Employee_6      95842.57        2010-10-06
7       Employee_7      50618.08        2015-06-02
[postgres@lxtrdpgdsgv01 ~]$

Step 2: Restore using psql command

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=#
edpua=# drop table blp.employees;
DROP TABLE
edpua=# exit
[postgres@lxtrdpgdsgv01 ~]$


psql -U postgres -d edpua < /pgBackup/pgsql15/backup/test/blp_employees.sql

[postgres@lxtrdpgdsgv01 ~]$ psql -U postgres -d edpua < /pgBackup/pgsql15/backup/test/blp_employees.sql
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 5000000
[postgres@lxtrdpgdsgv01 ~]$

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

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=# SELECT COUNT(*) FROM blp.employees;
  count
---------
 5000000 <-----
(1 row)

edpua=#

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

PSQL

PostgreSQLadvanced PostgreSQL, asynchronous replication PostgreSQL, autovacuum tuning, change PGDATA, compile PostgreSQL, create database PostgreSQL, database administration, database performance tuning, DBA Training, install PostgreSQL Linux, Linux PostgreSQL DBA, logical replication PostgreSQL, manual failover PostgreSQL, migrate Oracle to PostgreSQL, Open Source Database, Oracle DBA to PostgreSQL, Patroni PostgreSQL, pg_basebackup, pg_dump, pg_dumpall, pg_hba.conf, pg_restore, pg_stat_activity, pg_wal, point in time recovery, Postgres admin guide, Postgres DBA course, Postgres PITR, Postgres query optimization, Postgres real time scenarios, PostgreSQL, PostgreSQL 17, PostgreSQL access control, PostgreSQL administration, PostgreSQL architecture, PostgreSQL archive mode, PostgreSQL Authentication, PostgreSQL automatic failover, PostgreSQL AWS, PostgreSQL Azure, PostgreSQL Backup, PostgreSQL backup and recovery, PostgreSQL CentOS, PostgreSQL cloud, PostgreSQL Cluster Backup, PostgreSQL clustering, PostgreSQL Configuration, PostgreSQL data directory, PostgreSQL database creation, PostgreSQL DBA, PostgreSQL disaster recovery, PostgreSQL failover, PostgreSQL for beginners, PostgreSQL GCP, PostgreSQL HA, PostgreSQL high availability, PostgreSQL Indexing, PostgreSQL installation, PostgreSQL internals, PostgreSQL Linux 7, PostgreSQL logical backup, postgresql migration, PostgreSQL Monitoring, PostgreSQL on Linux, PostgreSQL Performance, PostgreSQL performance tuning, PostgreSQL physical backup, PostgreSQL PITR, PostgreSQL recovery, PostgreSQL replication, PostgreSQL Restore, PostgreSQL RHEL, PostgreSQL roles, PostgreSQL Roles and Privileges, PostgreSQL security, PostgreSQL service management, PostgreSQL source installation, PostgreSQL SSL setup, PostgreSQL step by step, PostgreSQL storage management, PostgreSQL Tablespace, PostgreSQL training, PostgreSQL Troubleshooting, PostgreSQL Tutorial, PostgreSQL user management, PostgreSQL users, PostgreSQL WAL, PostgreSQL WAL directory, REPMGR, SQL Tuning, Start PostgreSQL, Stop PostgreSQL, streaming replication, streaming replication PostgreSQL, synchronous replication PostgreSQL, vacuum analyze PostgreSQL, WAL archiving, WAL archiving PostgreSQL, write-ahead logging

PostgreSQL DBA Step by Step Learning

#	PostgreSQL DBA Topics
1	How to Install PostgreSQL ON Linux?
2	How to Install PostgreSQL on Linux 7 using source code?
3	How to START/STOP PostgreSQL ON Linux?
4	How to Create Database in PostgreSQL?
5	PostgreSQL User Management
6	PostgreSQL pg_hba.conf Guide
7	PostgreSQL Change Data Directory
8	Understanding WAL Files in PostgreSQL – For Oracle DBAs
9	Change PostgreSQL WAL Directory Path (pg_wal)
10	Enable Archive Mode in PostgreSQL 17
11	How to Disable ARCHIVELOG Mode
12	PostgreSQL Tablespace Management
13	PostgreSQL pg_dump and pg_restore Guide
14	PostgreSQL Backup and Restore Using pg_dumpall and psql
15	pg_basebackup – Backup, Restore, and Recovery
16	Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17	Backup & Restore PostgreSQL DB Cluster on Same Host
18	Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19	PostgreSQL PITR – Point in Time Recovery
20	Configure Streaming Replication in PostgreSQL
21	Manual Failover in PostgreSQL Streaming Replication
22	Convert Asynchronous Replication to Synchronous Replication

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