WittCode💻

How to Create a Docker Postgres Image with Preloaded Data

By

Learn how to create a Postgres Docker image with preloaded data. Specifically, we will create a table and insert some data into a Postgres image using initialization scripts.

Table of Contents 📖

Initialization Scripts

To initialize a Docker Postgres image with tables, configuration, and data we need to use initialization scripts. These are SQL or script files that are placed inside a specific folder in the docker image. For example, we can use the following SQL file to spin up a Postgres Docker container containing a table called subscriber with 4 rows of data.

CREATE TABLE subscriber (
  subscriber_id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

INSERT INTO subscriber (name, email) VALUES
('wittcode', 'a@a.com'),
('Sabin', 'b@b.com'),
('Greg', 'c@c.com'),
('Spencer', 'd@d.com');

docker-entrypoint-initdb.d folder

The folder that we need to copy this SQL file into is called docker-entrypoint-initdb.d. Any scripts placed inside this folder will be executed after the default postgres user and database are created. For example, the following Dockerfile will create a postgres image and copy the subscriber.sql file into the docker-entrypoint-initdb.d folder.

FROM postgres:16-alpine
ENV POSTGRES_PASSWORD=toor
ENV POSTGRES_USER=wittcode
ENV POSTGRES_DB=my_db
COPY subscriber.sql /docker-entrypoint-initdb.d/

Note that we also set some necessary environment variables for the postgres image to work properly. Now we just need to build the Docker image with the docker build command.

docker build -t p-i .

Next lets create a docker container from this image using the docker run command.

docker run --name p-c -p 9001:5432 -v my_postgres_volume:/var/lib/postgresql/data p-i

If we check the logs when the container is starting, we can see the following output from our SQL file.

...
/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/subscriber.sql
CREATE TABLE
INSERT 0 4

Note that these scripts will not be ran if the container is started with a data directory that already contains data. For example, we set up a volume to /var/lib/postgresql/data so that we can persist the data inside the container. If we create another image with this script, it will not be ran if we attach this volume as /var/lib/postgresql/data will be populated with data.

Querying the Subscriber Table

To further prove that this worked, lets go inside the Docker container and query the table for the data that we inserted.

docker exec -it p-c bash

Now lets use psql, a terminal based Postgres client, to connect to the database.

psql -U wittcode my_db

Now lets query the subscriber table.

SELECT * FROM subscriber;
subscriber_id |   name   |  email  
---------------+----------+---------
              1 | wittcode | a@a.com
              2 | Sabin    | c@c.com
              3 | Greg     | b@b.com
              4 | Spencer  | d@d.com
(4 rows)