How are you backing up Redash?

[guidopetri]

Us maintainers had a conversation in our monthly meeting about potentially automating database migration, so that upgrading Redash versions is super easy. Out of that, the question came up: how are people backing up Redash, in the first place? By knowing more about how you, our end users, are backing up things, we can come up with hopefully a good system for in-place upgrades that doesn't disrupt your backups, while also allowing those backups to work when they're needed.

If you're interested in talking about this, our next monthly meeting is on Nov 25. The specific time can be found in the wiki, and we welcome newcomers! Please join the discord to receive the video conference link.

[justinclift]

For people doing disk based snapshots, are you flushing the database to disk + freezing the filesystem while the snapshot is being taken?

eg to ensure you capture the very latest database state rather than relying on luck + filesystem/database recovery

[christoboucher]

I have gone down this road a few times.

PostgreSql Backup

• Add Bind mount in the docker-compose.yaml file to a folder for the pg_dump result
• I exclude the events and query_results tables as these are not essential.
• Use a docker container exec command to do the pg_dump to the bind mounted folder.
• Docker Container exec command: /usr/bin/docker container exec redash_postgres_1 pg_dump -v -Upostgres --exclude-table-data=events --exclude-table-data=query_results -dpostgres --file=/data/backup/$BACKUP_FILENAME

Backing Up the Other Stuff
I found out the hard way that there are other items that are required to successfully restore your backed up redash databass.

• env file.
  • Contains ProgreSQL Credentials, Cookie Secret and Secret Key.
• docker-compose.yaml
  • This is essential if you have made changes, eg. using a later version of PostgreSQL, different worker count, etc.
• setup.sh The script file you used to do the install
  • if you have made changes during your installation.

Automation
I created a bash script that:

• copies all the "Other Stuff" files into the same host folder as the PostgreSQL Bind Mounted folder.
• Does the pg_dump with the command above
• Compresses all the "Other Stuff" files and the pg_dump files into one tar.gz
• I then copy this tar.gz file to an offsite server and in turn is copied to an external device that is kept in a fireproof safe.
• External Devices are rotated on a weekly basis.

This bash script is run by a cron job every evening.

I have tested this method and done successful restores using this backup technique.

Hope this helps someone.

Thoughts and comments welcome.
Regards
Christo

[justinclift]

Thanks, that's good info. Skipping those two tables seems like a pretty good approach too. 😄

[guidopetri]

+1, that's an awesome approach, and thank you for elaborating! I'm curious what the setup.sh changes might be that you've made, if you don't mind explaining those as well?

[guidopetri]

Thanks everyone for voting and showing how you back up the database, this is super useful info. Closing the discussion now since it's been a month.