How to Point in Time Restore a PostgreSQL database without using 3rd party tools

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How to Point in Time Restore a PostgreSQL database without using 3rd party tools - 24 August 2026
• How to Backup a PostgreSQL Database Without Using 3rd Party Tools - 18 August 2026
• How memory works in PostgreSQL and what parameters do we configure? - 11 August 2026

In a previous article we had seen how to take full and log (WAL) backup without using 3rd party tools. In this article we will now see how we can restore the database with Point in Time Restore (PITR).

The footsteps

First we need to find where the postgres files are located:

sudo -u postgres psql -c "SHOW data_directory;"

Then we stop the database:

sudo systemctl stop postgresql-11

For example, we will delete the existing database with the following command:

sudo rm -rf /var/lib/pgsql/11/data/*

To restore the full backup that we had taken by compressing it into tar, we must run the following command, entering the path where the backup is located and the path we want to restore, respectively:

sudo tar -xzf /var/lib/postgresql/backup/full/full_20260721_094219/base.tar.gz -C /var/lib/pgsql/11/data/

Then, in order to be able to access the database at a specific point in time, we need to create the file recovery.signal, in this file we must define the path where the archive logs (WALs) are located and the target time, that is, the moment we want to reach:

sudo vi /var/lib/pgsql/11/data/recovery.signal

In this we put the corresponding parameters:

restore_command = 'cp /var/lib/postgresql/archived_wal/%f "%p"'
recovery_target_time = '2026-07-21 10:00:00'
recovery_target_action = 'promote'

Finally we start the service again:

sudo systemctl start postgresql-11

To see the latest log to ensure that we have no errors with the postgres user, we do tail with this parameter:

su postgres

tail -f $(ls -t /var/lib/pgsql/11/data/log/postgresql-*.log | head -n 1)

Either with super user from the system journal:

sudo journalctl -u postgresql-11.service -e --no-pager

If we want to see if the restore has completed, we connect to the terminal:

sudo -u postgres psql

We run the following select:

SELECT pg_is_in_recovery();

If it appears false means that the restore from the WALs has been completed.

Sources:

• PostgreSQL 18.4 Documentation