Table of Contents
PostgreSQL PITR relies primarily over its write ahead log (WAL) files, or what we call as WAL files. PostgreSQL maintains the WAL files under pg_wal/ subdirectory of the cluster’s data directory. The WAL files plays a very important role during Point-in-time recovery as it continuously records every change made to the database’s data files. Its purpose is crash-safety for which a database can be restored to achieve consistency by replying the log entries made since the last checkpoint, such as a full backup copy, or can also be used as a warm standby which continuously feed the series of WAL files to another machine which has previously loaded with the full backup copy (or base backup file).
In Backup Ninja, PITR for PostgreSQL and TimescaleDB works exactly the same and any PITR backups shall be applicable for both. PITR backups for Backup Ninja does not apply to all available backup methods we support for PostgreSQL and TimescaleDB. pg_dump and pg_dumpall do not produce file-system-level backups and cannot be used as part of a continuous-archiving solution. Such dumps are logical and do not contain enough information to be used by WAL replay. In that sense, Backup Ninja only allows PITR for <strong>pg_basebackup</strong> as the backup method.
Prerequisites before taking a PITR backup
But before you can utilize the PITR backups using Backup Ninja, specific configuration is required. See below:
<strong>pg_basebackup</strong>- Allows you to take a full backup and enable/disable PITR
- requires the following variables has to be set as follows:
<b>archive_mode</b>must be set to “on”<b>archive_command</b>should be just copy, no gzip or other modification of wal files eg. “test ! -f /tmp/wal/%f && cp %p /tmp/wal/%f”<b>wal_level</b>must be at least “replica”<b>max_wal_senders</b>must be > 0
Creating your PITR backup
In creating your PITR backup, it is expected that you have already created or added your Server. Let’s go over the steps on how to do this.
1.) Schedule a Backup
From the Backup Ninja dashboard, go to Schedules → Schedule Backup and follow the configuration wizard. You can check our How-To page on how to schedule a backup for PostgreSQL/TimescaleDB.
2.) Choose pg_basebackup as your backup method
At this point, you are now in the Details tab under the configuration wizard. Make sure that you have selected correctly your target PostgreSQL/TimescaleDB server that you have already created or added. Afterwards, simply choose the pg_basebackup as the backup method just like as what is shown below:
When enabling PITR, you are required to verify the rules that you need to set in your postgresql.conf as specified in our prerequisites section.
3.) Enable PITR
Simply choose Yes to enable the PITR backups.
3.) Specify the location of your WAL archive files
Specify the location where your WAL Archive files are located. Generally, your WAL files are located in pg_wals subdirectory of the cluster’s data directory. If your pg_wals subdirectory is a symbolic link for which your WAL files are in fact located to a different drive, for example, then you can choose its original physical location. If you have archives located on another directory, let say you have archived your WAL files to a separate location, then you can use that one as well. You just have to make sure that you are specifying the full path of your WAL Archive files.
4.) Clear WAL Archive location after each backup to save space.
Only check this box if you are sure to clear the location of your WAL Archives. Make sure that you know what you are doing. Ideally, using Backup Ninja as what we require, the target node has to be a replica. In that case, the Backup Ninja assure that the cluster’s health shall be in a safe situation. In case your WAL archives are cleared, those WAL files shall not be used by another replica or used by another server feed for continuous archiving. However, the WAL Archives are in fact used for other purposes such as feed to another server for continuous archiving, for example, then we recommend to leave it or uncheck this checkbox for safety. Although you have to manage clearing the unused WAL files or tune your PostgreSQL/TimescaleDB server to get rid of unused WAL Archives. To enable it, just check the checkbox as shown below:
Now that you are good to go, just hit the [keybtn]Continue[/keybtn] and proceed on the next tab Storages.
5. Choose the storage locations for WAL Archive backups.
Under the Storage tab, upon adding a storage location, you have the option to use same storage locations for WAL Archive backups. In that case, you just have to check the checkbox,
and that means all your WAL Archive backups shall be stored into the same location as where you specified your full backup copy taken by pg_basebackup.
Otherwise, if you left it uncheck, you are prompted to specify (required) the desired locations for your WAL Archive backups
Determining and viewing your PITR enabled backups
To determine your PITR enabled backups, go over to the Schedule List of your backups. Now choose the name of your scheduled backup for which the PITR is enabled. See the example scheduled backup below,
PITR label so it’s easy to identify which one of your backup has PITR enabled.
You get the full list of your backups and these are paginated. If you have lots of backup listed, you can navigate in accordance to its page selected.