@flamber thanks for your response.
We don't use the enterprise version, so serialization is not a option for us right now.
I've tried some things on our development instance database and I'll leave the code here so it may help someone in the future.
First, I fetched the databases IDs:
SELECT id, name FROM metabase_database
id name
11 new_db
12 old_db_1
13 old_db_2
14 old_db_3
After that, I performed the changes you've mentioned in report_card
table:
UPDATE report_card
SET database_id = 11
WHERE database_id IN (12, 13, 14)
UPDATE report_card
SET dataset_query = jsonb_set(dataset_query::jsonb, '{database}', '11')
WHERE database_id IN (12, 13, 14)
These 2 updates worked fine to upgrade SQL questions, but it broke the native query ones because of the links to the tables on metabase_table
.
So I tried to perform the following update to fix the native query questions:
UPDATE metabase_table
SET db_id = 11
WHERE db_id IN (12, 13, 14)
And it returned this constraint violation error. It was caused because the new database has the same tables as the old 3:
ERROR: duplicate key value violates unique constraint "idx_uniq_table_db_id_schema_name"
DETAIL: Key (db_id, schema, name)=(11, public, calendario) already exists.
As we were at a development instance, I tried to drop the constraint just to check if the update would fix the native query questions:
ALTER TABLE metabase_table DROP CONSTRAINT idx_uniq_table_db_id_schema_name
I performed the update again and now all the questions work fine.
For obvious reasons I won't change anything in production tables structure, so the secure way will be upgrading SQL questions directly in the database and native query questions manually.