Category: Backups

Warda Bibi explains why a large PostgreSQL database restoration can take a long time:

In July 2025, during the PG19-1 CommitFest, I reviewed a patch targeting the lack of parallelism when adding foreign keys in pg_restore. Around the same time, I was helping a client with a large production migration where pg_restore dragged on for more than 24 hours and crashed multiple times.

In this blog, I will talk about the technical limitations in PostgreSQL, the proposed fix, and a practical workaround for surviving large restores.

The reason for the slowdown is a bit wild to me, though I suppose somebody could edit the backup files manually after pg_dump writes them out.

Leave a Comment

Aaron Bertrand figures out available database restoration options:

In my last tip, Enhancing SQL Server Backups for Hundreds of Databases, I created a single, central backup catalog representing all the full, log, and differential backups across our environment. I demonstrated how I use it to build restore statements for any given database for validating the backup, performing DBCC checks, or to recover data at a specific point in time. But can this data help us in other ways, too? Let’s see how we can use this data for a point in time restore.

Read on for some neat queries you can run based on Aaron’s process in the original post.

Comments closed

Warda Bibi sets up a backup management system:

Recently, we worked with a client who was manually backing up their 800GB PostgreSQL database using pg_dump, which was growing rapidly and had backups stored on the same server as the database itself. This setup had several critical issues:

• Single point of failure: If the server failed, both the database and its backups would be lost.
• No point-in-time recovery: Accidental data deletion couldn’t be undone.
• Performance bottlenecks: Backups consumed local storage, impacting database performance.

Read on to see how pgbackrest can address each of these concerns.

Comments closed