Automating Postgres Deployment with Docker Compose and Init Scripts

Introduction to Running PostgreSQL in a Docker Container using Docker Compose

In this blog post, we will explore how to run PostgreSQL in a Docker container using Docker Compose. We will also break down and explain the init-user-db.sh script that is executed at startup to initialize the PostgreSQL tables. Running PostgreSQL in a Docker container provides several benefits, including ease of deployment, portability, and isolation. So let’s dive in and understand the process!

This post uses Docker Compose V2, if you are still using V1, consider upgrading otherwise use docker-compose instead of docker compose.

Why Run PostgreSQL in a Docker Container?

Running PostgreSQL in a Docker container offers numerous advantages. Here are a few key benefits:

• Easy Deployment: Docker simplifies the deployment process by encapsulating PostgreSQL and its dependencies into a container, making it easy to set up and manage.

• Portability: Docker containers are self-contained and can be run on any system that supports Docker, ensuring consistent behavior across different environments.

• Isolation: Running PostgreSQL in a container provides isolation from the host system, preventing potential conflicts with existing installations or dependencies.

Setting Up PostgreSQL in a Docker Container with Docker Compose

Before we start, ensure that you have Docker and Docker Compose installed on your system. Once both are set up, you can proceed with the following steps:

1. Create a Docker Compose file: Open a text editor and create a file called docker-compose.yml. Copy and paste the following contents into the file:

version: '3.3'
services:
  postgres:
    container_name: my-postgres
    image: postgres:latest
    restart: always
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      PGDATA: /var/lib/postgresql/data/pgdata
    ports:
      - "5432:5432"
    volumes:
      - ./data:/var/lib/postgresql/data/pgdata
      - ./init-user-db.sh:/docker-entrypoint-initdb.d/init-user-db.sh

In this Docker Compose configuration:

• The postgres service is defined with the specified container name (my-postgres), the latest PostgreSQL image (postgres:latest), and the restart policy set to always to ensure that the container restarts automatically if it stops.

• The environment section sets the PostgreSQL environment variables (POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB) using the provided values from the environment. In this example I have a .env file in the same directory as the docker-compose.yml file, which means that there is no need for sensitive data such as passwords to be stored in our infrastructure code. This .env file could be replaced by git secrets in part of a pipeline deployment.

• The ports section maps the host machine’s port 5432 to the container’s port 5432, allowing you to access the PostgreSQL database from your host machine.

• The volumes section mounts two directories:

  • ./data:/var/lib/postgresql/data/pgdata: This maps the ./data directory on your host machine to the /var/lib/postgresql/data/pgdata directory inside the container. It allows you to persist the PostgreSQL data files across container restarts.
  • ./init-user-db.sh:/docker-entrypoint-initdb.d/init-user-db.sh: This mounts the init-user-db.sh script from your host machine to the /docker-entrypoint-initdb.d/init-user-db.sh path inside the container. This script will be executed during container startup to initialize the PostgreSQL tables.

2. Start the PostgreSQL container: Open your terminal or command prompt, navigate to the directory containing the docker-compose.yml file, and run the following command:

docker compose up -d

The -d flag runs the containers in detached mode, allowing

them to run in the background.

Breaking Down the init-user-db.sh Script

The init-user-db.sh script is executed at startup to initialize the PostgreSQL tables. Let’s examine the script and understand its purpose:

#!/bin/bash

set -e
set -u

export PGDATABASE=${POSTGRES_DB}
export PGUSER=${POSTGRES_USER}
export PGPASSWORD=${POSTGRES_PASSWORD}

RUN_PSQL="psql -X --set AUTOCOMMIT=on --set ON_ERROR_STOP=on "

${RUN_PSQL} <<SQL
CREATE TABLE public.mytable
(
    id uuid NOT NULL,
    sensor_name text NOT NULL,
    battery double precision,
    humidity double precision,
    link_quality double precision,
    temperature double precision,
    voltage integer,
    "time" timestamp with time zone NOT NULL
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.mytable
    OWNER to dbadm;
SQL

Here’s a breakdown of the script:

• The set -e and set -u commands ensure that the script exits immediately if any command fails or encounters an unset variable.

• The export statements set environment variables to configure the PostgreSQL connection. The variables PGDATABASE, PGUSER, and PGPASSWORD are assigned values based on the corresponding Docker environment variables (derived from the .env file).

• The RUN_PSQL variable defines the psql command with specific options. -X disables transaction management, --set AUTOCOMMIT=on ensures that each command is executed in its own transaction, and --set ON_ERROR_STOP=on stops the script execution if any error occurs.

• The ${RUN_PSQL} <<SQL syntax starts a here-document that allows us to provide SQL commands inline.

• The SQL commands enclosed within the SQL delimiter create a table named mytable in the public schema with the specified columns and data types. The ALTER TABLE statement sets the owner of the table to dbadm if the table already exists.

With this updated Docker Compose configuration, you can easily manage and deploy your PostgreSQL container along with the initialization script. Running docker compose up -d will start the containers and execute the init-user-db.sh script to initialize the PostgreSQL tables.

Conclusion

In this blog post, we have learned how to run PostgreSQL in a Docker container using Docker Compose. We have seen the advantages of running PostgreSQL in a container and how to set it up with the docker-compose.yml file. Additionally, we have explored the init-user-db.sh script and its role in initializing the PostgreSQL tables.

By leveraging Docker and Docker Compose, you can easily deploy and manage PostgreSQL in a portable and isolated environment. This approach brings flexibility and scalability to your PostgreSQL deployments, making it an ideal choice for various applications.

Hopefully this blog post has been helpful in understanding the process of running PostgreSQL in a Docker container using Docker Compose. Stay tuned for more exciting tutorials and guides. Consider buying me a coffee to feed the addiction and help me solve more automation headaches!