PostgreSQL with Barman Backup Setup Guide

[santosh]

PostgreSQL with Barman Backup Setup Guide

Environment Setup

• PostgreSQL Database Server IP: 10.10.x.x
  
• Barman Backup Server IP: 10.10.x.x
  
• PostgreSQL Version: 14
  
• Barman Version: 3.11.1
  
• OS: Ubuntu 20.04 / 22.04
  

Architecture Overview

Code:

[ PostgreSQL Server 10.10.x.x ]  ----WAL Archive---->  [ Barman Server 10.10.x.x ]
        (Database)                                          (Backup)

Step 1 - Install PostgreSQL
Run on: PostgreSQL Server (10.10.x.x)

Code:

sudo apt update
sudo apt install postgresql-14

Step 2 - Configure postgresql.conf
Run on: PostgreSQL Server (10.10.x.x)

Code:

sudo nano /etc/postgresql/14/main/postgresql.conf

Add the following lines:

Code:

wal_level = replica
archive_mode = on
archive_command = 'rsync -a %p barman@BARMAN_IP:/var/lib/barman/main-db-server/incoming/%f'
max_wal_senders = 3
wal_keep_size = 256MB

Step 3 - Configure pg_hba.conf
Run on: PostgreSQL Server (10.10.x.x)

Code:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Add the following line:

Code:

host    all    barman    BARMAN_IP/32    md5

Step 4 - Restart PostgreSQL
Run on: PostgreSQL Server (10.10.x.x)

Code:

sudo systemctl restart postgresql
sudo systemctl status postgresql

Step 5 - Install Barman
Run on: Barman Backup Server (10.10.x.x)

Code:

sudo apt update
sudo apt install barman

Step 6 - SSH Key Setup
Run on: Barman Backup Server (10.10.x.x)

Generate SSH key on Barman server:

Code:

sudo -i -u barman
ssh-keygen -t rsa -b 4096 -f ~/.ssh/id_rsa -N ""

Copy key to PostgreSQL server:

Code:

ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@POSTGRES_IP

Test SSH connection:

Code:

ssh postgres@POSTGRES_IP

Step 7 - Configure Barman
Run on: Barman Backup Server (10.10.x.x)

Code:

sudo nano /etc/barman.d/main-db-server.conf

Add the following:

Code:

[main-db-server]
description = "Main PostgreSQL Server"
conninfo = host=POSTGRES_IP user=barman password=YOUR_PASSWORD port=5432 dbname=postgres
ssh_command = ssh postgres@POSTGRES_IP
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
backup_method = rsync
archiver = on
compression = gzip
parallel_jobs = 4

Step 8 - Create Barman User in PostgreSQL
Run on: PostgreSQL Server (10.10.x.x)

Code:

sudo -i -u postgres
psql -c "CREATE USER barman WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'YOUR_PASSWORD';"
psql -c "ALTER USER barman WITH SUPERUSER;"

Step 9 - Verify Setup
Run on: Barman Backup Server (10.10.x.x)

Code:

sudo -u barman barman check main-db-server

Expected Output:

Code:

Server main-db-server:
    PostgreSQL: OK
    superuser or standard user with backup privileges: OK
    wal_level: OK
    directories: OK
    retention policy settings: OK
    archive_mode: OK
    archive_command: OK
    continuous archiving: OK
    ssh: OK

Step 10 - Take First Backup
Run on: Barman Backup Server (10.10.x.x)

Code:

sudo -u barman barman backup main-db-server

List backups:

Code:

sudo -u barman barman list-backup main-db-server

Summary

• PostgreSQL Server - Sends WAL logs to Barman via rsync
  
• Barman Server - Receives and stores backups with 7 day retention
  
• SSH - Used for secure communication between both servers
  
• Always test restore procedure after setup