Резервное копирование PostgreSQL: pgBackRest, Barman, WAL-G

# Ubuntu/Debian
sudo apt install pgbackrest
# RHEL/CentOS
sudo dnf install pgbackrest

# /etc/pgbackrest/pgbackrest.conf
[global]
# Local backup repository
repo1-path=/var/lib/pgbackrest
# S3 repository (optional: for multi-repository setup)
repo2-type=s3
repo2-s3-bucket=my-pg-backups
repo2-s3-region=ap-northeast-2
repo2-s3-key=AKIAIOSFODNN7EXAMPLE
repo2-s3-key-secret=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
repo2-s3-endpoint=s3.amazonaws.com
repo2-path=/pgbackrest
# Retention policy
repo1-retention-full=4 # keep 4 full backups
repo1-retention-diff=14 # keep 14 differential backups
# Compression (zstd recommended: balanced speed and ratio)
compress-type=zstd
compress-level=3
# Encryption
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=MyStrongEncryptionPassphrase
# Parallel processes (half of CPU core count recommended)
process-max=4
# Logging
log-level-console=info
log-level-file=detail
log-path=/var/log/pgbackrest
[main] # stanza name
pg1-path=/var/lib/postgresql/17/main
pg1-user=postgres
pg1-port=5432

# Add to postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica

# Initialize stanza (one-time)
sudo -u postgres pgbackrest --stanza=main stanza-create
# Validate configuration
sudo -u postgres pgbackrest --stanza=main check
# Full backup
sudo -u postgres pgbackrest --stanza=main --type=full backup
# Differential backup (changes since last full backup)
sudo -u postgres pgbackrest --stanza=main --type=diff backup
# Incremental backup (changes since last backup)
sudo -u postgres pgbackrest --stanza=main --type=incr backup
# List backups
sudo -u postgres pgbackrest --stanza=main info
# Restore to latest backup
sudo -u postgres pgbackrest --stanza=main restore
# PITR to a specific point in time
sudo -u postgres pgbackrest --stanza=main restore \
--type=time \
--target="2026-04-14 14:34:59+09" \
--target-action=promote
# Delta restore (restore only changed files → faster)
sudo -u postgres pgbackrest --stanza=main restore --delta
# Verify WAL archive
sudo -u postgres pgbackrest --stanza=main check \
--archive-timeout=60

# crontab -u postgres -e
# Every Sunday at 1 AM: full backup
0 1 * * 0 pgbackrest --stanza=main --type=full backup
# Every day at 1 AM except Sunday: differential backup
0 1 * * 1-6 pgbackrest --stanza=main --type=diff backup
# Every hour on the hour: incremental backup
0 * * * * pgbackrest --stanza=main --type=incr backup

# Install on Barman server
sudo apt install barman barman-cli
# Install client on PostgreSQL servers
sudo apt install barman-cli

# Barman server: /etc/barman.conf (global config)
[barman]
barman_user = barman
barman_home = /var/lib/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = gzip

# /etc/barman.d/pg-primary.conf (per-server config)
[pg-primary]
description = "Primary PostgreSQL Server"
# PostgreSQL connection info
conninfo = host=pg-primary user=barman dbname=postgres
# Streaming replication connection info
streaming_conninfo = host=pg-primary user=streaming_barman
# Streaming mode settings
backup_method = postgres # use pg_basebackup
streaming_archiver = on # WAL streaming via pg_receivewal
slot_name = barman # replication slot name
create_slot = auto # auto-create slot
# Retention policy
retention_policy = RECOVERY WINDOW OF 7 DAYS
minimum_redundancy = 1
# Compression
compression = gzip

-- Create Barman-dedicated users on the PostgreSQL server
CREATE USER barman WITH SUPERUSER PASSWORD 'barman_pass';
CREATE USER streaming_barman WITH REPLICATION PASSWORD 'streaming_pass';

