PostgreSQL Regular Backup Instructions Using pg_dump

1. Prerequisites

• Install PostgreSQL Client Tools:

  # For Debian/Ubuntu
  sudo apt-get update
  sudo apt-get install postgresql-client
  
  # For CentOS/RHEL
  sudo yum install postgresql

• Ensure Database Access:

  • The backup user must have read permissions on the target database.

2. Configure Password-less Authentication

1. Create .pgpass File:

   touch ~/.pgpass
   chmod 600 ~/.pgpass

2. Add Database Credentials:

   hostname:port:database:username:password

   Example:

   localhost:5432:your_database:your_username:your_password

3. Create Backup Script

Example Script (backup_postgres.sh):

#!/bin/bash

# Variables
DB_NAME="your_database"
DB_USER="your_username"
BACKUP_DIR="/path/to/backup"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql.gz"
RETENTION_DAYS=30
EMAIL="admin@example.com"

# Create Backup Directory
mkdir -p "$BACKUP_DIR"

# Perform Backup
pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$BACKUP_FILE"

# Check Backup Success
if [ $? -eq 0 ]; then
    echo "Backup successful: $BACKUP_FILE"
else
    echo "Backup failed for $DB_NAME" | mail -s "PostgreSQL Backup Failure" "$EMAIL"
    exit 1
fi

# Remove Old Backups
find "$BACKUP_DIR" -type f -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -exec rm {} \;

# Optional: Log the Backup
echo "$(date): Backup completed for $DB_NAME" >> "$BACKUP_DIR/backup.log"

• Make Script Executable:

  chmod +x /path/to/backup_postgres.sh

4. Schedule Backups with Cron

1. Edit Crontab:

   crontab -e

2. Add Cron Job (Daily at 2 AM):

   0 2 * * * /path/to/backup_postgres.sh >> /path/to/backup.log 2>&1

5. Verify Backups

• Check Backup Directory:

  Ensure new .sql.gz files are appearing as scheduled.

• Test a Backup Restoration:

  createdb test_restore_db
  gunzip -c /path/to/backup/your_database_YYYYMMDD_HHMMSS.sql.gz | psql -U your_username -d test_restore_db

  • Verify Data:

    psql -U your_username -d test_restore_db -c "\\dt"

  • Remove Test Database:

    dropdb test_restore_db

6. Optional: Secure and Offsite Storage

• Encrypt Backups (Using GPG):

  gpg --symmetric --cipher-algo AES256 "$BACKUP_FILE"
  rm "$BACKUP_FILE"

• Sync to Remote Server:

  Add to the backup script:

  rsync -avz "$BACKUP_FILE.gpg" remote_user@remote_host:/remote/backup/dir/

  • Ensure SSH keys are set up for password-less access.

7. Monitoring and Alerts

• Check Logs Regularly:

  Review /path/to/backup.log for backup statuses.

• Set Up Email Alerts:

  Ensure the script sends emails on failure as shown in the backup script.

Restoration Steps

1. Create a New Database:

   createdb restored_db

2. Restore from Backup:

   gunzip -c /path/to/backup/your_database_YYYYMMDD_HHMMSS.sql.gz | psql -U your_username -d restored_db

   • If Encrypted:

     gpg --decrypt /path/to/backup/your_database_YYYYMMDD_HHMMSS.sql.gz.gpg | gunzip | psql -U your_username -d restored_db

3. Verify Restoration:

   psql -U your_username -d restored_db -c "\\dt"

4. Remove Test Database (if applicable):

   dropdb restored_db