Skip to content

Setting up PostgreSQL database in a Docker Container

Introduction

PostgreSQL, also known as Postgres is a powerful, open-source relational database management system (RDBMS). PostgreSQL has gained popularity due to its advanced features and capabilities. PostgreSQL is a reliable choice for a wide range of applications, from small projects to large-scale enterprise systems. Its open-source nature, strong adherence to standards, and extensive feature set making it a popular database solution in the application development.

In this lab, I will guide you through the process of creating Docker container for PostgreSQL database and run PostgreSQL database in the docker, and finally accessing the PostgreSQL database from pgadmin and psql command line tools.

Technical Scenario

As an Application Architect, you are responsible for setting up a microservices architecture and databases for your product development. Your task is to streamline the process of creating development environments for your project and developers. As part of this process, you will use Azure DevOps to manage our PostgreSQL project. We will create a separate database project, add Dockerfiles to the PostgreSQL database project, and push the container image to Azure Container Registry (ACR) so that developers can focus on building and testing applications without worrying about the complexities of setting up and managing PostgreSQL databases on their local machines. This approach improves productivity, ensures consistency, and enhances the overall development workflow.

Objective

The objective is to establish a local development environment for the PostgreSQL database. To accomplish this, you will create a docker compose file, run them locally. All of these tasks we are doing here will be useful in later chapters when deploying to the Azure Kubernetes Service (AKS).

In this exercise, our objective is to accomplish and learn the following tasks:

  • Step-1: Setup Git Repository for PostgreSQL database.
  • Step-2: Create Folder Structure for PostgreSQL database.
  • Step-3: Add Dockerfiles to the Database Project
  • Step-3.1: Docker Build Locally
  • Step-3.2: Docker Run Locally
  • Step-4: Create Docker Compose file
  • Step-4.1: Build PostgreSQL database locally.
  • Step-4.2: Run PostgreSQL database Container locally.
  • Step-5: Test the PostgreSQL database connection from psql tool
  • Step-6: Test the PostgreSQL database from pgadmin4 tool
  • Step-7: Push Docker Container to ACR

By the end of this lab, you will have a PostgreSQL database running in a Docker container, managed through Azure DevOps, and ready for use in your development and production environments.

Prerequisites

Before starting this lab, ensure you have the following prerequisites in place:

  • Docker Desktop: - Docker Downloads.
  • Docker compose installed
  • Git Client tool: - Git Downloads.
  • PostgreSQL installed - this will allow you to run psql command line tool
  • Basic understanding of Docker and PostgreSQL.
  • Access to an Azure Container Registry (ACR).

Verify the docker installation by running following commands:

docker version
# or
docker --version
# or
docker -v

Verify the docker compose by running following commands:

docker-compose version

Architecture Diagram

The following diagram shows the high level steps to create docker container for PostgreSQL database .

Step-1: Setup Git Repository for PostgreSQL database

Setting up a Git repository for your PostgreSQL database project allows you to manage your code effectively, work in teams, and track the changes of your database codebase.

  • Create a new project in Azure DevOps for your database-related work.
  • Create a repository within the project to store your database scripts and Dockerfiles.

For example to clone an existing repository, run the following command:

git clone https://keesari.visualstudio.com/Microservices/_git/microservices

Step-2: Create Folder Structure for PostgreSQL database

In this step, we'll create a dedicated project or folder for our PostgreSQL database

Create a new project:

Inside our Git repository, create a new directory or folder specifically for your PostgreSQL database. This folder will contain all the necessary files for PostgreSQL database, including database scripts, docker compose & Dockerfile and other psql files.

Here's a suggested folder structure for a PostgreSQL database project:

your-project-name/
│
├── psql/
│   ├── scripts/
│   │   ├── schema/
│   │   │   ├── tables/
│   │   │   │   ├── table1.sql
│   │   │   │   ├── table2.sql
│   │   │   │   └── ...
│   │   │   ├── views/
│   │   │   │   ├── view1.sql
│   │   │   │   ├── view2.sql
│   │   │   │   └── ...
│   │   │   ├── functions/
│   │   │   │   ├── function1.sql
│   │   │   │   ├── function2.sql
│   │   │   │   └── ...
│   │   │   └── procedures/
│   │   │       ├── procedure1.sql
│   │   │       ├── procedure2.sql
│   │   │       └── ...
│   │   └── data/
│   │       ├── seed_data.sql
│   │       └── ...
│   └── migrations/
│       ├── version1/
│       │   ├── up.sql
│       │   └── down.sql
│       ├── version2/
│       │   ├── up.sql
│       │   └── down.sql
│       └── ...
│
├── Dockerfile
├── docker-compose.yml
└── README.md

