1

Solution

The problem was that my VPN wasn't allowing any internal networking connections, I didn't realise that was possible.

What I'm trying to do

On my Ubuntu system run postgres within a docker container, then from the Ubuntu system connect to the container using psql. Note - I don't want to enter the container then run psql, I want to be able to connect to the running container from the OS using psql.

What I've tried

When I run the following command:

docker run --rm -d \
        -e POSTGRES_PASSWORD=password \
        -e POSTGRES_DB=example \
        -e POSTGRES_USER=user \
        -p 5432:5432 postgres:14.6-bullseye

I have the output:

> docker ps 
CONTAINER ID   IMAGE                    COMMAND                  CREATED         STATUS        PORTS                                       NAMES
bd655ef0830a   postgres:14.6-bullseye   "docker-entrypoint.s…"   3 seconds ago   Up 1 second   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   tender_ardinghelli

When I try connecting to this using the following psql command (this is run from my OS, not from within the container):

psql -h localhost --port 5432 --dbname example -U user

I get the error:

psql: error: connection to server at "localhost" (::1), port 5432 failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

I don't understand why this is happening - as I have explicitly stated which port I want to connect to, and I have set the ports (i think) correctly in the docker run command.

Updates

Try using a different port in docker run

"If you have a postgres running on the host OS, try to avoid issues by forwarding and connecting to a different port than 5432"

I tried altering the docker run command to:

docker run --name example \
    --rm -d \
    -e POSTGRES_PASSWORD=password \
    -e POSTGRES_DB=db -e POSTGRES_USER=user \
    -p 5499:5432 \
    postgres:14.6-bullseye

Where I've changed the host port from 5432 to 5499 in case there's a conflict with postgres already running on the host system.

After running the docker run command above I have docker ps output of:

$ docker ps
CONTAINER ID   IMAGE                    COMMAND                  CREATED         STATUS        PORTS                                       NAMES
4172d2d12bb8   postgres:14.6-bullseye   "docker-entrypoint.s…"   2 seconds ago   Up 1 second   0.0.0.0:5499->5432/tcp, :::5499->5432/tcp   example

Trying to connect from the host using psql i have:

$ psql -h localhost --port 5499 --dbname db -U user
psql: error: connection to server at "localhost" (::1), port 5499 failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

Which is the same error :S

Try changing listen_addresses in postgres.conf

I have updated /etc/postgresql/14/main/postgresql.conf to have the following line:

listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)

And restarted postgres using:

sudo systemctl restart postgresql

But I have the same error as above.

system info

Ubuntu system:

No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.1 LTS
Release:        22.04
Codename:       jammy

Docker version:

Docker version 20.10.22, build 3a2c30b

host psql version

$ psql --version 
psql (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)

Run from the host: systemctl status postgresql

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2022-12-31 02:18:33 GMT; 1min 0s ago
    Process: 17727 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 17727 (code=exited, status=0/SUCCESS)
        CPU: 1ms

What directories contain post, run from /etc:

$ /etc> find . -maxdepth 1 -type d | sort | grep post
./postgresql
./postgresql-common
baxx
  • 3,956
  • 6
  • 37
  • 75
  • I think I recall the default being Postgres only binds to the localhost interface (127.0.0.1) and you had to change some stuff in the configuration to get it listen to all the interfaces (such as the Docker one).. Check out [this post](https://stackoverflow.com/questions/3278379/how-to-configure-postgresql-to-accept-all-incoming-connections) – Mike Christensen Dec 31 '22 at 02:03
  • thanks @MikeChristensen, is this suggesting that I change `psql -h localhost --port 5432 --dbname example -U user` to `psql -h 0.0.0.0/0 --port 5432 --dbname example -U user` ? I tried that and it failed, I also tried without `-h ...` and that failed as well. – baxx Dec 31 '22 at 02:14
  • 1
    Are you running the database in a container, or on the host via systemd? Is it possible that systemd is binding to `127.0.0.1:5432`, attempting to start a non-container database, and failing? – David Maze Dec 31 '22 at 02:32
  • @baxx I'm saying you have to connect to your running container and modify your postgresql.conf file. It's kinda weird the Docker image isn't setup that way to begin with though. – Mike Christensen Dec 31 '22 at 03:25
  • 3
    I cannot replicate this. This error ("server closed the connection unexpectedly" immediately upon launching a connection) is generally caused be a port forwarder or network virtualization run amok. So I would say the problem is not with PostgreSQL, but with docker itself. What is your version of docker, and what OS is it running on? – jjanes Dec 31 '22 at 04:08
  • 2
    How do you get your container to run `sudo systemctl restart postgresql`? In my hands, this image will not have sudo installed on it. – jjanes Dec 31 '22 at 04:10
  • 1
    Docker images also don't have systemctl either. Perhaps you should `docker exec` **then** use `psql`? – OneCricketeer Dec 31 '22 at 05:27
  • @jjanes That's run from the OS, not the container. So I guess it's kinda pointless to do :S – baxx Dec 31 '22 at 14:02
  • @OneCricketeer I want to have a container running postgres, and connect to that using psql from the OS, rather than entering the container then running psql – baxx Dec 31 '22 at 14:03
  • @jjanes I've added information about the docker/OS versions to the post – baxx Dec 31 '22 at 14:06
  • 1
    @MikeChristensen ah - rather than editing the file within my OS, I need to edit the file within the container. – baxx Dec 31 '22 at 14:07
  • If you have PostgreSQL running on the docker host, that would prevent your container from being able to map the 5432 port as it would be used already. Normally this would throw an error and refuse to start the container (on Linux). How did you install docker? Jammy's repo gives me 20.10.17, which 5 bug fix releases behind yours. – jjanes Jan 01 '23 at 17:39
  • @jjanes I think that _might_ be an issue (PostgreSQL running on the docker host), but I'm not sure how to fix / check that. I installed docker following the guide on the website https://docs.docker.com/engine/install/ubuntu/ – baxx Jan 01 '23 at 18:52
  • @baxx If you have a postgres running on the host OS, try to avoid issues by forwarding and connecting to a different port than 5432. – Bergi Jan 01 '23 at 18:59
  • @Bergi thanks - I've added the output of that suggestion to the post above, seems that it fails in the same way – baxx Jan 01 '23 at 19:05

0 Answers0