search

rustprooflabs / pgosm-flex

PgOSM Flex provides high quality OpenStreetMap datasets in PostGIS (Postgres) using the osm2pgsql Flex output.
MIT License
100 stars 20 forks source link

Cannot connect to host PostgreSQL instance #381

Closed jmealo closed 4 months ago

jmealo commented 4 months ago

What version of PgOSM Flex are you using?

latest and 0.6.1

Docker image

sha256:e1ef484051197a834ea0df3dca43a844e230e4f59811165f7611b329e7458488

What operating system, osm2pgsql, and PostgreSQL/PostGIS versions are you using?

Ubuntu 22.04 / PostgreSQL 16

What did you do exactly?

Try to use the docker gateway IP and/or --network=host to connect to a locally running Postgres instance on the Docker host.

What did you expect to happen?

For it to connect/work.

What did happen instead?

What appears to be a DNS issue (even when I use an IP?)

2024-05-13 17:53:07,188:INFO:pgosm-flex:db:Checking for Postgres service to be available
2024-05-13 17:53:07,188:INFO:pgosm-flex:db:Connecting to Postgres using role "pgosm_flex" on host  "192.168.1.93:5432"  in database "osm"
2024-05-13 17:53:10,195:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:10,195:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:13,197:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:13,197:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:16,201:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:16,201:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:19,205:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:19,205:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:22,209:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:22,209:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:25,213:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:25,213:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:28,217:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:28,217:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:31,221:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:31,221:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:34,225:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:34,225:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:37,229:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:37,229:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:40,233:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:40,233:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:43,235:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:43,235:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:46,239:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:46,239:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:49,243:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:49,243:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-13 17:53:52,246:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-13 17:53:52,246:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.

What did you do to try analyzing the problem?

Used docker exec -it /bin/bash and psql to verify that I am able to connect with the provided details.

jmealo commented 4 months ago

I just tried to use the internal Postgres and the same thing happens... I've run pgosm-flex on this box before without issues 🤔 . Passing --dns 8.8.8.8 to docker run doesn't solve it.

I'm not having any DNS issues on the box inside or outside of Docker (and I'm also using IPs to connect)... and psql in the pgosm-flex container works fine 🤔 .

I tried with 0.6.1 and I get a different error (for both localhost and 192.168.1.93): 2024-05-14 07:49:05,004:ERROR:pgosm-flex:db:Database connection error. Error: connection is bad: Name or service not known

rustprooflabs commented 4 months ago

Hm, this is unexpected! Starting with Postgres connections internal to the container, I haven't been able to reproduce any issues with the internal Postgres instance with the :latest image. My sha256 matches yours using docker inspect --format='{{index .RepoDigests 0}}' rustprooflabs/pgosm-flex:latest. To use the inner Postgres service you shouldn't need to pass any docker related network or dns options in. It's possible they could cause issues. I don't use Docker all that much outside this project so am not fully aware of what those options do.

I haven't used --network=host with this project, so not sure exactly how it would be needed. Is it because your external Postgres is in its own Docker container on a named network? Have you set the environment variables for the database connection similar to shown in the external connection docs? This is the way external connections are designed to work. The inner workings require those env vars in order to operate, and if the code needs to be aware of something passed in from --network=host it isn't looking for it.

Can you share the full set of commands being ran? Seeing that might help spot some subtle differences that may be involved.

jmealo commented 4 months ago

I followed the instructions in the quick start guide and get these errors with any combination of the configuration options. I didn't deviate other than to address the connection issues. I use a lot of Docker, however, I'd have run this natively but I didn't see any documentation on how to do that? 🤔

It could be that pgosm-flex worked on my Mac and it never worked on Linux... I can try on another box.

I just don't understand why psql can connect in the container but not the Python script...

jmealo commented 4 months ago

@rustprooflabs: I'm completely flabbergasted. I just dd if=/dev/zero of=/dev/MY-DRIVE bs=1M count=1000 and re-installed a fresh copy of Ubuntu 24.04 on my machine and I'm still facing the exact same issue.

I've tried with Postgres inside the container and outside the container. Same results :|

Here's what I'm trying to run:

run-docker.sh

#!/bin/bash

source .env

