Setting up incremental backups with PostgreSql - Implementation - Part 2

🧭 Introduction

In the first part, we laid the foundation: understanding the different types of backups, the crucial role of WAL files, and what Barman has to offer. Now, it’s time to take action.

In this hands-on guide, we’ll configure PostgreSQL and Barman step by step to set up a robust and automated incremental backup system. The goal is simple: you should be able to replicate it in your own environment.

ℹ️ This article uses Ubuntu as the reference environment.

We’ll use two Ubuntu 22 servers named pg and barman, with respective IPs: 192.168.58.11 (PostgreSQL) and 192.168.58.12 (Barman).

Ready? Let’s dive in. 👇

🤔 Which Backup Strategy Should You Choose with Barman?

Choosing the right backup strategy is key to building a reliable system. Barman supports two main methods:

• Via rsync: a classic method using SSH to transfer files from the PostgreSQL server to the Barman server.




• Via pg_basebackup: a more modern approach using PostgreSQL’s native streaming replication protocol.

Since incremental backups with pg_basebackup are only supported starting with PostgreSQL 17, we’ll configure our setup using rsync.

The diagram below illustrates how to set up incremental backups with rsync:

To succeed with this configuration, we need:

• A standard connection to PostgreSQL (1)
  
  
  Used by Barman for management, coordination, and monitoring tasks. This is a classic TCP/IP connection on PostgreSQL’s port.




• SSH connection for base backups (2)
  
  
  Since we’re using rsync, Barman must use SSH to connect to the PostgreSQL server. The barman user (on the Barman server) connects as postgres (on the PostgreSQL server) to access backup files.




• SSH connection for WAL archiving (3)
  
  
  The archive_command in PostgreSQL (we’ll get to that 😉) triggers the PostgreSQL server to initiate an SSH connection. The postgres user (on the PostgreSQL server) connects as barman (on the Barman server) to send the WAL files.

🔑 SSH Configuration Between Barman and PostgreSQL

To ensure secure, passwordless communication between PostgreSQL and Barman, we must set up SSH key authentication between the two servers.

We'll generate SSH key pairs for both postgres and barman users and exchange them accordingly.

• On the PostgreSQL server:
  

su postgres

ssh-keygen -t rsa -b 2048

ssh-copy-id -i /var/lib/postgresql/.ssh/id_rsa [email protected]

💡 Make sure both postgres and barman have strong passwords set beforehand.

🔧 PostgreSQL Configuration for Incremental Backups (via rsync)

Before Barman can operate, we need to prepare PostgreSQL to provide the necessary WAL (Write-Ahead Log) files stored in /var/lib/postgresql/<version>/main/pg_wal/.

🗂️ Enable WAL Archiving in postgresql.conf

Open the postgresql.conf file (usually located in /etc/postgresql/<version>/main) and locate:

include_dir = 'conf.d'

This allows us to create a custom config file like archivage.conf inside conf.d. Add the following:

bashCopierModifierlisten_addresses = '*'

wal_level = replica

archive_mode = on

archive_command = 'rsync -a %p [email protected]:/var/lib/barman/pg/incoming/%f'

max_wal_senders = 3

Explanation:

• wal_level = replica: Enables WAL logging needed for replication and backups.




• archive_mode = on: Activates archiving of WAL files.




• archive_command: Command executed for every new WAL segment here using rsync to send it to the Barman server.




• max_wal_senders: Sets how many connections can send WALs concurrently.

✅ Tip: Test your rsync command manually between servers to confirm connectivity.

🔐 Allow Barman Connections in pg_hba.conf

In /etc/postgresql/<version>/main/pg_hba.conf, add:

# Backup access

host    replication     barman     192.168.58.12/32      md5

Then create the barman user in PostgreSQL:

$ createuser --superuser --replication -P barman

✅ Restart PostgreSQL to apply changes:

$ sudo systemctl restart postgresql

🛠️ Barman Configuration for Incremental Backups

Now that PostgreSQL is sending WAL files, let’s configure Barman to receive and manage them.

📁 Set Up the Backup Directory

By default, Barman uses /var/lib/barman. Let’s prepare a directory for our server:

$ sudo mkdir -p /var/lib/barman/pg

$ sudo chown -R barman:barman /var/lib/barman/pg

🧾 barman.conf Configuration

Global config: /etc/barman.conf


Per-server config: /etc/barman.d/pg.conf

Here’s a minimal config example:

[pg]

description = "Primary PostgreSQL server"

conninfo = host=192.168.58.11 user=barman dbname=test_db

ssh_command = ssh [email protected]

backup_method = rsync

streaming_archiver = on

slot_name = barman

Key Options:

• conninfo: Connection string to PostgreSQL




• ssh_command: SSH access via postgres




• backup_method: rsync




• streaming_archiver: Enables real-time WAL capture




• slot_name: Replication slot name for WAL streaming

⚙️ Optional Advanced Settings

You can customize your setup:

iniCopierModifierreuse_backup = link

retention_policy = RECOVERY WINDOW OF 7 DAYS

compression = gzip

• reuse_backup = link: Avoids file duplication




• retention_policy: Keep backups for 7 days




• compression: Saves disk space with gzip

📌 Full config options available in Barman's documentation

🔐 Store Passwords in .pgpass

To enable passwordless conninfo logins, add a .pgpass file for barman:

bashCopierModifiersu barman

cat <<'EOF' >>~/.pgpass

mon_serveur:5432:test_db:barman:password

EOF

chmod 0600 ~/.pgpass

Format:


[host]:[port]:[database]:[user]:[password]

✅ Check That Everything Works

Now test the Barman-PostgreSQL connection:

$ barman check pg

  Server pg:

  PostgreSQL: OK

  ...

  continuous archiving: OK

  archiver errors: OK

🚀 Run Your First Backup

$ barman backup pg

Starting backup using rsync...

Backup completed

WAL segments processed:

  000000010000000000000011

  000000010000000000000012

🔁 Automate Incremental Backups with Barman

Use cron to automate regular backups.

🕒 Schedule a Daily Backup at 2 AM

Edit Barman's crontab:

$ sudo -u barman crontab -e

Add this:

0 2 * * * barman backup pg

💡 Always test your command manually before automating.

📉 Optimize Disk Space

• Enable compression: compression = gzip




• Use retention policy: retention_policy = RECOVERY WINDOW OF 7 DAYS

🔍 Monitor and Manage Backups with Barman

✅ List Available Backups

$ barman list-backup pg

pg 20250415T201754 - Size: 54.9 MiB - WAL Size: 16.0 MiB

📊 Check Server Status

$ barman check pg

🔎 Inspect a Specific Backup

$ barman show-backup pg 20250415T201754

🧯 What If Something Fails?

If a backup fails:

• Run barman check




• Check logs in /var/log/postgresql and /var/log/barman/

🏁 Conclusion: Stay in Control of Your PostgreSQL Backups

In this second part, we focused on the technical setup required to build a solid incremental backup strategy using Barman. From PostgreSQL tuning and secure SSH keys to cron automation and proactive monitoring—each step boosts the resilience of your data.

Barman is a powerful, flexible, and open source solution that meets the needs of even the most demanding environments. But backup mastery doesn’t stop here.

In the next part, we’ll learn how to restore a PostgreSQL database, including Point-In-Time Recovery (PITR) and practical recovery scenarios.

👉 Stay tuned to complete your PostgreSQL backup strategy!