postgresqldockerdockerfilepgadmin

Dockerfile specifications for connecting pgadmin4 container to postgres container on startup

I am new to Docker and am not entirely sure whether what I am asking about is possible or not.

I have set up a docker container with Postgres database running in it (on localhost on 5432 port). My Dockerfile for setting the container up looks like this:

# syntax=docker/dockerfile:1
FROM postgres:latest
ENV POSTGRES_PASSWORD=somepassword
ENV POSTGRES_DB=somedb
COPY ./somesqlscript.sql /docker-entrypoint-initdb.d/
EXPOSE 5432
CMD ["postgres"]

Then I set up a pgadmin4 container with the following Dockerfile:

# syntax=docker/dockerfile:1
FROM dpage/pgadmin4:latest

ENV PGADMIN_DEFAULT_EMAIL=user@domain.com
ENV PGADMIN_DEFAULT_PASSWORD=somepassword

EXPOSE 80

CMD ["dpage/pgadmin4"]

What I wanted next is for the pgadmin container to connect to the container with postgres database on startup of the container with specified default credentials and open a connection to my database "somedb". I wanted any user building and running my containers to be able to navigate to localhost:5050 in their browsers and either directly be connected to the specified database with no credentials, or enter a login and password and be connected. The main point here is that the user wouldn't have to add the server manually.

If manually adding credentials, the following works from pgadmin "add server" UI:

name: container-postgres
host: host.docker.internal
database: postgres
user: postgres
password: somepassword

I found PGAdmin4 documentation, which specified the following:

*PGADMIN_LISTEN_ADDRESS

Default: [::]

Specify the local address that the servers listens on. The default should work for most users - in IPv4-only environments, this may need to be set to 0.0.0.0.

PGADMIN_LISTEN_PORT

Default: 80 or 443 (if TLS is enabled)

Allows the port that the server listens on to be set to a specific value rather than using the default.*

source: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

However, neither host.docker.internal nor IPAddress from the output of

docker inspect container-postgresdb -f "{{json .NetworkSettings.Networks }}"

works for me as ENV PGADMIN_LISTEN_ADDRESS, as both return the same error:

[2024-09-13 02:03:13 +0000] [1] [ERROR] Can't connect to ('host.docker.internal', 5432)

or

[2024-09-13 02:06:44 +0000] [1] [ERROR] Can't connect to ('172.17.0.2', 5432)

What should I specify there for my purpose?

Another stated in the documentation option is

*PGADMIN_CONFIG_

This is a variable prefix that can be used to override any of the configuration options in pgAdmin’s config.py file. Add the PGADMIN_CONFIG_ prefix to any variable name from config.py and give the value in the format ‘string value’ for strings, True/False for booleans or 123 for numbers. See below for an example...# External Database Settings All configuration settings are stored by default in the SQLite database. In order to use external databases like PostgreSQL sets the value of CONFIG_DATABASE_URI like below: dialect+driver://username:password@host:port/database PostgreSQL: postgresql://username:password@host:port/database* sources: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html ; https://www.pgadmin.org/docs/pgadmin4/latest/config_py.html#config-py

But if I try to add ENV PGADMIN_CONFIG_CONFIG_DATABASE_URI='postgresql://postgres:qwerty123@localhost:5432/lira_db' to my Dockerfile, it results in an error pointing to "postgresql://" as invalid syntax.

Is my request possible at all? If so, what should I add to my Dockerfile for the schema to work?

Solution

  • docker-compose.yml

    services:
  postgres:
    image: postgres:latest
    container_name: postgres-db
    environment:
      POSTGRES_PASSWORD: somepassword
      POSTGRES_USER: postgres
      POSTGRES_DB: somedb
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./somesqlscript.sql:/docker-entrypoint-initdb.d/somesqlscript.sql
    expose:
      - "5432"
    ports:
      - "5432:5432"
  
  pgadmin:
    image: dpage/pgadmin4:latest
    container_name: pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: user@domain.com
      PGADMIN_DEFAULT_PASSWORD: somepassword
    volumes:
      - pgadmin-data:/var/lib/pgadmin
      - ./servers.json:/pgadmin4/servers.json
    ports:
      - "5050:80"
    depends_on:
      - postgres

volumes:
  postgres-data:
  pgadmin-data:

    The servers.json file is used to automatically add the Postgres server to pgAdmin on startup.

    Create a file named servers.json in the same directory as your docker-compose.yml file. This JSON file defines the Postgres server that pgAdmin will connect to:

    {
  "Servers": {
    "1": {
      "Name": "Postgres Server",
      "Group": "Servers",
      "Host": "postgres-db",
      "Port": 5432,
      "MaintenanceDB": "postgres",
      "Username": "postgres",
      "Password": "somepassword",
      "SSLMode": "prefer",
      "ConnectNow": true
    }
  }
}

    sqlscript.sql

    -- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(50)
);

-- Insert initial data
INSERT INTO users (username, password) VALUES
('admin', 'password123'),
('guest', 'guestpass');

    Save the docker-compose.yml ,sqlscript.sql and servers.json files in the same directory and run docker-compose up.