Custom Postgres Docker Image with predefined Database and Tables with permanent storage

Introduction

This is the first of many articles on becoming a super full stack developer. With any Information Technology system, persistence is key. In this article, we are going to learn how to set up a custom Postgres docker image with predefined databases and tables. We will also expose the database application from inside the image to the outside via port forwarding. And the last interesting part will be persisting the tables information on the outside of the container by mounting the volumes to a dedicated directory.

Please look at the overview image of what we are going to accomplish today below:

Prerequisites

You need to have Docker application(Windows, Mac or Linux) installed, please download it from the URL below:

Docker Desktop for Mac and Windows | Docker

Then you need to configure a volume for docker to be able to persist database or table information onto your computer’s hard drive. This will be required later on in this article.

Open your docker settings. Select Resources and click on FILE SHARING. Then you will be able to add a directory of your choosing, I’ve added /opt/workspace/nerdcode/docker as my Mac’s preferred path as indicated on the image below.

Next, we will pull the PostgreSQL database image from the Docker Hub repository by opening your terminal(Linux/Mac)/command prompt(Windows) and type the below command:

docker pull postgres:12

Once pulled, verify the image is successfully pulled locally by using the below command:

docker images

It should list all your docker containers. Now let’s do the more exciting part of this article.

Create your own image with connection/database settings predefined and also add a SQL script which should run once the database is up and running. For this step, you will need 2 settings files and an init script file alongside your Dockerfile. Let's start

1. We need to access the current settings files within this default docker image, so let's start the default image in order for us to be able to copy the files.

Use the command below to start your image which automatically assigns a password to the Postgres database, for this article I will leave it as a password.

docker run --rm -e POSTGRES_PASSWORD=password postgres:12 &

2. Check your container ID to use to access the running container by running the below command:

docker ps -a

Results should be similar to the below image:

results from docker ps -a

3. Use the container ID to access the already running container by typing the below command:

docker exec -it 916fc79719fc /bin/bash

And change directory to the files we interested in as the image depict below:

We are only interested in pg_hba.conf and posrgresql.conf files

4. Exit the container by typing the command below:

exit

5. Copy files from the container to your local directory by using the following commands:

docker cp 916fc79719fc:/var/lib/postgresql/data/pg_hba.conf /opt/workspace/nerdcode/docker/online

and

docker cp 916fc79719fc:/var/lib/postgresql/data/postgresql.conf /opt/workspace/nerdcode/docker/online

6. Change the pg_hba.conf file by adding the following 2 lines at the end of the file to allow the database to accept connections from any IP addresses:

host all all 0.0.0.0/0 md5
host all all ::/0 md5

7. Change the posrgresql.conf file by locating the parameter called listen_addresses to listen to any address which might try and access this database

listen_addresses = ‘*’

Please Note: On this posrgresql.conf file, you can also change memory settings, number of connections, and logging mechanism. Not relevant to this article

8. Create a init script file called ‘init.sql’ to create a database within this Postgres, create a user, and assign the user super privileges to the database. Also, modify Postgres user password. Your file contents must contain the following:

CREATE USER superdevuser;
CREATE DATABASE superdevdb;
GRANT ALL PRIVILEGES ON DATABASE superdevdb TO superdevuser;
ALTER USER postgres with PASSWORD ‘P@ssword1’;
ALTER USER superdevuser with PASSWORD ‘P@ssword1’;

9. Last file, ‘Dockerfile’, create this file within the same directory as the previous files and add the following lines and instructions:

FROM postgres:12
ADD pg_hba.conf /var/lib/postgresql/data/
ADD postgresql.conf /var/lib/postgresql/data/
COPY init.sql /docker-entrypoint-initdb.d/

Finally, we can now create our own image, start docker container of our custom image, mount the persistence volume, and expose internal container ports to your Windows/Linux/Mac environment. Let's start…

1. Create a custom Docker image

Make sure your terminal/command prompt is within the directory where all the files were created and run the below script(don’t forget the dot at the end):

docker build --t medium/database:latest .

2. Check if the image exists by running the below command:

docker images

3. Final step, run the new image and assign it to the mount point you configured initially for permanent storage while exposing the container internal ports to the external environment

docker run --rm -e POSTGRES_PASSWORD=P@ssword1 -v /opt/workspace/nerdcode/docker/data/medium:/var/lib/postgresql/data:rw -p 5432:5432 medium/database:latest &

4. Once the above is done, find the container ID of the started container and change the user to Postgres user and access your database. Follow the steps below:

docker ps -a
docker exec -it f6fdacfeeb2d /bin/bash
su — postgres

Conclusion

Now we have completed what this article was intended for, I hope you had fun and now you can do quick database setup configurations using your own image and permanently store your data. Share, like, and teach others… The next article will focus on using this article to persist Microservice Data