PostgreSQL Backup & Restore to New Host with WAL Archive files

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/