ClusterControl – Advanced Backup Management – PostgreSQL

Information is one of the most valuable assets in a company, so you will need a good Disaster Recovery Plan (DRP) to prevent data loss in the event of an accident or hardware failure. Backups are a basic step in all DR plans, but the management and monitoring of them could be a difficult task if you have a complex environment.

ClusterControl has many Advanced Backup Management features (among others important features like Auto Failover, Monitoring, etc.), that allow you not only to take different types of backups, in different ways, but also compress, encrypt, verify, and even more.

In this blog, we will see how you can use ClusterControl to manage your backups in an advanced way for your PostgreSQL database cluster.

Backup Types

First, let’s mention what types of backups you can use to keep your data safe.

• 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 as a way to decrease the amount of time and disk space usage that 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.

By using ClusterControl, you can take all these types of backups for your PostgreSQL database or even combine them to improve your Backup Strategy.

ClusterControl Backup Management Features

Now, let’s see how ClusterControl can help you to manage all the different types of backups from the same user-friendly UI and system.

We will assume you have your ClusterControl server installed and it is managing your PostgreSQL cluster. Otherwise, you can follow our Official Documentation to install ClusterControl and deploy or import your PostgreSQL cluster using it.

Creating a Backup

For this, go to ClusterControl -> Select your PostgreSQL Cluster -> Backup -> Create Backup.

You can create a new backup or configure a scheduled one. For our 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 PostgreSQL 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 of the entire database cluster of a running PostgreSQL database cluster. These are taken without affecting other clients to 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 algorithms that are optimized 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) in the same backup job by enabling the corresponding option.

Then, you can specify compression, encryption, and the retention period of your backups.

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

Restoring a Backup

Once the backup is finished, you can restore it by using ClusterControl. For this, in your backup section (ClusterControl -> Select PostgreSQL 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 it in an existing database node, restore and verify the backup on a standalone host, or create a new cluster from the backup.

If you are trying to restore a PITR compatible backup, you also need to 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 that your PostgreSQL service will be restarted in the destination node.

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

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 PostgreSQL backups and avoid surprises in case you need to restore it.

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 common options like method or storage, you also need to 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.

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. You can keep this node running for testing or reporting, or shut down the node until the next verification job.

After restoring, you can see the verification icon in the ClusterControl Backup section.

Conclusion

Backups are mandatory in any environment as they help you to protect your data. To manage them, it is important to have a good tool with advanced backup features, to make it as simple as possible.

ClusterControl has many features to help you in this task, like backup scheduling, monitoring, backup verification, and even more. It also supports different backup methods and you can combine them to have a good DRP in place.