Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Alexander Farber
Date:
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

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

From
Tom Lane
Date:
Alexander Farber <alexander.farber@gmail.com> writes:
> 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

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

From
Alexander Farber
Date:
Thank you, Tom -

On Sun, Dec 1, 2024 at 8:58 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexander Farber <alexander.farber@gmail.com> writes:
> 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"?

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 = '*'


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

From
Alexander Farber
Date:
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



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

From
Alexander Farber
Date:
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

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

From
Tom Lane
Date:
Alexander Farber <alexander.farber@gmail.com> writes:
> it is empty in my docker container (but "localhost" on my prod server),
> 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

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

From
Adrian Klaver
Date:
On 12/1/24 11:34, Alexander Farber wrote:
> Good evening,
> 
> I am trying to create a Dockerfile for development purposes, which
> would run Jetty and PostgreSQL. The PostgreSQL related part is below:
> 

> When I connect to the docker container via terminal,
> 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?

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


> Best regards
> Alex
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

From
Peter Kleiner
Date:
Seems too easy but have you tried

psql -p 6432 -h localhost words_de
?

On Sun, Dec 1, 2024 at 3:59 PM Alexander Farber <alexander.farber@gmail.com> wrote:
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

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

From
Alexander Farber
Date:
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"

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 $LANGUAGES; do
    createdb --username=postgres --owner=words words_$L
    cd /dict/$L && psql words_$L < words_$L.sql
done

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

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

From
Alexander Farber
Date:
Hi again, I would like to share a slightly better workaround
(does not restart PostgreSQL) for enabling localhost connections
(for example for servlets) in a postgres:17-alpine3.20 based Dockerfile:

RUN sed -i "s/listen_addresses=''/listen_addresses='localhost'/" /usr/local/bin/docker-entrypoint.sh

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

From
Torsten Krah
Date:
Am Sonntag, dem 01.12.2024 um 21:59 +0100 schrieb Alexander Farber:
> 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

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

From
Alexander Farber
Date:
Thank you Torsten,

On Mon, Dec 2, 2024 at 12:32 PM Torsten Krah <krah.tm@gmail.com> wrote:
Am Sonntag, dem 01.12.2024 um 21:59 +0100 schrieb Alexander Farber:
> 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


I will try that option too!
 

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

From
Jeremy Smith
Date:

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: