Add a backup / restore procedure in the documentation

[Benji81]

Hello,

I do not find any information about backup and restoration processes.

I do not know if I only have to backup the database or if other files (likes settings or custom user files ) that may exist on the filesystem need to also be saved.

A little chapter in the documentation on this topic would be great.

Regards,
Benjamin

[nytai]

The metadata database should be the only thing you need to backup. As for how to backup the database, that would depend on which database or service you're using.

[dagostinelli]

I agree. This product needs a well-defined (and supported) backup/restore procedure.

For example, one (apparently) supported way of installing this product is via Docker Compose. This kind of installation creates a local database using a docker volume. Anyone that's tried, knows what a problematic issue it is to backup and restore a docker volume. This has nothing to do with which backend we selected for our DB. If the supported solution is to run docker exec, then execute a pg command to script out the database to a .sql, and if restore is the associated restore process, that really should be spelled out somewhere. Even better, provide the .sh script to do this -- backup-pg.sh and restore-pg.sh. Then we can add it to a cron job.

[sfirke]

I think the first level of improvement to the docs would be to point this out:

what a problematic issue it is to backup and restore a docker volume

And suggest running a stateful database service instead of relying on the docker container / volumes.

That said, if you or anyone want to contribute a script to pg dump and restore, it could be a nice asset to include. Ideally it would cover both Postgres in a docker container and a Postgres instance running elsewhere (e.g., AWS).

[fightingtexasaggie]

I put together a simple backup script that uses pg_dump to back up the database created by the superset docker-compose.yml (actually docker-compose-image-tag.yml) file.

You'll need a bind mount to get scripts in and data out of your container, so I added a bind mount in the volume definition of the db. If you're running traditional root docker, you'll need to chown this directory root:root or otherwise allow access via a facl.

db:
... stuff ...
volumes:
    - db_home:/var/lib/postgresql/data
    - ./docker/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
    - type: bind
          source: /home/superset/superset_backup
          target: /superset_backup

I then created a quick and dirty backup script in my host backup directory. It will be run in the container:

    #!/bin/bash
    pg_dump -c -U superset superset > /superset_backup/$(date +%Y%m%dT%H%M%SUTC -u)-backup.sql

Back on the host, I created another bash script to call this backup script via docker exec:

    #!/usr/bin/bash
    /usr/bin/docker exec superset_db /superset_backup/scripts/backup.sh
    pbzip2 /home/superset/superset_backup/*-backup.sql

I set a cronjob to run this regularly. To restore, start with a clean Docker install, then load into the database. The -c in the pg_dump will cause existing objects to be dropped, so shouldn't be a problem.

The other thing I do is a daily backup of the superset volumes, after shutting down superset. It creates a few minutes of downtime, which is acceptable on this project. To restore those, I shut down docker and replace the superset_db volume from the backup. This seems to work fine and is clean, just be mindful of permissions.

Whatever your approach, test test test!

[sfirke]

Good points. Anyone is free to submit a documentation pull request to improve this. I'd be happy to review.

[sfirke]

I've submitted a documentation pull request that describes the risk of storing metadata in a Docker volume, suggests an alternative (a separate managed database), and has a link to a popular StackOverflow post showing how to dump and restore to/from a PostgreSQL database volume. #24461