Backing up all data on my own hosted redash


#1

Hi,

We are hosting our own redash instance (I worked with it in the past - it rocks!)
I’d like to create a daily backup of all the data, so that in the case where we need to re-install the instance, we can just restore it from the backup.

What is the best way to do it?
I noticed that there are numerous api endpoints I can call (/api/[queries|dashboards|…]), but I couldn’t find any documentation for them, it is not clear if I need anything else and how what is the process to restore things with the data I got from the above api.

Thanks,
Yotam.

P.S., btw, the in only reason for us to be using a self-hosted redash is security…


#2

Hi!

The easiest way is to take a backup of the Postgres database used for metadata by Redash. This way you get a snapshot of everything and there are standard tools to do this backup.


#3

Follow up Question - I tried to back up the DB using pg_dump and It took a lot of time because of the events table (it has 10M rows) and the query_results table(1M rows). can u please elaborate what this data is used for and if it can be purged?


#4

Im a bit stuck here as well. I created all my queries and some dashboards on a vm and now want to move them all to our production machine.

I did: sudo -u redash pg_dump > backup_filename.sql
Then I copied the sql file to the new server - both servers were running the newest and same version of redash.

on the new server I ran: sudo -u redash psql -d redash < backup_filename.sql

There were a lot of errors telling me about access permissions and that things already exist. I still cant see the queries or the dashboards. What am I doing wrong. I realise it’s probably some noob error.


#5

events table can be purged / backed up without data (just the structure). query_results needs to be backed up, although you probably don’t need all the 1M rows (only the ones still referenced by queries.latest_query_data_id). There is a cleanup process that runs once a week, you might need to run it more frequently so it can keep up with deleting the unused query results.


#6

Maybe your new server already has database created? Just drop it before trying to restore the old one.


#7

Thank you arikfr. For others like me who want to move all their quires/users/dashboards from one server to a new server this is what I did to get it working after I copied the backup_filename.sql to the new server (im not using a docker version):

first stop everything because you cant drop the db without stopping the services that are using the db:
sudo supervisorctl stop all

then drop the existing redash db (I would probably would make a backup of this before you do it - like you did to backup the other server but call it something else):
sudo -u redash dropdb redash

create an new empty db:
sudo -u postgres createdb redash --owner=redash

replace the db with the sql you took from your other instance
sudo -u redash psql -d redash < backup_filename.sql

restart everything:
sudo supervisorctl restart all

It should be good to go. This works on a clean install where you haven’t set us the server yet and created the admin account. I noticed that on one of the servers that I had created the admin account it was not displaying the new queries (the same email address was used for the admin) - when I checked the .env file there were missing lines (database_url, redis_url, log_level). Once I put them back in and restarted the services it all worked.