9

I used docker-compose from the Openpoiservice project.  Both Docker containers were launched successfully.

kshnkvn@kshnkvn-vb:~$ docker ps
CONTAINER ID        IMAGE                           COMMAND                  CREATED             STATUS              PORTS                    NAMES
10fafbab73dc        openpoiservice_gunicorn_flask   "/ops_venv/bin/gunic…"   23 minutes ago      Up 22 minutes       0.0.0.0:5000->5000/tcp   openpoiservice_gunicorn_flask_1
a66fe5691455        kartoza/postgis:11.0-2.5        "/bin/sh -c /docker-…"   23 minutes ago      Up 22 minutes       5432/tcp                 openpoiservice_psql_postgis_db_1

But when trying to check the service for functionality, it could not connect to the database.  I tried to do it manually:

kshnkvn@kshnkvn-vb:~$ docker exec -it 10fafbab73dc /bin/bash
root@10fafbab73dc:/deploy/app# psql -h localhost -U gis_admin-gis
psql: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Cannot assign requested address
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
root@10fafbab73dc:/deploy/app# 

Strange, checked just in case that the type of container network is the bridge:

kshnkvn@kshnkvn-vb:~$ docker network ls
NETWORK ID          NAME                         DRIVER              SCOPE
81001dac99c0        bridge                       bridge              local
8e65fb4ef6f8        host                         host                local
94ce4e1605ef        none                         null                local
a3f48ac3facc        openpoiservice_default       bridge              local
e3d4286df013        openpoiservice_poi_network   bridge              local

Checked Postgres launch logs:

kshnkvn@kshnkvn-vb:~$ docker logs a66fe5691455
Add rule to pg_hba: 0.0.0.0/0
Add rule to pg_hba: replication replicator 
Setup master database
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
2020-02-08 13:50:20.675 UTC [25] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-02-08 13:50:20.683 UTC [25] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-02-08 13:50:20.756 UTC [37] LOG:  database system was interrupted; last known up at 2020-02-08 13:35:17 UTC
2020-02-08 13:50:21.830 UTC [48] postgres@postgres FATAL:  the database system is starting up
psql: FATAL:  the database system is starting up
2020-02-08 13:50:22.726 UTC [37] LOG:  database system was not properly shut down; automatic recovery in progress
2020-02-08 13:50:22.730 UTC [37] LOG:  redo starts at 0/21CCC50
2020-02-08 13:50:22.730 UTC [37] LOG:  invalid record length at 0/21CCC88: wanted 24, got 0
2020-02-08 13:50:22.730 UTC [37] LOG:  redo done at 0/21CCC50
2020-02-08 13:50:22.867 UTC [25] LOG:  database system is ready to accept connections
                              List of databases
   Name    |   Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+-----------+----------+---------+---------+-----------------------
 gis       | gis_admin | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres  | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |         |         | postgres=CTc/postgres
 template1 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |         |         | postgres=CTc/postgres
(4 rows)

postgres ready
Setup postgres User:Password
Creating superuser gis_admin
ALTER ROLE
Creating replication user replicator
ALTER ROLE
gis db already exists
                              List of databases
   Name    |   Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+-----------+----------+---------+---------+-----------------------
 gis       | gis_admin | UTF8     | C.UTF-8 | C.UTF-8 | 
 postgres  | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |         |         | postgres=CTc/postgres
 template1 | postgres  | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |           |          |         |         | postgres=CTc/postgres
(4 rows)

2020-02-08 13:50:24.785 UTC [25] LOG:  received smart shutdown request
2020-02-08 13:50:24.799 UTC [25] LOG:  background worker "logical replication launcher" (PID 58) exited with exit code 1
2020-02-08 13:50:24.801 UTC [53] LOG:  shutting down
2020-02-08 13:50:24.838 UTC [25] LOG:  database system is shut down
Postgres initialisation process completed .... restarting in foreground
2020-02-08 13:50:25.842 UTC [148] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-02-08 13:50:25.842 UTC [148] LOG:  listening on IPv6 address "::", port 5432
2020-02-08 13:50:25.850 UTC [148] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-02-08 13:50:25.880 UTC [150] LOG:  database system was shut down at 2020-02-08 13:50:24 UTC
2020-02-08 13:50:25.887 UTC [148] LOG:  database system is ready to accept connections

It looks like Postgres started on IP address 0.0.0.0.

I looked at what IPs are used by the Docker ip addr show command.  Tried to reconnect using this IP:

psql: could not connect to server: Connection refused
    Is the server running on host "172.17.0.1" and accepting
    TCP/IP connections on port 5432?
root@10fafbab73dc:/deploy/app# psql -h 172.17.255.255 -U gis_admin-gis
psql: could not connect to server: Connection timed out
    Is the server running on host "172.17.255.255" and accepting
    TCP/IP connections on port 5432?

What can I try to do to connect the script to the database?

2 Answers 2

12

TLDR;

psql -h psql_postgis_db -U gis_admin gis
# or
psql -h psql_postgis_db gis gis_admin

Problem with server address

None of the IPs you are trying are actually correct.

  • 127.0.0.1 is the localhost address. Since you launch the command from your flask container, there is no posgres service running there.
  • 172.17.0.1 is the ip of a docker bridge. This is actually the ip of your docker engine host as seen by your containers on the same bridge. Unless you have a postgres running on your machine and listening to that ip, you'll get no answer (and this would not be the correct postgres server anyway)
  • 172.17.255.255 is a network broadcast address for the previous bridge network.

From your start logs we can see that your postgres should be listening correctly. 0.0.0.0 is not actually a real ip: it stands for "any ip configured on this host".

You could look for the ip of your postgres container to contact it (see for example this answer on SO), but you don't even have to. docker/docker-compose are making this easy for you by mapping container/service names on the same network to their respective IPs automagically. So your db server is reachable using the service name psql_postgis_db

Problem with user and db name

I don't really get what your wrote in your -U option to the psql command (a mix of user + db name...). Anyway, it should be the username you want to use to connect to the postgres server. From your compose file it is gis_admin. Since there is no db with the same name as the user, you need to specify the db name you want to connect to. You either use the -U option(psql -U <user> <db>) or use positional parameters (psql <db> <user>)

2
  • Wow... using the service name instead of any ip or host name is the key. Thanks.
    – blissweb
    Commented Nov 15, 2021 at 6:52
  • What if we run the Postgres container without using the docker-compose file, what will be the host in that case?. Because I can't able to connect using localhost hostname. Commented Apr 10, 2022 at 6:52
3

Just add the ports parameters to the docker-compose.yml.

Example:

# Use postgres/example user/password credentials
version: '3.1'

services:

  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: example
    ports:
      - "5432:5432"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .