Docker and PostgreSQL for Development

Intro

This is a short post to show you how to run a PostgreSQL database with Docker to quickly connect to during development.

This allows you to utilize PostgreSQL without installing it on your host machine and also helps prevent having stale database tables laying around.

Software Versions

The following software versions were used in this post.

• Docker Community Edition - 20.10.21
• Ubuntu - 22.04.1 LTS

Container Run

Start the container by running the below docker container run command. This will start the PostgreSQL container based on the postgres image on Docker Hub.

docker container run \
  --detach \
  --rm \
  --name pgdev \
  --env POSTGRES_USER=postgres \
  --env POSTGRES_PASSWORD=postgres \
  --env POSTGRES_DB=dev \
  `# Optionally, mount a local volume into the container` \
  `# --volume ${PWD}/tmp/pgdata:/var/lib/postgresql/data` \
  --publish 5432:5432 \
  postgres

The above command switches are explained as follows:

• --detach - Run the container in the background
• --rm - Delete the container once it exits (is stopped)
• --name pgdev - Name the container pgdev
• --env POSTGRES_USER=postgres - Set the admin user to postgres
• --env POSTGRES_PASSWORD=postgres - Set the admin users password to postgres
• --env POSTGRES_DB=dev - Create a databased named dev
• --volume ${PWD}/tmp/pgdata:/var/lib/postgresql/data - Mount a local folder to the container
• --publish 5432:5432 - Connect the host port 54321 to the container port 54321

Container Attach

You can attach to the container using the docker container exec command.

docker container exec -it pgdev bash

PostgreSQL Connect

Connect to PostgrSQL with the psql command. Use the -U postgres switch to connect as the postgres user.

psql -U postgres

Clean Up

To clean up the container, use the docker container kill command. This will stop the container, and because we started the container with the --rm switch, it will be automagically deleted.

docker container kill pgdev

Bonus Round

I usually create a shell script that allows me to start and destroy the container without having to type so much or copy and paste.

Create a file named pgdev.sh with the following contents.

#! /usr/bin/sh

ACTION=$1

if [ $ACTION = "up" ]; then
  docker container run \
    --detach \
    --rm \
    --name pgdev \
    --env POSTGRES_USER=postgres \
    --env POSTGRES_PASSWORD=postgres \
    --env POSTGRES_DB=dev \
    --publish 5432:5432 \
    postgres;
elif [ $ACTION = "rm" ]; then
  docker container kill pgdev;
elif [ $ACTION = "con" ]; then
  docker container exec -it pgdev bash -c "psql -U postgres";
else
  echo "unknown action '$ACTION'";
fi

Then, make the file executable with the chmod +x pgdev.sh command.

Now you can start the container with the ./pgdev.sh up command, destroy the conatiner with the ./pgdev.sh rm command, and connect to the container with the ./pgdev.sh con command.

Outro

This post is mostly documentation for future Brad so that's it for now. Keep being awesome, and say hi to your mum for me ✌️