Skip to main content

🤖 Backup Strategies

PostgreSQL ma data protection bahuj important che. Different types of backups che ane darek ni specific use cases che.

Types of Backups​

Logical Backups​

Logical backups ma SQL statements export thay che je data recreate kare che.

pg_dump​

Single database backup mate pg_dump use karo:

# Simple backup - SQL format
pg_dump dbname > dbname_backup.sql

# Compression sathe (space save thay)
pg_dump dbname | gzip > dbname_backup.sql.gz

# Custom format (recommended - restore options vadhare che)
pg_dump -Fc dbname > dbname_backup.dump

# Directory format (parallel backup - fast)
pg_dump -Fd dbname -j 4 -f backup_directory

pg_dumpall​

Badha databases ane roles backup mate:

# All databases backup
pg_dumpall > all_databases_backup.sql

# Faqat roles ane global objects
pg_dumpall --globals-only > globals_backup.sql

Kada use karvu: Cross-version compatibility, selective restore, small to medium databases

Physical Backups​

Physical backups ma actual database files copy thay che.

pg_basebackup​

# Basic base backup
pg_basebackup -D /backup/base -Ft -z -P

# WAL files sathe (recommended)
pg_basebackup -D /backup/base -Ft -z -P -X stream

# Transfer rate limit (network load control karva mate)
pg_basebackup -D /backup/base -Ft -z -P -X stream --max-rate=100M

Benefits: Fast restore, PITR support, full cluster backup

File System Level Backup​

# PostgreSQL stop karo
sudo systemctl stop postgresql-16

# Data directory backup levo
tar -czf /backup/pgdata_backup.tar.gz /var/lib/postgresql/16/main/

# Service start karo
sudo systemctl start postgresql-16

⚠️ Warning: Aa method downtime chahiye - production ma avoid karo

WAL Archiving (Continuous Backup)​

WAL archiving thi point-in-time recovery possible bane che.

Configuration​

postgresql.conf ma configure karo:

# WAL archiving enable karo
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
archive_timeout = 60

# Archive directory create karo
mkdir -p /mnt/wal_archive
chown postgres:postgres /mnt/wal_archive
chmod 700 /mnt/wal_archive

Backup Schedule Examples​

Daily Backup Script​

#!/bin/bash
# daily_backup.sh

BACKUP_DIR="/backup/daily"
DATE=$(date +%Y%m%d_%H%M%S)
DBNAME="mydb"

# Backup directory create karo
mkdir -p $BACKUP_DIR

# Backup levo
pg_dump -Fc $DBNAME > $BACKUP_DIR/${DBNAME}_${DATE}.dump

# Last 7 days j rakho, old backups delete karo
find $BACKUP_DIR -name "${DBNAME}_*.dump" -mtime +7 -delete

# Log ma entry karo
echo "$(date): Backup completed - ${DBNAME}_${DATE}.dump" >> /var/log/pg_backup.log

Cron ma set karo:

# Roj raat 2 AM pe backup
0 2 * * * /path/to/daily_backup.sh

Weekly Full Backup​

#!/bin/bash
# weekly_basebackup.sh

BACKUP_DIR="/backup/weekly"
DATE=$(date +%Y%m%d)

mkdir -p $BACKUP_DIR/$DATE

# Base backup levo WAL sathe
pg_basebackup -D $BACKUP_DIR/$DATE -Ft -z -P -X stream

# 4 weeks thi juna delete karo
find $BACKUP_DIR -maxdepth 1 -type d -mtime +28 -exec rm -rf {} \;

echo "$(date): Base backup completed - $DATE" >> /var/log/pg_basebackup.log

Best Practices​

  1. 3-2-1 Rule - 3 copies, 2 different media, 1 offsite location
  2. Test Restores - Monthly ek vaari restore test karo
  3. Monitoring - Backup failures mate alerts set karo
  4. Encryption - Sensitive data mate backups encrypt karo
  5. Retention Policy - Clear retention policy define karo
  6. Document - Restore procedures document karo
  7. Automation - Manual mistakes avoid karva mate automate karo

Backup Tools Comparison​

ToolTypeSizeSpeedPITRParallel
pg_dumpLogicalSmallerSlowerNoLimited
pg_basebackupPhysicalLargerFasterYesYes
pgBackRestPhysicalOptimizedFastYesYes
BarmanPhysicalOptimizedFastYesYes
WAL-GPhysicalCompressedFastYesYes

Cloud Backup Examples​

AWS S3​

# S3 ma backup upload karo
pg_dump dbname | gzip | aws s3 cp - s3://my-bucket/backups/dbname_$(date +%Y%m%d).sql.gz

# Restore mate
aws s3 cp s3://my-bucket/backups/dbname_20240101.sql.gz - | gunzip | psql dbname

Azure Blob Storage​

# Azure ma upload
pg_dump dbname | gzip | az storage blob upload \
  --account-name mystorageaccount \
  --container-name backups \
  --name dbname_$(date +%Y%m%d).sql.gz \
  --file -

Google Cloud Storage​

# GCS ma upload
pg_dump dbname | gzip | gsutil cp - gs://my-bucket/backups/dbname_$(date +%Y%m%d).sql.gz

Monitoring Backups​

Backup success check karva mate script:

#!/bin/bash
# check_backups.sh

BACKUP_DIR="/backup/daily"
EXPECTED_FILE="mydb_$(date +%Y%m%d)*.dump"

# Aaje nu backup che ke nai check karo
if ls $BACKUP_DIR/$EXPECTED_FILE 1> /dev/null 2>&1; then
    echo "Backup successful"
    exit 0
else
    echo "ERROR: Backup missing for today!"
    # Alert send karo (email, Slack, etc.)
    exit 1
fi

Next Steps: Recovery procedures shikhva mate (coming soon)