Set Up Automated Database Backups with pg_dump on a Cron Schedule

Your workflows, credentials, and execution history live in PostgreSQL. Losing this database means rebuilding everything from scratch. A daily pg_dump piped to compressed storage with retention policies is the minimum viable backup strategy. Store backups off-host -- an S3-compatible bucket is the standard choice.

Real-world example: You run a daily backup at 02:00 UTC, compress it with gzip, upload to S3, and automatically delete backups older than 30 days.

#!/usr/bin/env bash
set -euo pipefail

# Configuration

BACKUP_DIR="/opt/n8n-backups"
S3_BUCKET="s3://my-company-n8n-backups"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="n8n_backup_${TIMESTAMP}.sql.gz"

# Create backup directory if it does not exist

mkdir -p "${BACKUP_DIR}"

# Dump the database from the running PostgreSQL container

docker exec n8n-postgres-1 pg_dump \
  -U n8n_user \
  -d n8n_db \
  --no-owner \
  --clean \
  --if-exists | gzip > "${BACKUP_DIR}/${BACKUP_FILE}"

# Verify the backup file is not empty

if [ ! -s "${BACKUP_DIR}/${BACKUP_FILE}" ]; then
  echo "ERROR: Backup file is empty" >&2
  exit 1
fi

# Upload to S3

aws s3 cp "${BACKUP_DIR}/${BACKUP_FILE}" "${S3_BUCKET}/${BACKUP_FILE}"

# Remove local backup after successful upload

rm -f "${BACKUP_DIR}/${BACKUP_FILE}"

# Delete remote backups older than retention period

aws s3 ls "${S3_BUCKET}/" | while read -r line; do
  file_date=$(echo "$line" | awk '{print $1}')
  file_name=$(echo "$line" | awk '{print $4}')
  file_epoch=$(date -d "$file_date" +%s 2>/dev/null || date -j -f "%Y-%m-%d" "$file_date" +%s)
  cutoff_epoch=$(date -d "-${RETENTION_DAYS} days" +%s 2>/dev/null || date -v-${RETENTION_DAYS}d +%s)
  if [ "$file_epoch" -lt "$cutoff_epoch" ]; then
    aws s3 rm "${S3_BUCKET}/${file_name}"
    echo "Deleted old backup: ${file_name}"
  fi
done

echo "Backup completed: ${BACKUP_FILE}"
```text
```cron title="crontab -e"

# Daily n8n database backup at 02:00 UTC

0 2 * * * /opt/scripts/backup-n8n-db.sh >> /var/log/n8n-backup.log 2>&1
```text
> **Warning: Test Your Restores**
>
> A backup that has never been restored is not a backup. Test the restore process quarterly by spinning up a temporary PostgreSQL container and importing a recent dump:
> ```bash
> gunzip -c n8n_backup_20250115_020000.sql.gz | \
>   docker exec -i test-postgres psql -U n8n_user -d n8n_db
> ```

This gives you daily point-in-time recovery capability with 30 days of history and off-site storage.

**Related:** [Set a Unique Encryption Key and Back It Up](../security-best-practices/01-set-a-unique-encryption-key-and-back-it-up.md) | [Configure Payload Size and Binary Data Mode for Large Files](../performance-and-large-files/01-configure-payload-size-and-binary-data-mode-for-large-files.md)