Keeping Query IDs when migrating from hosted to OSS

Support Self Hosted Redash Support
1

Hi there,
As hosted Redash about to reach EoL, we are working on migrating to the open source version of Redash. From what we understand, query IDs are expected to change in this process. This will create friction to our work as many users have bookmarks to dashboards/queries that contains the current query ID.

Is there a way to complete the migration in a manner that would be transparent to the end user (i.e - query IDs will not change)?

Thank you,
Yair

2

This is correct.

Not without a significant effort. You an of course update the metadata database to force a different ID. But you’ll need to do so on every single table that references those IDs. This leaves a lot of room for error. You could hose your entire instance if you’re not careful.

Also, this change wouldn’t be entirely transparent since the URL would change, no?

3

If you are still want to migrate the IDs as well, I have written a script that can do it for you! You should have already migrated all your queries in the new hosted Redash first. And then use the updated meta.json with the script below.

Before that, you need to change the foreign key constraint on the visualizations table like this.

ALTER TABLE visualizations
DROP CONSTRAINT visualizations_query_id_fkey;

ALTER TABLE visualizations
ADD FOREIGN KEY (query_id) REFERENCES queries (id) ON UPDATE CASCADE;

Then with the script above, execute these three methods (you may need to do modifications since I executed them with the Rails framework) and you should get as a result a SQL file with all the necessary UPDATE statements for updating the IDs in the database.

Scripts::RedashMigration.get_all_query_infos
Scripts::RedashMigration.attach_original_id_to_old_id
Scripts::RedashMigration.build_sql_file_from_correspondance_file

Finally, follow these steps.

SSH into instance
- ssh -i redash-sandbox.pem ubuntu@ec2-xxx-xxx-xxx-xxx.ap-northeast-1.compute.amazonaws.com

Go to /opt/redash folder
- cd /opt/redash

Get the container ID of postgres container and launch PSQL
- docker ps -a 
- docker exec -it 9cff181b7315 bash 
- psql -h localhost -p 5432 -U postgres -W # this should prompt a password that is visible inside the /opt/redash/env file

Change foreign key constraint of `visualizations` table inside PSQL
- ALTER TABLE visualizations
  DROP CONSTRAINT visualizations_query_id_fkey;
  ALTER TABLE visualizations
  ADD FOREIGN KEY (query_id) REFERENCES queries (id) ON UPDATE CASCADE;

Finally paste the contents of redash_migration_script.sql to update all IDs.
1 Like
4

Thanks for writing this up.

:warning: For anyone trying this: back up your database first! :warning:

This is not something for non-technical users to attempt.

1 Like