How to get access to a containerized postgresql database from outside / apps

Question

I have been struggling to get connection to postgresql container from both localhost and from outside.

Here is the very nice demo which works as such very well as it is demonstrated at https://linuxhint.com/postgresql_docker/. And below is the docker-compose.yml by which the postgres:12.2 and pgadmin are containerized and run. And indeed after running the docker-compose.yml file (by docker-compose up -d) they work.

You can then go in your browser to http://localhost:8080/ and have the direct access (with given crendentials, i.e., [email protected], secret) to pgadmin and start operating on your postgres:12.2 installation on docker.

However my difficulties start when I try to find get connection to this postgres:12.2 -database directly from localhost or outside/some application/ip (like Java Spring Boot).

Admittedly I am not an expert with docker, just the student - so, I asking for help. Hopefully someone can show me what should be done to make connection possible to (this) containerized postgres database from outside/app. I am using Win 10.

UPDATE:

Can anyone say why a web-app (added below to docker.compose.yml, see below) is shutting down persistently after its launching?
docker logs gives the following error:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Hikari.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is org.postgresql.util.PSQLException: Connection to localhost:5433 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
here is Dockerfile for the app:

FROM maven:3-jdk-11

VOLUME /tmp

EXPOSE 8095

ADD /target/spring-boot-demo-0.0.1-SNAPSHOT.jar app.jar

RUN ls -ls

ENTRYPOINT ["java","-Djava.security.egd=file:/dev/./urandom","-jar","/app.jar"]
and here is connection details in the application.properties file:

spring.datasource.url=jdbc:postgresql://db:5432/postgres

spring.datasource.username=admin

spring.datasource.password=secret
two other servies (db and pgadmin starts normally)

docker-compose.yml

version: "3.7"

services:

# ========== ADDED APP ===============
 web:
   #restart: on-failure
   image: app-springboot-postgresql
   build: ./
   ports:
     - "8095:8095"
   environment:
     WAIT_HOSTS: postgres:5432
   depends_on:
    - db  
# ========== ADDED APP ===============

  db:
    image: postgres:12.2
    restart: always
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret
      PGDATA: /var/lib/postgresql/data
    volumes:
      - db-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
 
  pgadmin:
    image: dpage/pgadmin4:4.18
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: secret
      PGADMIN_LISTEN_PORT: 80
    ports:
      - "8080:80"
    volumes:
      - pgadmin-data:/var/lib/pgadmin
    links:
      - "db:pgsql-server"

volumes:
  db-data:
  pgadmin-data:

Only means I have been able to connect to containerized postgresql (as defined above in the yml-file) has been: "docker-compose run db bash". And then: "psql -h db -U admin -d postgres" and "Password for user admin: secret". — M.Y.
– M.Y., Commented Oct 11, 2020 at 14:00
from the error: Connection; nested exception is org.postgresql.util.PSQLException: Connection to localhost:5433 refused. you have the postgresql configured on 5433 and from yaml you start that on 5432 — Edwin
– Edwin, Commented Oct 13, 2020 at 19:35
Edwin, you are right, there is inconsistency between the error message and the code. That error was related to some the strange memory update error I reported about in my comment below. Thank you for your sharp observation. — M.Y.
– M.Y., Commented Oct 14, 2020 at 9:58

rokpoto.com · Accepted Answer · 2020-10-11 13:11:23Z

1

docker-compose.yml publishes port 5432 of Postgres to machine where docker daemon runs.

So applications should connect to 127.0.0.1 as Postgres host and 5432 as Postgres port assuming they run on the machine.

answered Oct 11, 2020 at 13:11

rokpoto.com

11k22 gold badges87 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

M.Y. Over a year ago

By this I can connect from localhost: psql -h 127.0.0.1 -p 5432 -U postgres, BUT connection does not go to app in containerized postgresql (i.e the one defined in yml-file above) but instead it open up the connection to local uncontainerized postgresql.

rokpoto.com Over a year ago

if you successfully ran docker-compose up command in the folder where docker-compose.yml file is located, containerized Postgres port 5432 of containerized Postgres is mapped to port 5432 of docker host (machine where you docker daemon runs) and when you run psql the way you published you connect to containerized Postgres

