Connecting Metabase to Postgresql - 2 docker containers

[muthubuntu]

Hi all, first time poster so apologies if I haven't found this solution somewhere else already.

I have both Metabase and Postgresql running successfully in separate docker containers, both on my local machine.

Metabase cannot connect to the Postgresql docker. I have connections via DBeaver and QGIS to Postgresql that are working fine. Both psql and nc are connecting to postgresql fine both via localhost and 0.0.0.0 host names.

The error I am getting is: "Hmm, we couldn't connect to the database. Make sure your Host and Port settings are correct"

Does anyone know of a limitation connecting 2 docker containers in the method I am attempting?

Thank you!

-m

[Roberto]

Hi there.

It might be related to the fact that your Postgresql Docker Container does not allow connections.

Can you connect to your Postgresql database from your local machine using psql?

This is a good test to understand if the problem comes from the Postgresql Database itself.

Best

[muthubuntu]

Hi there,

Yes - as I stated above: I have connections via DBeaver and QGIS to Postgresql that are working fine. Both psql and nc are connecting to postgresql fine both via localhost and 0.0.0.0 host names.

-m

[Roberto]

Did you try to set up a docker network, something like:

docker network create mynetwork

and then connecting your two containers to that specific network?

docker network connect mynetwork my_postgres_container
docker network connect mynetwork my_metabase_container

Best

[muthubuntu]

Darn - doesn't seem to work. Same error as before.

Thanks for that tip, though, I'm adding it to my docker command arsenal !!

-m

[Luiggi]

the containers need to be in the same network to be able to see each other

[muthubuntu]

Hi there - both containers are running on my local machine - does that mean they are on the same network?

Both the container IP addresses are 0.0.0.0 (3000 and 5432 ports).

Thank you!

-m

[Roberto]

More than welcome.

So I have been trying to reproduce your scenario, but instead of using two separate containers, I use docker compose, which i strongly suggest for these kind of activities.

docker-compose.yml

version: '3.8'

services:
  postgres:
    image: postgres:latest
    platform: linux/arm64/v8
    container_name: testPostgresql
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: mysecretpassword
      POSTGRES_DB: mydatabase
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

  metabase:
    image: metabase/metabase:v0.41.2-arm64
    container_name: testMetabase
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: mydatabase
      MB_DB_PORT: 5432
      MB_DB_USER: postgres
      MB_DB_PASS: mysecretpassword
      MB_DB_HOST: postgres

volumes:
  postgres_data:

Now when you connect Postgresql to Metabase, you can use these credentials:

• Database Type: PostgreSQL
• Host: postgres
• Port: 5432
• Database Name: mydatabase
• Database Username: postgres
• Database Password: mysecretpassword

Let me know how it goes!

Best

[muthubuntu]

OK Let me dig into this - this would be my first time using docker compose / yml files...

Seems like this is a better workflow in general, so let me try this out and I will report back for sure.

Thank you again!

[muthubuntu]

Thank you again for this. I was able to use docker compose to spin up a postgresql database AND a metabase instance that would use that database to create a host of tables within the public schema. The database is visible from psql and dbeaver, and I can see the tables created when Metabase was spinning up.

However, I am still not able to connect to that database from within Metabase, still getting the same connection errors I described above.

I'm not sure why the docker compose workflow would allow the two instances to see each other, but not in the main Metabase database connection window.

Apologies if I'm not describing the components correctly, but I think I've done everything correct in order to set this up as you have described. Any more thoughts are appreciated!

Thank you again!

[Roberto]

I have tried to set up the docker container, and everything works well.

First of all, the mydatabase database is already visible to Metabase, cause it is pre-declared in the docker container:

Anyway, if you try to connect to mydatabase from within metabase, it works using the following fields, as declared in the docker-compose file (password is mysecretpassword):

Hope this helps!

Have a great Sunday

[muthubuntu]

Yup that looks like what I have going

Here is the YAML file I used to spin it all up, as per your example (I used postgis/postgis which is just postgresql with the postgis extension installed and enabled, as well as a few other geospatial extensions)

version: '3.8'

services:
  postgres:
    image: postgis/postgis
    platform: linux/amd64
    container_name: dcomp-postgis
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: *******
      POSTGRES_DB: denver
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

  metabase:
    image: metabase/metabase
    container_name: dcomp-metabase
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: denver
      MB_DB_PORT: 5432
      MB_DB_USER: postgres
      MB_DB_PASS: ********
      MB_DB_HOST: postgres

volumes:
  postgres_data:

Other than that, my connection dialog looks the same.

Are there any other tests I should be running to examine connectivity?

-m

[Roberto]

I have tried with your docker compose yml, and indeed I cannot instantiate it, it keeps loading initial screen.

What about using a normal postgres database and then install the extensions you need in a second moment?

You can install anything you want on your postgres db simply connecting to the docker container.

[muthubuntu]

I finally figured this out, and it has to do with the IP address of the postgresql database, which I had to get using docker inspect.

At the bottom of docker inspect mypostgres-container there is this section:

  "Networks": {
                "bridge": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": null,
                    "MacAddress": "02:42:ac:11:00:03",
                    "NetworkID": "b422b6c410ce979310c6c249f50dc11574d5d7132393e2432b74d5aa04bf943c",
                    "EndpointID": "e55978743e646fe1fa101f52107866409c98bf2d2c642e0c8934c14f4dc35313",
                    "Gateway": "172.17.0.1",
                    "IPAddress": "172.17.0.3",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "DriverOpts": null,
                    "DNSNames": null
                }
            }

The IP address that Metabase needs is under "IPAddress", which in this case is 172.17.0.3

Once I used that for the 'host' in the Metabase database connection dialog, everything worked just fine!

Oddly enough I could not find any reference to this anywhere, so perhaps this would be useful to add to the documentation?

(I was going down a rabbit-hole of trying to figure out if this was a pg_hba.conf access issue, but that doesn't seem to be easily addressed (or necessary) with docker instances of postgresql)

THANK YOU VERY MUCH for helping with ALL OF THIS as I learned a lot about Docker Compose figuring it out!!!

[Roberto]

Happy to hear!

And thanks to you for sharing your solution!

[muthubuntu]

Would you happen to have a better explanation than my description of the solution I provided? Just for anyone else who is perhaps trying to figure this out... Thanks!

[Roberto]

@muthubuntu

docker inspect mypostgres-container

I think you gave a solid explanation how you solved that issue. Eventually when people will look for a similar issue, will end up here, finding your answer!

[chrowe]

Found this while trying to connect a postgres docker container as a database and neither localhost or the IP address worked. What worked for me was using host.docker.internal as the host.