---
title: Database backups
url: https://blog.guigpap.com/en/workflows/database-backup/
url_md: https://blog.guigpap.com/en/workflows/database-backup.md
category: automation
date: '2026-03-28'
maturite: production
techno:
  - postgresql
application:
  - infrastructure
  - operations
---

# Database backups

> Automated nightly backups of PostgreSQL databases to Google Drive with rotation and alerts

## 1. What? — Definition and context

Every night at 3 AM, a script backs up the four PostgreSQL databases of the infrastructure, the Odoo filestore, and the N8N encryption key to Google Drive. If anything fails, an alert fires immediately through the Notification Hub.

> **Note - Rclone**
>
> **Rclone** is a CLI tool that syncs files with cloud services (Google Drive, S3, etc.). It handles chunked uploads for large files and supports automatic rotation of older backups.

### What is backed up

| Data | Source | Typical size |
|------|--------|--------------|
| N8N database | n8n-stack PostgreSQL | ~500 MB |
| Odoo database (`guig_db`) | odoo-stack PostgreSQL | ~200 MB |
| TimeTrackr database | timetrackr-stack PostgreSQL | ~10 MB |
| Odoo filestore | `/var/lib/odoo/filestore/` | ~1 GB |
| N8N key | `N8N_ENCRYPTION_KEY` | A few bytes |

---

## 2. Why? — Stakes and motivations

### Problems solved

| Problem | Without auto backup | With auto backup |
|---------|---------------------|------------------|
| **Data loss** | DB corruption = everything gone | Restore from GDrive |
| **Forgotten backups** | "When was the last one?" | Daily 3 AM cron |
| **No retention** | Local disk fills up | Local rotation 7 d, GDrive 30 d |
| **Silent failure** | Backup fails, nobody knows | Instant Telegram alert |

---

## 3. How? — Technical implementation

### Architecture

```mermaid
flowchart TD
  Cron["Host cron · 03:00 daily"]

  subgraph Script["scripts/backup-databases.sh"]
    direction TB
    Dump["pg_dump · 3 DB via docker exec"]
    GzDB["gzip dumps"]
    Filestore["tar Odoo filestore"]
    Key["Export N8N_ENCRYPTION_KEY"]
    Upload["rclone copy → GDrive"]
    LocalRot["Local rotation · -7 d"]
    RemoteRot["GDrive rotation · -30 d"]
  end

  Local["/backups/ · retention 7 d"]
  GDrive["GDrive · retention 30 d"]
  Hub["Notification Hub · alert on failure"]

  Cron --> Dump --> GzDB --> Filestore --> Key --> Local
  Local --> Upload --> GDrive
  Upload --> LocalRot --> RemoteRot
  Script -.->|failure| Hub
```

### The `backup-databases.sh` script

The script is triggered by crontab at 3 AM (`0 3 * * *`). It runs in order:

1. **PostgreSQL dump** — `pg_dump` of every database via `docker exec`
2. **Compression** — gzip of the dumps
3. **Filestore copy** — tar archive of the Odoo filestore
4. **N8N key export** — extracts `N8N_ENCRYPTION_KEY` from environment variables
5. **GDrive upload** — `rclone copy` to the remote folder
6. **Local rotation** — deletes files older than 7 days
7. **GDrive rotation** — deletes files older than 30 days

If a step fails, the script keeps going and sends an error notification to the [Notification Hub](/en/workflows/notification-hub/) at the end.

### Retention policy

| Storage | Retention | Rotation |
|---------|-----------|----------|
| Local (`/backups/`) | 7 days | Daily script |
| Google Drive | 30 days | rclone delete `--min-age 30d` |

### Verification

```bash
# View the latest local backups
ls -lh /backups/

# View the logs
tail -20 /var/log/backup-databases.log

# Check the cron entry
crontab -l | grep backup
```

> **Caution - N8N encryption key**
>
> The `N8N_ENCRYPTION_KEY` is required to decrypt the credentials stored in the N8N database. Without it, imported workflows cannot reach external services. It is backed up in a separate file on GDrive.

---

## 4. What if? — Perspectives and limits

### Current limits

| Limit | Impact | Mitigation |
|-------|--------|------------|
| **No restore test** | Issues surface at the worst possible moment | Monthly test planned |
| **GDrive single point** | If GDrive is down, no remote backup | Add a second provider (S3) |
| **No incremental backup** | Full dump every night | Acceptable at current volumes |

### Evolution scenarios

**If data volume grows**:
- Switch to incremental backups (PostgreSQL WAL archiving)
- Compress with zstd instead of gzip (faster, better ratio)

**If fast restoration is required**:
- Automated restore script
- Weekly automatic restore test in an ephemeral container

---

## Related pages

### Infrastructure
- [VPS Architecture](/en/infrastructure/architecture-vps/) — Stacks overview
- [N8N in queue mode](/en/infrastructure/n8n-queue-mode/) — N8N database backed up
- [Odoo 18 on Docker](/en/infrastructure/odoo-18-setup/) — Odoo database backed up

### Workflows
- [Notification Hub](/en/workflows/notification-hub/) — Failure alerts
- [N8N export to GitHub](/en/workflows/n8n-export-github/) — Complementary workflow backup

## Metadonnees agent

- Cet article est issu du blog GuiGPaP Lab.
- Contexte global du blog: https://blog.guigpap.com/llms.txt
- Contact auteur: https://odoo.guigpap.com/mon-cv
- Licence: CC-BY-SA 4.0