Backup و Recovery در PostgreSQL

Backup و Recovery از مهم‌ترین مسئولیت‌های یک DBA است. در این فصل با ابزارهای بک‌آپ pg_dump، pg_basebackup، تکنیک Point-in-Time Recovery (PITR)، ابزارهای حرفه‌ای مثل pgBackRest و WAL-G و استراتژی‌های بک‌آپ پروداکشن آشنا می‌شویم.

انواع Backup

pg_dump – Backup منطقی

فرمت‌های pg_dump

# 1. Plain SQL - readable، با psql restore
pg_dump -U postgres -d shopdb > shopdb.sql

# 2. Custom format (پیشنهاد) - فشرده، parallel restore
pg_dump -U postgres -d shopdb -F c -f shopdb.backup

# 3. Directory format - parallel dump و restore
pg_dump -U postgres -d shopdb -F d -j 4 -f shopdb_dir

# 4. Tar format
pg_dump -U postgres -d shopdb -F t -f shopdb.tar

گزینه‌های مهم

# با timestamp در نام فایل
pg_dump -U postgres 
  --host=localhost 
  --format=custom 
  --compress=9 
  --verbose 
  --no-owner 
  --no-privileges 
  --file=shopdb_$(date +%Y%m%d_%H%M%S).backup 
  shopdb

# فقط schema (بدون داده)
pg_dump -U postgres -d shopdb -s -f shopdb_schema.sql

# فقط داده (بدون schema)
pg_dump -U postgres -d shopdb -a -f shopdb_data.sql

# فقط جدول‌های خاص
pg_dump -U postgres -d shopdb -t products -t orders -f partial.sql

# همه به‌جز جدول‌های خاص
pg_dump -U postgres -d shopdb -T audit_log -T temp_data -f main.sql

# فقط schema خاص
pg_dump -U postgres -d shopdb -n public -f public_only.sql

parallel dump

# فقط با directory format کار می‌کند
pg_dump -U postgres -d shopdb 
  -F d 
  -j 8                         # 8 worker موازی
  -f shopdb_dir

# 8x سریع‌تر برای دیتابیس بزرگ

pg_dumpall – همه دیتابیس‌ها

# بک‌آپ کامل cluster (همه دیتابیس‌ها + role‌ها + tablespace‌ها)
pg_dumpall -U postgres -f cluster_backup.sql

# فقط globals (role‌ها، tablespace‌ها)
pg_dumpall -U postgres -g -f globals.sql

Restore

از Plain SQL

# ساخت دیتابیس جدید
createdb -U postgres shopdb_new

# restore
psql -U postgres -d shopdb_new -f shopdb.sql

از Custom Format با pg_restore

# restore ساده
pg_restore -U postgres -d shopdb_new shopdb.backup

# parallel restore
pg_restore -U postgres -d shopdb_new -j 8 shopdb.backup

# verbose + clean (drop قبل از create)
pg_restore -U postgres -d shopdb_new 
  --verbose 
  --clean 
  --if-exists 
  shopdb.backup

# فقط schema
pg_restore -U postgres -d shopdb_new -s shopdb.backup

# فقط داده (schema باید قبلاً ساخته شده باشد)
pg_restore -U postgres -d shopdb_new -a shopdb.backup

# فقط یک جدول
pg_restore -U postgres -d shopdb_new -t products shopdb.backup

# لیست محتویات بدون restore
pg_restore -l shopdb.backup > backup_contents.txt

# انتخابی restore
pg_restore -U postgres -d shopdb_new -L selected.txt shopdb.backup

pg_basebackup – Backup فیزیکی

کپی byte-level از کل cluster، مبنای PITR:

# بک‌آپ کامل
pg_basebackup 
  -h localhost 
  -U replicator 
  -D /backups/base_$(date +%Y%m%d) 
  --format=tar 
  --gzip 
  --progress 
  --verbose 
  --checkpoint=fast 
  --write-recovery-conf

# فایل‌های خروجی:
# base.tar.gz   - کل cluster
# pg_wal.tar.gz - WAL files

Restore از pg_basebackup

# متوقف کردن سرور
sudo systemctl stop postgresql

