DB backups

[profy12]

🇫🇷
Les bases de données doivent pourvoir être sauvegardées, il faut trouver un mécanisme de backup/restauration.

Les backups doivent être stockés dans un mount bind sur le poste apprenant, avec une rétention d'une semaine.

Pourquoi pas un stockage cloud par la suite.

Edit@Translation:

🇬🇧

Databases must be able to be backed up, and a backup/restore mechanism must be found.

Backups should be stored in a mount bind on the learner workstation, with a retention period of one week.

Why not store them in the cloud afterwards?

[clementgineste]

We currently host 3 DMBS:

• MariaDB
• PostgreSQL
• MongoDB

The first solution is to add and host single volumes, one for each system.
Another solution could be to use a single volume and backup only that one. This could simplify the backup solution. However, it's important to note that when multiple containers access the same volume on a host, file locks must be managed by the applications running in the containers, otherwise there may be risks.

Exemple for a single, shared, volume:

services:
  db_mysql:
    image: mysql:latest
    volumes:
      - db_data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: password

  db_postgres:
    image: postgres:latest
    volumes:
      - db_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: password

volumes:
  db_data:

Documentation for backing up data volumes

Another solution could be to use remote volume hosting (such as Amazon EBS) or to enhance volume management with volume plugins, some examples.

Just to recall something: data volumes can be initiated with backup through --volumes-from option.

Another another solution could be to use the container backup.

Just letting everything sink in before chosing solution.

[profy12]

No I want that each DB is independant, and I don't want physical backup by logical IE dumps SQL, generated with mysqldump, pgdump, mongodump etc. Theses dumps sould be stocked in the local backup directory by default in the repository, with a mount bind. This directory should be customisable like the code dir.

[clementgineste]

So:

• One volume for each DBMS
• One backup container
  • Local dir using a mount bind
  • Backup local dir can be configured with .env
  • Using mysqldump, pg_dump and mongodump
  • Crontab
  • The backup starts after each container startup. First check if a backup has been made recently
  • A manual backup (by the student) can be made on request
• Backup retention : 1 week
  • Keep the last 5 files if backups older than one week
• In the future could be use for drive sync

[profy12]

Crontab is uselessl, a backup at every start will do the job.
Name every backup with the day name will automate a 7 day retention, ex: monday-mysql.sql.gz, monday-pg.sql.gz, etc.

[clementgineste]

As we talked about, the current objective is to go fast and use simplicity before developing a better backup management system in the future.
The first introduction to the backup container is therefore on this PR #92

Backup container

• Always restart
• Make backup, sleep for 4 hours and restart
• One entrypoint script (called by command in the compose file) calling 3 scripts
• 3 scripts, one for each db system (yep could be only one with argument for each manual save or db choice, etc, we'll see in the future)
• Manual save can be call through --save argument : docker compose exec backup /scripts/backup-mongodb.sh --save
  • Alias can be added in terminal container like backup mysql (not done yet)
• Script logic:
  • Check if current day backup is already made, if yes do nothign
  • If no, do the daily backup
  • If asked, do the manual backup (different name than daily backup, no rotation here)
  • Backup rotation (deleted with mtime +6d) for 1 week
  • Install dependancies if needed (no custom image yet, first run is not that long)

[clementgineste]

Logic have been changed to :

• docker in docker (including dind image from docker)
• scripts directly call dump app though docker exec