Has anyone deployed Metabase to Google Cloud Run?

I’m trying to deploy Metabase on Google Cloud Run backed by Postgres on Cloud SQL. However, I’m struggling to establish a connection from Metabase to Cloud SQL.

Apparently I can only connect to Cloud SQL via Unix socket. I’ve tried the following values for MB_DB_CONNECTION_URI, but it seems that Metabase completely ignores the setting and simply uses H2 instead:

postgres://user:pwd@/db?sslmode=disable&host=/cloudsql/instance:connection:name/
postgres://user:pwd@/db?sslmode=disable&host=/cloudsql/instance:connection:name/.s.PGSQL.5432
postgres://user:pwd@/db?sslmode=disable

Anyone else here had luck with this?

Hi @ismail
Is Run different than AppEngine, or is it just a name change?
Seems like others have been able to use Google Cloud AppEngine with success: Google Cloud AppEngine

Yes, it’s a different product entirely that lets you serve arbitrary containers. I believe App Engine requires you to use a Google-approved base image.

I think a major difference is that Cloud Run only supports connections to Cloud SQL via Unix socket. This does not seem to work though, likely because Cloud SQL sockets contain “:” characters that break the regex that reads “MB_DB_CONNECTION_URI”.

@ismail
Just for reference, so others can find the feature request, if they search the forum:
https://github.com/metabase/metabase/issues/11414
Have you tried %3A instead of colon?

@ismail Have you checked the example code?
https://github.com/GoogleCloudPlatform/java-docs-samples/tree/master/cloud-sql/postgres/servlet
https://github.com/GoogleCloudPlatform/java-docs-samples/blob/master/cloud-sql/postgres/servlet/src/main/java/com/example/cloudsql/ConnectionPoolContextListener.java

That looks like you can make a string like this:
MB_DB_CONNECTION_URI="postgres://<MY-DB-USER>:<MY-DB-PASS>@/<MY-DB>?cloudSqlInstance='<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>'&socketFactory=com.google.cloud.sql.mysql.SocketFactory

Alternatively try to use %3A instead of the : And try without the single-quote too.

EDIT:
It seems like Postgres JDBC (check the section “Unix sockets”) doesn’t support sockets, but maybe you could do a workaround with a TCP forward:
https://coderwall.com/p/c3wyzq/forwarding-tcp-traffic-to-a-unix-socket
Seems like it’s similar to what Google hints at:
https://cloud.google.com/sql/docs/postgres/connect-external-app

@flamber, thanks for suggestions. Some attempts:

Have you tried %3A instead of colon

Yes. I set MB_DB_CONNECTION_URL to postgres://user:pwd@/dbname?host=/cloudsql/project%3Aregion%3Aproject/, but Metabase seems to ignore this and reverts to H2 without throwing any exceptions.

That looks like you can make a string like this:
MB_DB_CONNECTION_URI="postgres://<MY-DB-USER>:<MY-DB-PASS>@/<MY-DB>?cloudSqlInstance='<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>'&socketFactory=com.google.cloud.sql.mysql.SocketFactory

I think this works if you’ve included the relevant Google Cloud libraries in the jar itself, but I don’t know how to do that.

…but maybe you could do a workaround with a TCP forward

This worked for me, finally!

I made my own Dockerfile like this:

FROM metabase/metabase

RUN apk update
RUN apk add --no-cache socat

# Set up symlink to Cloud SQL UNIX socket
RUN ln -s /cloudsql/<instance-name-goes-here>/.s.PGSQL.5432 pg.sock

COPY startup.sh startup.sh
RUN chmod +x startup.sh
ENTRYPOINT ["./startup.sh"]

… where ./startup.sh looks like:

#! /bin/bash

# Forward TCP:5432 to Cloud SQL Unix socket
nohup socat -d -d TCP4-LISTEN:5432,fork UNIX-CONNECT:pg.sock &

# Runs Metabase
/app/run_metabase.sh

I still had to use a symlink because even socat can’t handle paths that contain :.

2 Likes

@ismail What environment variables did you use? Also I’m guessing by instance-name-goes-here you mean an instance connection string like ‘project_id:region:instance_id’?

Were you able to get this to work as you described? Any chance you could explain in more detail? I tried the same thing but it looks like the container keeps restarting, I just see the same log messages repeated (ie. “starting metabase” and installing different drivers), but the application never starts for real…

@vimota I have the same scenario as you have. Have you made any progress with this problem?

So I did used @ismail Dockerfile and startup.sh

I edited the Dockerfile and added the cloud sql instance name (This can be found on your SQL instance overview) as @ismail mentions.

After deployed/pushed the image to Google Container Registry I just went and created a new Google Cloud Run service.

Added 2GB of memory but noticed that 1GB is also fine, selected my image (that I already pushed to Container Registry)

The heres the tricky part:

on the “VARIABLES” tab you need to add:
MB_DB_TYPE = in my case ‘postgres’ without the quotes
MB_DB_DBNAME = your dbname
MB_DB_USER = your db username
MB_DB_PASS = the db password
MB_DB_HOST = 127.0.0.1
JAVA_OPTS = ‘-Xmx2048m’ <---- without the quotes but with the dash at the beginning, this is to allow the container to use almost all the 1GB of memory instead of only 400Mb as default.

