Setting up Postgres inside a Docker container

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.
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
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.
POSTGRES_PASSWORD=thisisapassword
POSTGRES_DB=hackerbox_test_db
Run Postgres as a Docker container
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
docker ps -a
Create a User
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.
psql -U postgres
At this point you should be connected as the postgres user, and your terminal prompt should look something like:
postgres=#
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.
\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.
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)
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).
I hope that you enjoyed reading my article, and that you'll share it around.
Now that you're done.......
If you like indie / punk rock, have a listen to this onBandcamp(orYouTube):
If you liked that article, then why not try:
Installing and renewing a Let's Encrypt certificate without downtime
Let's Encrypt is a "free, automated, and open certificate authority" that provides the browser-trusted certificates required to establish https connections. Whilst the success of Let's Encrypt came as wonderful news for poor developers everywhere, I still wondered whether it would be possible to create, install and renew certificates on my NGINX server without incurring downtime.