Building a PostgreSQL Environment with Docker

This article will walk you through the process of creating a PostgreSQL environment using Docker. PostgreSQL is one of the most popular open-source relational database management systems. By using Docker, we can quickly set up a PostgreSQL server for development or testing purposes without going through the lengthy process of manually installing and configuring a PostgreSQL server on a host OS.

Setting Up docker-compose.yml

To create a PostgreSQL server container, we will first create a docker-compose.yml file. This file will contain the necessary configuration to pull the PostgreSQL image from Docker Hub, create a new PostgreSQL container, and set up the necessary environment variables.

Here is the docker-compose.yml file:

version: '3'

services:
  db:
    image: postgres:14
    container_name: postgres
    ports:
      - 5432:5432
    volumes:
      - db-store:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=passw0rd
volumes:
  db-store:

Starting PostgreSQL Container

Once the docker-compose.yml file has been set up, we can start our PostgreSQL server container. Docker Compose allows us to manage our application's services with just a single command. In the terminal, navigate to the directory where your docker-compose.yml file is located, then run the following command:

$ docker compose up -d

To confirm that our PostgreSQL server container is up and running, we can use the docker ps command. This command lists all running Docker containers. Here is the command and the expected output:

$ docker ps

CONTAINER ID   IMAGE         COMMAND                  CREATED         STATUS         PORTS                    NAMES
da5beb67905a   postgres:14   "docker-entrypoint.s…"   9 minutes ago   Up 9 minutes   0.0.0.0:5432->5432/tcp   postgres

After confirming that the PostgreSQL container is running, you can access the PostgreSQL container's shell by using the docker exec command:

$ docker exec -it postgres /bin/sh

#

Now that we are inside the PostgreSQL container, we can connect to the PostgreSQL server by using the PostgreSQL client CLI. To connect as the root user, use the following command:

# psql -h localhost -U postgres

psql (14.8 (Debian 14.8-1.pgdg120+1))
Type "help" for help.

postgres=#

Operating PostgreSQL

Once you're connected to the PostgreSQL server, you can now perform various database operations such as creating tables, inserting data, and querying data. Here's an example of creating a table, inserting data into it, and then retrieving that data:

postgres=# create table test(id int, name varchar(10));

CREATE TABLE

postgres=# insert into test(id, name) values (1,'john');

INSERT 0 1

postgres=# select * from test;

 id | name
----+------
  1 | john
(1 row)

Cleanup

Once you're done with your operations, it's good practice to terminate the PostgreSQL connection:

postgres=# exit

#

This command will log you out from the PostgreSQL server.

After exiting the PostgreSQL connection, you will be back in the Docker container's shell. You can also exit the Docker container:

# exit

To ensure that no unnecessary resources are consumed, it's best to stop and remove the Docker container when you're done using it. Use the following command to stop and remove the Docker container:

$ docker compose down

[+] Running 2/2
 ⠿ Container postgres         Removed                    2.8s
 ⠿ Network my_proj_default    Removed