Configure google cloud sql as a service

Jean-Xavier Raynaud

Hi all,

 

What I'm trying to do is to configure a pipeline that would connect to postgresql google cloud instance, and run django migrations.

I experimented a bit, and so far I wasn't very successful.

I can use the google/cloud-sdk image to connect to the instance, and it seems to be runing fine using those commands:

-step:
  image: google/cloud-sdk
  script:
    - echo $GOOGLE_CLIENT_SECRET > ./gcloud-api-key.json 
    - gcloud auth activate-service-account --key-file gcloud-api-key.json
    - gcloud config set project $CLOUDSDK_CORE_PROJECT
    - apt-get install wget
    - wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
    - chmod +x cloud_sql_proxy
    - ./cloud_sql_proxy -instances="[$DEV_GCP_DB_INSTANCE]"=tcp:5432

However if I run this as a step, it won't help much because it run as a server:

<span>+ ./cloud_sql_proxy -instances="[$DEV_GCP_DB_INSTANCE]"=tcp:5432<br>2018/11/20 00:21:38 current FDs rlimit set to 1048576, wanted limit is 8500. Nothing to do here.<br>2018/11/20 00:21:38 Listening on 127.0.0.1:5432 for [test-django-pg:europe-west1:test-django-db]<br>2018/11/20 00:21:38 Ready for new connections<br><br><br></span>

<span>So I need to run it as a service. I tried multiple solutions but so far I was unlucky.</span>

<span>Here's the last one:</span>

<span>  branches:<br>&nbsp;&nbsp;&nbsp; dev:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - step:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; image: python:3.6.2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;    caches:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - pip<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; script:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - pip install -r requirements.txt <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - python monsite/manage.py migrate <br><br>definitions:<br>&nbsp; services:&nbsp;&nbsp;&nbsp; <br>    cloudsql:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; image: google/cloud-sdk<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; command: <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - echo $GOOGLE_CLIENT_SECRET > ./gcloud-api-key.json <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - gcloud auth activate-service-account --key-file gcloud-api-key.json<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - gcloud config set project $CLOUDSDK_CORE_PROJECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - apt-get install wget<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - chmod +x cloud_sql_proxy<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - ./cloud_sql_proxy -instances="[$DEV_GCP_DB_INSTANCE]"=tcp:5432<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; restart: always</span>

 I also tried using the image: gcr.io/cloudsql-docker/gce-proxy:1.13 but this image requires a credential file to be passed as an argument to cloud_sql_proxy and I wasn't able to do it.

 

Any help welcome !

 

Thanks

Jean-Xavier Raynaud

I finally found a solution: keep it simple. Run the proxy in background directly in the step:

 branches: 
 # Deployement to gcp on test server
   dev:
     - step:
         image: python:3.6.2
         caches:
           - pip
         script: 
           # First we need to download the gcloud auth.
           - curl -o /tmp/google-cloud-sdk.tar.gz https://dl.google.com/dl/cloudsdk/channels/rapid/downloads/google-cloud-sdk-183.0.0-linux-x86_64.tar.gz
           - tar -xvf /tmp/google-cloud-sdk.tar.gz -C /tmp/
           - /tmp/google-cloud-sdk/install.sh -q
           - source /tmp/google-cloud-sdk/path.bash.inc

           # Activate gcloud register secret json and link to the project
           - echo $GOOGLE_CLIENT_SECRET > ./gcloud-api-key.json 
           - gcloud auth activate-service-account --key-file gcloud-api-key.json
           - gcloud config set project $CLOUDSDK_CORE_PROJECT

           # Download cloud_sql_proxy and run it as proxy
           - wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
           - chmod +x cloud_sql_proxy
           - ./cloud_sql_proxy -instances="$DEV_GCP_DB_INSTANCE"=tcp:5432 > cloudsql.log 2>&1 &

           # Migrate, collecstatic, check for superuser, test, coverage etc.
           - pip install -r requirements.txt # Install or upgrade dependencies
           - python monsite/manage.py migrate # Apply database migrations
           - mkdir static # Used to store static files 
           - python monsite/manage.py collectstatic --noinput # Collect static files
           - coverage run --source='monsite/' monsite/manage.py test --noinput # Run converage functions 
           - coverage report # To get the report directly in pipelines 

           # Generate app.yaml and push to server

         # Save log as artifact
         artifacts:
           - cloudsql.log

 

You'll need the following repository variables to make it work:

• $GOOGLE_CLIENT_SECRET : copy paste the content of the json key file from gcp

• $CLOUDSDK_CORE_PROJECT: your core project

• $DEV_GCP_DB_INSTANCE: your cloud sql instance id (not the instance name, the whole thing with the project name, the project zone and the instance name)

Also note that your service account must have the following roles:

• storage admin
• app engine admin
• sql client editor

And the SQL admin api must be enable.