# 260320-Mise en place de la sauvegarde backup des bases de données du VPS

C'est noté. On va droit à l'essentiel pour que tu puisses copier-coller ce "manuel de survie" dans tes notes. Voici la synthèse technique de ta configuration de sauvegarde **VPS Alteris → NAS Synology**.

---

##### 🛠️ 1. Côté VPS (Préparation des données)

**Installation du moteur :**

<div class="code-block ng-tns-c1673804520-510 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-bash"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c1673804520-510 ng-star-inserted"><span class="ng-tns-c1673804520-510">Bash</span><div class="buttons ng-tns-c1673804520-510 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c1673804520-510"><div class="animated-opacity ng-tns-c1673804520-510">  
</div></div></div>```
sudo apt update && sudo apt install rsync -y

```

<div class="code-block ng-tns-c1673804520-510 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--2"><div class="formatted-code-block-internal-container ng-tns-c1673804520-510"><div class="animated-opacity ng-tns-c1673804520-510"></div></div></div>**Script de backup SQL (`/usr/local/bin/docker_db_backup.sh`) :**

<div class="code-block ng-tns-c1673804520-511 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-bash-1"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c1673804520-511 ng-star-inserted"><span class="ng-tns-c1673804520-511">Bash</span><div class="buttons ng-tns-c1673804520-511 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c1673804520-511"><div class="animated-opacity ng-tns-c1673804520-511">  
</div></div></div>```

#!/bin/bash

# --- CONFIGURATION ---

BACKUP_DIR="/backups/sql"
DATE=$(date +%Y-%m-%d)
mkdir -p $BACKUP_DIR

echo "--- Début du backup global : $(date) ---"

# 1. Backup Bookstack (MariaDB) - OK (Utilisateur dédié)
echo "📦 Sauvegarde Bookstack..."
docker exec bookstack_db /usr/bin/mariadb-dump -u bookstack -p'260202ALTERIS!' bookstackapp --single-transaction --quick | gzip > $BACKUP_DIR/bookstack_db_$DATE.sql.gz

# 2. Backup FreshRSS (PostgreSQL) - OK
echo "🐘 Sauvegarde FreshRSS..."
PG_USER_FRESH=$(docker exec freshrss-db printenv POSTGRES_USER || echo "postgres")
docker exec freshrss-db pg_dumpall -U $PG_USER_FRESH | gzip > $BACKUP_DIR/freshrss-db_$DATE.sql.gz

# 3. Backup Alteris PostGIS (PostgreSQL) - OK
echo "🐘 Sauvegarde Alteris PostGIS..."
PG_USER_ALT=$(docker exec alteris_postgis printenv POSTGRES_USER || echo "postgres")
docker exec alteris_postgis pg_dumpall -U $PG_USER_ALT | gzip > $BACKUP_DIR/alteris_postgis_$DATE.sql.gz

# --- PERMISSIONS ET NETTOYAGE ---
# On donne les droits à l'utilisateur du NAS
chown -R synobackup:synobackup $BACKUP_DIR
chmod -R 644 $BACKUP_DIR/*.gz

# Nettoyage : On ne garde que le fichier du jour sur le VPS
find $BACKUP_DIR -type f -mtime +0 -name "*.sql.gz" -not -name "*$DATE*" -delete

echo "--- Fin du backup global : $(date) ---"

```

<div class="code-block ng-tns-c1673804520-511 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--5"><div class="formatted-code-block-internal-container ng-tns-c1673804520-511"><div class="animated-opacity ng-tns-c1673804520-511"></div></div></div>*Note : `chmod +x /usr/local/bin/docker_db_backup.sh` pour l'activer.*

**Automatisation (Crontab `sudo crontab -e`) :**

<div class="code-block ng-tns-c1673804520-512 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-bash-2"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c1673804520-512 ng-star-inserted"><span class="ng-tns-c1673804520-512">Bash</span><div class="buttons ng-tns-c1673804520-512 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c1673804520-512"><div class="animated-opacity ng-tns-c1673804520-512">  
</div></div></div>```
0 2 * * * /usr/local/bin/docker_db_backup.sh

```

