How do I use Postgres in Bitbucket Pipelines

SebC
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 12, 2017

How do I get a postgres database to run with my pipelines?

4 answers

1 accepted

2 votes
Answer accepted
SebC
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 12, 2017

You'll need to use services in bitbucket pipelines

In the most simple cases you can connect to localhost:5432 with database 'postgres', user 'postgres' and no password.

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

If you want to specify a different user or password; the below yaml will create a database server on localhost:5432 with database 'pipelines', user 'test_user' and password 'test_user_password'

pipelines:
  default:
    - step:
        image: node
        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
Jacques Yakoub May 22, 2017

nothing about the psql command ? I would like to use this to prepare the database before I run test ....

Like # people like this
abdulsemiu-atanda July 10, 2017

Hi I tried following your answer here but seems I'm missing something 

abdulsemiu-atanda July 10, 2017
image: node:8.1.3

definitions:
services: postgres: image: postgres
environment: POSTGRES_DB: postgres POSTGRES_USER: postgres DATABASE_URL: postgres://postgres@127.0.0.1:5432/testdb pipelines: default: - step: caches:
- node - postgres script: # Modify the commands below to build your repository. - psql -c 'drop database if exists testdb;' -U postgres - psql -c 'create database testdb;' -U postgres
- npm install - npm run model - npm test services:
- postgres
Like Marc Tranzer likes this
abdulsemiu-atanda July 10, 2017

I figured out the error in my config file. Thanks

Werner Lindgård September 21, 2017

I have followed these steps as well, running a node image, and I have the postgres service up and running. 

I also have a set of .sql files in my project that defines various tables etc. that I need for integration testing.

In my bitbucket-pipelines.yml file, under the script section, I also have the "psql -U postgres -f \"xyz.sql\"" command to initialise the database.

When Pipelines is running this, I get an error message that "psql: not found"... 

If the above example is running, can anyone point me in the direction of how to get this running in my project?

Werner Lindgård September 22, 2017

I have tried to do the same setup in my pipeline.
I run a Node image (as parent), has set up the Postgres service, and can see that they are both running in the Pipeline when picked up by BitBucket.

 

I have an issue running the scripts though. I get the error message "psql: not found".

 

How to be able to run a given set of scripts, located in my *.sql files in the project, to enable integration tests?

I figure I can create my own Docker image, install Postgres in this image, and then run it from there, but doesn't that defeat the entire purpose of using the services?

Any help is appreciated :)

Pratik Mandrekar October 25, 2017

You will probably need to install the psql client manually.

 

So add the below line of installation script before you invoke psql

sudo apt-get update && sudo apt-get install -y postgresql-client
Like # people like this
sergio_pelin July 7, 2018

@Werner Lindgård Hi Werner. I assume that you managed to solve the "psql: not found" issue. I would greatly appreciate if you could share your solution. Many thanks!

sergio_pelin July 9, 2018

@abdulsemiu-atanda Could you post your version that worked please.

I'm trying all the ways but cannot make a postgres image work. My best result is:

psql: could not connect to server: No such file or directory
abdulsemiu-atanda July 9, 2018

@sergio_pelin

 

pipelines:
default:
- step:
image: node
script: # Modify the commands below to build your repository.
- npm install
-
npm run migrations
- npm run model
- npm test
services:
- postgres

definitions:
services:
postgres:
image: postgres
environment:
POSTGRES_DB: postgres
POSTGRES_USER: postgres
DATABASE_URL: postgres://postgres@127.0.0.1:5432/postgres
sergio_pelin July 9, 2018

Thanks @abdulsemiu-atanda!

However, I can see that you finally didn't use `psql` command in this version. Did you succeed using this command in some configuration?

abdulsemiu-atanda July 9, 2018

yeah...it wasn't necessary since I already specified that I'm using a Postgres image which bitbucket provides expressly with this configuration.

sergio_pelin July 9, 2018

@abdulsemiu-atanda Again, thanks!

abdulsemiu-atanda July 9, 2018

@sergio_pelin

the only thing missing in the config I shared is the node image which you can add to the top of this configuration as it is in my failing config earlier in this thread. I hope it works for you too

sergio_pelin July 9, 2018

@abdulsemiu-atanda My issue is that I want to execute command line instructions via `psql` at the beginning of the test, but I can't find the way to do so...

Perhaps @SebC could help me :)

abdulsemiu-atanda July 9, 2018

@sergio_pelin have you tried executing the command in the script section 'cause you should be able to since psql command should be made available as a result of postgres image being available in your test environment. Also, you may want to see if Bitbucket pipelines have a before_script command like TravisCI config so you can have your psql commands there.

sergio_pelin July 9, 2018

@abdulsemiu-atanda I tried a lot in different ways, but it doesn't work. Hence I opened this question with a bit more detail.

abdulsemiu-atanda July 9, 2018

@sergio_pelin

can you try this in your bitbucket-pipelines.yml and post back what errors you are getting here

image: node:8.1.3

pipelines:
default:
- step:
image: node
script: # Modify the commands below to build your repository.
- psql # your psql command here
services:
- postgres
definitions:
services:
postgres:
image: postgres
environment:
POSTGRES_DB: postgres
POSTGRES_USER: postgres
DATABASE_URL: postgres://postgres@127.0.0.1:5432/postgres
sergio_pelin July 9, 2018

@abdulsemiu-atanda Here's the log:

+ psql
bash: psql: command not found 

 It seems though, that the service is starting on a wrong address: "0.0.0.0" instead of "127.0.0.1":

2018-07-09 23:35:38.940 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432

Could you see this output in your case?

abdulsemiu-atanda July 9, 2018

@sergio_pelin are you trying to prepopulate your database with the psql command 'cause if that's the case, you may want to use seeders instead, Also, you may want to add the command to your package.json script. it would help me understand the issue better if I know what the command is to do

abdulsemiu-atanda July 9, 2018

@sergio_pelin update the address in that case to "0.0.0.0". I didn't have your issue 'cause my tests didn't need anything that requires me to run psql commands

sergio_pelin July 10, 2018

I found a solution (explained here).

Thanks to @abdulsemiu-atanda and @Pratik Mandrekar for hints and support!

0 votes
Anoob Bava June 30, 2021

Is there a solution to this issue? mentioned by @rajeshkanhasoft @patriceharapeti @SebC 

0 votes
rajeshkanhasoft May 30, 2019

Hello, 

I am also getting the same problem connecting psql. I am using below script for connecting please let me know if i am making any mistake.

image: python:3.7.3
definitions:
services:
postgres:
image: postgres
environment:
POSTGRES_DB: postgres
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres

pipelines:
default:
- step:
caches:
- pip
script: # Modify the commands below to build your repository.
- psql -c 'drop database if exists testdb;' -U postgres
- psql -c 'create database testdb;' -U postgres
- pip install -r requirements.txt
- python manage.py makemigrations
- python manage.py migrate
services:
- postgres

I am getting bash: psql: command not found error.

In my case i want to drop the database if already exists and recreate the database.

Please anyone help me to get solution for this.

Thanks in advance.

patriceharapeti July 5, 2019

apt-get install postgresql -y

Anoob Bava July 1, 2021

Is there a solution to this issue? mentioned by @SebC @patriceharapeti @rajeshkanhasoft 

0 votes
Werner Lindgård July 11, 2018

@sergio_pelin Hello again!
This issue has been on the back-burner for a while, so I have not come up with a good solution yet.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events