Install Postgres extensions in bitbucket pipeline

Question

So I've setup a bitbucket-pipelines.yml for my python app. It needs a postgres database so I've followed the tutorial here (https://confluence.atlassian.com/bitbucket/test-with-databases-in-bitbucket-pipelines-856697462.html) which has led me to the following config:

image: node 
pipelines: 
  default: 
- step: 
    script: 
      - npm install
      - npm test
    services: 
      - postgres

definitions:  
   services: 
      postgres: 
        image: postgres 
        environment: 
           POSTGRES_DB: 'pipelines' 
           POSTGRES_USER: 'test_user'
           POSTGRES_PASSWORD: 'test_user_password'

I need some specific extensions in my db, how can I add these. I tried to add an extra in the script that installs them but at that point postgres doesn't seem to be up and running.

Answer 1

This worked for me, without the need to build my own image (I add 2 extensions to postgres):

image: node:8.11.1 # or any image you need

clone:
  depth: 1       # include the last commit

definitions:
  services:
    postgres:
      image: postgres
      environment:
        POSTGRES_DB: test
        POSTGRES_USER: postgres
        POSTGRES_PASSWORD: your_password

pipelines:
  default:
    - step:
        caches:
          - node
        script:
          - npm install
          - apt-get update
          - apt-get -y install postgresql-client
          - ./bin/utilities/wait-for-it.sh -h localhost -p 5432 -t 30
          - PGPASSWORD=your_password psql -h localhost -p 5432 -c "create extension if not exists \"uuid-ossp\"; create extension if not exists pg_trgm;" -U postgres test;
          - npm test test/drivers/* test/helpers/* test/models/*
        services:
          - postgres

wait-for-it.sh makes sure that postgres is ready to run, you can find it here: https://github.com/vishnubob/wait-for-it

Then, I run psql to create the extensions in the test database. Here note the variable PGPASSWORD I set before running that and that I use the -h and -p parameters to connect to the running postgres instance (otherwise it will try to do it with unix sockets, which doesn't seem to work).

Answer 2

You need to create your own image based on postgres, then push it to repository and use in pipeline

definitions:  
   services: 
     postgres: 
        image: your_custom_image_based_on_postgres 
        environment: 
           POSTGRES_DB: 'pipelines' 
           POSTGRES_USER: 'test_user'
           POSTGRES_PASSWORD: 'test_user_password'

You can also find image that suit your requirements in https://hub.docker.com/