Install Postgres extensions in bitbucket pipeline

[Imdad Ahad]

So reading the docs I see that setting up Postgres in a bitbucket pipeline is as simple as adding the following:

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'

What I'd like to know is how I can install some extensions in the database before proceeding further?

[Alexandros Solanos]

Hey, I had the same problem, and I've answered it here: https://stackoverflow.com/a/50109002/873227

[Yadvendra_Singh]

Hi Alexandros,

I am facing the same problem but unable install postgis extension.

Thanks

[Alexandros Solanos]

@Yadvendra_Singhhow does it fail for you? What it basically does is to isntall "psql" and run the create extension commands inside the database, seems pretty fail-safe!

I still use this way everyday!

[Yadvendra_Singh]

it is failing at create extension postgis.

- PGPASSWORD=XXXXXX psql -h localhost -p 5432 -c "create extension postgis; create extension postgis_topology;" -U postgres bgs;

ERROR: could not open extension control file "/usr/share/postgresql/12/extension/postgis.control": No such file or directory

[Alexandros Solanos]

@Yadvendra_Singhthis is a postgis-related problem. With a quick google search with this error message, a lot of results were returned, especially in stackoverflow, with highly voted answers, like this one: https://gis.stackexchange.com/a/108401

[Jack Lim]

Hi @Alexandros Solanos . I have installed the postgresql-12-postgis-3-scripts and checked that I have the postgis.control file in the directory:

find /usr -name postgis.control

/usr/share/postgresql/12/extension/postgis.control

but I'm still getting the error:

PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d test -c "CREATE EXTENSION postgis;";

ERROR: could not open extension control file "/usr/share/postgresql/12/extension/postgis.control": No such file or directory

My script sequence:

1) apt-get install postgis postgresql-12-postgis-3 postgresql-12-postgis-3-scripts  -y

2) find /usr -name postgis.control # to check I have postgis.control in "/usr/share/postgresql/12/extension/" directory

3) chmod +x ./wait-for-it.sh # To allow me to run wait-for-it.sh else I get permission denied error

4) ./wait-for-it.sh -h localhost -p 5432 -t 30

5) PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d test -c "CREATE EXTENSION postgis;";

Any idea why it's not working for me?

[Alexandros Solanos]

Hey @Jack Lim, my stack has changed since the post but I can try to help, sure.

You have 2 docker images at play here: The first one is the one that is running your pipeline. If you haven't specified one, it's the default image specified by atlassian, more on that here.

The second docker image is the one where postgres is running into.

The "apt get install" command you are running is being run in the first container and the "psql -h location -p ..." command is run in the first container but it causes it to communicate with the second container, causing it to execute the "CREATE EXTENSION postgis;". So the postgis extension is installed on the first container but it should really be installed on the second container.

In order to solve this, you should change your service definition on your bitbucket pipelines and use a postgres image that is built with the postgis extension. By a quick google search this seems to be one. Of course, you can build your own image and upload it to a container registry yourself if you can do that, too.

On how to define a custom service (e.g. use a custom image) in your bitbucket pipelines, you can see here.

[Jack Lim]

Hey @Alexandros Solanos . Thank you very much for your explanation and help. Now I understand why I'm getting the error.

I've changed my service definition to use the image kartoza/postgis you have suggested and my issue is resolved.  

definitions:
 services:
  postgres:
    image: kartoza/postgis
    variables:
     POSTGRES_DBNAME: 'test'
     POSTGRES_USER: 'postgres'
     POSTGRES_PASS: 'postgres'
     POSTGRES_MULTIPLE_EXTENSIONS: 'postgis'

Thanks!

[Alexandros Solanos]

Hello, I had the same problem and I managed to solve it with this configuration:

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

My full answer with more explanations can be found here: https://stackoverflow.com/a/50109002/873227