How to Configure Automatic PostgreSQL Backup (n8n) – Aplichost
Objective: This guide explains how to configure automatic backups for the PostgreSQL database used by n8n.
What This Setup Includes
- Daily automatic backup
- Compressed file (.sql.gz)
- 7-day retention policy
- Automatic cron execution
- Backup logging
Prerequisites
- Root access to the server
- PostgreSQL installed
- Existing database (e.g.
n8n_db) - Database user created (e.g.
n8n_user)
Step 1 — Create Secure Backup Directory
mkdir -p /backup/postgres
chown postgres:postgres /backup/postgres
chmod 700 /backup/postgresStep 2 — Create Backup Script
nano /usr/local/bin/backup_n8n.shInsert:
#!/bin/bash
DATE=$(date +"%Y-%m-%d_%H-%M")
BACKUP_DIR="/backup/postgres"
DB_NAME="n8n_db"
DB_USER="n8n_user"
export PGPASSWORD='@senha2026_secure'
pg_dump -U $DB_USER -h 127.0.0.1 $DB_NAME | gzip > $BACKUP_DIR/n8n_$DATE.sql.gz
find $BACKUP_DIR -type f -mtime +7 -deleteStep 3 — Make Script Executable
chmod +x /usr/local/bin/backup_n8n.shStep 4 — Test Backup
/usr/local/bin/backup_n8n.sh
ls -lh /backup/postgresStep 5 — Schedule Daily Cron Job
crontab -eAdd:
0 3 * * * /usr/local/bin/backup_n8n.sh >> /var/log/n8n_backup.log 2>&1How to Restore
gunzip backupfile.sql.gz
psql -U n8n_user -h 127.0.0.1 n8n_db < backupfile.sqlProduction Environment – Aplichost
