Manual PostgreSQL backups become unsustainable as your database grows. Weekly backups might work for small projects, but production systems demand daily (or even hourly) protection. The risks of data loss increase exponentially when relying on human memory for critical operations.

PostgreSQL offers multiple built-in backup methods we can automate:

1. pg_dump (logical backups)
2. pg_basebackup (physical backups)
3. Continuous Archiving (WAL-based)

Here's a robust bash script example for daily backups:

#!/bin/bash
DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/var/backups/postgres"
DB_NAME="production_db"
PG_USER="postgres"

# Create backup directory if not exists
mkdir -p $BACKUP_DIR

# Execute backup
pg_dump -U $PG_USER -Fc $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# Optional: compress older backups
find $BACKUP_DIR -name "*.dump" -mtime +30 -exec gzip {} \;

# Cleanup backups older than 90 days
find $BACKUP_DIR -name "*.dump.gz" -mtime +90 -delete

For systemd-based systems (recommended for better logging):

[Unit]
Description=PostgreSQL Daily Backup

[Service]
Type=oneshot
ExecStart=/usr/local/bin/pg_backup_script.sh

[Install]
WantedBy=multi-user.target

Then create a timer unit:

[Unit]
Description=Run PostgreSQL backup daily

[Timer]
OnCalendar=daily
Persistent=true

[Install]
WantedBy=timers.target

For mission-critical systems, combine daily backups with WAL archiving:

# postgresql.conf settings:
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'

Never trust unverified backups. Implement checks:

#!/bin/bash
# Test restore on a temporary database
TEMPDB="backup_test_$(date +%s)"
createdb $TEMPDB
pg_restore -d $TEMPDB latest_backup.dump
# Run sanity checks...
psql -d $TEMPDB -c "SELECT count(*) FROM important_table;" > /dev/null
# Cleanup
dropdb $TEMPDB

Extend the script to upload to AWS S3:

#!/bin/bash
# After local backup completes
aws s3 cp $BACKUP_DIR/${DB_NAME}_${DATE}.dump s3://your-bucket/postgres-backups/
# Set lifecycle policy in S3 to automatically expire old backups

Modify the script to notify on errors:

if ! pg_dump -U $PG_USER -Fc $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump; then
    echo "PostgreSQL backup failed!" | mail -s "Backup Alert" admin@example.com
    exit 1
fi

Manual database backups become unsustainable as systems scale. The risks of human error and missed backups increase exponentially with daily operations. PostgreSQL's robust utilities like pg_dump and Write-Ahead Logging (WAL) are specifically designed for automated backup scenarios.

# Basic pg_dump command template
pg_dump -U username -h hostname -Fc database_name > backup_file.dump

# Parallel dump for large databases (PostgreSQL 9.3+)
pg_dump -j 8 -U postgres -Fd mydb -f /backups/mydb

For Linux/Unix systems, cron remains the most reliable scheduler. Create a shell script (/usr/local/bin/pg_backup.sh) with proper permissions:

#!/bin/bash
DATE=$(date +%Y-%m-%d)
PGPASSWORD="yourpassword" pg_dump -U postgres -h localhost -Fc mydb > /backups/mydb_$DATE.dump
find /backups -name "*.dump" -mtime +30 -delete

Then configure the cron job:

# Edit crontab
crontab -e

# Add this line for 2 AM daily backups
0 2 * * * /usr/local/bin/pg_backup.sh

For mission-critical systems, implement WAL archiving in postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'

For AWS environments, integrate with S3:

#!/bin/bash
DATE=$(date +%Y-%m-%d)
FILE="/backups/mydb_$DATE.dump"
pg_dump -U postgres mydb > $FILE
aws s3 cp $FILE s3://your-bucket/postgres-backups/

Always implement backup verification. A simple checksum test:

# After backup creation
sha256sum /backups/mydb_$DATE.dump > /backups/mydb_$DATE.sha256

# For verification
sha256sum -c /backups/mydb_$DATE.sha256

For enterprise-grade solutions, consider pgBackRest:

# Installation
sudo apt-get install pgbackrest

# Configuration (/etc/pgbackrest.conf)
[mydb]
pg1-path=/var/lib/postgresql/12/main

[global]
repo1-path=/backups
repo1-retention-full=7