How to fix: 'Psql: could not connect to server: Connection refused' when using Postgres in a docker container in gitlab-ci

Docker Hub CI/CD
,
1

I’m currently trying to run fossology in Gitlab CI. Fossology requires an external database that can be set up from a schema created using pg_dump. When I’m trying to use psql I get the title error.

At the moment, I have a script that sets up a container that runs the required version of postgres (9.6). It then tries to run an .sql script via psql in the postgres container via docker exec . Upon doing so it gets the title error.

I have tried specifying both a port and a host when issuing the psql statement, neither of which worked. I have tried using localhost, 127.0.0.1, the IP address of the postgres container and the name of the container as a host. I have tried rewriting things in different scripts, but nothing seems to work.

After extensive google searching, many people seem to have the same error message but not for the same reasons and not usually when using a docker container to host the database.

When I have run the contents of my script in the command line, i do not get this error, the script works fine and I can connect to Fossology. The issue only arises when trying to do the same in Gitlab CI.

The sequence of steps (i.e. pasted line by line) that works when using the command line on Mac:

# creates blank database and hosts it in a docker container
docker run -d --name fossdb -p 5432:5432 postgres:9.6
docker cp /fossology_db_schema.sql fossdb:/fossy.sql 
docker exec -it fossdb bash
psql postgres -U postgres

# creates user needed for database to work with fossology
create user fossy with password 'fossy';
create database fossology;
grant all privileges on database fossology to fossy;
\q

# builds the fossology database in the hosted blank database
psql fossology < fossy.sql
psql postgres -U postgres
\connect fossology
exit

What I am attempting in GitLab CI:

# creates container with postgres image
docker run -d --name fossdb -p 5432:5432 --network foss-net postgres:9.6

# creates blank database (error occurs here)
docker exec fossdb psql -h $(docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' fossdb) -f ./createBlank.sql -U postgres

# builds fossology database from schema 
docker exec fossdb psql -h $(docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' fossdb) fossology < ./schema.sql -U postgres

Expected results: runs createBlank.sql to create a blank database called fossology, then builds fossology database from schema

Actual results: psql: could not connect to server: Connection refused Is the server running on host “172.19.0.2” and accepting TCP/IP connections on port 5432?

2

If fossdb is the container where your Postgres is running then you don’t have to use the -h flag because you connect to localhost.

3

I have tried not using the host flag, but unfortunately it gets the same error. I have updated the pg_hba.conf & the postgresql.conf to allow pretty much all connections but nothing seems to be working.

I’m not sure that I am mounting the volume right. I am creating a new volume using docker volume create, placing the two files inside that volume, and then using -v vol:/var/lib/postgresql/data/ in my docker run statement. Do you know if this is the correct way to go about it?

4

There are many correct ways. I would recommend you use docker-compose and pack everything into a compose file, then things are easier to reproduce. And I don’t recommend that you touch the config files that come with the Postgres image.
When I look at your exec command: Why is -U postgres placed after <?

5

I’m going to look a bit further into your docker-compose suggestion, at the moment I’m just running a script to create it. I’m only touching the config files to try open up connections as no matter the type of host I specify (if i specify one) i get the same type of error, either that it cannot connect or that the connection has been refused.

After realising that mounting a volume to a specific directory replaces everything in the directory, I have decided to try to edit the files in a temporary postgres container, and then create a new image from this container. However, it is now saying that none of the config files exist.

-U postgres was mistakenly placed after <, but after changing this it hasn’t made a difference.

6

The Postgres image works, I use it nearly every day for development and connect from other containers to it. So there is no need to manipulate anything.
Mounting doesn’t replace files in a directory, it replaces the directory itself.
Seems there is a misunderstanding about the type of the connection. If you call docker exec fossdb the following command runs inside the container and psql connects through a Unix domain socket. All commands that you type into the shell of the container should work with docker exec too.

1 Like