PostgreSQL with Init Data using Docker Compose

PostgreSQL with Init Data using Docker Compose

Preface

This post assumes that you have some basic understanding of Docker/Podman, Docker Compose, and the key components used in the docker ecosystem. Get up to speed, with the Prepare Your Container Environment with Docker or Podman section of Docker docs.

  1. Install Docker or Podman
  2. install Docker Compose

Deploy PostgreSQL with Init Data using Docker Compose

Create the docker-compose.yml with the following:

 services:
  postgres:
    image: postgres:17
    container_name: postgres
    volumes:
      - postgresql_data:/var/lib/postgresql/data/
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=PWD123
    ports: ["5432:5432"]
    networks: ["postgres"]
    restart: unless-stopped
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "postgres"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s
    deploy:
      resources:
        limits:
          cpus: '0.5'
          memory: 512M
        reservations:
          cpus: '0.1'
          memory: 256M
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"
networks:
  postgres:
volumes:
  postgresql_data: 

Create the init.sql with the following content:

 CREATE USER vbv WITH PASSWORD 'vbv';
CREATE DATABASE vbvdb;
GRANT ALL PRIVILEGES ON DATABASE vbvdb TO vbv;

\connect vbvdb;

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (name, position, salary) VALUES
('Bhuvi', 'Manager', 675000.00),
('Vibhu', 'Developer', 555000.00),
('Rudra', 'Analyst', 460000.00);

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO vbv; 

Run

docker-compose up -d

Verify

# Container status
docker ps -a
# Check the Data is pre initialised
docker exec -it postgres bash
psql -U vbv -d vbvdb
SELECT * FROM employees;

OUTPUT

docker ps -a                                                                            ░▒▓ 2 ✘  01:48:36 am 
CONTAINER ID  IMAGE                          COMMAND     CREATED         STATUS                   PORTS                   NAMES
52c54521505e  docker.io/library/postgres:17  postgres    20 seconds ago  Up 21 seconds (healthy)  0.0.0.0:5432->5432/tcp  postgres

~/jinnabalu.github.io/_i/postgres:/# docker exec -it postgres bash                                    ░▒▓ ✔  01:48:47 am 
root@52c54521505e:/# psql -U vbv -d vbvdb
psql (17.4 (Debian 17.4-1.pgdg120+2))
Type "help" for help.

vbvdb=> SELECT * FROM employees;
 id | name  | position  |  salary
----+-------+-----------+-----------
  1 | Bhuvi | Manager   | 675000.00
  2 | Vibhu | Developer | 555000.00
  3 | Rudra | Analyst   | 460000.00
(3 rows)

Activity log

https://gist.github.com/jinnabaalu/89bd8eeba3b8845cf337b85a807748f1

Conclusion

Now that we’ve had hands-on experience initializing a PostgreSQL container with data, following the above steps, you’ve provisioned a container that automatically sets up a database, user, and seed data using a simple SQL script. This setup is perfect for local development, testing environments, and CI/CD pipelines where consistent and repeatable database states are essential.