DEV Community

ZINSOU Trinité
ZINSOU Trinité

Posted on

Setting up incremental backups with PostgreSql - Implementation - Part 2

#ubuntu #postgres #tutorial #devops

🧭 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:

Incremental Backup 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 barman@192.168.58.12
Enter fullscreen mode Exit fullscreen mode

💡 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 barman@192.168.58.12:/var/lib/barman/pg/incoming/%f'
max_wal_senders = 3
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Then create the barman user in PostgreSQL:

$ createuser --superuser --replication -P barman
Enter fullscreen mode Exit fullscreen mode

✅ Restart PostgreSQL to apply changes:

$ sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

🛠️ 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
Enter fullscreen mode Exit fullscreen mode

🧾 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 postgres@192.168.58.11
backup_method = rsync
streaming_archiver = on
slot_name = barman
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

  • 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

🚀 Run Your First Backup 

$ barman backup pg
Starting backup using rsync...
Backup completed
WAL segments processed:
  000000010000000000000011
  000000010000000000000012
Enter fullscreen mode Exit fullscreen mode

🔁 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
Enter fullscreen mode Exit fullscreen mode

Add this:

0 2 * * * barman backup pg
Enter fullscreen mode Exit fullscreen mode

💡 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
Enter fullscreen mode Exit fullscreen mode

📊 Check Server Status 

$ barman check pg
Enter fullscreen mode Exit fullscreen mode

🔎 Inspect a Specific Backup 

$ barman show-backup pg 20250415T201754
Enter fullscreen mode Exit fullscreen mode

🧯 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!

Top comments (0)