PostgreSQL PITR: How to Restore & Recover to a Specific Point in Time
Table of Contents
0. Goal
Scenario: Application team deleted critical data at 2025-10-12 22:05:29 PM.
Action: Recover database to 2025-10-12 22:05:00 PM using base backup and WAL archives.
---> Restore and recover on other server
---> Take backup of table
---> Restore the table back.
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_bkp12OCT2025 | restore_command on target server for full recovery |
| recovery_target_time | Table dropped at2025-10-12 22:05:29 | Recover until time 2025-10-12 22:05:00 | recovery_target_time on target server for PITR |
| 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=# 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=#
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 ~]$
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/base_bkp12OCT2025
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/archive_bkp12OCT2025
[postgres@lxicbpgdsgv01 ~]$
4. Take Base Backup and WAL Archive
4.1. Take Base Backup
[postgres@lxicbpgdsgv01 ~]$ nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/base_bkp12OCT2025 -Ft -Xs -P -v > /pgBackup/pgsql17/backup/base_bkp12OCT2025.log 2>&1 &
[1] 3347
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ cat /pgBackup/pgsql17/backup/base_bkp12OCT2025.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/91000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3350"
7754/535860 kB (1%), 0/3 tablespaces (...ckup/base_bkp12OCT2025/16542.tar)
7754/535860 kB (1%), 1/3 tablespaces (...ckup/base_bkp12OCT2025/16542.tar)
15474/535860 kB (2%), 1/3 tablespaces (...ckup/base_bkp12OCT2025/16545.tar)
15474/535860 kB (2%), 2/3 tablespaces (...ckup/base_bkp12OCT2025/16545.tar)
191400/535860 kB (35%), 2/3 tablespaces (...ackup/base_bkp12OCT2025/base.tar)
372072/535860 kB (69%), 2/3 tablespaces (...ackup/base_bkp12OCT2025/base.tar)
525644/535860 kB (98%), 2/3 tablespaces (...ackup/base_bkp12OCT2025/base.tar)
535873/535873 kB (100%), 2/3 tablespaces (...ackup/base_bkp12OCT2025/base.tar)
535873/535873 kB (100%), 3/3 tablespaces
pg_basebackup: write-ahead log end point: 0/91000120
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_bkp12OCT2025
540M /pgBackup/pgsql17/backup/base_bkp12OCT2025
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgBackup/pgsql17/backup/base_bkp12OCT2025
total 552508
-rw-------. 1 postgres postgres 7940096 Oct 12 21:57 16542.tar
-rw-------. 1 postgres postgres 7905792 Oct 12 21:57 16545.tar
-rw-------. 1 postgres postgres 532888576 Oct 12 21:57 base.tar
-rw-------. 1 postgres postgres 243763 Oct 12 21:57 backup_manifest
-rw-------. 1 postgres postgres 16778752 Oct 12 21:57 pg_wal.tar
[postgres@lxicbpgdsgv01 ~]$
4.2. Insert new row on table test.emp
postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# INSERT INTO test.emp VALUES ('DEV', 'DBA MANAGER', '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
DEV | DBA MANAGER | RCM | iGATE <-- New row
(5 rows)
dell=# SELECT NOW();
now
-------------------------------
2025-10-12 22:04:29.793434+08
(1 row)
dell=#
dell=# SELECT pg_switch_wal();
pg_switch_wal
---------------
0/92000298
(1 row)
dell=# SELECT pg_switch_wal();
pg_switch_wal
---------------
0/93000000
(1 row)
dell=#
4.3. Dropped table test.emp accidentally
dell=# SELECT NOW();
now
-------------------------------
2025-10-12 22:05:29.954216+08 <----
(1 row)
dell=# DROP TABLE TEST.EMP; <-----
DROP TABLE
dell=# SELECT NOW();
now
-------------------------------
2025-10-12 22:05:42.890468+08
(1 row)
dell=#
dell=# SELECT pg_switch_wal();
pg_switch_wal
---------------
0/93007FF0
(1 row)
dell=# SELECT pg_switch_wal();
pg_switch_wal
---------------
0/94000000
(1 row)
dell=#
5. Transfer Base Backup and WAL Archives to Target
# Copy WAL Archive Fils
[postgres@lxicbpgdsgv01 ~]$ cp /pgArch/pgsql17/arch/* /pgBackup/pgsql17/backup/archive_bkp12OCT2025
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 backup]$ tar -cvf base_bkp12OCT2025.tar base_bkp12OCT2025
base_bkp12OCT2025/
base_bkp12OCT2025/16542.tar
base_bkp12OCT2025/pg_wal.tar
base_bkp12OCT2025/16545.tar
base_bkp12OCT2025/base.tar
base_bkp12OCT2025/backup_manifest
[postgres@lxicbpgdsgv01 backup]$
[postgres@lxicbpgdsgv01 backup]$ tar -cvf archive_bkp12OCT2025.tar archive_bkp12OCT2025
archive_bkp12OCT2025/
archive_bkp12OCT2025/00000001000000000000008E
archive_bkp12OCT2025/00000001000000000000008F
archive_bkp12OCT2025/000000010000000000000090
archive_bkp12OCT2025/000000010000000000000091
archive_bkp12OCT2025/000000010000000000000091.00000028.backup
archive_bkp12OCT2025/000000010000000000000092
archive_bkp12OCT2025/000000010000000000000093
[postgres@lxicbpgdsgv01 backup]$ pwd
/pgBackup/pgsql17/backup
[postgres@lxicbpgdsgv01 backup]$
[postgres@lxicbpgdsgv01 backup]$ ls -ltr
total 650832
drwxr-xr-x. 2 postgres postgres 97 Oct 12 21:57 base_bkp12OCT2025
-rw-r--r--. 1 postgres postgres 1286 Oct 12 21:57 base_bkp12OCT2025.log
drwxr-xr-x. 2 postgres postgres 4096 Oct 12 22:13 archive_bkp12OCT2025
-rw-r--r--. 1 postgres postgres 565770240 Oct 12 22:36 base_bkp12OCT2025.tar <---
-rw-r--r--. 1 postgres postgres 100669440 Oct 12 22:36 archive_bkp12OCT2025.tar <---
[postgres@lxicbpgdsgv01 backup]$
[postgres@lxicbpgdsgv01 backup]$ scp *.tar 192.168.2.52:/pgbackup/pgsql17/backup
postgres@192.168.2.52's password:
archive_bkp12OCT2025.tar 100% 96MB 61.0MB/s 00:01
base_bkp12OCT2025.tar 100% 540MB 67.0MB/s 00:08
[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
134356985 0 lrwxrwxrwx 1 postgres postgres 18 Oct 12 01:50 /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 pmon
root 3529 3228 0 23:50 pts/0 00:00:00 grep --color=auto pmon
[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 650824
-rw-r--r--. 1 postgres postgres 100669440 Oct 12 22:38 archive_bkp12OCT2025.tar
-rw-r--r--. 1 postgres postgres 565770240 Oct 12 22:38 base_bkp12OCT2025.tar
[postgres@lxicbpgdsgv02 backup]$
[postgres@lxicbpgdsgv02 backup]$ tar -xvf base_bkp12OCT2025.tar
base_bkp12OCT2025/
base_bkp12OCT2025/16542.tar
base_bkp12OCT2025/pg_wal.tar
base_bkp12OCT2025/16545.tar
base_bkp12OCT2025/base.tar
base_bkp12OCT2025/backup_manifest
[postgres@lxicbpgdsgv02 backup]$
[postgres@lxicbpgdsgv02 backup]$ ls -ltr
total 650824
drwxr-xr-x. 2 postgres postgres 97 Oct 12 21:57 base_bkp12OCT2025
-rw-r--r--. 1 postgres postgres 100669440 Oct 12 22:38 archive_bkp12OCT2025.tar
-rw-r--r--. 1 postgres postgres 565770240 Oct 12 22:38 base_bkp12OCT2025.tar
[postgres@lxicbpgdsgv02 backup]$ cd base_bkp12OCT2025/
[postgres@lxicbpgdsgv02 base_bkp12OCT2025]$ ls -ltr
total 552508
-rw-------. 1 postgres postgres 7905792 Oct 12 21:57 16545.tar
-rw-------. 1 postgres postgres 7940096 Oct 12 21:57 16542.tar
-rw-------. 1 postgres postgres 16778752 Oct 12 21:57 pg_wal.tar
-rw-------. 1 postgres postgres 532888576 Oct 12 21:57 base.tar
-rw-------. 1 postgres postgres 243763 Oct 12 21:57 backup_manifest
[postgres@lxicbpgdsgv02 base_bkp12OCT2025]$
7.2. Restore Base Data Directory
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/
total 0
[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/base_bkp12OCT2025/base.tar -C /pgdata/pgsql17/data > /pgbackup/pgsql17/backup/base_restore.log 2>&1 &
[1] 3607
[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------. 4 postgres postgres 68 Oct 12 21:57 pg_logical
-rw-------. 1 postgres postgres 68 Oct 12 21:57 tablespace_map
drwx------. 2 postgres postgres 6 Oct 12 21:57 pg_replslot
-rw-------. 1 postgres postgres 227 Oct 12 21:57 backup_label
drwx------. 2 postgres postgres 6 Oct 12 23:17 pg_stat
drwx------. 4 postgres postgres 45 Oct 12 23:53 pg_wal
drwx------. 2 postgres postgres 4096 Oct 12 23:53 global
drwx------. 2 postgres postgres 188 Oct 13 2025 log
-rw-------. 1 postgres postgres 30 Oct 13 2025 current_logfiles
[postgres@lxicbpgdsgv02 ~]$
7.3. Restore WAL Directory
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal/
total 0
[postgres@lxicbpgdsgv02 ~]$ nohup tar -xvf /pgbackup/pgsql17/backup/base_bkp12OCT2025/pg_wal.tar -C /pgwal/pgsql17/wal > /pgbackup/pgsql17/backup/wal_restore.log 2>&1 &
[1] 3610
[postgres@lxicbpgdsgv02 ~]$
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgwal/pgsql17/wal/
total 16384
-rw-------. 1 postgres postgres 16777216 Oct 12 21:57 000000010000000000000091
[postgres@lxicbpgdsgv02 ~]$
7.4. Remove and Symlink pg_wal
[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 23:57 /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_bkp12OCT2025/16542.tar -C /pgtsdata01/pgsql17/tbs/delltbs01 > /pgbackup/pgsql17/backup/16542_restore.log 2>&1 &
[1] 3652
[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_bkp12OCT2025/16545.tar -C /pgtsdata01/pgsql17/tbs/orcltbs01 > /pgbackup/pgsql17/backup/16545_restore.log 2>&1 &
[1] 3655
[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
# Please use this location for restore_command in postgresql.conf file
[postgres@lxicbpgdsgv02 ~]$ cd /pgbackup/pgsql17/backup/
[postgres@lxicbpgdsgv02 backup]$ tar -xvf archive_bkp12OCT2025.tar
archive_bkp12OCT2025/
archive_bkp12OCT2025/00000001000000000000008E
archive_bkp12OCT2025/00000001000000000000008F
archive_bkp12OCT2025/000000010000000000000090
archive_bkp12OCT2025/000000010000000000000091
archive_bkp12OCT2025/000000010000000000000091.00000028.backup
archive_bkp12OCT2025/000000010000000000000092
archive_bkp12OCT2025/000000010000000000000093
[postgres@lxicbpgdsgv02 backup]$
[postgres@lxicbpgdsgv02 backup]$ cd archive_bkp12OCT2025/
[postgres@lxicbpgdsgv02 archive_bkp12OCT2025]$ pwd
/pgbackup/pgsql17/backup/archive_bkp12OCT2025
[postgres@lxicbpgdsgv02 archive_bkp12OCT2025]$ ls -ltr
total 98308
-rw-------. 1 postgres postgres 16777216 Oct 12 22:13 000000010000000000000093
-rw-------. 1 postgres postgres 16777216 Oct 12 22:13 000000010000000000000092
-rw-------. 1 postgres postgres 341 Oct 12 22:13 000000010000000000000091.00000028.backup
-rw-------. 1 postgres postgres 16777216 Oct 12 22:13 000000010000000000000091
-rw-------. 1 postgres postgres 16777216 Oct 12 22:13 000000010000000000000090
-rw-------. 1 postgres postgres 16777216 Oct 12 22:13 00000001000000000000008F
-rw-------. 1 postgres postgres 16777216 Oct 12 22:13 00000001000000000000008E
[postgres@lxicbpgdsgv02 archive_bkp12OCT2025]$
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 13 00:09 /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 (Mandatory)
# • recovery_target_time (Mandatory)
# • archive_mode (Optional)
# • archive_command (Optional)
# eg: echo "recovery_target_time = '2025-10-12 22:05:00'" >> /pgdata/pgsql17/data/postgresql.conf
[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_bkp12OCT2025/%f %p'
# Update recovery_target_time = '2025-10-12 22:05:00'
[postgres@lxicbpgdsgv02 ~]$ cat /pgdata/pgsql17/data/postgresql.conf | egrep -i 'archive_|command|recover'
archive_mode = on
archive_command='cp %p /pgarchive/pgsql17/archive/%f'
restore_command = 'cp /pgbackup/pgsql17/backup/archive_bkp12OCT2025/%f %p'
recovery_target_time = '2025-10-12 22:05:00'
[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 Mon 2025-10-13 00:28:04 +08; 10min ago
Docs: https://www.postgresql.org/docs/17/static/
Process: 3859 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 3878 (postgres)
Tasks: 6 (limit: 20496)
Memory: 52.4M
CPU: 221ms
CGroup: /system.slice/postgresql-17.service
├─3878 /usr/pgsql-17/bin/postgres -D /pgdata/pgsql17/data
├─3885 "postgres: logger "
├─3886 "postgres: checkpointer "
├─3887 "postgres: background writer "
├─3888 "postgres: startup waiting for 000000010000000000000094" <---- NO Need to copy this archive log file.
└─3901 "postgres: postgres dell [local] idle"
Oct 13 00:28:03 lxicbpgdsgv02.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 13 00:28:03 lxicbpgdsgv02.rajasekhar.com postgres[3878]: 2025-10-13 00:28:03.490 +08 [3878] LOG: redirecting log output to logging collector process
Oct 13 00:28:03 lxicbpgdsgv02.rajasekhar.com postgres[3878]: 2025-10-13 00:28:03.490 +08 [3878] HINT: Future log output will appear in directory "log".
Oct 13 00:28:04 lxicbpgdsgv02.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv02 ~]#
10. Final Validation
10.1. Review PostgreSQL Logs
2025-10-13 00:28:03.490 +08 [3878] 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-13 00:28:03.494 +08 [3878] LOG: listening on IPv6 address "::1", port 5432
2025-10-13 00:28:03.494 +08 [3878] LOG: listening on IPv4 address "127.0.0.1", port 5432
2025-10-13 00:28:03.498 +08 [3878] LOG: listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2025-10-13 00:28:03.504 +08 [3878] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-10-13 00:28:03.510 +08 [3888] LOG: database system was interrupted; last known up at 2025-10-12 21:57:50 +08
2025-10-13 00:28:03.512 +08 [3888] LOG: creating missing WAL directory "pg_wal/archive_status"
2025-10-13 00:28:03.512 +08 [3888] LOG: creating missing WAL directory "pg_wal/summaries"
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp12OCT2025/00000002.history': No such file or directory
2025-10-13 00:28:04.414 +08 [3888] LOG: starting backup recovery with redo LSN 0/91000028, checkpoint LSN 0/91000080, on timeline ID 1
2025-10-13 00:28:04.434 +08 [3888] LOG: restored log file "000000010000000000000091" from archive
2025-10-13 00:28:04.486 +08 [3888] LOG: starting point-in-time recovery to 2025-10-12 22:05:00+08
2025-10-13 00:28:04.497 +08 [3888] LOG: redo starts at 0/91000028
2025-10-13 00:28:04.518 +08 [3888] LOG: restored log file "000000010000000000000092" from archive
2025-10-13 00:28:04.591 +08 [3888] LOG: restored log file "000000010000000000000093" from archive
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp12OCT2025/000000010000000000000094': No such file or directory
2025-10-13 00:28:04.651 +08 [3888] LOG: completed backup recovery with redo LSN 0/91000028 and end LSN 0/91000120
2025-10-13 00:28:04.651 +08 [3888] LOG: consistent recovery state reached at 0/91000120
2025-10-13 00:28:04.652 +08 [3888] LOG: recovery stopping before commit of transaction 897, time 2025-10-12 22:05:35.873371+08
2025-10-13 00:28:04.652 +08 [3888] LOG: pausing at the end of recovery
2025-10-13 00:28:04.652 +08 [3888] HINT: Execute pg_wal_replay_resume() to promote.
2025-10-13 00:28:04.652 +08 [3878] LOG: database system is ready to accept read-only connections
10.2. Execute pg_wal_replay_resume()
[postgres@lxicbpgdsgv02 ~]$ psql
psql (17.6)
Type "help" for help.
postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
postgres=#
10.3. Review PostgreSQL Logs again
...
2025-10-13 00:30:22.955 +08 [3982] STATEMENT: pg_wal_replay_resume()
pg_wal_replay_resume();
2025-10-13 00:32:11.748 +08 [3973] LOG: redo done at 0/93007B70 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 174.26 s
2025-10-13 00:32:11.768 +08 [3973] LOG: restored log file "000000010000000000000093" from archive
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp12OCT2025/00000002.history': No such file or directory
2025-10-13 00:32:11.827 +08 [3973] LOG: selected new timeline ID: 2
cp: cannot stat '/pgbackup/pgsql17/backup/archive_bkp12OCT2025/00000001.history': No such file or directory
2025-10-13 00:32:11.957 +08 [3973] LOG: archive recovery complete
2025-10-13 00:32:11.960 +08 [3971] LOG: checkpoint starting: end-of-recovery immediate wait
2025-10-13 00:32:11.987 +08 [3971] LOG: checkpoint complete: wrote 8 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.002 s, sync=0.012 s, total=0.029 s; sync files=7, longest=0.004 s, average=0.002 s; distance=16414 kB, estimate=16414 kB; lsn=0/93007B70, redo lsn=0/93007B70
2025-10-13 00:32:12.006 +08 [3969] LOG: database system is ready to accept connections
[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. 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 ~]$
10.6. 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.7. 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=#
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
DEV | DBA MANAGER | RCM | iGATE
(5 rows)
dell=#
10.8. Validate Symbolic Links for Tablespaces
[postgres@lxicbpgdsgv02 ~]$ ls -ltr /pgdata/pgsql17/data/pg_tblspc/
total 0
lrwxrwxrwx. 1 postgres postgres 33 Oct 13 00:28 16545 -> /pgtsdata01/pgsql17/tbs/orcltbs01
lrwxrwxrwx. 1 postgres postgres 33 Oct 13 00:28 16542 -> /pgtsdata01/pgsql17/tbs/delltbs01
[postgres@lxicbpgdsgv02 ~]$
11. Take Backup of dropped table test.emp
[postgres@lxicbpgdsgv02 ~]$ pg_dump -U postgres -d dell -t test.emp -Fc -v -f /pgbackup/pgsql17/backup/table_dell_test-emp.dmp
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading subscription membership of tables
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
pg_dump: dumping contents of table "test.emp"
[postgres@lxicbpgdsgv02 ~]$
12. Transfer backup to source server
[postgres@lxicbpgdsgv02 ~]$ scp /pgbackup/pgsql17/backup/table_dell_test-emp.dmp 192.168.2.51:/pgBackup/pgsql17/backup
postgres@192.168.2.51's password:
table_dell_test-emp.dmp 100% 1396 836.2KB/s 00:00
[postgres@lxicbpgdsgv02 ~]$
13. Restore dropped table and Validation
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgBackup/pgsql17/backup/table_dell_test-emp.dmp
-rw-r--r--. 1 postgres postgres 1396 Oct 13 01:07 /pgBackup/pgsql17/backup/table_dell_test-emp.dmp
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.
postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=#
dell=# SELECT * FROM TEST.EMP;
ERROR: relation "test.emp" does not exist
LINE 1: SELECT * FROM TEST.EMP;
^
dell=#
[postgres@lxicbpgdsgv01 ~]$ pg_restore -l /pgBackup/pgsql17/backup/table_dell_test-emp.dmp
;
; Archive created at 2025-10-13 01:05:27 +08
; dbname: dell
; TOC Entries: 6
; Compression: gzip
; Dump Version: 1.16-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 17.6
; Dumped by pg_dump version: 17.6
;
;
; Selected TOC Entries:
;
218; 1259 16553 TABLE test emp postgres
4334; 0 16553 TABLE DATA test emp postgres
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ pg_restore -U postgres -d dell -v /pgBackup/pgsql17/backup/table_dell_test-emp.dmp
pg_restore: connecting to database for restore
pg_restore: creating TABLE "test.emp"
pg_restore: processing data for table "test.emp"
[postgres@lxicbpgdsgv01 ~]$
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
DEV | DBA MANAGER | RCM | iGATE
(5 rows)
dell=#
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/