Subject: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

From: Alexander Farber <[email-masked]>
Date: Sun, 1 Dec 2024 20:34:48 +0100

Good evening,

I am trying to create a Dockerfile for development purposes, which
would run Jetty and PostgreSQL. The PostgreSQL related part is below:

FROM postgres:17-alpine3.20
RUN apk update && apk upgrade && apk add --no-cache curl wget openjdk21 tini

# Tell docker-entrypoint.sh to create superuser "postgres"
# with password "mypassword" and database "postgres"
ENV POSTGRES_PASSWORD mypassword

# Tell docker-entrypoint.sh to change these params in postgresql.conf
ENV POSTGRES_INITDB_ARGS "--set port=6432 --set max_connections=20 --set
max_wal_size=2GB"

# The PostgreSQL port is changed from 5432 to 6432 to emulate pg_bouncer
ENV PGPORT 6432
ENV PGUSER words
ENV PGPASSWORD mypassword

WORKDIR /docker-entrypoint-initdb.d
COPY ["run-after-initdb.sh", "."]
RUN chmod +x run-after-initdb.sh && cat run-after-initdb.sh

USER postgres

EXPOSE 6432 9090

ENTRYPOINT ["/sbin/tini", "--"]

CMD ["sh", "-c", "docker-entrypoint.sh postgres & cd $JETTY_BASE && sleep
10 && java -Djdbc.drivers=org.postgresql.Driver -jar $JETTY_HOME/start.jar"]

And below is my run-after-initdb.sh script:

#!/bin/sh

createuser --username=postgres words

psql --username=postgres -c "GRANT USAGE ON SCHEMA public TO words;"
psql --username=postgres -c "ALTER USER words PASSWORD 'mypassword';"

for L in de en ru fr nl pl; do
    createdb --username=postgres --owner=words words_$L
    cd /dict/$L && psql words_$L < words_$L.sql
done

Then I build and run the docker image and that works without errors:

# docker build -t my_docker_image --progress=plain .
# docker run --name my_container -p 8080:8080 -p 6432:6432 my_docker_image

When I connect to the docker container via terminal,
I am able to connect with "psql words_de" command.

But the "psql -h localhost words_de" command fails:

psql: error: connection to server at "localhost" (127.0.0.1), port 6432
failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 6432 failed: Address not
available
        Is the server running on that host and accepting TCP/IP connections?

I need the connection via 127.0.0.1 though,
because of my Java servlet hosted by the Jetty.

I have looked at the config files and env vars:

# ls -al /var/run/postgresql/
srwxrwxrwx    1 postgres postgres         0 Dec  1 19:06 .s.PGSQL.6432
-rw-------    1 postgres postgres        64 Dec  1 19:06 .s.PGSQL.6432.lock

# echo $PGDATA
/var/lib/postgresql/data

# grep -v '^#' $PGDATA/pg_hba.conf
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

# grep -v '^.*#' $PGDATA/postgresql.conf
listen_addresses = '*'
max_wal_size = 2GB
min_wal_size = 80MB
log_timezone = UTC
datestyle = 'iso, mdy'
timezone = UTC
default_text_search_config = 'pg_catalog.english'

Is anybody able spotting, what am I doing wrong here? Thank you

TLDR "psql words_de" works, but "psql -h localhost words_de" does not

Best regards
Alex

---

From: Tom Lane <[email-masked]>
Date: Sun, 01 Dec 2024 14:58:47 -0500

> TLDR "psql words_de" works, but "psql -h localhost words_de" does not

I'd try connecting the first way and seeing what "show
listen_addresses" gives.  Per your report it should be "*",
but maybe something is overriding that.

If it is "*", then it seems like something is interfering with
expanding that.  Maybe try explicitly setting it to "localhost"?

			regards, tom lane

---

From: Alexander Farber <[email-masked]>
Date: Sun, 1 Dec 2024 21:36:28 +0100

Thank you, Tom -

> expanding that.  Maybe try explicitly setting it to "localhost"?

it is empty in my docker container (but "localhost" on my prod server),
so I probably need to investigate that...

$ psql words_de
psql (17.2)
Type "help" for help.

words_de=> show listen_addresses;
 listen_addresses
------------------

(1 row)

words_de=> set listen_addresses='localhost';
ERROR:  parameter "listen_addresses" cannot be changed without restarting
the server

# grep listen_addresses $PGDATA/postgresql.conf
listen_addresses = '*'

---

From: Alexander Farber <[email-masked]>
Date: Sun, 1 Dec 2024 21:50:52 +0100

I have tried changing the line in the Dockerfile, but it has not helped yet:

ENV POSTGRES_INITDB_ARGS "--set port=6432 --set max_connections=20 --set
max_wal_size=2GB --set listen_addresses=localhost"

ENV POSTGRES_INITDB_ARGS "--set port=6432 --set max_connections=20 --set
max_wal_size=2GB --set listen_addresses='localhost'"

Maybe the quotes get lost somehow when using the "--set" option?

$ grep listen $PGDATA/postgresql.conf
listen_addresses = localhost

---

From: Alexander Farber <[email-masked]>
Date: Sun, 1 Dec 2024 21:59:25 +0100

My problem is related to
https://github.com/docker-library/postgres/pull/440/files

But I am yet not sure how to enable listening at localhost again

---

From: Tom Lane <[email-masked]>
Date: Sun, 01 Dec 2024 16:09:35 -0500

> so I probably need to investigate that...

I'm betting the postgresql.conf entry is being overridden with a
postmaster command-line switch (not an initdb switch, which merely
sets up a postgresql.conf entry).

			regards, tom lane

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 1 Dec 2024 13:12:50 -0800

> I am able to connect with "psql words_de" command.

The terminal is in the container host?

If so does the host have an instance of Postgres running on it?

-- 
Adrian Klaver
[email-masked]

---

From: Peter Kleiner <[email-masked]>
Date: Sun, 1 Dec 2024 16:21:34 -0500

Seems too easy but have you tried

psql -p 6432 -h localhost words_de

?

---

From: Alexander Farber <[email-masked]>
Date: Sun, 1 Dec 2024 22:25:54 +0100

Yes thank you, I have tried specifying the port at the CLI too... that did
not help.

But restarting PostgreSQL in my
custom /docker-entrypoint-initdb.d/run-after-initdb.sh
has helped, even though I am not sure if it is the best way:

#!/bin/sh

LANGUAGES="de en fr nl pl ru"

for L in $LANGUAGES; do

# Restart PostgreSQL to make it listen at localhost too
pg_ctl --options "-c listen_addresses='localhost'" --wait restart

---

From: Torsten Krah <[email-masked]>
Date: Mon, 02 Dec 2024 12:31:56 +0100

> But I am yet not sure how to enable listening at localhost again

Instead of using a TCP/IP connection, why not use the unix socket to
connect to your database [1]?

[1] https://jdbc.postgresql.org/documentation/use/#unix-sockets

---

From: Alexander Farber <[email-masked]>
Date: Mon, 2 Dec 2024 12:49:13 +0100

Thank you Torsten,

> [1] https://jdbc.postgresql.org/documentation/use/#unix-sockets
I will try that option too!

---

From: Jeremy Smith <[email-masked]>
Date: Mon, 2 Dec 2024 08:25:13 -0500

> Is anybody able spotting, what am I doing wrong here? Thank you

Unless I'm mistaken, it seems like you are trying to run jetty and postgres
in the same container.  You should really use two containers and run them
both with docker compose.  Docker is very much designed to run a single
service.  This should also greatly simplify your Dockerfile - you could
probably just use the vanilla postgres image for the postgres container.
Here's some info on how to achieve this:

https://www.docker.com/blog/how-to-use-the-postgres-docker-official-image/#Using-Docker-Compose