docker run --name pgosm -d --rm \
    -v /faster/osm/pgosm-data:/app/output \
    -v /faster/osm/custom-layerset:/custom-layerset \
    -v /etc/localtime:/etc/localtime:ro \
    -e POSTGRES_USER=$POSTGRES_USER \
    -e POSTGRES_PASSWORD=$POSTGRES_PASSWORD \
    -d rustprooflabs/pgosm-flex:0.6.1

docker exec -it \
    -e POSTGRES_USER=$POSTGRES_USER \
    -e POSTGRES_PASSWORD=$POSTGRES_PASSWORD \
    pgosm python3 docker/pgosm_flex.py \
    --ram=64 \
    --region=north-america \
    --srid 4326

customer-layerset

[layerset]
poi=true
road=true

config.json

{
  "osm_types": ["lines"],
  "layer_set": "roads"
}

.env

export POSTGRES_USER=pgosm_flex
export POSTGRES_PASSWORD=<REDACTED>
export POSTGRES_HOST=192.168.1.93
export POSTGRES_DB=osm
export POSTGRES_PORT=5432
jmealo commented 4 months ago

I checked AppArmor (Ubuntu SELinux) and dmesg and didn't see anything relevant. I turned off AppArmor and it still can't connect to PostgreSQL inside or outside of the container.

jacopofar commented 4 months ago

@jmealo which output do you get when running the script above? If I understand correctly you have a Postgis instance already running on your machine, but I don't see where the Postgis host is passed to the script.

jmealo commented 4 months ago

@jacopofar same output with and without passing the host. It won't connect to the internal or external Postgresql. If I use psql from inside the docker container it works fine.

rustprooflabs commented 4 months ago

@jmealo I am pretty confident it's not a generic Linux problem, 100% of my dev/testing is on Linux. Though, I haven't tried anything on Ubuntu 24.04 yet. Your original report mentioned Ubuntu 22.04 and that OS itself is known to work, that's what my prod environment is on.

Your .env file defines an external connection but your docker run doesn't pass all the necessary options, so that could be part of it. The run command should look more like this in order to connect externally.

docker run --name pgosm -d --rm \
    -v ~/pgosm-data:/app/output \
    -v /etc/localtime:/etc/localtime:ro \
    -e POSTGRES_USER=$POSTGRES_USER \
    -e POSTGRES_PASSWORD=$POSTGRES_PASSWORD \
    -e POSTGRES_HOST=$POSTGRES_HOST \
    -e POSTGRES_DB=$POSTGRES_DB \
    -e POSTGRES_PORT=$POSTGRES_PORT \
    -p 5433:5432 -d rustprooflabs/pgosm-flex

I'm also unsure what the config.json represents. Where is that file located and what do you expect it to do? If it has some hook into osm2pgsql I'm unaware of what it is, but am certain that doesn't do anything for PgOSM Flex itself.

After docker run, what does docker ps -a on the host report?

jmealo commented 4 months ago

@rustprooflabs: I just tested copy/pasting the exact command from the Quick Start guide on two different Ubuntu boxes and that's working. I have no idea why I can't connect to a locally running Postgres instance, but, I agree it's not a generic Ubuntu issue.

For context, I've tried passing in a HOST and not passing in a HOST, I provided the internal docker commands to show that even that isn't working for me (which makes no sense to me).

When I do pass in the host, and it tries to connect, that also doesn't work.

Sorry for the goose chase, I've been trying everything I can to get this to run, it ran fine on my MacBook, but I haven't been able to get it to run on my Homelab (where the RAM is at 😓 ).

jmealo commented 4 months ago

@rustprooflabs:

docker ps -a

3fc3e23c8ea4   rustprooflabs/pgosm-flex                 "docker-entrypoint.s…"   9 minutes ago   Up 9 minutes            5432/tcp

