Study Notes 1.2.3: Connecting pgAdmin and PostgreSQL

Overview from lecture 1.2.3.

• 
  Purpose: To connect pgAdmin, a web-based GUI tool, to a PostgreSQL database for easier database management and querying.


• 
  Context: Utilizing Docker to run PostgreSQL and pgAdmin in containers.

Key Concepts

1. Introduction to pgAdmin

• pgAdmin is a popular tool for managing PostgreSQL databases.


• Provides a graphical interface for executing queries, managing database objects, and visualizing data.

2. Setting Up Docker

• 
  Docker: A platform for developing, shipping, and running applications in containers.


• 
  Containers: Lightweight, standalone, executable packages that include everything needed to run a piece of software.

3. Creating a Docker Network

• Create a dedicated network for the PostgreSQL and pgAdmin containers to communicate.


• 
  

  Command to create a network:
  
  

  
  

  docker network create my_network
  
  
  
  

  
  
  

4. Running PostgreSQL Container

• 
  

  Example command to run a PostgreSQL container:
  
  

  
  

  docker run --name postgres_container --network my_network -e POSTGRES_PASSWORD=mysecretpassword -d postgres
  
  
  
  

  
  
  


• 
  

  Parameters:

  
  
  
  
  
  • 
    -name: Assigns a name to the container.
  
  
  • 
    -network: Connects the container to the specified network.
  
  
  • 
    e: Sets environment variables (e.g., password).
  
  
  • 
    d: Runs the container in detached mode.
  
  
  
  
  
  

5. Running pgAdmin Container

• 
  

  Example command to run a pgAdmin container:
  
  

  
  

  docker run --name pgadmin_container --network my_network -e [email protected] -e PGADMIN_DEFAULT_PASSWORD=admin -p 80:80 -d dpage/pgadmin4
  
  
  
  

  
  
  
  

  In our case:
  
  

  
  

  docker run -it \
  
      -e PGADMIN_DEFAULT_EMAIL='[email protected]' \
  
      -e PGADMIN_DEFAULT_PASSWORD='root' \
  
      -p 8080:80 \
  
      dpage/pgadmin4
  
  

  
  
  


• 
  

  Parameters:

  
  
  
  
  
  • 
    p: Maps the host port to the container port (e.g., 80:80).
  
  
  • 
    e: Sets default email and password for pgAdmin.
  
  
  
  
  
  

6. Connecting pgAdmin to PostgreSQL

• Open pgAdmin in a web browser (usually at http://localhost).
  In our case, we use http://localhost:8080/
  


• 
  

  Since we created separate container for pgAdmin and Postgres, we need to use Server to make them able to communicate with each other.

  
  
  
  
  
  • Create Network: docker network create pg-network
    
  
  
  • 
    

    Run Docker container:
    
    

    
    

    docker run -it \
    
      -e POSTGRES_USER="root" \
    
      -e POSTGRES_PASSWORD="root" \
    
      -e POSTGRES_DB="ny_taxi" \
    
      -v c:/workspace/de-zoomcamp/1_intro_to_data_engineering/docker_sql/ny_taxi_postgres_data:/var/lib/postgresql/data \
    
      -p 5433:5432 \
    
      --network=pg-network \
    
      --name pg-database \
    
      postgres:13
    
    

    
    
    
    

    In our case:
    
    

    
    

    docker run -it \
    
      -e PGADMIN_DEFAULT_EMAIL="[email protected]" \
    
      -e PGADMIN_DEFAULT_PASSWORD="root" \
    
      -p 8080:80 \
    
      --network=pg-network \
    
      --name pgadmin \
    
      dpage/pgadmin4
    
    

    
    
    
  
  
  
  
  
  



• 
  
  

  Create a new server connection:

  
  
  
  
  
  • 
    General Tab: Name the server connection.
  
  
  • 
    Connection Tab:
    
    
    
    
    • Host: postgres_container (the name of the PostgreSQL container) or its ip address. If connection failed, Try using IP Address:
      Use the IP address of the hopeful_yonath container (172.19.0.3) in the pgAdmin configuration instead of the container name or pg-database.
    
    
    • Port: 5432 (default Docker PostgreSQL container port, NOT HOST PORT).
    
    
    • Username: root.
    
    
    • Password: The password set during container creation.
    
    
    
    
    
    
  
  
  
  
  
  

7. Exploring the Database

• After connecting, navigate through the database schema.


• Execute SQL queries directly in pgAdmin.


• 
  

  Use the GUI to create, read, update, and delete database entries.

  
  
  

  E.g. Query Tool:
  
  

  
  

  SELECT
  
      COUNT(1)
  
  FROM
  
      yellow_taxi_data;
  
  

  
  
  

8. Troubleshooting Connection Issues

• Ensure both containers are running in the same network.


• Check firewall settings and port mappings.


• Verify the credentials and connection parameters.

Conclusion

• Connecting pgAdmin to PostgreSQL enhances database management through a user-friendly interface.


• Utilizing Docker simplifies the setup process and ensures a consistent environment for development and testing.