There’s no MB_DB_PORT defined? And are the settings prefixed with export ?
After you run the migration (java -jar metabase.jar load-from-h2 /path/to/metabase.db), then remove the H2 file and it should read the export settings.
You can check the log to see what settings Metabase are loaded with.
Thanks @flamber. Ahh, yeah it was the missing port (had the ‘export’ though). Now when I run the migration, it looks like it is trying to connect to the local postgres, but failing.
I ran ALTER USER metabase WITH PASSWORD “blabla” using the superuser postgres
and updated my MB_DB_PASS to ‘blabla’ also. Then I got:
01-23 21:00:12 INFO metabase.db :: Verifying postgres Database Connection ...
Jan 23, 2019 9:00:12 PM org.postgresql.core.v3.ConnectionFactoryImpl log
WARNING: SQLException occurred while connecting to localhost:5432
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "metabase"
Then I changed /etc/postgresql/9.5/main/pg_hba.conf
to allow local connections to metabase db using metabase user, with md5 authentication.
psql: FATAL: Peer authentication failed for user "metabase"
I think this is weird because my pg_hba.conf file has (just changed the unix socket config from md5 to trust, and db user from metabase to all) :
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local metabase all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
edit: don’t have my pg logs setup, not familiar with that process. they are currently going to stderr
@mbuser
I don’t use Postgres, but Metabase doesn’t connect via socket, so that local ... line is probably creating some confusion.
Try connecting again:
@flamber
I could really die right now. So I ran the command you showed above, it asked me for my password. I pasted it in and it gave me an error. That’s weird! I went and tried to Alter user again and you know what the problem was?
When I set up the stupid metabase user originally, I forgot a semi-colon after setting the password. WHAT!!! Tried it again and it worked.
Don’t think I would have figured that out without your help haha, thanks!
So, let me make sure I got this right.
I ran: java -jar metabase.jar load-from-h2 ~/mb_archive/metabase.db
and I got:
01-23 22:26:32 INFO metabase.util :: Loading Metabase...
01-23 22:26:32 INFO metabase.util :: Maximum memory available to JVM: 239.8 MB
01-23 22:26:38 INFO util.encryption :: Saved credentials encryption is DISABLED for this Metabase instance. 🔓
For more information, see https://www.metabase.com/docs/latest/operations-guide/start.html#encrypting-your-database-connection-details-at-rest
01-23 22:26:51 INFO metabase.db :: Verifying postgres Database Connection ...
01-23 22:26:51 INFO metabase.db :: Verify Database Connection ... ✅
01-23 22:26:51 INFO metabase.db :: Running Database Migrations...
01-23 22:26:51 INFO metabase.db :: Setting up Liquibase...
01-23 22:26:51 INFO metabase.db :: Liquibase is ready.
01-23 22:26:51 INFO metabase.db :: Checking if Database has unrun migrations...
01-23 22:26:54 INFO metabase.db :: Database Migrations Current ... ✅
Temporarily disabling DB constraints...
[OK]
Transfering 1 instances of Database....[OK]
Transfering 3 instances of Setting....[OK]
Transfering 4 instances of Table....[OK]
Transfering 36 instances of Field....[OK]
Transfering 6 instances of FieldValues....[OK]
Transfering 1 instances of Activity....[OK]
Transfering 3 instances of PermissionsGroup....[OK]
Transfering 5 instances of Permissions....[OK]
Transfering 13 instances of TaskHistory....[OK]
Transfering 14 instances of DataMigrations....[OK]
Setting postgres sequence ids to proper values...
[OK]
Looks like a success. Metabase doesn’t start so I guess that was just a migration.
I try and run java -jar metabase.jar again, and it still goes to
01-23 22:28:10 INFO metabase.core :: Starting Metabase version v0.31.2 (89c37eb release-0.31.2) ...
01-23 22:28:10 INFO metabase.core :: System timezone is 'Etc/UTC' ...
WARNING: any? already refers to: #'clojure.core/any? in namespace: monger.collection, being replaced by: #'monger.collection/any?
01-23 22:28:11 INFO metabase.core :: Setting up and migrating Metabase DB. Please sit tight, this may take a minute...
01-23 22:28:11 INFO metabase.db :: Verifying h2 Database Connection ...
01-23 22:28:12 INFO metabase.db :: Verify Database Connection ... ✅
01-23 22:28:12 INFO metabase.db :: Running Database Migrations...
01-23 22:28:12 INFO metabase.db :: Setting up Liquibase...
01-23 22:28:12 INFO metabase.db :: Liquibase is ready.
01-23 22:28:12 INFO metabase.db :: Checking if Database has unrun migrations...
01-23 22:28:15 INFO metabase.db :: Database Migrations Current ... ✅
That looks like it’s back at h2. Actually freezes at INFO sync.util :: STARTING: Sync metadata for h2 Database 1 'Sample Dataset'
Do I need to run metabase differently in order to always start it with postgres? And when I turn off metabase and turn it back on again, it will go back and restore everything from the pg db and I don’t need to do setup anymore?
@mbuser
Okay, great - so now we’re one step closer
So let’s just make sure that your export settings hasn’t changed for some strange reason by running this:
So I fixed the freezing up by rebooting the server, tried to run postgres and it failed with:
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
journalctl -b | grep postgres identified an issue in my pg_hba.conf which I resolved
systemctl list-units | grep postgres identified the failed unit, restarted with sudo systemctl restart postgresql@9.5-main.service
^ leaving all that there in case someone stumbles onto this eventually.
edit:
btw, i can’t run the migration load-from-h2 anymore, I get: java.lang.IllegalArgumentException: No matching clause: :h2.
Just running the jar stops at INFO metabase.core :: Metabase Initialization COMPLETE
@mbuser
Okay, so from what I understand, you have Postgres running and it’s working now?
If those variables are exported, then you should be able to run it with this (as the same user):