Backup Management Tips for TimescaleDB

Information is one of the most valuable assets in a company, and it goes without saying that you should have a Disaster Recovery Plan (DRP) to prevent data loss in the event of an accident or hardware failure. A backup is the simplest form of DR. It might not always be enough to guarantee an acceptable Recovery Point Objective (RPO), but it is a good first approach.

Whether a 24×7 highly loaded server or a low-transaction-volume environment, you will need to make backups a seamless procedure without disrupting the server’s performance in a production environment.

If we talk about TimescaleDB, there are different types of backup for this engine for time-series data. The type of backup that you should use depends on many factors, like the environment, infrastructure, load, etc.

In this blog, we’ll see these different types of backups available and how ClusterControl can help you centralize your backup management for TimescaleDB.

Backup Types

There are different types of backups for databases. Let’s look at each in detail:

• Logical: The backup is stored in a human-readable format like SQL.
• Physical: The backup contains binary data.
• Full/Incremental/Differential: The definition of these three types of backups is implicit in the name. The full backup is a full copy of all your data. Incremental backup only backs up the data that has changed since the previous backup, and the differential backup only contains the data that has changed since the last full backup executed. The incremental and differential backups were introduced to decrease the amount of time and disk space it takes to perform a full backup.
• Point In Time Recovery compatible: PITR involves restoring the database at any given moment in the past. To be able to do this, you will need to restore a full backup and then apply all the changes that happened after the backup until right before the failure.

ClusterControl Backup Management Feature

Let’s see how ClusterControl can help you to manage different types of backups.

Creating a Backup

For this task, go to ClusterControl -> Select TimescaleDB Cluster -> Backup -> Create Backup.

You can create a new backup or configure a scheduled one. For this example, we will create a single backup instantly.

Here you have one method for each type of backup that we mentioned earlier. 

Backup Type	Tool	Definition
Logical	pg_dumpall	It is a utility for writing out all TimescaleDB databases of a cluster into one script file. The script file contains SQL commands that can be used to restore the databases.
Physical	pg_basebackup	It is used to make a binary copy of the database cluster files while making sure the system is put in and out of backup mode automatically. Backups are always taken from a running TimescaleDB database cluster’s entire database cluster. These are taken without affecting other clients in the database.
Full/Incr/Diff	pgbackrest	It is a simple, reliable backup, and restore solution that can seamlessly scale up to the largest databases and workloads by utilizing optimized algorithms for database-specific requirements. One of the most important features is the support for Full, Incremental, and Differential Backups.
PITR	pg_basebackup + WALs	To create a PITR compatible backup, ClusterControl will use pg_basebackup and the WAL files, to be able to restore the database at any given moment in the past.

You must choose one method, the server from which the backup will be taken, and where you want to store the backup. You can also upload your backup to the cloud (AWS, Google Cloud, or Azure) by enabling the corresponding button.

Keep in mind that if you want to create a PITR compatible use pg_basebackup in this step and take the backup from the primary node.

Then, you can specify the usage of compression, encryption, and the retention of this backup.

In the backup section, you can see the progress of the backup and information like the method, size, location, and more.

Enabling Point In Time Recovery

If you want to use the PITR feature, you need to have the WAL Archiving enabled. For this, you can go to ClusterControl -> Select TimescaleDB Cluster -> Node actions -> Enable WAL Archiving, or just go to ClusterControl -> Select TimescaleDB Cluster -> Backup -> Settings -> Backup Settings and enable the option “Enable Point-In-Time Recovery (WAL Archiving)” as you will see in the next image.

Keep in mind that to enable WAL Archiving, a database restart is required. ClusterControl can do this for you too.

In addition to the options common to all backups like the “Backup Directory” and the “Backup Retention Period”, here you can also specify the WAL Retention Period. By default is 0, which means forever.

To confirm that you have WAL Archiving enabled, you can select your Master node in ClusterControl -> Select TimescaleDB Cluster -> Nodes, and you should see the WAL Archiving Enabled message, as you can see in the following image:

Restoring a Backup

Once the backup is finished, you can restore it by using ClusterControl. For this, in your backup section (ClusterControl -> Select TimescaleDB Cluster -> Backup), you can select “Restore Backup” or directly “Restore” on the backup that you want to restore.

You have three options to restore the backup. You can restore the backup in an existing database node, restore and verify the backup on a standalone host, or create a new cluster from this backup.

If you are trying to restore a PITR compatible backup, you must specify the time.

The data will be restored as it was at the time specified. Take into account that the UTC timezone is used, and your TimescaleDB service in the primary node will be restarted.

You can monitor the progress of your restore job from the Activity section in your ClusterControl UI.

Automatic Backup Verification

A backup is not a backup if it is not restorable. Verifying backups is something that is usually neglected by many. Let’s see how ClusterControl can automate the verification of TimescaleDB backups and help avoid any surprises.

In ClusterControl, select your cluster and go to the “Backup” section, then, select “Create Backup”.

The automatic verify backup feature is available for the scheduled backups. So, let’s choose the “Schedule Backup” option.

When scheduling a backup, in addition to selecting the standard options like method or storage, you must specify schedule/frequency.

In the next step, you can compress and encrypt your backup and specify the retention period. Here, you also have the “Verify Backup” feature available.

To use this feature, you need a dedicated host (or VM) that is not part of the cluster.

ClusterControl will install the software and restore the backup in this host to confirm that everything goes fine. After restoring, you will see the verification icon in the ClusterControl Backup section.

Conclusion

Backups are mandatory in any environment. They help you protect your data, and the usage of incremental backups helps reduce the amount of time and storage space used for the backup process. Transaction logs are also crucial for Point-in-Time-Recovery. 

ClusterControl can help you automate the backup process for your TimescaleDB databases in case of failure with a few clicks. Also, you can minimize the RPO by using the PITR compatible backup and improve your Disaster Recovery Plan. Read more about checking, restoring, or verifying your backups using ClusterControl.

To keep up to date with all ClusterControl news, follow us on Twitter and LinkedIn and subscribe to our newsletter.