PostgreSQL Point in Time Recovery: How Does It Work?

Point-in-time recovery is something that comes up as soon as any types of backups are mentioned. In this blog post we are going to tell you how point-in-time recovery works with PostgreSQL.

What is Point-in-Time Recovery in PostgreSQL?

In general, point-in-time recovery (or PITR for short) refers to the recovery of data when an administrator recovers a set of data or a particular setting from a time in the past. PostgreSQL in particular comes with the ability to do incremental backups and point-in-time recovery out of the box. PostgreSQL has these files called “WAL” files – WAL stands for “write ahead log” and it’s the cornerstone of PostgreSQL’s ability to do point-in-time recovery. To perform point-in-time recovery in PostgreSQL, start from the restoration of a full backup, then progressively fetch and apply the archived WAL files up to a specified timestamp. To do that, follow these steps:

1. Setup WAL archiving: create the directory in which the WAL files will be stored, then make sure it’s owned by the ‘postgres’ user.
2. Make sure to enable the archive mode in the PostgreSQL configuration (archive_mode=on), set the wal_level to archive and set the archive_command to such a command that enables to copy the files into their destination. It can be a one-liner or a call to a complex script or a binary.
3. Restart PostgreSQL.
4. Take a base backup:
5. pg_basebackup -Uusername -h127.0.0.1 --progress -D /basebackup/
6. Figure out a point at which you want to restore your data and stop the PostgreSQL server.
7. Remove all of the data directory, restore the base backup and then remove any existing WAL files:
8. rm -rf /var/lib/postgresql/10/main/*
9. cp -r /basebackup/* /var/lib/postgresql/10/main/
10. rm -rf /var/lib/postgresql/10/main/pg_wal/*
11. Prepare the recovery.conf file:
12. vim /var/lib/postgresql/10/main/recovery.conf
13. restore_command = ‘cp /wal_archive/%f “%p”’
14. recovery_target_lsn = ‘3/72658818’
15. In this example, a particular LSN used was a recovery target (i.e the recovery is stopping at that point)
16. Finally, make sure that all of the files in the PostgreSQL data directory have a correct owner and start PostgreSQL.

Once you do that, the server will be in recovery mode. Switch it to operate normally (run SELECT pg_wal_replay_resume()) and tell recovery.conf how PostgreSQL should behave after the recovery using recovery_target_action. You have three options, but since promote is the one that will end the recovery mode as soon as the recovery itself completes, we will suggest you use that. You can also shutdown the server after the recovery. After the recovery completes, your recovery.conf file will be renamed to recovery.done.

Performing Point-in-Time Recovery with Backup Ninja

Point-in-time recovery for PostgreSQL can also be done with Backup Ninja. First, schedule your PITR backups:

Once you have enabled PITR and configured the rest of your settings, you can also schedule the backup as a last step of the process:

Backup Ninja also allows you to see all of the backups that have been completed already:

If you want to restore any of the backups that you see, simply click the “Restore” button:

Summary

Point-in-time recovery is something that concerns database administrators that deal with all kinds of databases, be it MySQL or PostgreSQL. If you are using PostgreSQL and want to use point-in-time recovery to help your application or database, Backup Ninja can prove to be a very good choice.