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.


#8

Hi,

Thanks for the post, it helped me a lot in setting up redash for the first time.

Though I am stuck at this point right now where I am not able to get past my login screen after dumping the old data to new screen.

So now, I am not getting setup redash screen.
instead, I am getting sign in screen and once I put anything and press enter it gives this error:

Internal Server Error
The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

Not sure what am I missing here.