Accessing PostgreSQL Using pgAdmin with a Dockerized Apache Airflow Setup

In this blog, i will guide you through the process of accessing your PostgreSQL database using pgAdmin in a Dockerized Apache Airflow setup. pgAdmin is a powerful and user-friendly tool that allows you to manage and interact with your PostgreSQL databases efficiently. By the end of this tutorial, you'll be able to visualize your database schema, run queries, and manage your database objects with ease.

Setup Airflow

Refer to my previous article https://vipinmp.hashnode.dev/quick-and-easy-apache-airflow-setup-tutorial on setting up Airflow in the dockerizd environment.

Setting Up pgAdmin

Turn Down the Existing Airflow Service: If your Airflow setup is already running, you need to stop the service first. Use the following command in your terminal:

docker-compose down

Update Your Docker Compose Configuration: Next, let's add the pgAdmin service to your Docker Compose setup. Open your docker-compose.yml file and update it with the following content to include pgAdmin:

pgadmin:
    image: dpage/pgadmin4
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "5050:80"

Access pgAdmin

After updating the docker-compose.yml file, restart the services:

docker-compose up

Open your browser and go to http://localhost:5050 to access pgAdmin. Use the email admin@admin.com and the password admin to log in.

Connect pgAdmin to PostgreSQL

1. Get the IP address of postgres container.

   

   

2. Create a New Server:

   • Right-click on "Servers" in the pgAdmin interface and select "Register" -> "Server...".

3. Configure the Connection:

   • General Tab: Name your server (e.g., "postgreSQL_airflow").

   • Connection Tab:

     • Hostname/Address: 172.19.0.2

     • Port: 5432

     • Maintenance database: postgres

     • Username: airflow

     • Password: airflow

4. Save the Configuration:

   • Click "Save" to create the server connection.

Now, you should be able to see your PostgreSQL database in pgAdmin. You can browse the schema, run queries, and manage your database objects.

Practice task:

Create a database called ecommerce. With the help of Query Tool(Right Click e-commerce database →Query Tool), create a table called employees, insert some records, and execute queries on this table.

Conclusion

By following these steps, you can set up a Dockerized Apache Airflow environment and access your PostgreSQL database using pgAdmin. This setup allows you to easily manage and interact with your databases, enhancing your workflow and data analysis capabilities.