Explanation:

  • sql/: This folder contains all SQL-related files for your project.
  • scripts/: Contains scripts for creating database objects like tables, views, functions, and stored procedures.
    • schema/: Contains subfolders for different types of database objects.
    • tables/, views/, functions/, procedures/: Each of these folders contains SQL scripts for the respective database objects.
  • data/: Contains data scripts such as seed data.
  • migrations/: Contains SQL migration scripts for managing database schema changes over time. Each migration version should have an up.sql script for applying the migration and a down.sql script for reverting it.
  • Dockerfile: The Dockerfile for building a Docker image for your PostgreSQL database.
  • docker-compose.yml: A Docker Compose file that defines the services, networks, and volumes for your PostgreSQL container.
  • README.md: Include documentation or instructions for using the repository, such as setup steps, environment variables, or specific details about the database scripts.

Feel free to customize this structure according to your project's needs. You can add additional folders or files as required.

Dockerfile vs Docker Compose

A Dockerfile contains a set of instructions and commands used by Docker to automatically build a new container image.

The docker-compose.yaml file is a configuration file used by Docker Compose, a tool for defining and running multi-container Docker applications. With a single command, Docker Compose uses the docker-compose.yaml file to create and start all the services defined in the file.

In this lab I'll show you both approaches for creating PostgreSQL database.

Step-3: Add Dockerfiles to the Database Project

To build a Docker image for PostgreSQL, create a Dockerfile in your project's root directory:

# Use the official PostgreSQL image as the base image
FROM postgres:latest

# Set environment variables for PostgreSQL
ENV POSTGRES_USER=myuser
ENV POSTGRES_PASSWORD=mypassword
ENV POSTGRES_DB=mydatabase

In this Dockerfile:

  • We use the official PostgreSQL image as our base image.
  • Set environment variables to configure the PostgreSQL instance.

Step-3.1: Docker Build Locally

Navigate to your project's root directory and build the Docker image locally using the following command:

docker build -t my-postgresql-image .

Replace my-postgresql-image with a meaningful name for your image.

Docker desktop > Image

Alt text

Step-3.2: Docker Run Locally

To run your PostgreSQL container locally for testing and development, use the following command:

docker run -d --name my-postgresql-container -p 5432:5432 my-postgresql-image

This command creates a container named my-postgresql-container and maps port 5432 from the container to the host.

Docker desktop > Container

Alt text

Step-4: Create Docker Compose file

To setup the PostgreSQL database with docker compose you need to first create a docker compose file that defines the PostgreSQL database service and any necessary dependencies.

Create a file named docker-compose.yml in your project directory. This file will define the services and configurations for your PostgreSQL database setup.

In the docker-compose.yml file, define the PostgreSQL database service. Use the official PostgreSQL database Docker image and specify any necessary configurations. Here's an example of a PostgreSQL database service definition:

docker-compose.yml
# Database type: PostgreSQL
# Database name: postgres
# Database username: postgres
# Database password: example
# ADVANCED OPTIONS; Database host: postgres

version: '3'

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER : postgres
      POSTGRES_PASSWORD: example
    # ports:
    #     - "5432:5432"
    restart: always
  • Uses the postgres:16 Docker image.
  • Maps port 5432 on your host to port 5432 in the PostgreSQL database container.
  • Sets up an initial user and password for PostgreSQL database.

Step-4.1: Build PostgreSQL database locally

The docker-compose up command is used to start and initialize the services defined in a Docker Compose file. We will build the Docker container locally using the docker compose and ensure that the containerized application working as expected.

docker-compose up

# or - -d flag, it tells Docker Compose to run the containers in detached mode
docker-compose up -d

#output
[+] Running 33/2
 ✔ postgres 14 layers [⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿]      0B/0B      Pulled
[+] Running 3/3
 ✔ Network Postgresql_default       Created
 ✔ Container Postgresql-postgres-1  Started

List running Docker containers on your system.

docker ps

# output
CONTAINER ID   IMAGE         COMMAND                  CREATED          STATUS          PORTS                    NAMES
a433ad35d0a1   postgres:16   "docker-entrypoint.s…"   20 seconds ago   Up 19 seconds   0.0.0.0:5432->5432/tcp   postgresql-postgres-1

List Docker images that are currently available on your local system.

docker image ls

# output
REPOSITORY   TAG       IMAGE ID       CREATED       SIZE
postgres     16        488c2842403b   4 weeks ago   448MB

Step-4.2: Run PostgreSQL database Container locally

Run the Docker container locally to verify that the PostgreSQL database working correctly within a containerized environment. This step ensures that the containerized PostgreSQL database works as expected on your local machine.

List the running Docker containers on your system

docker container ls

# output
CONTAINER ID   IMAGE         COMMAND                  CREATED          STATUS          PORTS                    NAMES
a433ad35d0a1   postgres:16   "docker-entrypoint.s…"   42 seconds ago   Up 41 seconds   0.0.0.0:5432->5432/tcp   postgresql-postgres-1

List the Docker networks that are available on your local system

docker network ls

# output
NETWORK ID     NAME      DRIVER    SCOPE
a63fce88f432   bridge    bridge    local
3a43b39f60b0   host      host      local
21fbef3d5c78   none      null      local

if you open the docker desktop you will notice the new image & container started running.

