Creating a Custom PostgreSQL Docker Image with Initialization Scripts

Containerisation has become a common practice of modern software development after its introduction a decade ago. It allowed developers to package applications together with their dependencies into reproducible, isolated units. Databases are no exception — and PostgreSQL, with its stability and rich SQL features, is a go-to choice for many.

In this article, we’ll walk through how to create a custom PostgreSQL Docker image that automatically initialises your schema and loads snapshot data when the container starts for the first time.

If you haven’t seen it yet, you might also want to read Creating a Custom MySQL Docker Image with Initialization Scripts — the process is quite similar, but there are a few PostgreSQL-specific nuances we’ll cover here.

Here is how we are going to do it step by step:

1. Create a Dockerfile that extends the official PostgreSQL image
2. Add your SQL initialization scripts
3. Build and run the image with a new database instance
4. Verify that the database and data are loaded correctly

Creating the Dockerfile

First, let’s start by laying out the directory structure in which we will work.

$mkdir my-postgres-image
$cd my-postgres-image && mkdir init-scripts
$touch Dockerfile

Next, we will extend the official PostgreSQL base image.

FROM postgres:16
LABEL description="My Custom PostgreSQL Docker Image with Snapshot Data"

ENV POSTGRES_USER=postgres
ENV POSTGRES_PASSWORD=secret
ENV POSTGRES_DB=cards

# Copy initialization scripts into the automatic execution directory
COPY ./init-scripts/ /docker-entrypoint-initdb.d/

Just like the MySQL version, the key in the Dockerfile configuration is the special directory /docker-entrypoint-initdb.d/: All the .sql files and other scripts placed in here will run automatically only when PostgreSQL detects an empty data directory — that is, during the very first container start-up time.

With the docker COPY instruction, we are copying our scripts from the init-scripts directory into the image’s docker-entrypoint-initdb directory.

Other settings that we introduced to the Dockerfile are there to help PostgreSQL container start.

Adding initilazation scripts

Let’s create our initialisation scripts now. Under the init-scripts folder create the fiels with names 01_create_schema.sql , and 02_insert_data.sql.

CREATE TABLE IF NOT EXISTS card (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT
);

INSERT INTO card (name, description)
VALUES
('Visa Gold', 'A premium gold credit card'),
('MasterCard Platinum', 'A card with global access benefits'),
('Amex Blue', 'Popular for its cashback rewards');

Building the custom PostgreSQL image and running the container

Build your image with the following command:

$ docker build -t my-custom-postgres . 
[internal] load build definition from Dockerfile
	....
[1/2] FROM docker.io/library/postgres:16@sha256:21f6013073bc6b92830a2129570e2f5ec42a6c734
....
[2/2] COPY ./init-scripts/ /docker-entrypoint-initdb.d/
exporting to image
....
	naming to docker.io/library/my-custom-postgres 0/0 0.004

If the image is built successfully, run the container as usual:

$ docker run -d \
  --name my-custom-postgres-container \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  my-custom-postgres

Alternatively using Docker Compose:

services:
  postgres:
    image: my-custom-postgres
    container_name: my-custom-postgres-container
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: secret
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Mounting the volume ensures that the initialisation scripts don’t re-run each time the container starts. They will only execute the first time the data directory is empty.

It’s now time to move to the result verification step.

Verifying the container state

To verify the container state and data we need to connect the running container in interactive mode. Execute the following command:

$ docker exec -it my-custom-postgres-container  psql -U postgres -d cards

Let’s first verify if the tables are in place:

cards=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | card | table | postgres

next the data verification with a simple SELECT query. You should see something like:

cards=#
select * from card;
 id |        name         |            description
----+---------------------+------------------------------------
  1 | Visa Gold           | A premium gold credit card
  2 | MasterCard Platinum | A card with global access benefits
  3 | Amex Blue           | Popular for its cashback rewards
(3 rows)

If everything worked so far, the custom PostgreSQL image initialized the database and seeded the initial data.

docker rm -f my-custom-postgres-container
docker volume ls | grep pgdata
local     postgresql-image-with-data_pgdata
docker volume rm postgresql-image-with-data_pgdata

You can extend this basic setup to update your database schema, snapshot data or integrate into CI/CD pipelines to spin up reproducible PostgreSQL snapshots for testing.

For another use case involving schema migrations and JPA integration, you might also like Dockerized PostgreSQL/MySQL with Snapshot Data for Spring Boot (JPA).

Conclusion

You’ve now built a reusable PostgreSQL image that automatically initialises a database schema and loads snapshot data at startup. This approach keeps your development environments consistent, and your CI/CD pipelines reproducible.

If you’re curious about the same setup for MySQL, head over to the companion guide:
Creating a Custom MySQL Docker Image with Initialization Scripts.