{"id":6178,"date":"2026-08-24T07:00:00","date_gmt":"2026-08-24T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6178"},"modified":"2026-08-28T13:36:07","modified_gmt":"2026-08-28T10:36:07","slug":"pos-kanoyme-point-in-time-restore-vasi-dedomenon-postgresql-chori-2","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-point-in-time-restore-vasi-dedomenon-postgresql-chori-2\/","title":{"rendered":"How to Point in Time Restore a PostgreSQL database without using 3rd party tools"},"content":{"rendered":"

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)<\/strong>.<\/p>\n\n\n\n

<\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

First we need to find where the postgres files are located:<\/p>\n\n\n\n

sudo -u postgres psql -c \"SHOW data_directory;\"<\/pre>\n\n\n\n
Then we stop the database:<\/p>\n\n\n\n
sudo systemctl stop postgresql-11<\/pre>\n\n\n\n
For example, we will delete the existing database with the following command:<\/p>\n\n\n\n
sudo rm -rf \/var\/lib\/pgsql\/11\/data\/*<\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
sudo tar -xzf \/var\/lib\/postgresql\/backup\/full\/full_20260721_094219\/base.tar.gz -C \/var\/lib\/pgsql\/11\/data\/<\/pre>\n\n\n\n
Then, in order to be able to access the database at a specific point in time, we need to create the file recovery.signal<\/strong>, 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:<\/p>\n\n\n\n
sudo vi \/var\/lib\/pgsql\/11\/data\/recovery.signal<\/pre>\n\n\n\n
In this we put the corresponding parameters:<\/p>\n\n\n\n
restore_command = 'cp \/var\/lib\/postgresql\/archived_wal\/%f \"%p\"'\nrecovery_target_time = '2026-07-21 10:00:00'\nrecovery_target_action = 'promote'<\/code><\/pre>\n\n\n\n
Finally we start the service again:<\/p>\n\n\n\n
sudo systemctl start postgresql-11<\/pre>\n\n\n\n
To see the latest log to ensure that we have no errors with the postgres user, we do tail with this parameter:<\/p>\n\n\n\n
su postgres\n\ntail -f $(ls -t \/var\/lib\/pgsql\/11\/data\/log\/postgresql-*.log | head -n 1)<\/pre>\n\n\n\n
Either with super user from the system journal:<\/p>\n\n\n\n
sudo journalctl -u postgresql-11.service -e --no-pager<\/pre>\n\n\n\n
If we want to see if the restore has completed, we connect to the terminal:<\/p>\n\n\n\n
sudo -u postgres psql<\/pre>\n\n\n\n
We run the following select:<\/p>\n\n\n\n
SELECT pg_is_in_recovery();<\/pre>\n\n\n\n
If it appears false <\/strong>means that the restore from the WALs has been completed.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n