# پاک کردن data directory
sudo rm -rf /var/lib/postgresql/16/main/*

# extract
sudo tar -xzf base.tar.gz -C /var/lib/postgresql/16/main/
sudo tar -xzf pg_wal.tar.gz -C /var/lib/postgresql/16/main/pg_wal/

# تنظیم مالکیت
sudo chown -R postgres:postgres /var/lib/postgresql/16/main/

# شروع
sudo systemctl start postgresql

Continuous WAL Archiving

برای PITR (Point-in-Time Recovery)، باید همه WAL‌ها archive شوند:

# postgresql.conf
wal_level = replica                        # یا logical
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

# %p = path WAL فعلی
# %f = filename فقط
# archive_command باید 0 برگرداند در صورت موفقیت

archive_command پیشرفته

# کپی به remote storage با rsync
archive_command = 'rsync -a %p backup-server:/archive/%f'

# آپلود به S3
archive_command = 'aws s3 cp %p s3://my-bucket/wal/%f'

# با pgBackRest
archive_command = 'pgbackrest --stanza=main archive-push %p'

Point-in-Time Recovery

با base backup + WAL archive می‌توان به هر لحظه از گذشته بازگشت:

# 1. متوقف کردن سرور خراب
sudo systemctl stop postgresql

# 2. backup data فعلی (در صورت لزوم)
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_broken

# 3. extract base backup
sudo mkdir /var/lib/postgresql/16/main
sudo tar -xzf base.tar.gz -C /var/lib/postgresql/16/main/

# 4. recovery configuration
sudo touch /var/lib/postgresql/16/main/recovery.signal

# در postgresql.conf اضافه کنید
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-04-30 14:30:00 UTC'    # زمان دقیق

# یا
recovery_target_xid = '12345'                       # transaction id
recovery_target_name = 'before_disaster'            # named savepoint
recovery_target = 'immediate'                       # تا آخرین consistent state

recovery_target_action = 'promote'                  # بعد از recovery، promote شود

# 5. شروع سرور
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo systemctl start postgresql

# سرور WAL‌ها را replay می‌کند تا زمان مورد نظر

Named Restore Points

-- قبل از deploy خطرناک، یک نقطه نشانه‌گذاری
SELECT pg_create_restore_point('before_deploy_v2');
-- اگر مشکل پیش آمد:
-- recovery_target_name = 'before_deploy_v2'

pgBackRest – ابزار حرفه‌ای

pgBackRest پیشرفته‌ترین ابزار open-source برای backup PostgreSQL است:

• Parallel compression و backup
• Incremental و differential backup
• Encryption
• S3/Azure/GCS support
• Backup verification
• Retention policy خودکار

sudo apt install pgbackrest

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=secret
process-max=4
log-level-console=info

[main]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
pg1-user=postgres

# ساخت stanza
sudo -u postgres pgbackrest --stanza=main stanza-create

# Full backup
sudo -u postgres pgbackrest --stanza=main backup --type=full

# Differential backup (سریع‌تر)
sudo -u postgres pgbackrest --stanza=main backup --type=diff

# Incremental backup (سریع‌ترین)
sudo -u postgres pgbackrest --stanza=main backup --type=incr

# لیست بک‌آپ‌ها
sudo -u postgres pgbackrest info

# Restore
sudo -u postgres pgbackrest --stanza=main restore

# PITR
sudo -u postgres pgbackrest --stanza=main 
  --type=time 
  --target='2026-04-30 14:30:00' 
  restore

WAL-G – cloud-native

WAL-G از Yandex بهینه برای cloud (S3، GCS، Azure):

go install github.com/wal-g/wal-g/cmd/pg@latest

# config
export WALG_S3_PREFIX=s3://my-backup-bucket/postgres
export AWS_ACCESS_KEY_ID=...
export AWS_SECRET_ACCESS_KEY=...

# در postgresql.conf
archive_command = 'wal-g wal-push %p'
restore_command = 'wal-g wal-fetch %f %p'

# گرفتن backup
wal-g backup-push /var/lib/postgresql/16/main

# لیست
wal-g backup-list

# restore
wal-g backup-fetch /var/lib/postgresql/16/main LATEST

استراتژی‌های پروداکشن

قاعده 3-2-1

• 3 نسخه از داده
• 2 رسانه مختلف
• 1 off-site

برنامه نمونه

RPO و RTO

• RPO (Recovery Point Objective): چقدر داده می‌توانیم از دست بدهیم؟ – با continuous WAL archiving، چند دقیقه
• RTO (Recovery Time Objective): چقدر طول می‌کشد بازیابی؟ – بستگی به اندازه داده دارد

تست بک‌آپ – حیاتی!

# script ماهانه برای تست
#!/bin/bash
set -e

# 1. restore به دیتابیس test
sudo -u postgres pgbackrest --stanza=main 
  --pg1-path=/tmp/test_restore 
  restore

# 2. شروع سرور test
pg_ctl -D /tmp/test_restore -o "-p 5433" start

# 3. تست داده
psql -p 5433 -c "SELECT count(*) FROM products;"

# 4. cleanup
pg_ctl -D /tmp/test_restore stop
rm -rf /tmp/test_restore

Backup خودکار با cron

# /etc/cron.d/postgres-backup
# هر روز ساعت 2 صبح
0 2 * * * postgres pgbackrest --stanza=main backup --type=incr

# یکشنبه‌ها full backup
0 1 * * 0 postgres pgbackrest --stanza=main backup --type=full

# روزانه رفع expired
0 3 * * * postgres pgbackrest --stanza=main expire

monitoring بک‌آپ

# بررسی آخرین backup
sudo -u postgres pgbackrest info --stanza=main

# alert اگر backup ساعت‌ها قدیمی است
# در Prometheus + Grafana

-- بررسی archiving در PG
SELECT * FROM pg_stat_archiver;
-- archived_count: تعداد WAL archive شده
-- failed_count: تعداد ناموفق
-- last_archived_time: آخرین زمان موفق

بهترین شیوه‌ها

• backup در محل جداگانه از سرور اصلی
• off-site برای DR (مثلاً S3 در منطقه دیگر)
• encrypt کردن بک‌آپ‌ها
• monitoring تناوب backup
• تست restore ماهانه
• retention policy واضح
• مستندسازی روند restore
• WAL archiving برای minimal RPO
• pgBackRest یا WAL-G برای پروداکشن جدی

جمع‌بندی

• pg_dump: logical، انتخابی، cross-version
• pg_basebackup: physical، کل cluster، مبنای PITR
• WAL archiving + base backup = PITR
• pgBackRest: ابزار حرفه‌ای، incremental، parallel
• WAL-G: cloud-native، S3
• قاعده 3-2-1: ۳ نسخه، ۲ رسانه، ۱ off-site
• بک‌آپ بدون تست = بی‌اعتبار