Backup management is the backbone of any reliable database administration strategy, and while pgBackRest simplifies this process, its default configuration often combines backup and expiry together. At times, due to slow network bandwidth, huge database sizes, or any other resource crunch, expiring backups (i.e., deleting the older backups) require more than taking backups for databases. We can optimize the backup management process by separating backups and expiry operations to address this. Here, we’ll dig into how to break free from the conventional approach by decoupling backup and expiry operations in pgBackRest.

Why separate backups and expiry?

Separating backups and expiry provides several benefits:

  • Faster backups: Backups can be performed more frequently without being slowed down by the potentially time-consuming expiration process.
  • Improved flexibility: One can customize the scheduling of backups and expiry independently to meet specific requirements.
  • Reduced backup window: By running backups more frequently and expiring backups less frequently, the impact on the system during backup operations can be minimized.

Steps to separate backups and expiry

1. Configuration adjustment

Open the pgbackrest.conf file, usually located in the pgBackRest configuration directory. Adjust the retention settings based on the backup strategy by commenting on the retention for backups and setting the retention for archives.

In this example, we are retaining backups for an infinite time and retaining required archives for FULL backup consistently so that issues are not faced while restoring full backups.

2. Execute backups

Run the backup command to perform a backup.

Notice the warnings while executing the backups that are expected.

3. Execute expiry

Let’s check the backup info and see how many backups are available in the repo before expiring the backups:

Run the expiry command to remove outdated backups based on the configured retention policies.

Notice even though we have not given any attribute for expiring differential backups while expiring the full backups, pgBackRest will automatically remove the subsequent differential backups, which are dependent on the expiring full backup set.

Now let’s again check the backup information to find out which backup sets were expired and which were retained.

As expected, it has kept one full backup as per the attributes given in the expiry command. Also it has also retained the relevant differential backup against the full backup set, making room for PITR to be performed at any given time.

4. Schedule backups and expiry

Create separate cron jobs or scheduled tasks for backups and expiry based on the preferred frequency.

In this example, differential backups run on weekdays at 3 AM, full backup runs on Saturdays, and expiry runs weekly on Sundays at 4 AM. Notice expiry command has –repo1-retention-full=1, which will allow us to keep at least one full backup. pgBackRest will automatically take care of the expiring subsequent differential backups, along with expiring each full backup set.

Conclusion

This approach ensures that backup operations remain swift and responsive, even if the database grows. I saw this issue for one of our clients where they had a backup repository on the cloud, and expiry took three to four times more time than the backup, hampering multiple things. We also used the file bundle option of pgBackRest, which helped decrease the time taken for the backup. However, expiry was still taking more time. Decoupling backup and expiry operations with pgBackRest isn’t just a configuration tweak – it’s a strategic move toward a more resilient and adaptable database management approach.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together. Run PostgreSQL in your production and mission-critical environments and easily deploy and orchestrate reliable PostgreSQL in Kubernetes.

 

Download Percona Distribution for PostgreSQL Today!

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments