Migration to new server and docker instance


#1

So I need to move my metabase instance to a different server. I’m using the docker deployment method. I have setup docker and run a test install on new machine and it works with fresh install. However when I try to migrate old postgres database data backup into a new db and install that way I run into an error. I have updated both instances to the latest metabase/metabase release via docker pull. It says permission denied, but I’ve verified that the User ROLE associated with database should have ALL privileges associated with it.

UPDATE: so it does appear to be a permission issue, my user can connect but for some reason doesn’t have permissions. I ran:
GRANT ALL PRIVILEGES ON DATABASE metabase TO username;
[username obviously being unique]

Docker log error:
03-14 23:25:06 INFO metabase.db :: Verifying postgres Database Connection …
03-14 23:25:06 INFO metabase.db :: Verify Database Connection … :white_check_mark:
03-14 23:25:06 INFO metabase.db :: Running Database Migrations…
03-14 23:25:06 INFO metabase.db :: Setting up Liquibase…
java.sql.BatchUpdateException: Batch entry 0 UPDATE databasechangelog SET FILENAME = ‘migrations/000_migrations.yaml’ was aborted: ERROR: permission denied for relation databasechangelog Call getNextException to see other errors in the batch.
at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:145)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2191)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:472)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:791)
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1563)
at clojure.java.jdbc$execute_batch.invokeStatic(jdbc.clj:533)
at clojure.java.jdbc$execute_batch.invoke(jdbc.clj:526)
at clojure.java.jdbc$db_do_execute_prepared_statement$fn__10903.invoke(jdbc.clj:960)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:784)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:711)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:724)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:711)
at clojure.java.jdbc$db_do_execute_prepared_statement.invokeStatic(jdbc.clj:959)
at clojure.java.jdbc$db_do_execute_prepared_statement.invoke(jdbc.clj:945)
at clojure.java.jdbc$db_do_prepared.invokeStatic(jdbc.clj:983)
at clojure.java.jdbc$db_do_prepared.invoke(jdbc.clj:963)
at clojure.java.jdbc$execute_BANG_$execute_helper__10968.invoke(jdbc.clj:1364)
at clojure.java.jdbc$execute_BANG_.invokeStatic(jdbc.clj:1366)
at clojure.java.jdbc$execute_BANG_.invoke(jdbc.clj:1337)
at clojure.java.jdbc$execute_BANG_.invokeStatic(jdbc.clj:1356)
at clojure.java.jdbc$execute_BANG_.invoke(jdbc.clj:1337)
at metabase.db$consolidate_liquibase_changesets.invokeStatic(db.clj:225)
at metabase.db$consolidate_liquibase_changesets.invoke(db.clj:209)
at metabase.db$migrate_BANG_$fn__20229.invoke(db.clj:255)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:741)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:711)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:776)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:711)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:724)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:711)
at metabase.db$migrate_BANG_.invokeStatic(db.clj:246)
at metabase.db$migrate_BANG_.invoke(db.clj:227)
at metabase.db$run_schema_migrations_BANG_.invokeStatic(db.clj:381)
at metabase.db$run_schema_migrations_BANG_.invoke(db.clj:376)
at metabase.db$setup_db_BANG_.invokeStatic(db.clj:399)
at metabase.db$setup_db_BANG_.doInvoke(db.clj:392)
at clojure.lang.RestFn.invoke(RestFn.java:421)
at metabase.core$init_BANG_.invokeStatic(core.clj:140)
at metabase.core$init_BANG_.invoke(core.clj:119)
at metabase.core$start_normally.invokeStatic(core.clj:248)
at metabase.core$start_normally.invoke(core.clj:241)
at metabase.core$_main.invokeStatic(core.clj:269)
at metabase.core$_main.doInvoke(core.clj:264)
at clojure.lang.RestFn.invoke(RestFn.java:397)
at clojure.lang.AFn.applyToHelper(AFn.java:152)
at clojure.lang.RestFn.applyTo(RestFn.java:132)
at metabase.core.main(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation databasechangelog
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
… 45 more
03-14 23:25:06 ERROR metabase.core :: Metabase Initialization FAILED: Batch entry 0 UPDATE databasechangelog SET FILENAME = ‘migrations/000_migrations.yaml’ was aborted: ERROR: permission denied for relation databasechangelog Call getNextException to see other errors in the batch.
03-14 23:25:06 INFO metabase.core :: Metabase Shutting Down …
03-14 23:25:06 INFO metabase.core :: Metabase Shutdown COMPLETE


Heroku Metabase-Deploy Upgrade -> ERROR: permission denied for relation databasechangelog
#2

SOLVED
It was a permissions issue and had nothing to do with metabase.
For those PSQL noobs migrating and dumping into a new server database, the missing part for me was that once I created database and added role as I usually do, I also needed to connect to the migrated db:
su - postges & then connect to metabase " \c metabase" via postgres user. Then run GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

UPDATE
you also need to run this command to fix user permissions within the app:
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO username;