Migration to new server and docker instance

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

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;

2 Likes

@cosmicdot I am having the same problem, but our postgres db is running in RDS, which I think is preventing me from GRANTING sequence permissions to the user (as the superuser role is reserved by Amazon).

This GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite;
Returns Query 1 ERROR: ERROR: permission denied for sequence activity_id_seq

Any ideas?