Why pg_dump is not enough for Production environments?
When I first started managing Postgres, I thought a nightly pg_dump was sufficient. That was until my system database hit 500GB and a client requested a data restore at 10:15 AM—right between two backup windows. In this situation, pg_dump was useless because it only allowed a rollback to the previous night’s state. All transactions from those 10 hours in the morning would have been lost.
This is why I switched to Barman (Backup and Recovery Manager). It’s an open-source tool for managing PostgreSQL backups to enterprise standards. The biggest “selling point” of Barman is its support for Point-in-Time Recovery (PITR). You can easily restore the database to a specific point in time, accurate to the second, right before an incident occurred.
Quick Start: Set up Barman in 5 minutes
For security and safety, you should install Barman on a separate server. This protects your backups if the database server suffers a hardware failure or other catastrophic issues.
1. Install Barman
# On Ubuntu/Debian
sudo apt update
sudo apt install barman barman-cli -y2. Configure Passwordless SSH
Barman needs access to the DB server via the postgres user to copy raw data. Generate a key and copy it to the DB server using the following commands:
# Switch to barman user on the Barman server
sudo su - barman
ssh-keygen -t rsa
ssh-copy-id postgres@<IP_DB_SERVER>3. Configure the Server in Barman
Create a configuration file at /etc/barman.d/pg_production.conf:
[pg_production]
description = "Main PostgreSQL Production Database"
conninfo = host=<IP_DB_SERVER> user=barman dbname=postgres
ssh_command = ssh postgres@<IP_DB_SERVER>
retention_policy = RECOVERY WINDOW OF 2 WEEKS
archiver = on4. Run the first backup
# Check connection
barman check pg_production
# Perform backup
barman backup pg_productionWAL Archive Mechanism – The Soul of PITR
Barman’s power lies in how it handles WAL (Write-Ahead Logging). In Postgres, every data change is recorded in small log files (usually 16MB). Barman continuously collects (archives) these files to the backup server.
When recovery is needed, Barman takes a Base Backup (a full copy) and then applies the WAL files in chronological order. This process is like rewinding a video tape to the exact scene you want to watch. This is the essence of Point-in-Time Recovery (PITR).
Configure log shipping from the PostgreSQL Server
Edit the postgresql.conf file on the DB server to enable log shipping to Barman:
wal_level = replica
archive_mode = on
archive_command = 'rsync -a %p barman@<IP_BARMAN_SERVER>:/var/lib/barman/pg_production/incoming/%f'Practical experience shows that WAL files can accumulate very quickly. Without proper management, they can fill up your disk in just a few peak hours. Barman helps you solve this by automatically compressing and deleting old files based on your defined retention policy.
Point-in-Time Recovery Masterclass (Last-minute Save)
Imagine a nightmare scenario: A buggy migration script runs at 2 AM and wipes the customers table. With Barman, there’s no need to panic. Simply identify the timestamp 01:59:59—just before the faulty command executed.
Command to recover to a specific point in time:
barman recover --target-time "2023-10-27 01:59:59" \
pg_production \
/path/to/new/data/directoryBarman will then automatically calculate which base backup and WAL files are needed for recovery. Once finished, you simply point the database to the new directory and restart. The system will operate as if the error never happened.
Real-world Experience Operating Barman
Optimizing Storage Capacity
My 1TB database initially took up a lot of backup space. After enabling compression = gzip in the config file, the backup size dropped by over 65% to about 350GB. This significantly saves on disk costs without slowing down the recovery process too much.
Don’t Forget Monitoring
Automated backups don’t mean you can set it and forget it. I always set up a cron job to run barman check every hour. If the status isn’t OK—for instance, due to a full disk or SSH error—the system immediately sends an alert via Telegram. Trust me, finding a backup error when you’re free is much better than finding it right after the database crashes.
Handling Data After Recovery
Often after restoring for debugging, I need to quickly extract data to CSV for comparison. To convert CSV to JSON for analysis or importing into NoSQL tools, I often use the tool at toolcraft.app/en/tools/data/csv-to-json. It processes directly in the browser, so sensitive data isn’t sent to a server, making it very safe and convenient.
Valuable Advanced Features
- Streaming Backup: Barman supports continuous WAL streaming instead of waiting for a full 16MB file to be sent. This reduces data loss risk to almost zero (Zero Data Loss).
- Hook Scripts: You can use
post-backup-scriptto automatically push backups to S3 or Google Cloud Storage. This is a critical step in completing a 3-2-1 backup strategy. - Barman Cloud: This toolkit allows direct backups from Postgres to Cloud Object Storage without an intermediate server, perfect for cloud-native architectures.
Conclusion
Barman is more than just a backup tool; it’s a comprehensive data safety management system. Spending a few hours configuring Barman today will help you sleep better every night. Don’t wait until data disappears to regret staying loyal to pg_dump for too long.
If you’re running PostgreSQL on your own server (VM or Bare-metal), Barman is the top choice. Even though services like AWS RDS have similar mechanisms built-in, understanding how Barman works helps you master the nature of PostgreSQL and feel more confident in database troubleshooting scenarios.
