PostgreSQL backup strategies - pg_dump vs pg_basebackup vs Barman

Ahmed alkhair

Ahmed alkhair

— 3 min read

Introduction

Choosing the right PostgreSQL backup strategy is a critical decision for any organisation operating in the GCC and beyond. This post compares PostgreSQL backup strategies - pg_dump vs pg_basebackup vs Barman — describing use-cases, pros and cons, and practical commands you can use today. As a senior PostgreSQL DBA working with enterprise customers aligned with Vision 2030 and PDPL requirements, I focus on reliable, auditable backups that work across on-prem, regional cloud, and hybrid topologies.

Why backups matter (regional considerations)

Saudi organisations must meet PDPL and national cybersecurity guidelines; that requires proven recovery objectives and demonstrable integrity. Backup choices affect RTO/RPO, storage costs, and compliance. For cloud-first initiatives—Quick Deploy or local cloud providers—understanding logical vs physical backups and third-party tools like Barman is essential.

Overview: logical vs physical backups

Briefly:

  • Logical backups (pg_dump/pg_restore): export SQL or archive files with schema/data. Good for logical migrations and selective restores.
  • Physical backups (pg_basebackup, Barman): copy the database cluster files and use WAL streaming for PITR. Required for point-in-time recovery and large clusters.

1) pg_dump — best for logical exports and migrations

pg_dump is a logical backup tool shipped with PostgreSQL. Use it for schema changes, logical migration between versions, and when you need human-readable SQL or granular restores.

Example: create a compressed dump

pg_dump -h db.prod.example -U app_user -F c -j 4 -f /backups/appdb_$(date +%F).dump appdb
# restore
pg_restore -h db.new -U app_user -d appdb -j 4 /backups/appdb_2026-03-01.dump

Pros

  • Portable between major/minor PostgreSQL versions (with care).
  • Granular restore: tables, schemas, or rows.
  • Small clusters or targeted backups are efficient.

Cons

  • Large databases are slow to dump and restore; not suitable for fast RTO.
  • Does not support PITR (point-in-time recovery).
  • Logical dumps may miss non-SQL objects (extensions, custom file storage).

2) pg_basebackup — simple physical base backups

pg_basebackup is a built-in tool for taking physical, hot backups of an entire cluster. It is straightforward and works with WAL archiving for PITR.

Example: hotspot base backup with streaming WAL

pg_basebackup -h primary.example -U replication -D /backups/pg_basebackup -Ft -z -P
# enable WAL archiving in postgresql.conf
archive_mode = on
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'

Pros

  • Fast for large clusters; binary copy ensures consistent state.
  • Supports PITR when combined with WAL archives or streaming replication.
  • Simple to integrate into scripts and recovery workflows.

Cons

  • Restores are cluster-level (not table-level); less flexible than pg_dump.
  • Requires sufficient storage and management of WAL segments.
  • Manual retention, verification, and cataloging need additional tooling.

3) Barman — enterprise-ready backup orchestration

Barman (Backup and Recovery Manager) is a mature, enterprise-focused backup tool that orchestrates physical backups and WAL streaming, with retention policies, compression, verification, and remote management. It is ideal for production systems where recoverability, auditing, and automation are required.

Minimal barman.conf example

[barman]
compression = gzip
retention_policy = RECOVERY WINDOW OF 7 DAYS

[proddb]
description = Production PostgreSQL
conninfo = host=primary.example user=barman dbname=postgres
backup_method = rsync
streaming_conninfo = host=primary.example user=replication dbname=replication

Barman commands

# check configuration and list backups
barman check proddb
barman list-backups proddb
# start base backup
barman backup proddb
# recover to specific time
barman recover --target-time "2026-03-01 10:30:00" proddb /recovery/targetdir

Pros

  • Centralised backup management for multiple servers with retention and compression.
  • Automated backup verification and WAL management reduce human error.
  • Better suited to enterprise compliance: audit trails, retention windows, and scripted recovery — important for PDPL proofs.

Cons

  • Operational overhead and learning curve; requires a backup server and secure network.
  • Initial setup more complex than pg_basebackup; monitoring needed.
  • Not a replacement for logical backups — combine with periodic pg_dump for logical-level recovery.

Choosing the right strategy (practical guidance)

Recommendations based on real-world operations:

  • Small databases or migration tasks: use pg_dump for portability and selective restores.
  • Large production clusters where PITR and low RTO matter: use pg_basebackup + WAL archiving or streaming.
  • Multi-server, enterprise deployments requiring retention policies, verification, and auditability: use Barman as the orchestrator.

Combine methods: schedule daily physical backups (pg_basebackup/Barman) and weekly logical dumps (pg_dump) for schema-level restores and cross-version migrations. Automate testing of restores — backups are only useful if they are proven.

Operational checklist

  • Define RTO/RPO aligned with business and PDPL expectations.
  • Encrypt backups at rest and in transit (PGEE TDE can help for database-at-rest encryption).
  • Automate retention and periodic verification (barman check).
  • Document recovery steps and run drills quarterly — include cloud-region failover if using Quick Deploy or local cloud providers.

In the Saudi market, where regulatory compliance and cloud adoption are accelerating under Vision 2030, adopting a robust backup strategy that blends logical and physical approaches will reduce operational risk and speed recovery.

Ready to Transform Your Database Infrastructure?

Worlber helps companies across Saudi Arabia and the Middle East build reliable, secure, and cost-effective database solutions.

📧 Email us: contactus@worlber.com

🌐 Visit: worlber.sa/contact

📞 Call: Talk to our database experts today

Worlber — Your trusted PostgreSQL partner in the Middle East

Read more