Visualize Postgres DB with pgAdmin inside Docker

In today’s data-driven world, managing and visualizing databases effectively is essential. If you’re working with PostgreSQL and want a streamlined way to manage your database visually, using pgAdmin within Docker is a fantastic solution. This setup combines PostgreSQL’s robust data management capabilities with pgAdmin’s user-friendly interface, all contained within Docker for easy deployment and scalability. In this guide, we'll walk through setting up a PostgreSQL database and visualizing it using pgAdmin, making data management more efficient and accessible directly from your Docker environment.

1. Let’s create the docker-compose.yml file which contains the configuration of our docker containers like Postgres DB and pgAdmin

version: '3.9'

services:
  postgres:
    image: postgres:latest
    container_name: CONTAINER_NAME
    hostname: postgres
    expose:
      - 5432
    ports:
      - 5432:5432
    restart: unless-stopped
    environment:
      POSTGRES_USER: YOUR_USERNAME
      POSTGRES_PASSWORD: YOU_PASSWORD
      POSTGRES_DB: YOUR_DATABASE_NAME
    volumes:
      - postgres-data:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    container_name: CONTAINER_NAME
    environment:
      PGADMIN_DEFAULT_EMAIL: YOUR_ADMIN_EMAIL
      PGADMIN_DEFAULT_PASSWORD: YOUR_ADMIN_PASSWORD
    ports:
      - 5050:80
    volumes:
      - pgadmin-data:/var/lib/pgadmin
    depends_on:
      - postgres
    restart: unless-stopped

volumes:
  postgres-data:
  pgadmin-data:

Let’s break it down:

version: '3.9'

• Specifies the version of the Docker Compose syntax being used. Version 3.9 is compatible with the latest Docker Compose features.

services

• Defines the containers (services) that make up the application. In this case, there are two services: postgres and pgadmin.

postgres service

• image: postgres:latest: Specifies the Docker image for PostgreSQL, using the latest version.

• container_name: SERVER_NAME: Names the container server_name instead of a randomly generated one.

• hostname: postgres: Sets the hostname inside the container, which is useful for networked references within Docker Compose.

• Ports and Networking:

  • expose: 5432: Makes the PostgreSQL port 5432 available to other containers but does not expose it to the host machine.

  • ports: 5432:5432: Exposes port 5432 on the host machine, allowing external access to PostgreSQL.

• Restart Policy:

  • restart: unless-stopped: Automatically restarts the container unless it’s explicitly stopped.

• Environment Variables:

  • Sets up the PostgreSQL database user credentials and database name:

    • POSTGRES_USER: The database user, set to user.

    • POSTGRES_PASSWORD: The user’s password, set to password.

    • POSTGRES_DB: The name of the database to create, database_name.

• Volumes:

  • volumes: postgres-data:/var/lib/postgresql/data: Mounts a named volume postgres-data for persistent data storage to the PostgreSQL data directory, preserving data across restarts.

---

pgadmin service

• image: dpage/pgadmin4: Specifies the Docker image for pgAdmin, which is a web-based PostgreSQL management tool.

• container_name: pg_admin_container_name: Names this container spring_server_manager.

• Environment Variables:

  • PGADMIN_DEFAULT_EMAIL: Sets the pgAdmin login email to admin_email.

  • PGADMIN_DEFAULT_PASSWORD: Sets the pgAdmin login password to admin_password.

• Ports:

  • ports: 5050:80: Maps port 80 (the default port used by pgAdmin) in the container to port 5050 on the host machine. This allows access to pgAdmin at localhost:5050.

• Volumes:

  • volumes: pgadmin-data:/var/lib/pgadmin: Mounts a named volume pgadmin-data to store pgAdmin’s configuration and data, allowing it to persist.

• Depends On:

  • depends_on: postgres: Ensures that the postgres service starts before pgadmin.

• Restart Policy:

  • restart: unless-stopped: Restarts the pgAdmin container automatically unless manually stopped.

---

volumes

Defines named volumes to persist data:

• postgres-data: Stores PostgreSQL data, ensuring data remains after container restarts.

• pgadmin-data: Stores pgAdmin’s configuration and session data, so settings are retained across restarts.

Now run your docker on you machine, then open the terminal and run this command:

docker-compose up -d

docker gonna pull images and start building the containers, at the you’ll see you stack with its containers running

go to `http://localhost:5050` and you’ll this page

enter you credentials that you’ve put in the yml file,

Right click on Server → Register → Server…, you’ll get this

Enter any name you want and then pass to Connection?

Since out db is inside docker container than docker will be our host, enter your username and password and click save

Your server is registered and everything is set now you can track you database tables and usage, also manipulate db data either by buttons or running sql queries.

EXTRA:
to run sql queries, right click on the target table and

Conclusion

In conclusion, setting up pgAdmin alongside PostgreSQL in Docker offers an efficient, flexible way to manage and visualize databases without the need for complex installation steps. By leveraging Docker's isolated environment, you can run both PostgreSQL and pgAdmin seamlessly, ensuring a consistent setup across different development or production environments. This approach also allows for straightforward scaling, easy configuration updates, and better resource management, making it a reliable choice for database administration. Whether you're a developer, database administrator, or data analyst, using pgAdmin in Docker is a valuable tool for enhancing productivity and database control.