(even though a host was passed in, which means internal postgres shouldn't have spawned?)

#!/bin/bash

source .env

docker run --name pgosm -d --rm \
    -v /faster/osm/pgosm-data:/app/output \
    -v /faster/osm/custom-layerset:/custom-layerset \
    -v /etc/localtime:/etc/localtime:ro \
    -e POSTGRES_USER=$POSTGRES_USER \
    -e POSTGRES_PASSWORD=$POSTGRES_PASSWORD \
    -e POSTGRES_HOST=$POSTGRES_HOST \
    -e POSTGRES_PORT=$POSTGRES_PORT \
    -e POSTGRES_DB=$POSTGRES_DB \
    -d rustprooflabs/pgosm-flex

docker exec -it \
    -e POSTGRES_USER=$POSTGRES_USER \
    -e POSTGRES_PASSWORD=$POSTGRES_PASSWORD \
    -e POSTGRES_HOST=$POSTGRES_HOST \
    -e POSTGRES_PORT=$POSTGRES_PORT \
    -e POSTGRES_DB=$POSTGRES_DB \
    pgosm python3 docker/pgosm_flex.py \
    --ram=64 \
    --region=north-america \
    --srid 4326

This still doesn't work for some reason. When I do an docker exec -it pgosm /bin/bash and do psql -h $POSTGRES_HOST it connects fine.

I tried a docker system prune -a and switching from 0.61 to latest ... still no dice.

From inside the pgosm container:

root@3fc3e23c8ea4:/app# export PGPASSWORD=$POSTGRES_PASSWORD
root@3fc3e23c8ea4:/app# psql -h $POSTGRES_HOST -U $POSTGRES_USER -d $POSTGRES_DB
psql (16.2 (Debian 16.2-1.pgdg110+2))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

osm=>

I have the following in my pg_hba.conf for testing purposes:

host    all             all             0.0.0.0/0               md5
host    all             all             0.0.0.0/0               scram-sha-256

Which should allow pgosm to connect using md5 or scram-sha-256 from anyhost.

Firewall is disabled.

Postgres is listening on *.

jeff@z420:/faster/osm$ ./run-docker.sh 
Unable to find image 'rustprooflabs/pgosm-flex:latest' locally
latest: Pulling from rustprooflabs/pgosm-flex
5d0aeceef7ee: Already exists 
4d1cf9ce3f39: Pull complete 
1d47661f78f1: Pull complete 
4c2608e82435: Pull complete 
0cd1e199e3bc: Pull complete 
e4269e527992: Pull complete 
82b2392dddcf: Pull complete 
c34cd012bbb3: Pull complete 
41f1a1460359: Pull complete 
d31b21012d2c: Pull complete 
30162a32851e: Pull complete 
7bba136630fd: Pull complete 
83767cf0f329: Pull complete 
4a28598c732d: Pull complete 
344e5988a2e6: Pull complete 
4f4fb700ef54: Pull complete 
0998885404aa: Pull complete 
4e0ca54959c1: Pull complete 
26d7a99f596d: Pull complete 
91a5058c00b5: Pull complete 
523fd8fd10ab: Pull complete 
e6f18b2304ee: Pull complete 
83e5692ae27d: Pull complete 
2c77fc6f03f3: Pull complete 
e5c3561a185e: Pull complete 
0e7548b5e4d5: Pull complete 
ea3ff4d86285: Pull complete 
4a92108995f8: Pull complete 
Digest: sha256:e1ef484051197a834ea0df3dca43a844e230e4f59811165f7611b329e7458488
Status: Downloaded newer image for rustprooflabs/pgosm-flex:latest
2e7f868c55b3d654aa903f5a582392a1ea32c65f74a1e137ab79dad966d7fdc3
2024-05-16 09:40:46,747:INFO:pgosm-flex:pgosm_flex:PgOSM Flex starting...
2024-05-16 09:40:46,748:INFO:pgosm-flex:helpers:SRID set: 4326
2024-05-16 09:40:46,748:INFO:pgosm-flex:db:Checking for Postgres service to be available
2024-05-16 09:40:46,748:INFO:pgosm-flex:db:Connecting to Postgres using role "pgosm_flex" on host  "172.17.0.1:5432"  in database "osm"
2024-05-16 09:40:49,756:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-16 09:40:49,756:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-16 09:40:52,759:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-16 09:40:52,759:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-16 09:40:55,762:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-16 09:40:55,763:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-16 09:40:58,766:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-16 09:40:58,766:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-16 09:41:01,770:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-16 09:41:01,770:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
2024-05-16 09:41:04,774:ERROR:pgosm-flex:db:Database connection error. Error: [Errno -2] Name or service not known
2024-05-16 09:41:04,774:WARNING:pgosm-flex:db:Error checking version, likely waiting for Postgres to start. Only an error if it does not go away after a few attempts.
jmealo commented 4 months ago

@rustprooflabs: Is it possible that there's an issue with passwords with special characters not being escaped properly?

jmealo commented 4 months ago

@rustprooflabs: There's an issue with control characters in the password, closing these two issues and opening a new one.

jmealo commented 4 months ago

Replaced with #383.