Hello,
I configured different questions (counters, list, pie charts, timeline) and dashboards.
The database has then been modified, the table and some columns name have changed.
How can I do to change table and columns names in all requests with replacement without having to modify each question individually with SQL editor?
Thanks
Hi @Noosymer
Metabase currently does not have the ability to modify your SQL, so you would have to do that manually.
Or do a replace via the application database table report_card.dataset_query
Make sure to have a backup before making such changes.
Hi @flamber
Ok, and what is the application database table report_card.dataset_query ?
How to use it ?
Thank you
@Noosymer The Metabase application database:
https://www.metabase.com/docs/latest/operations-guide/configuring-application-database.html
@flamber So I have to migrate H2 database to a PostgreSQL db (for example) to then be able to request it?
Hello @flamber
I finally figured out how to migrate H2 DB to PostgreSQL.
But, from what I see it converts the .db and with env variables to use PostgreSQL, it creates the DB schema but when I connects to metabase, I have the setup page...
And I don't seem to have any data into the DB
I don't see any command telling metabase to inject data into PostgreSQL
@Noosymer If you are migration, then follow this guide:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
Yes, that's the documentation I'm using...
I use java through a docker because I can't install java on the server.
$ docker run -it -v ~/:/home --rm anapsix/alpine-java java -jar /home/metabase.jar load-from-h2 /home/metabase-test/metabase.db/metabase.db
Warning: environ value /opt/jdk for key :java-home has been overwritten with /opt/jdk1.8.0_202/jre
2021-12-14 14:55:21,624 INFO metabase.util :: Maximum memory available to JVM: 3.5 GB
2021-12-14 14:55:31,529 INFO util.encryption :: Saved credentials encryption is DISABLED for this Metabase instance. 🔓
For more information, see https://metabase.com/docs/latest/operations-guide/encrypting-database-details-at-rest.html
2021-12-14 14:55:34,101 INFO driver.impl :: Registered abstract driver :sql 🚚
↙ Load driver :sql took 609.9 ms
2021-12-14 14:55:34,110 INFO driver.impl :: Registered abstract driver :sql-jdbc (parents: [:sql]) 🚚
Load driver :sql-jdbc took 622.7 ms
2021-12-14 14:55:34,114 INFO driver.impl :: Registered driver :h2 (parents: [:sql-jdbc]) 🚚
2021-12-14 14:55:34,133 INFO driver.impl :: Registered driver :mysql (parents: [:sql-jdbc]) 🚚
2021-12-14 14:55:34,894 INFO driver.impl :: Registered driver :postgres (parents: [:sql-jdbc]) 🚚
2021-12-14 14:55:36,989 INFO metabase.core ::
Metabase v0.41.3.1 (20f9097 release-x.41.x)
Copyright © 2021 Metabase, Inc.
Metabase Enterprise Edition extensions are NOT PRESENT.
2021-12-14 14:55:36,999 WARN metabase.core :: WARNING: You have enabled namespace tracing, which could log sensitive information like db passwords.
2021-12-14 14:55:37,047 WARN db.env :: WARNING: Using Metabase with an H2 application database is not recommended for production deployments. For production deployments, we highly recommend using Postgres, MySQL, or MariaDB instead. If you decide to continue to use H2, please be sure to back up the database file regularly. For more information, see https://metabase.com/docs/latest/operations-guide/migrating-from-h2.html
2021-12-14 14:55:37,049 INFO cmd.copy :: Set up h2 source database and run migrations...
2021-12-14 14:55:37,052 INFO db.setup :: Verifying h2 Database Connection ...
2021-12-14 14:55:37,302 INFO db.setup :: Successfully verified H2 1.4.197 (2018-03-18) application database connection. ✅
2021-12-14 14:55:37,302 INFO db.setup :: Running Database Migrations...
2021-12-14 14:55:37,305 INFO db.setup :: Setting up Liquibase...
2021-12-14 14:55:37,341 INFO db.setup :: Liquibase is ready.
2021-12-14 14:55:37,342 INFO db.liquibase :: Checking if Database has unrun migrations...
2021-12-14 14:55:38,537 INFO db.setup :: Database Migrations Current ... ✅
2021-12-14 14:55:38,537 INFO db.data-migrations :: Running all necessary data migrations, this may take a minute.
2021-12-14 14:55:38,546 INFO db.data-migrations :: Finished running data migrations.
Database setup took 1.5 s
2021-12-14 14:55:38,547 INFO cmd.copy :: [OK]
2021-12-14 14:55:38,548 INFO cmd.copy :: Set up h2 target database and run migrations...
2021-12-14 14:55:38,549 INFO db.setup :: Verifying h2 Database Connection ...
2021-12-14 14:55:38,553 INFO db.setup :: Successfully verified H2 1.4.197 (2018-03-18) application database connection. ✅
2021-12-14 14:55:38,553 INFO db.setup :: Running Database Migrations...
2021-12-14 14:55:38,554 INFO db.setup :: Setting up Liquibase...
2021-12-14 14:55:38,560 INFO db.setup :: Liquibase is ready.
2021-12-14 14:55:38,560 INFO db.liquibase :: Checking if Database has unrun migrations...
2021-12-14 14:55:38,961 INFO db.liquibase :: Database has unrun migrations. Waiting for migration lock to be cleared...
2021-12-14 14:55:39,038 INFO db.liquibase :: Migration lock is cleared. Running migrations...
2021-12-14 14:55:40,201 INFO db.setup :: Database Migrations Current ... ✅
Database setup took 1.7 s
2021-12-14 14:55:40,202 INFO cmd.copy :: [OK]
2021-12-14 14:55:40,203 INFO cmd.copy :: Testing if target h2 database is already populated...
2021-12-14 14:55:40,204 INFO cmd.copy :: [OK]
2021-12-14 14:55:40,206 INFO cmd.copy :: Temporarily disabling DB constraints...
2021-12-14 14:55:40,207 INFO cmd.copy :: [OK]
2021-12-14 14:55:40,213 INFO cmd.copy :: Copying instances of Database...
2021-12-14 14:55:40,223 INFO cmd.copy :: copied 2 instances.
2021-12-14 14:55:40,224 INFO cmd.copy :: Copying instances of User...
2021-12-14 14:55:40,225 INFO cmd.copy :: copied 2 instances.
2021-12-14 14:55:40,228 INFO cmd.copy :: Copying instances of Setting...
2021-12-14 14:55:40,233 INFO cmd.copy :: copied 14 instances.
2021-12-14 14:55:40,235 INFO cmd.copy :: Copying instances of Table...
2021-12-14 14:55:40,238 INFO cmd.copy :: copied 28 instances.
2021-12-14 14:55:40,250 INFO cmd.copy :: Copying instances of Field...
2021-12-14 14:55:40,302 INFO cmd.copy :: copied 381 instances.
2021-12-14 14:55:41,257 INFO cmd.copy :: Copying instances of FieldValues...
2021-12-14 14:55:41,297 INFO cmd.copy :: copied 191 instances.
2021-12-14 14:55:41,303 INFO cmd.copy :: Copying instances of Revision...
2021-12-14 14:55:41,311 INFO cmd.copy :: copied 182 instances.
2021-12-14 14:55:41,322 INFO cmd.copy :: Copying instances of ViewLog...
2021-12-14 14:55:41,484 INFO cmd.copy :: copied 3,296 instances.
2021-12-14 14:55:41,485 INFO cmd.copy :: Copying instances of Session...
2021-12-14 14:55:41,486 INFO cmd.copy :: copied 12 instances.
2021-12-14 14:55:41,487 INFO cmd.copy :: Copying instances of Collection...
2021-12-14 14:55:41,489 INFO cmd.copy :: copied 7 instances.
2021-12-14 14:55:41,490 INFO cmd.copy :: Copying instances of Dashboard...
2021-12-14 14:55:41,492 INFO cmd.copy :: copied 4 instances.
2021-12-14 14:55:41,505 INFO cmd.copy :: Copying instances of Card...
2021-12-14 14:55:41,510 INFO cmd.copy :: copied 22 instances.
2021-12-14 14:55:41,512 INFO cmd.copy :: Copying instances of DashboardCard...
2021-12-14 14:55:41,514 INFO cmd.copy :: copied 15 instances.
2021-12-14 14:55:41,515 INFO cmd.copy :: Copying instances of DashboardCardSeries...
2021-12-14 14:55:41,516 INFO cmd.copy :: copied 7 instances.
2021-12-14 14:55:41,518 INFO cmd.copy :: Copying instances of Activity...
2021-12-14 14:55:41,526 INFO cmd.copy :: copied 174 instances.
2021-12-14 14:55:41,527 INFO cmd.copy :: Copying instances of Pulse...
2021-12-14 14:55:41,528 INFO cmd.copy :: copied 2 instances.
2021-12-14 14:55:41,529 INFO cmd.copy :: Copying instances of PulseCard...
2021-12-14 14:55:41,530 INFO cmd.copy :: copied 2 instances.
2021-12-14 14:55:41,530 INFO cmd.copy :: Copying instances of PulseChannel...
2021-12-14 14:55:41,531 INFO cmd.copy :: copied 2 instances.
2021-12-14 14:55:41,532 INFO cmd.copy :: Copying instances of PulseChannelRecipient...
2021-12-14 14:55:41,532 INFO cmd.copy :: copied 2 instances.
2021-12-14 14:55:41,533 INFO cmd.copy :: Copying instances of PermissionsGroup...
2021-12-14 14:55:41,534 INFO cmd.copy :: copied 3 instances.
2021-12-14 14:55:41,534 INFO cmd.copy :: Copying instances of PermissionsGroupMembership...
2021-12-14 14:55:41,535 INFO cmd.copy :: copied 4 instances.
2021-12-14 14:55:41,536 INFO cmd.copy :: Copying instances of Permissions...
2021-12-14 14:55:41,536 INFO cmd.copy :: copied 5 instances.
2021-12-14 14:55:41,539 INFO cmd.copy :: Copying instances of LoginHistory...
2021-12-14 14:55:41,540 INFO cmd.copy :: copied 11 instances.
2021-12-14 14:55:41,541 INFO cmd.copy :: Copying instances of DataMigrations...
2021-12-14 14:55:41,542 INFO cmd.copy :: copied 12 instances.
2021-12-14 14:55:41,544 INFO cmd.copy :: Re-enabling DB constraints...
2021-12-14 14:55:41,578 INFO cmd.copy :: [OK]
After that migration command, I check the PostgreSQL :
root@6351a5f7cf28:/# su postgres
postgres@6351a5f7cf28:/$ psql
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
metabase | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c metabase
You are now connected to database "metabase" as user "postgres".
metabase=# \dt
Did not find any relations.
metabase=#
And I don't have any schema at that step...
The schema is created once I launch metabase docker with env variables telling it to use PostgreSQL:
docker run -d -p 31603:3000 \
-e "MB_DB_TYPE=postgres" \
-e "MB_DB_DBNAME=metabase" \
-e "MB_DB_PORT=5432" \
-e "MB_DB_USER=<pgsql user>" \
-e "MB_DB_PASS=<pgsql pass>" \
-e "MB_DB_HOST=<pgsql IP>" \
--name metabase-pgsql metabase/metabase
But I don't have data into those tables...