# Full server health check
barman check pg-primary
# Sample output:
# Server pg-primary:
# PostgreSQL: OK
# is_superuser: OK
# streaming replication: OK
# wal_level: OK
# replication slot: OK
# archive_mode: OK
# continuous archiving: OK
# Run a backup
barman backup pg-primary
# List backups
barman list-backup pg-primary
# Show backup details
barman show-backup pg-primary latest
# PITR to a specific point in time
barman recover pg-primary latest \
/var/lib/postgresql/17/main \
--target-time "2026-04-14 14:34:59" \
--remote-ssh-command "ssh postgres@pg-primary"
# Recover to a specific transaction ID
barman recover pg-primary latest \
/var/lib/postgresql/17/main \
--target-xid 1234567 \
--remote-ssh-command "ssh postgres@pg-primary"
# Delete a backup
barman delete pg-primary 20260414T010000
# Clean up WAL archive (apply retention policy)
barman cron

# Download the latest binary from GitHub Releases
WAL_G_VERSION="v3.0.8"
curl -L "https://github.com/wal-g/wal-g/releases/download/${WAL_G_VERSION}/wal-g-pg-ubuntu-20.04-amd64.tar.gz" \
-o /tmp/wal-g.tar.gz
tar -xzf /tmp/wal-g.tar.gz -C /usr/local/bin/
chmod +x /usr/local/bin/wal-g

# Environment variable config file (envdir approach recommended)
mkdir -p /etc/wal-g.d/env
# AWS S3 settings
echo "s3://my-pg-backups/wal-g" > /etc/wal-g.d/env/WALG_S3_PREFIX
echo "ap-northeast-2" > /etc/wal-g.d/env/AWS_REGION
echo "AKIAIOSFODNN7EXAMPLE" > /etc/wal-g.d/env/AWS_ACCESS_KEY_ID
echo "wJalrXUtnFEMI..." > /etc/wal-g.d/env/AWS_SECRET_ACCESS_KEY
# Compression method (zstd or brotli recommended)
echo "zstd" > /etc/wal-g.d/env/WALG_COMPRESSION_METHOD
# Max delta steps between full backups (required for delta backups to work)
echo "5" > /etc/wal-g.d/env/WALG_DELTA_MAX_STEPS
# Parallel upload/download count
echo "4" > /etc/wal-g.d/env/WALG_UPLOAD_CONCURRENCY
echo "4" > /etc/wal-g.d/env/WALG_DOWNLOAD_CONCURRENCY
# PGDATA path
echo "/var/lib/postgresql/17/main" > /etc/wal-g.d/env/PGDATA
chown -R postgres:postgres /etc/wal-g.d/env
chmod 600 /etc/wal-g.d/env/*

# Add to postgresql.conf
archive_mode = on
archive_command = 'envdir /etc/wal-g.d/env wal-g wal-push %p'
wal_level = replica

# Full base backup (pushed directly to S3)
sudo -u postgres envdir /etc/wal-g.d/env \
wal-g backup-push /var/lib/postgresql/17/main
# Delta backup (changed files only)
# WALG_DELTA_MAX_STEPS must be set for this to take effect
sudo -u postgres envdir /etc/wal-g.d/env \
wal-g backup-push --full=false /var/lib/postgresql/17/main
# List backups
sudo -u postgres envdir /etc/wal-g.d/env wal-g backup-list
# Restore to latest backup
sudo -u postgres envdir /etc/wal-g.d/env \
wal-g backup-fetch /var/lib/postgresql/17/main LATEST
# Restore to a specific backup by name
sudo -u postgres envdir /etc/wal-g.d/env \
wal-g backup-fetch /var/lib/postgresql/17/main base_20260414T010000Z
# Apply retention policy (keep last 7 full backups)
sudo -u postgres envdir /etc/wal-g.d/env \
wal-g delete retain FULL 7 --confirm
# Verify WAL archive integrity
sudo -u postgres envdir /etc/wal-g.d/env \
wal-g wal-verify integrity timeline

# postgresql.conf: PITR recovery settings
restore_command = 'envdir /etc/wal-g.d/env wal-g wal-fetch %f %p'
recovery_target_time = '2026-04-14 14:34:59 Asia/Seoul'
recovery_target_action = 'pause'

pgBackRest (block-level incremental)
Full backup: 1 TB
Incremental backup: only changed 8 KB blocks → can reach single-digit GB
Barman / WAL-G (file-level incremental)
Full backup: 1 TB
Incremental backup: entire changed files → limited benefit when files are large

Low <------------------------------------> High
WAL-G Barman pgBackRest
(simple setup) (moderate) (feature-rich, complex)