Press "Enter" to skip to content

Category: Backups

Moving Away from pg_dump

Pat Wright explains why pg_dump isn’t the best solution for backup and restore:

I’m still fairly new to Postgres having only started about 5 years ago. I started with Pg9.6 and we quickly moved that environment to pg10.   When I arrived at this company,  pg_dump was the only backup we were using.  The DB at that time was still around 50GB, it was reasonable to do the backups in a timeframe that worked for us.  A dump was done every night and stored off to another server.   After some time we started to test Pg_basebackup. This allowed us to full server backup each night. It was a huge improvement as far as speed and ability to handle much larger data sizes. 

Read on for the tradeoffs around tools and various thoughts from Pat.

Comments closed

The Importance of Validating Postgres Backups

Grant Fritchey brings an important insight:

I’m very much just beginning my journey of learning PostgreSQL. I’ve been documenting that learning over here at Simple-Talk (more on the way there), including backups. For this post, I’m not going to tell you about my “experience” maintaining a PostgreSQL backup routine because, well, there isn’t any. Instead, I have something else to say about backups that I learned, the hard way I might add, while working in SQL Server, that is 100% applicable to PostgreSQL.

Click through for Grant’s thoughts. The “what” makes sense. The “how” is the important part.

Comments closed

Thoughts on Postgres Backups

Ryan Booz shares some thoughts on backups in Postgres:

To be honest, I feel pretty unqualified to talk much about backups in Postgres, partially because it’s been a couple of years since I’ve had to manage the uptime of an application. As PostgreSQL has grown in popularity and usage, there is a lot of effort being put into tools like pgBackrestpgBarman, and some newer solutions like pgcopydb (ok, I realize this isn’t billed as a “backup” tool, but…).

What I can share are a couple of things I’ve learned about restoring clusters and databases, particularly given the extensible nature of Postgres and the variety of tooling.

Read on for thoughts on a variety of topics.

Comments closed

Backups and Restores when a NAS Requires a Password

Jana Sattainathan needs to give the daily password:

Sometimes, you have a share (like Azure Data Box via SMB as was the case for me) that you can access only with a UserName and Password. This is fine as long as you are accessing it interactively by typing it in, but how about accessing it from SQL Server for the purposes of backing up and restoring?

This is where “NET USE” command comes in handy becomes necessary

Read on to see how that can help you out.

Comments closed

Geo-Zone Redundant Storage for SQL MI Backups

Niko Neugebauer moves the backups pretty far away:

The new Geo-Zone Redundant Storage (GZRS) backup storage option combines the best of two worlds – Geo-Redundant and Zone-Redundant storage, keeping backups safe from both regional (Geo-Redundant) and Data Center (Zone-Redundant) failures. It provides the highest availability for storage currently offered on Azure, improving recovery speed and enabling Point-In-Time Restore (PITR) of backups in the event of a zone failure.  

Geo-Zone Redundant Storage for Azure SQL Managed Instance backups provides 3 synchronous copies in different availability zones within the same primary region, plus an additional asynchronous copy within a single availability zone in the paired secondary region, as shown on the following picture: 

Click through for that picture and what it does for expected availability. Basically, a whole bunch of data centers would need to fail before you lose a backup. Or someone messes up DNS and makes everything unavailable for a day, not that that’s ever happened before with a large cloud service provider…

Comments closed

MySQL Backups via mysqldump

Lukas Vileikis begins a series on MySQL backups with the classic option:

mysqldump is one of the most popular database backup tools in the MySQL world.

The tool is prevalent partly because it’s very basic and quite powerful – mysqldump database backup tool is command line-based, very simple and very straightforward to use. As far as MySQL or its flavors (MariaDB and Percona Server) are concerned, this command line-based tool is one of the top choices for junior and senior database engineers across the world alike.

Click through to see how it works.

Comments closed

Monitoring Azure SQL Backup History

Mustafa Ashour wants you to check your backups:

Database backups are an essential part of any business continuity and disaster recovery strategy, because they help protect your data from corruption or deletion. These backups enable database restore to a point in time within the configured retention period. By default, Azure SQL Database & Azure SQL Managed Instance stores data in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases/instance to a different region in the event of a regional outage/disaster.

Read on to learn more about how Azure SQL DB and Azure SQL Managed Instance perform backups, their cadence, and how you can find information on backup history.

Comments closed

SQL Server Backup and Restore Operations for S3

Hugo Queiroz shows off something new in SQL Server 2022:

Backup and restore to simple storage service (S3)–compatible object storage is a new feature introduced in SQL Server 2022 that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.

There are some differences from other backup operations, so you should definitely read up on it before using it. One interesting side benefit I got to try out recently is that Pure Storage’s FlashBlade product has an S3 API, allowing you to use that interface for backup/restore operations as well as data virtualization.

Comments closed