Setting up Postgres for app DB in a production deploy on EC2

metabase · 2019-01-23 19:20:45 UTC

First of all, I can’t believe this username wasn’t taken!

I have my .jar file in an ec2 instance running ubuntu 16.04. It works fine on h2 (haven’t done much though because I know I don’t want to use h2).

I am trying to follow the instructions at https://metabase.com/docs/latest/operations-guide/start.html#migrating-from-using-the-h2-database-to-mysql-or-postgres

Even after I export all of those new variables into my environment, it still loads as h2.

I created the postgres DB in the same ec2 instance as the jar file. It has it’s own user, I set up a password etc.

my settings:

MB_DB_DBNAME=metabase
MB_DB_HOST=localhost
MB_DB_PASS=*******************
MB_DB_TYPE=postgres
MB_DB_USER=metabase

Anyone know what I’m doing wrong? Do I need to create a separate RDS instance for the db?

flamber · 2019-01-23 19:54:24 UTC

@metabase

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.

metabase · 2019-01-23 21:34:23 UTC

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.

Still got the same error. Any ideas?

flamber · 2019-01-23 21:45:47 UTC

@metabase
So if you just try to access via psql from localhost what happens?

psql DATABASE USERNAME

I don’t know what auth method Metabase uses, but it would be strange if anything uses md5 anymore. Have you checked your Postgres logs?

metabase · 2019-01-23 22:05:24 UTC

@flamber
I get:

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

flamber · 2019-01-23 22:17:41 UTC

@metabase
I don’t use Postgres, but Metabase doesn’t connect via socket, so that local ... line is probably creating some confusion.
Try connecting again:

psql -h localhost -p 5432 -U USERNAME DATABASE

metabase · 2019-01-23 22:36:05 UTC

@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?

flamber · 2019-01-23 22:54:05 UTC

@metabase
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:

echo MB_DB_TYPE: $MB_DB_TYPE
echo MB_DB_HOST: $MB_DB_HOST
echo MB_DB_PORT: $MB_DB_PORT
echo MB_DB_USER: $MB_DB_USER
echo MB_DB_PASS: $MB_DB_PASS
echo MB_DB_DBNAME: $MB_DB_DBNAME

metabase · 2019-01-25 00:02:25 UTC

@flamber this took me a while haha

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.

Now, if I run the jar, it still uses h2.

$ env | grep MB_DB
MB_DB_USER=metabase
MB_DB_PASS=my_password
MB_DB_PORT=5423
MB_DB_TYPE=postgres
MB_DB_DBNAME=metabase
MB_DB_HOST=localhost

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

flamber · 2019-01-25 03:01:20 UTC

@metabase
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):

env | grep "MB_"
java -jar metabase.jar

But you would probably want to set it up as a service and running on it’s on non-privileged user, while also using a reverse-proxy. This guide will get you going on that:
https://metabase.com/docs/latest/operations-guide/running-metabase-on-debian.html

And if you cannot run the java -jar metabase.jar, then I have no idea what has happened and would probably recommend to reinstall the server and try Docker instead:
https://metabase.com/docs/latest/operations-guide/running-metabase-on-docker.html