Point-in-Time Recovery (PITR) Backups in Backup Ninja

Point-in-Time Recovery (PITR) Backups in Backup Ninja

Profile picture for user LukasVileikis
Lukas Vileikis
27 May 2021

If you are a frequent reader of this blog, you probably already know the kinds of backups available in Backup Ninja. One of Backup Ninja's features include the ability to provide Point-in-Time Recovery (PITR) backups.

What is Point in Time Recovery?

If you are a system or a database administrator, you probably already know what point-in-time recovery is. However, if you do not, point-in-time recovery, or PITR for short, refers to the recovery of data changes up to a given point in time. This kind of recovery is also called incremental. Some of the key advantages regarding point in time recovery include the fact that it allows you to restore a system (a database in this case) to a specified date and time. Once your data has been recovered from a specified point in time, you will find it in the state it was at the specific date and time that was set. Point in Time Recovery (PITR) is usually pretty common amongst database administrators including SQL Server and MySQL DBAs. It’s common because it generally provides safety and assurance against a full backup.

Point-in-Time Recovery in Backup Ninja

Backup Ninja, as already mentioned, supports a wide range of technologies including MySQL, PostgreSQL, MariaDB, MongoDB, Percona and TimescaleDB. Point-in-Time Recovery in Backup Ninja is available in PostgreSQL or MySQL, also flavors of MySQL including MariaDB and Percona Server.

If you find yourself using Backup Ninja’s Point in Time Recovery capabilities with PostgreSQL, keep in mind that you should probably use pg_basebackup. If you do so, set variables as follows:

  • Set the archive_mode variable to “on”
  • Set the archive_command to “copy” without any modification of the WAL files
  • Set the wal_level to at least “replica
  • Increase the max_val_senders value to above zero.

If you find yourself using these Backup Ninja’s capabilities with MySQL, MariaDB or Percona Server, make sure you do the following when using mysqldump:

  • Enable binary logging for MySQL.
  • Specify a server-id.
  • Set log-bin to the binary logs location.
  • Restart your MySQL, MariaDB or Percona Server.

PITR backups can be taken once they are scheduled:

PITR

Finally, once you have the backup setup and you have configured all of the settings relevant to it, you can proceed to schedule the backup as a last step of the process:

Scheduling Backups

Summary

Point in time recovery backups can be a really useful tool in your shed - that is true regardless of the database management system you use. If you elect to use Backup Ninja as your companion during your PITR journey, keep in mind that it supports a wide variety of technologies, but the concrete steps you need to take does depend on the technology you elect to use.

Tags