DEV Community

ZINSOU Trinité
ZINSOU Trinité

Posted on

Setting up incremental backups with PostgreSql - Recovery - Part 3

#backup #database #postgres

🧭 Introduction

Setting up backups is essential. But knowing how to restore them effectively when needed is even more crucial. This is where PITR (Point-In-Time Recovery) comes into play, a powerful feature offered by PostgreSQL and fully managed by Barman.

Whether it’s to correct a human error, revert after a failed deployment, or analyze the state of the database at a specific moment, Barman allows you to bring PostgreSQL back in time, replaying only the changes up to a defined point.

In this chapter, we’ll explore:

  • What PITR actually is,

  • The prerequisites for it to work,

  • And most importantly, how to use it step by step with Barman, reliably.

Ready to travel back in time with PostgreSQL? Let’s go 👇

🕰️ What is PITR (Point-In-Time Recovery)?

Point-In-Time Recovery, or PITR, is a powerful PostgreSQL feature that allows you to restore a database to a precise moment in the past.

It’s a bit like a time machine 🕳️🕒.

Imagine a bug or human error corrupted data this morning at 10:30 AM. With PITR, you can restore the database exactly as it was at 09:29 AM, just before the issue.

For this to work, PostgreSQL logs all database writes in files called WALs (Write Ahead Logs). These files contain the history of all changes made to the database.

Barman uses these files to replay the database history up to the chosen moment, starting from a full backup. It essentially rewinds the database to a specific point captured by the existing backups.

🎯 RPO and RTO: The Two Pillars of a Solid Backup/Restore Strategy

When talking about business continuity and disaster recovery, two concepts are essential to understand before even considering backups or tools like Barman:

📌 Recovery Point Objective (RPO)

RPO, or Recovery Point Objective, represents the maximum amount of data you can afford to lose in case of an incident.

👉 Example:

If you can accept losing up to 5 minutes of data, your RPO is 5 minutes. This means your backups (or WAL captures) should occur at least every 5 minutes.

⏱ Recovery Time Objective (RTO)

RTO, or Recovery Time Objective, represents the maximum acceptable downtime after an incident.

👉 Example:

If you consider a 30 minutes service outage acceptable, your RTO is 30 minutes. This means your tools and restoration mechanisms (like Barman and your automations) must be able to restore and bring the service online within this timeframe.

⚖️ Finding the Right Balance Between Ideal and Reality

We’d all love RPO = 0 (no data loss) and RTO = 0 (no downtime).

But in real life, there’s usually a trade-off between cost and service criticality.

For example:

  • For a brochure website, an RPO/RTO of several hours may be acceptable.

  • For a banking application, every second counts: RPO/RTO must be near zero.

💡 RPO and RTO with PostgreSQL and Barman

Good news: with a well-configured open-source stack, you can get very good performance:

  • RPO = 0 🎯

    Thanks to PostgreSQL’s synchronous streaming replication, your data can be protected without loss.

  • Minimal RTO ⏱

    By combining Barman for backups and repmgr for high availability, near-instant recovery is possible in case of issues.

🧰 Prerequisites for PITR Restoration

Before attempting a point-in-time restore, some elements must be in place for a smooth process:

1. Valid backup available

You must have at least one full backup in Barman. This will serve as the starting point for restoration.

2. WAL archiving enabled

PITR relies on replaying WALs (Write Ahead Logs) up to a precise moment. Ensure archiving works correctly and files are present in Barman’s wals directory.

3. Sufficient disk space

Restoration will create a new copy of the database. Ensure there is enough free space on the target server.

4. Timestamp or XID for restoration

To trigger PITR, specify the restore point, either:

  • a date/time ('2025-04-11 16:00:00'),

  • a transaction ID (XID),

  • or simply the end of the backup (if you don’t replay WALs).

5. Test environment (optional but recommended)

Before restoring in production, it’s strongly recommended to test the restore in an isolated environment.

🔄 Data Recovery with Barman (Practical Example)

Let’s create a table in our test databas

e and make several manipulations to test our backup system. Here’s a script you can use:

--- create articles table
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

--- insert 20 rows into articles table
INSERT INTO articles (title, content)
SELECT
  'Article title ' || i,
  'Lorem ipsum dolor sit amet, consectetur adipiscing elit. 
   Sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. 
   Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.'
FROM generate_series(1, 20) AS s(i);
Enter fullscreen mode Exit fullscreen mode

Next, take an incremental backup before simulating a data loss (e.g., accidental deletion) using barman backup pg (assuming pg is your PostgreSQL server in Barman’s config):

# on the barman server
$ barman backup pg
Starting backup using rsync-concurrent method for server pg in /var/lib/barman/pg/base/20250415T201516
Backup start at LSN: 0/14000028 (000000010000000000000014, 00000028)
Starting backup copy via rsync/SSH for 20250415T201516
Copy done (time: 2 seconds)
Asking PostgreSQL server to finalize the backup.
Backup size: 38.9 MiB. Actual size on disk: 4.8 MiB (-87.55% deduplication ratio).
Backup end at LSN: 0/14000100 (000000010000000000000014, 00000100)
Backup completed (start time: 2025-04-15 20:15:16.336556, elapsed time: 4 seconds)
Processing xlog segments from file archival for pg
    000000010000000000000013
    000000010000000000000014
    000000010000000000000014.00000028.backup