<div class="code-block ng-tns-c1673804520-512 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--8"><div class="formatted-code-block-internal-container ng-tns-c1673804520-512"><div class="animated-opacity ng-tns-c1673804520-512"></div></div></div>---

##### 🔑 2. Côté NAS (La liaison SSH sécurisée)

**Génération de la clé compatible Synology (sur le NAS) :**

<div class="code-block ng-tns-c1673804520-513 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-bash-3"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c1673804520-513 ng-star-inserted"><span class="ng-tns-c1673804520-513">Bash</span><div class="buttons ng-tns-c1673804520-513 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c1673804520-513"><div class="animated-opacity ng-tns-c1673804520-513">  
</div></div></div>```
# Création de la clé RSA format PEM
openssl genrsa -out ~/.ssh/id_rsa_syno 4096
chmod 600 ~/.ssh/id_rsa_syno

# Extraction de la clé publique
ssh-keygen -y -f ~/.ssh/id_rsa_syno > ~/.ssh/id_rsa_syno.pub

# Envoi de la clé au VPS (mot de passe synobackup demandé)
cat ~/.ssh/id_rsa_syno.pub | ssh synobackup@79.137.14.202 "mkdir -p ~/.ssh && cat >> ~/.ssh/authorized_keys && chmod 700 ~/.ssh && chmod 600 ~/.ssh/authorized_keys"

```

<div class="code-block ng-tns-c1673804520-513 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--12"><div class="formatted-code-block-internal-container ng-tns-c1673804520-513"><div class="animated-opacity ng-tns-c1673804520-513"></div></div></div>**Récupération de la clé privée pour l'interface DSM :**

<div class="code-block ng-tns-c1673804520-514 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk-bash-4"><div class="code-block-decoration header-formatted gds-title-s ng-tns-c1673804520-514 ng-star-inserted"><span class="ng-tns-c1673804520-514">Bash</span><div class="buttons ng-tns-c1673804520-514 ng-star-inserted"></div></div><div class="formatted-code-block-internal-container ng-tns-c1673804520-514"><div class="animated-opacity ng-tns-c1673804520-514">  
</div></div></div>```
cat ~/.ssh/id_rsa_syno

```

<div class="code-block ng-tns-c1673804520-514 ng-animate-disabled ng-trigger ng-trigger-codeBlockRevealAnimation" id="bkmrk--15"><div class="formatted-code-block-internal-container ng-tns-c1673804520-514"><div class="animated-opacity ng-tns-c1673804520-514"></div></div></div>*(Copier le bloc `-----BEGIN RSA PRIVATE KEY-----` vers un fichier `backup.pem` sur ton PC).*

---

##### 🎡 3. Configuration Active Backup (Interface DSM)

<table id="bkmrk-param%C3%A8tre-valeur-ser"><thead><tr><td>**Paramètre**</td><td>**Valeur**</td></tr></thead><tbody><tr><td><span>**Service**</span></td><td><span>Serveur de fichiers &gt; rsync &gt; **rsync shell**</span></td></tr><tr><td><span>**Hôte / Port**</span></td><td><span>`79.137.14.202` / `22`</span></td></tr><tr><td><span>**Utilisateur**</span></td><td><span>`synobackup`</span></td></tr><tr><td><span>**Authentification**</span></td><td><span>**Clé privée** (Charger `backup.pem`)</span></td></tr><tr><td><span>**Dossier Source 1**</span></td><td><span>`/backups/sql` (Les bases de données)</span></td></tr><tr><td><span>**Dossier Source 2**</span></td><td><span>`/home` (Les configs et Dockerfiles)</span></td></tr><tr><td><span>**Planification**</span></td><td><span>Quotidien à **04:00**</span></td></tr><tr><td><span>**Rétention**</span></td><td><span>Multi-version (7 jours minimum)</span></td></tr></tbody></table>

---

##### ✅ Ce qui est maintenant "Automatique" :

1. **02h00** : Le VPS crée les fichiers SQL compressés.
2. **04h00** : Le NAS se connecte sans mot de passe, compare les fichiers et télécharge uniquement les nouveautés.
3. **Sécurité** : Si ton VPS brûle, tu as les bases ET les fichiers de config chez toi.

**Est-ce que tu veux que je te génère aussi la commande pour tester la restauration d'une base sur ton VPS en cas de besoin ?**