Creating postgres docker container in Ubuntu 18.04 like...
docker run -it -e POSTGRES_USER="root" -e POSTGRES_PASSWORD="root" -e POSTGRES_DB="ny_taxi" -v /my/absolute/path/to/my/local/folder/ny_taxi_postgres_data:/var/lib/postgresql/data:rw -p 5432:5432 postgres:13
...but getting errors like:
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (127.0.0.1), port 5432 failed: server closed the connection unexpectedly
when trying to connect via
from sqlalchemy import create_engine
engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')
engine.connect()
I also can't connect when using psql
➜ ~ psql -h localhost -p 5432 -U root -W
Password for user root:
psql: connection to server at "localhost" (127.0.0.1), port 5432 failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
or using pgadmin4 from my local desktop or changing "localhost" to the value "0.0.0.0"
I see no local postgres service running:
➜ ~ systemctl status postgresql
Unit postgresql.service could not be found.
➜ ~ service postgresql status
Unit postgresql.service could not be found.
and this is the only thing that appears to be using the specified port:
sudo lsof -i -P -n | grep LISTEN
.
.
.
docker-pr 31534 root 4u IPv6 4358622 0t0 TCP *:5432 (LISTEN)
This is also the only docker container I have running and the image was just downloaded today:
➜ ~ docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
406c9a5efc34 postgres:13 "docker-entrypoint.s…" 8 minutes ago Up 7 minutes 0.0.0.0:5432->5432/tcp affectionate_bose
Anyone have any ideas what could be going on here? Any debugging suggestions?
*UPDATE
The original debug observation by @jjanes in the comments initially worked for my issue (I have now installed the lasted version of docker and containers now use both IPv4 and IPv6), but now no longer working though I don't recall changing anything since the last time I checked.
Am still / once again getting errors when running
from sqlalchemy import create_engine
engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')
engine.connect()
with an error message
OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (127.0.0.1), port 5432 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
and cannot connect to the DB with pgadmin4 or psql like
➜ psql -h localhost -p 5432 -U root -d ny_taxi -W
Password for user root:
psql: connection to server at "localhost" (127.0.0.1), port 5432 failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Yet I see now that both IPv4 and IPv6 are being used:
➜ sudo lsof -i -P -n | grep LISTEN
.
.
.
docker-pr 29152 root 4u IPv4 6892807 0t0 TCP *:5432 (LISTEN)
docker-pr 29164 root 4u IPv6 6890891 0t0 TCP *:5432 (LISTEN)
➜ docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
47c77203991c postgres:13 "docker-entrypoint.s…" 25 minutes ago Up 25 minutes 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp sleepy_chandrasekhar
The logged output I see from the docker container is
2022-08-12 03:59:12.942 UTC [1] LOG: starting PostgreSQL 13.8 (Debian 13.8-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2022-08-12 03:59:12.942 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-08-12 03:59:12.942 UTC [1] LOG: listening on IPv6 address "::", port 5432
2022-08-12 03:59:12.954 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-08-12 03:59:12.971 UTC [62] LOG: database system was shut down at 2022-08-12 03:59:12 UTC
2022-08-12 03:59:12.982 UTC [1] LOG: database system is ready to accept connections
This log output for the DB container does not change even when trying to psql connect using a wrong username or password (eg. having some message about a failed login attempt) -- thus making me suspect some kind of port forwarding issue with docker.
Seems very weird. Anyone know what could be going on here? Anything I should check?
