Failure to connect to postgres docker container running locally

Question

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

Answer 1

My problem was I installed postgress server before on my computer. Because of this I didn't have free port 5432. I changed my port to 5438, and I connected to my server successfully.