Category: Backups

Muhammad Ali shows off another way to back up and restore data in Postgres databases:

Barman is a production grade tool for managing the backup and recovery process of PostgreSQL databases. It not only handles the physical backups but also provides automatic management of retention policies, compression, near zero Recovery point objective(RPO) and enables recovery at any desired point(PITR) in time within the recovery window.

Barman’s Stream Archiving feature stands out as a key component in achieving zero Recovery Point Objective (RPO). This is achieved by using pg_recievewal utility which continuously backs up Write-Ahead Logging (WAL) files in real-time to a designated Barman server. This capability is particularly important for applications where even minimal data loss is unacceptable.

Read on for a couple questions about it and a demonstration of how Barman works.

Comments closed

David Wiseman needs to clear out a significant amount of backup history:

Recently, I encountered an issue running sp_delete_backuphistory on servers that hosted a large number of databases with frequent log backup & restore operations. The clean up task hadn’t been scheduled and the history tables had grown very large over several months. The msdb databases was also hosted on a volume with limited IOPs.

Attempting to run sp_delete_backuphistory under these conditions you will likely encounter these issues:

Click through for that list of issues, as well as a way of mitigating the problem. I’ve noticed this kind of pattern appears fairly often in Microsoft-provided cleanup procedures: the code works well until you reach a certain scale, at which point it falls over. It’d be great if the original sp_delete_backuphistory performed batch deletion from the get-go, but David shows us a way to get around the issue.

Comments closed

Robert Haas talks about a new feature:

Five days before Christmas I committed my patch to add incremental backup to PostgreSQL. Actually, I’ve been committing preparatory patches for some months now, but December 20 saw the two main patches land. Since then, there’s been a bunch of bug-fix commits, and there are still a few pending items that need to be addressed, but the core of the feature is now committed. If you want a quick overview of the feature, Lukas Fittl has a great video about that. Here, I’d like to talk about the architecture of the feature itself in a little more detail, and specifically with how we decide which data to copy.

Most people who are likely to read this blog are probably already familiar with the core idea of an incremental backup: instead of copying the whole database instance, just copy the data that has changed. That’s faster, and takes up less space on disk. But, to work properly, you have to be able to quickly and reliably identify which data has, in fact, changed. There’s more than one way to do that.

Read on for some of the complexity around this. It’s interesting to see what goes on behind the scenes in a relational database.

Comments closed

Adron Hall keeps a copy:

In the realm of data management, ensuring the safety and recoverability of data is paramount. Relational databases, being at the core of many business operations, require robust backup and recovery strategies. This article delves into the general concepts of backup and recovery in relational databases, followed by specific strategies for SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL.

Click through for a high-level overview of general database backup concepts and a variety of options available in the major relational database platforms.

Comments closed

Anthony Nocentino has a public service announcement:

SQL Server 2022 introduces a new feature to enable application-consistent snapshot backups. TSQL Snapshot Backups enable the SQL Server to control the database quiesce without external tools. Using TSQL Snapshot backups enables instantaneous restores, independent of the size of data, for a database, group, or server backups, including point-in-time recovery.

When you use this feature, it freezes I/O. You’ll see a record like this in your error log when you execute the command ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. This blog post will show you that the I/O freeze is just for write operations. You can continue to read from the database during this frozen state.

Read on to understand what’s going on, what “frozen” really means, and why this is a huge improvement over the classic behavior of the volume snapshot service.

Comments closed