Containerized Database Management - Connecting PgAdmin 4 with PostgreSQL Server using Docker

Introduction

PostgreSQL is a great and powerfull free open-source RDBMS database system with SQL compliance for any enterprise application. To use PosgreSQL in your desktop you can download it in their website. But for this post we are going to use and download it in the background with docker container.

For using PostgreSQL in your local computer, we need tools for the databases administration.

Database administration in PostgreSQL using PgAdmin is a popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world. PgAdmin may be used on Linux, Unix, macOS and Windows to manage PostgreSQL and EDB Advanced Server 10 and above. As for the purpose of this post and demonstration I am using PgAdmin version 4.

There are several configuration to write in the Docker container docker-compose.yml setup for connecting PostgreSQL into the PGAdmin 4 Administration.

Docker Setup

I am using Docker as a tool for my web development environment. Docker is very helpful on my local machine to support the entire ecosystem of application services. You can run your web application with the database services on the same machine and you can set things all up in auto start if you reboot or restart your machine in the Docker Desktop settings.

Compare to the manual setup from these web development setups, Docker is very straightforward and does not need to figure out what works on my machine and what does not work on another machine on the OS level.

For me, to using Docker container service, I need to create a separate local directory for any Docker container services apart from the development source directories such as ./Docker and create a docker-compose.yml file for each service directory:

Docker
├── README.md
├── mariadb-service
│   └── docker-compose.yml
├── mongodb-service
│   ├── README.md
│   └── docker-compose.yml
├── mysql-service
│   └── docker-compose.yml
└── postgre-service
└── docker-compose.yml

Docker Container Development Setup for PostgreSQL

This is the commands for PostgreSQL Docker container on docker-compose.yml file:

version: '3.3'
services:
  postgredb:
    container_name: postgredb
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example
    volumes:
      - ./data:/var/lib/postgresql/data
    ports:
      - 5432:5432
    expose:
      - 5432

networks:
  default:
    name: postgre-service_default

From these credentials from the environment variables such as POSTGRES_PASSWORD with the default username: root or postgres could be inserted on both PGAdmin 4 and Adminer. PostgreSQL Database default port :5432 will be forwarded on port :5432.

Setup PgAdmin 4

PgAdmin is an administrative application interface for PostgreSQL Database, you can create, design, and other database adminstrative tasks using these tools. Download PGAdmin 4 with this link if you do not have one.

Docker Compose Command to Run PostgreSQL Server Container

This Docker Compose command will start your PostgreSQL Server service in the Docker machine and download the requirement if the Image does not exist. Type the Docker compose command in the terminal to start up the services. The -d command will detach the container from the terminal console and will be a stand-alone service that will always start when your computer restart. Change directory into your postgre-service and start to execute this command to starting-up PostgreSQL Database Server Container.

docker-compose -f docker-compose.yml up -d

Connect PostgreSQL Database Container into PgAdmin 4

Open up your PgAdmin 4 and insert your master password before entering the Dashboard

Create a server or add your server group and name to continue

Create or add your server name to continue

Insert your credential username (postgres) and password database along with the host connection address to continue

Databases will be displayed if created previously

PostgreSQL Databases Schemas

PostgreSQL Table List

Perform a Container bash login

Sometimes we need to login into the OS Layer to see if the config or any administration works. Docker Container provide a bash login commands such as:

docker exec -it postgredb bash
docker exec -it mongodb bash
docker exec -it mysqldb bash

Connect your Vercel Postgres Database Store to PgAdmin 4

Vercel has introduced support for PostgreSQL in its Beta release, expanding its hosting capabilities. Now, you can seamlessly integrate PostgreSQL with your JavaScript stack application, including the popular Next.js framework. To begin using PostgreSQL on Vercel, simply log in to your Vercel account and navigate to the stores section. From there, you can effortlessly create PostgreSQL database instances and obtain the necessary credentials and URL. These credentials can then be used to connect your local PostgreSQL instance on Docker as well as PgAdmin 4. For a comprehensive guide on connecting Vercel's PostgreSQL with PgAdmin 4, we have prepared a detailed blog post that you can find here.

Closing

If you are using other RDBMS or NoSQL databases, such as MySQL or MongoDB, you can explore our other blog posts on connecting these servers from Docker containers. Read about how to connect MySQL server from a Docker container, as well as MongoDB, here.

From the development side, Docker PostgreSQL Container is really useful for a persistent development environment and keeps all the services on the same host machine without worrying about operating-system-level trouble.

If you work with ORMs such as Prisma or Sequelize in a Node.js JavaScript stack for web development, switching between development and production database environments might be challenging. This is where containerized databases can offer a solution. With containerization, you can easily switch and manage between development, testing, and production databases.

** All images and logos belong to their respective owners.