How to Create a Postgres Database in Docker

Have you ever experienced that feeling of frustration when you have to install a database on two different machines?

On one, it works fine; on the other, there are bugs you can't explain? Even though they are both the same version?

Have you ever felt the burden of having to redo the same commands and the same configurations over and over again when changing workstations?

Well, Docker was invented to solve this type of problem, but also to facilitate the portability of systems and applications.

In this tutorial, I will show you how to set up a Postgres database under Docker.

Install Docker

To use docker, first you will need to install it. As I’m a Mac user, I installed docker on my Mac. But It’s also available for Windows or Linux users.

Once the installation is complete, start your docker application and let’s jump into the tutorial.

Create a Dockerfile

First, to start a Postgres database in a docker container, we have to create a Dockerfile.

FROM postgres:15.1-alpine

LABEL author="Your Name"
LABEL description="Postgres Image for demo"
LABEL version="1.0"

COPY *.sql /docker-entrypoint-initdb.d/

Note that the line COPY below will copy all the sql files in our source folder, where we have our Dockerfile, and add them in the /docker-entrypoint-initdb.d/

COPY *.sql /docker-entrypoint-initdb.d/

This folder in your Postgres container is where you can add additional initialization scripts (creating the directory if necessary).

Create a docker-compose file

Once you have created your docker file, now to run the Postgres container in a clean way, you can create a docker-compose.yml file.

services:
  postgres:
    build:
      context: .
      dockerfile: postgres.dockerfile
    image: "postgres-tutorials"
    container_name: ${PG_CONTAINER_NAME}
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      PGDATA: ${PGDATA}
    volumes:
       - dbtuto:/data/postgres-tuto
    ports:
      - "5432:5432"
    restart: unless-stopped
volumes:
    dbtuto:
      external: true

The values which are in this form ${PG_CONTAINER_NAME} have been defined in an env file; to be managed easily. To do so, create a .env file in your source folder and add all the environment variables, like below.

PG_CONTAINER_NAME='postgres_tuto'
POSTGRES_USER='tuto'
POSTGRES_PASSWORD='admingres'
POSTGRES_DB='tutos'
PGDATA='/data/postgres-tuto'

Create SQL scripts files

Now that our compose file is ready, we can create our SQL scripts file that must be copied in /docker-entrypoint-initdb.d/

-- CREATE TYPE
DROP TYPE IF EXISTS genre;
CREATE TYPE genre AS ENUM (
    'ADVENTURE',
    'HORROR',
    'COMEDY',
    'ACTION',
    'SPORTS'
);

-- CREATE TABLE
DROP TABLE IF EXISTS movies;
CREATE TABLE movies (
    id SERIAL PRIMARY KEY,
    title VARCHAR NOT NULL,
    release_year SMALLINT,
    genre genre,
    price NUMERIC(4, 2)
);

-- LOAD DATAS
INSERT INTO movies(id, title, release_year, genre, price)
VALUES
    (1, 'The Shaw shank Redemption', 1994, 'HORROR', 15.99),
    (2, 'Ant Man', 2019, 'ADVENTURE', 15.00),
    (3, 'Fallen', 1996, 'HORROR', 23.99),
    (4, 'The barbershop', 2006, 'COMEDY', 6.50),
    (5, 'The last dance', 2021, 'SPORTS', 55.99),
    (6, 'Peter Pan', 2004, 'ADVENTURE', 15.99),
    (7, 'Fast & Furious 7', 2018, 'ACTION', 36.00),
    (8, 'Harry Potter', 2000, 'ACTION', 26.50),
    (9, 'Jungle book', 2004, 'ADVENTURE', 25.00);

We start the names of those 2 files with 01-* and 02-* because these initialization files will be executed in sorted name. So we want the database to be created first, then load the data.

Run our Postgres container

Before running our Postgres container, we have specified in our docker-compose file that we will use an external volume.

# Here ↓
volumes:
    dbtuto:
      external: true

So in other to have an external volume we have to create it:

docker volume create dbtuto

Now we can launch our Postgres database with docker compose:

docker-compose up -d

PostgreSQL in action

Once our service is up we can now open our favorite SQL Management software, personally I use DataGrip from JetBrains.

So this is what it looks once the container is fully started. In the password field, enter the one which is in the .env file

Once the credentials have validated and the login is ok, now we can run our SELECT command to test that all the data in the SQL scripts have been played.

Wrap up

In this tutorial, we have covered:

• How to create a Postgres database with Docker
• How to configure a docker file and docker compose file
• How to run SQL scripts when starting a Postgres container with a docker-compose file.

You can found all the code snippets of this article on my GitHub.