Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Install Postgres extensions in bitbucket pipeline

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?

2 answers

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

Hi Alexandros,

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

Thanks

@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!

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

@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

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?

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.

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!

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Bitbucket

New improvements to user management in Bitbucket Cloud 👥

Hey Community! We’re willing to wager that quite a few of you not only use Bitbucket, but administer it too. Our team is excited to share that we’ll be releasing improvements throughout this month of...

3,840 views 10 16
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you