Also on the “CONNECTIONS” tab:
Select you Cloud SQL Connection (it should match your startup.sh instance name)

And deploy…

After deploy if you watch your logs it keeps restarting over and over and the metabase login screen never appears, it keeps saying polishing tables and things like that.

I found that its kind of a BUG, so when you’re on this screen “polishing tables, and all those messages” hit refresh, wait to load and then hit refresh again over and over, you will se how the loading bar will start to advance until it actually gets you to the login screen!!

The only issue is that you need to do the “refresh workaround” every time the container starts…

BUT IT WORKS.

thanks @ismail for the files.

Hope it works for everyone trying to run it on Google Cloud Run

I managed to deploy Metabase on Google Run as well as working around the start up refresh issue.

The trick was to create simple curl that pings main host until the API is ready.
It is also necessary to add a keep alive scheduler around every 5 minutes to prevent the container being destroyed.

If anyone is interested I automated the whole pipeline with GitHub actions here:

thanks @Ismail and @eljamz, I succeeded in deploying metabase on cloud run thanks to you!
As I had to fiddle around a lot to make it work, I thought I would share the detailed steps.
TL;DR we set up postgres in cloud sql as the backend database of metabase, and then deploy a metabase docker file in Cloud Run
I just wanted to play around, so this is proabably not made for production. Here is my step by step guide. I just assume you already have a google cloud project with billing enabled.

I. enable apis
to make your cloud run instance communicate with the postgres instance we are going to set up , we need to enable cloud sql admin api:

  1. go to APIs and service, library
  2. search for google cloud SQL admin api
  3. enable

II. set up the application database using cloud SQL

  1. Go to Storage >> Cloud SQL in GCP console.
  2. Click Create Instance.
  3. Click Choose Postgres.
  4. Enter instance name.
  5. The password you enter here will be for the root user of the database so copy it safe.
  6. I chose version 10

optional:
7. select the region closest to where you are located
8. customize your instance, and choose machine type, shared core (cheaper)

  1. once your postgres SQL instance is ready, click on it to get the conection name (should be something like projectname:datacentrelocation:databasename) and copy it somewhere

III. set up google cloud CLI and then prepare metabase docker in upload in cloud run

  1. in order to send your local docker files to your cloud instance, you have to install the Google Cloud CLI command line tool by going to Installa l'interfaccia a riga di comando gcloud  |  Google Cloud and follow instructions
  2. in your local computer, in command line, run
gcloud init

and follow instructions
3. on your local computer, create a new directory in your computer named metabase
4. Open your favorite editor and create a file named Dockerfile inside the metabase directory. Don't forget the upper case on the D or it wont work
5. Copy the below code and add this in your Dockerfile file .
Replace the cloudsql_connection_name with your Cloud SQL instance connection name.

FROM metabase/metabase

RUN apk update
RUN apk add --no-cache socat

# Set up symlink to Cloud SQL UNIX socket
RUN ln -s /cloudsql/cloudsql_connection_name/.s.PGSQL.5432 pg.sock

COPY startup.sh startup.sh
RUN chmod +x startup.sh
ENTRYPOINT ["./startup.sh"]
  1. Save your file.
  2. Create a new file in the same metabase directory and name it as startup.sh

Copy the below code and add this in your startup.sh file.

#! /bin/bash

# Forward TCP:5432 to Cloud SQL Unix socket
nohup socat -d -d TCP4-LISTEN:5432,fork UNIX-CONNECT:pg.sock &

# Runs Metabase
/app/run_metabase.sh
  1. save file
  2. Inside your terminal switch to the metabase folder you jsut created and set your Gcloud project by running:

gcloud config set project your_project_name

  1. Then, we can build the image (image name contains project name and the folder name, metabase in this case):

gcloud builds submit --tag Google Cloud console

  1. It takes some time, and then we can deploy it to the Cloud run by:

gcloud run deploy metabase --region europe-west1 --platform managed --image Google Cloud console --port 80

  1. During the very first deployment, it will ask if we want to allow unauthenticated invocations — which will basically make the instance public or accessible with permissions.

  2. And now, when we check in the Gcloud, we’ll see our instance running. it is not working yet. now, in the google console, click on cloud run, and then on your metabase instance

  3. at the top, click on edit & deploy new revision and add the following parameters:
    container port: 3000
    optinonaly , to keep cost down, set memory to1 Gib and CPU to 1, autoscaling minimum isntance to 1 and max to 1
    15 below, set environment variables to the following:
    MB_DB_TYPE = in my case ‘postgres’ without the quotes
    MB_DB_DBNAME = your dbname
    MB_DB_USER = your db username
    MB_DB_PASS = the db password
    MB_DB_HOST = 127.0.0.1
    JAVA_OPTS = ‘-Xmx2048m’ <---- without the quotes but with the dash at the beginning, this is to allow the container to use almost all the 1GB of memory instead of only 400Mb as default.

  4. click on the “CONNECTIONS” tab:
    Select you Cloud SQL Connection (it should match your startup.sh instance name)

  5. click on deploy

  6. now wait for the deployment to finish, go back to your cloud run instance and you should have a url at the top, click on it and you start playing around!

1 Like