Note

Ensure that you test the PostgreSQL connection using either the pgAdmin tool or the psql command-line tool.

Step-5: Test the PostgreSQL database connection from psql tool

The psql tool is a command-line interface (CLI) provided by PostgreSQL that allows users to interact with PostgreSQL databases directly from the terminal or command prompt.

To test a PostgreSQL database connection using the psql command-line tool, follow these steps:

Opening a terminal or command prompt on your computer.

Use the psql utility to connect to your PostgreSQL database. 

psql -h <hostname> -U <username> -d <database_name>

Replace the placeholders with your specific information:

  • <hostname>: The hostname of the PostgreSQL server.
  • <username>: Your PostgreSQL username.
  • <database_name>: The name of the PostgreSQL database you want to connect to.

For example, to connect to a PostgreSQL database running on the local machine with the username "myuser" and the database "mydatabase," you would use the following command:

psql -h localhost -U myuser -d mydatabase

If the PostgreSQL server is running on the default port (5432), you don't need to specify the port. However, if it's running on a different port, you can use the -p option to specify the port number.

Provide Password(if required):

If your PostgreSQL database is configured to require a password for the specified username, you will be prompted to enter the password. Type the password and press Enter.

Successful Connection:

If the connection is successful, you should see the psql command prompt, which indicates that you are connected to the PostgreSQL database. It will look something like this:

psql (12.7)
Type "help" for help.

mydatabase=#

You are now in an interactive session with the PostgreSQL database, and you can run SQL commands and queries.

Test SQL Commands:

To further test the database connection, you can execute SQL commands to retrieve or manipulate data. For example, you can run a simple SQL query like:

SELECT version();

This query will return the PostgreSQL version, confirming that you can interact with the database.

Exit psql:

To exit the psql session and return to your command prompt, you can type:

\q

or press Ctrl + D (on Unix-based systems) or Ctrl + Z (on Windows).

Alt text

By following these steps, you can connect to a PostgreSQL database using the psql tool and start interacting with the database using SQL commands directly from the command line.

Create a Database

Creating a database is really simple. Execute the command below, and the database should be created.

# command
postgres=# CREATE DATABASE [databasename];

# example
CREATE DATABASE mydatabase2;

Step-6: Test the PostgreSQL database from pgadmin4 tool

pgAdmin is a popular open-source graphical user interface (GUI) administration tool for PostgreSQL. It provides a comprehensive set of features for managing PostgreSQL databases, including creating and managing databases, schemas, tables, indexes, users, and permissions. pgAdmin allows users to interact with PostgreSQL databases visually, making database administration tasks more intuitive and efficient.

To connect to a PostgreSQL database using the pgAdmin tool, follow these steps:

Install and Launch pgAdmin 4:

Download and install pgAdmin 4 on your computer. Once installed, launch the pgAdmin 4 application.

Log in to pgAdmin

When you launch pgAdmin for the first time, you'll be prompted to set the master password. Enter the password you want to use to log in to pgAdmin. You can also choose to save the login information for future use.

Add a Server:

After logging in, you'll see the pgAdmin interface. To connect to a PostgreSQL server, you need to add a server to pgAdmin. Follow these steps:

  • In the left sidebar, expand "Servers" to reveal the "PostgreSQL" group.
  • Right-click on "PostgreSQL" and select "Register > Server..."

  • Configure Server Connection: you'll need to provide the following information:

  • Name: Give your server a descriptive name to identify it within pgAdmin.

  • Host name/address: Specify the hostname or IP address of the PostgreSQL server you want to connect to.
  • Port: Enter the port number where PostgreSQL is running (default is 5432).
  • Maintenance database: Specify the name of a database to connect to initially (e.g., "postgres" or "mydatabase").
  • Username: Enter the PostgreSQL username you want to use for this connection.
  • Password: Provide the password for the specified username.

for example:

hostname - `localhost`
default database - `postgres`
port - `5432` - default postgresql port #
default user - `user` - this is the owner of default database postgres
password - `example` - this is what we've setup in our container

Alt text

Once you've entered the connection details, click the "Save" button to add the server.

Step-7: Push Docker Container to ACR

Push your PostgreSQL container image to Azure Container Registry (ACR) for use in AKS. Follow these steps:

Log in to your Azure account using the Azure CLI:

az login

Authenticate to your ACR:

az acr login --name myacr

Replace myacr with your ACR name.

Tag your local Docker image with the ACR login server:

docker tag my-postgresql-image myacr.azurecr.io/my-postgresql-image:v1

Push the Docker image to ACR:

docker push myacr.azurecr.io/my-postgresql-image:v1

Replace myacr and v1 with your ACR name and desired image version.

Now, your PostgreSQL container image is stored in Azure Container Registry and can be easily pulled and deployed from AKS to Azure Database for PostgreSQL - Flexible Server.

Conclusion

You have successfully created a Docker container for PostgreSQL database, container created as part of this task will be used in the future labs in AKS.

References