Introduction

I've been doing a bit of work with Postgres recently, and encountered a few gotchas whilst getting things set up inside a Docker container for the first time. I thought I'd share my final process here, just in case it comes in handy for the equally uninitiated.

I'll focus on setting up a very simple database for local development purposes, and then hopefully you'll be able to build on it and create something useful for yourself.

For anyone that wants to dive straight into a working example, please check out my github repo. Just be aware that the repo uses scripted versions of the commands that I describe in this article.

I'll be using Postgres version 11.1 for this tutorial.

Goals

By the end of this article, you should be able to:

• run Postgres inside a Docker container
• configure the Dockerised Postgres instance, and set up a simple database

Prerequisites

• OSX Mojave
• Existing Docker installation, plus basic knowledge of this technology
• Basic knowledge of relational databases

OSX Mojave isn't an essential requirement, but that's what I've used to test everything.

Set up the project

First of all, let's set a couple of environment variables for use inside the Postgres container. It's far easier to manage these values by keeping them together in a file.

Navigate to your project root, and create a file named .env

Inside the .env file, enter the following:

POSTGRES_PASSWORD=thisisapassword
POSTGRES_DB=hackerbox_test_db

This password will be fine for now, but you'll definitely need to create a stronger one if you're using any of this in production.

Run Postgres as a Docker container

Run the following command in the terminal:

docker run --name hackerbox-postgres-test --env-file ./.env -p 5432:5432 -d postgres:11.1

Let's break that command down.

• we've based our container on the official Postgres version 11.1 image:

postgres:11.1

• chosen to run the container detached from the terminal:

-d

• opened port 5432, which Postgres listens to by default:

-p 5432:5432

• loaded the environment variables from the file we created earlier:

--env-file ./.env

• given the container a name:

--name hackerbox-postgres-test

Run the following command to ensure that the container is running:

docker ps -a

Create a User

Run the following command to connect to your running Postgres container:

docker exec -it hackerbox-postgres-test bash

We can use the built-in psql tool to run commands against Postgres, but we need to let it know which user we wish to connect.

We don't have any users set up at the moment, so specify the default user postgres:

psql -U postgres

At this point you should be connected as the postgres user, and your terminal prompt should look something like:

postgres=#

If you don't specify a user, then psql will attempt to connect as the current user, which in the case of our container would be root. As there is currently no Postgres user named root then an error will be thrown.

Connect to the database

Remember the POSTGRES_DB variable that we entered into our .env file earlier on? Well, the value that we supplied to that variable (hackerbox_test_db) has magically been used to create a database of the same name.

We can connect to this database by running the following command:

\c hackerbox_test_db

The terminal should confirm the connection with a message similar to:

You are now connected to database "hackerbox_test_db" as user "postgres".

Create a simple table

Our database is completely bare at the minute, so let's create a table.

Run the following command to create a table (named people) that has two varchar fields and an auto-incrementing integer primary key:

create table people (id serial NOT NULL PRIMARY KEY, forename varchar(40), surname varchar(40));

Inserting data

If you query the people table at this point, you should see that it doesn't contain any rows:

hackerbox_test_db=# select * from people;
 id | forename | surname
----+----------+---------
(0 rows)

Add some data into the people table:

insert into people (forename, surname) values ('Alan', 'Turing');

If you query the people table again, you should see that a row has been inserted and the id has been created automatically:

hackerbox_test_db=# select * from people;
 id | forename | surname
----+----------+---------
  1 | Alan     | Turing
(1 row)

Summary

So that's the "quick and dirty" on how to use Postgres inside a Docker container. You can connect to your running container externally, possibly using a GUI tool such as pgAdmin, by specifying your container ip (this will just be 127.0.0.1 if you're running Docker locally) along with the password that we set in the .env file (thisisapassword).

As always, do your own research around Postgres before you even consider putting any of these ideas into production. This is particularly important where security is concerned, and you'll want to look into setting up different users using the principle of least privilege.

I hope that you enjoyed reading my article, and that you'll share it around.