M.Y. Over a year ago

I can run "docker-compose up" (in the same folder) with success and if I then run "psql -h 127.0.0.1 -p 5432 -U postgres", I only get connection to my non-containerized database (with fully different databases and tables from the one that are in the containerized one). And if change the username to admin and use the corresponding password (these are the credentials given in the docker-compose.yml file) and when I run psql -h 127.0.0.1 -p 5432 -U admin) then comes the password-prompt and I get the error: could not connect to server: FATAL: password authentication failed for user "admin".

David Maze Over a year ago

If you're running a separate PostgreSQL on your host, you need to change the published ports:; change only the first number to some different port, like 5433:5432. But I'd expect you to have problems starting up if there's a host-port conflict. Are you running Docker Toolbox or Docker Desktop?

M.Y. Over a year ago

How can I know if I am running Docker Toolbox or Docker Desktop?

|

khashashin · Accepted Answer · 2020-12-05 17:19:59Z

Check if they are in the same network:

docker network ls

# Output

NETWORK ID          NAME                DRIVER              SCOPE
e094bb99be32        bridge              bridge              local
cde9fb0d3fef        django              bridge              local
1aeb83753889        host                host                local
f152a346ca96        none                null                local
348c59c37462        web                 bridge              local

Then inspect the network:

docker network inspect django

# Output

[
    {
        "Name": "django",
        "Id": "6f5fd395946d1bff8583a4ddbe4fb8ea1204875124679f878d24a57f3601b0e6",
        "Created": "2020-10-11T13:16:42.750571609+02:00",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": null,
            "Config": [
                {
                    "Subnet": "172.20.0.0/16",
                    "Gateway": "172.20.0.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": true,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {
            "079d303b3c2d17196e7cb49056ec7cfc7385c480c3cbf225dc51e53ebeb43f10": {
                "Name": "pgadmin",
                "EndpointID": "10e1f181f18e751ce0b3521f537e30948433e62b454fb9707b96333395f5ba36",
                "MacAddress": "02:42:ac:14:00:04",
                "IPv4Address": "172.20.0.4/16",
                "IPv6Address": ""
            },
            "bff75ff8e23ab998cfc99bc87bec05ea9fb55adbcae1993da1cbc719eea90fd3": {
                "Name": "postgres",
                "EndpointID": "edd547148b50c6c3319f2b65ec151816e14f9ea9e7dd9acb9b5895d44fcd6cda",
                "MacAddress": "02:42:ac:14:00:02",
                "IPv4Address": "172.20.0.2/16",
                "IPv6Address": ""
            }
        },
        "Options": {},
        "Labels": {}
    }
]

In the example above postgres and pgadmin are in the same network, so they see each other.

If in you case they are not in the same network, define it explicitly.

# docker-compose.yml

services:
  django:
    container_name: dj
    build:
      context: ./server
    restart: unless-stopped
    volumes:
      - staticfiles:/app/staticfiles
      - mediafiles:/app/media
    command: gunicorn nohchi_mott.wsgi:application --bind 0.0.0.0:8000
    env_file: ./server/.env
    expose:
      - 8000
    networks:
      - web
      - django
    depends_on:
      - db
  db:
    container_name: postgres
    image: postgres:12.0-alpine
    restart: unless-stopped
    labels:
      - "traefik.enable=false"
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    expose:
      - 5432
    networks:
      - django
    env_file: ./db/.env
  pgadmin:
    container_name: pgadmin
    image: dpage/pgadmin4:latest
    restart: unless-stopped
    volumes:
      - pgadmin_data:/var/lib/pgadmin
    env_file: ./db/pgadmin/.env
    links:
      - db:pgsql-server
    networks:
      - web
      - django
    depends_on:
      - db

volumes:
  staticfiles:
  mediafiles:
  postgres_data:
  pgadmin_data:

networks:
  web:
    external: true
  django:
    external: false

Collectives™ on Stack Overflow

How to get access to a containerized postgresql database from outside / apps

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related