Enter fullscreen mode Exit fullscreen mode

Note the backup end time, which will be used to set your --target-time during restoration.

Now, simulate data loss:

--- delete articles with even IDs
DELETE FROM articles WHERE id % 2 = 0;

--- verify deletion
SELECT COUNT(id) FROM articles;

/*
--------------------------------------------
                OUTPUT
--------------------------------------------
*/
 count 
-------
    10
(1 row)
Enter fullscreen mode Exit fullscreen mode

💥 All articles with even IDs are now deleted. This is the perfect moment to use incremental recovery and revert to the original state. First, take a new backup and stop the PostgreSQL server:

# on the barman server
$ barman backup pg
Starting backup using rsync-concurrent method for server pg in /var/lib/barman/pg/base/20250415T201754
Backup start at LSN: 0/17000028 (000000010000000000000017, 00000028)
Starting backup copy via rsync/SSH for 20250415T201754
Copy done (time: 2 seconds)
Asking PostgreSQL server to finalize the backup.
Backup size: 38.9 MiB. Actual size on disk: 129.7 KiB (-99.67% deduplication ratio).
Backup end at LSN: 0/17000100 (000000010000000000000017, 00000100)
Backup completed (start time: 2025-04-15 20:17:55.167843, elapsed time: 4 seconds)
Processing xlog segments from file archival for pg
    000000010000000000000016
    000000010000000000000017
    000000010000000000000017.00000028.backup

$ barman list-backup pg
pg 20250415T201754 - Tue Apr 15 20:17:58 2025 - Size: 54.9 MiB - WAL Size: 0 B
pg 20250415T201516 - Tue Apr 15 20:15:19 2025 - Size: 54.9 MiB - WAL Size: 48.0 MiB
pg 20250413T113210 - Sun Apr 13 11:32:14 2025 - Size: 54.8 MiB - WAL Size: 32.0 MiB

# on the PostgreSQL server
$ sudo service postgresql stop
Enter fullscreen mode Exit fullscreen mode

Two backups surround the incident: 20250415T201516 and 20250415T201754. The first one (pre-incident) will be used for PITR.

To restore, run this command on the Barman server:

$ barman recover --remote-ssh-command "ssh postgres@192.168.58.11" --target-time="2025-04-15 20:17:58.270833+00:00" pg 20250415T201754 /var/lib/postgresql/12/main
Starting remote restore for server pg using backup 20250415T201754
Destination directory: /var/lib/postgresql/12/main
Remote command: ssh postgres@192.168.58.11
Doing PITR. Recovery target time: '2025-04-15 20:17:58.270833+00:00'
Using safe horizon time for smart rsync copy: 2025-04-15 20:17:54.690087+00:00
Copying the base backup.
Copying required WAL segments.
Generating recovery configuration
Identify dangerous settings in destination directory.

IMPORTANT
These settings have been modified to prevent data losses

postgresql.conf line 755: archive_command = false

WARNING
You are required to review the following options as potentially dangerous

postgresql.conf line 41: data_directory = '/var/lib/postgresql/12/main'     # use data in another directory
postgresql.conf line 43: hba_file = '/etc/postgresql/12/main/pg_hba.conf'   # host-based authentication file
postgresql.conf line 45: ident_file = '/etc/postgresql/12/main/pg_ident.conf'   # ident configuration file
postgresql.conf line 49: external_pid_file = '/var/run/postgresql/12-main.pid'          # write an extra PID file
postgresql.conf line 66: unix_socket_directories = '/var/run/postgresql'    # comma-separated list of directories
postgresql.conf line 102: ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
postgresql.conf line 104: ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
postgresql.conf line 740: include_dir = 'conf.d'            # include files ending in '.conf' from

Recovery completed (start time: 2025-04-16 15:14:41.903013, elapsed time: 8 seconds)

Your PostgreSQL server has been successfully prepared for recovery!
Enter fullscreen mode Exit fullscreen mode

Command options:

  • --remote-ssh-command: SSH command to connect to the PostgreSQL server

  • --target-time: date/time to restore the data

  • server_name: name of the server

  • backup_id: backup to use

  • destination_directory: folder for restoration

Restoration can be done in any folder other than PostgreSQL’s main directory if you reconfigure PostgreSQL to point to it. After recovery:

--- verify data count
SELECT COUNT(id) FROM articles;

/*
--------------------------------------------
                OUTPUT
--------------------------------------------
*/
 count 
-------
    20
(1 row)
Enter fullscreen mode Exit fullscreen mode

🏁 Conclusion

Human errors, software corruption, or hardware failures are not a matter of if, but when. In this context, the ability to revert PostgreSQL to a precise previous state is essential.

With Barman and Point-In-Time Recovery (PITR), you have a reliable, robust, and powerful tool to ensure business continuity. By combining full backups with archived WAL files, you can restore your database to the exact second before an incident.

Moreover, as you’ve seen, this process can be automated, tested, and industrialized, offering a lot of advantages for enhancing your PostgreSQL architecture’s resilience.

Top comments (0)