Imagine this scenario, we have one or more database servers, one backup server. How do we setup automatic backup server?

Let's try with this stupid strategy. It not stupid if this work, right?

  • Use pg_dump for logical backups (better for smaller databases and easier to restore selectively)
  • Include timestamp in backup names
  • Compress the backup to save space
  • Handle error logging
  • Clean up old backups

This is the script we will use. Please change the credential and adjust the host. Since I am using docker, you might need to adjust it also.

#!/bin/bash

# Backup configuration
BACKUP_DIR="/path/to/local/backup"
POSTGRES_CONTAINER="your-postgres-container-name"
DB_USER=""
DB_PASSWORD=""
DB_NAME="your_database_name"
BACKUP_RETENTION_DAYS=7

# Remote backup configuration
REMOTE_USER="backup_user"
REMOTE_HOST="your.backup.server"
REMOTE_DIR="/path/to/remote/backup"

# Create timestamp
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILENAME="postgres_${DB_NAME}_${TIMESTAMP}.sql.gz"

# Ensure backup directory exists
mkdir -p ${BACKUP_DIR}

# Create backup
echo "Starting backup of ${DB_NAME}"
docker exec -e PGPASSWORD="${DB_PASSWORD}" ${POSTGRES_CONTAINER} \
    pg_dump -h localhost -U ${DB_USER} ${DB_NAME} | gzip > ${BACKUP_DIR}/${BACKUP_FILENAME}

# Check if backup was successful
if [ $? -eq 0 ]; then
    echo "Database backup completed: ${BACKUP_FILENAME}"
    
    # Sync to remote backup server
    rsync -avz --remove-source-files ${BACKUP_DIR}/${BACKUP_FILENAME} ${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_DIR}/
    
    # Clean up old backups (both local and remote)
    find ${BACKUP_DIR} -type f -name "postgres_${DB_NAME}_*.sql.gz" -mtime +${BACKUP_RETENTION_DAYS} -delete
    ssh ${REMOTE_USER}@${REMOTE_HOST} "find ${REMOTE_DIR} -type f -name 'postgres_${DB_NAME}_*.sql.gz' -mtime +${BACKUP_RETENTION_DAYS} -delete"
else
    echo "Error creating backup ${BACKUP_FILENAME}"
    exit 1
fi

To do it automatically, I am using cron job. Hit cronbtab -e.

# Add this line to run backup daily at 2 AM
0 2 * * * /path/to/backup_script.sh >> /var/log/postgres_backup.log 2>&1

You might want generate ssh if yo don't have any from your database server

# Generate SSH key
ssh-keygen -t ed25519 -C "backup@primary-server"

Add it to your backup server

# Copy key to backup server
ssh-copy-id backup@primary-server

Test the restore process regularly

gunzip -c backup_file.sql.gz | docker exec -i postgres-container psql -U postgres -d database_name

And it works!