1

We had an incident the other day as a result of we lost our Redash EC2 instance. I’ve spent the last half a day attempting to restore it. I thought I should probably share this with the rest of the community as a part feedback and part tips and tricks for the benefit of anyone else that might have to deal with such a problem.

The first problem I’ve stumbled upon is that Redash doesn’t really provide any instructions regarding how to restore from backup. The admin guide is pretty short and only mentions how to take a DB dump but not how to restore it. Looking around the discussions over here, I’ve gathered some pointers about how to act but the general advice is to follow the instructions for Postgres backup restore.
Very helpful was the message from @warmice in this thread.
In general it was pretty disappointing to see such a complete lack of clarity about restore procedure.

The next problem was that we were trying to restore a backup from an instance that was installed and configured in a different way to how it is done on the latest official AMI. The old Redash instance was installed from the bootstrap.sh script (v3-v4) and upgraded as time went on. As a result, the db owner was actually “redash” user and not “postgres”. This introduced another round of complications for someone who’s not familiar with DB recovery. Moreover, I wasn’t even sure which version we had installed (I was not the one who installed it).

When attempting to restore the backup via psql, I would get multiple errors that looked like role "redash" does not exist. Grepping the SQL backup I figured it’s probably because that all the create table statements have “TO OWNER redash” in the end. While I toyed around with the idea of just changing it to postgres, I wasn’t sure if that would just work.
Instead I followed the instructions I’ve found on stackoverflow and converted the backup from plaintext format to custom. This would allow me to use pg_restore and let it handle the owner issue:

cat db.dump | docker exec -i redash_postgres_1 pg_restore --clean --if-exists --no-acl --no-owner -U postgres -d postgres

Tips:

  1. Make sure to write down the exact version of redash next to your db backup.
  2. Try to recover on a similar setup if you can
  3. Take backups in custom format instead of plain sql.

Lastly, Redash introduced the SECRET_KEY property which encodes certain sensitive DB fields. However, to my knowledge, there is no advice anywhere in the admin guide or the docs about the necessity to backup this key. As a result I was stuck with restored DB with no access to the data sources config. In theory it shouldn’t be a big problem if you have these details saved in a seperate place. However the UI won’t load if the key is incorrect, blocking you from overwriting the data sources details.

To solve this issue:

  • Set SECRET_KEY to whatever value you want. Save it next to your backup. In fact, backup your /opt/redash/env as well.
  • Wipe and re-create the db
  • Login into redash and create your data sources.
  • Copy the content of the data_source table, specifically, encrypted_options is what you care the most
  • Clear db again, restore backup and manually update the data_sources table.

Hopefully it will be helpful for some people. I also hope that in the future versions of Redash there will be easier way to restore from backup.

Cheers.

2 Likes