Handling Several PostgreSQL Versions With Docker

Photo by Baby Natur.

PostgreSQL is a powerful, open-source object-relational database known as the world's most advanced open source database. It is often compared to MySQL, which on its side is known as the world's most popular open-source database.

These two adjectives describe pretty accurately the difference between the two solutions. While PostgreSQL provides a feature-rich database that can handle complex queries and massive databases, MySQL is much simpler and easy to use.

One of the pain points of PostgreSQL, in my opinion, is its installation. Particularly if you need to upgrade it, or worse, to use several versions at the same time. I remember spending quite some time performing these simple tasks, going through multiple documentation every time.

But it is a closed chapter now, thanks to Docker!

Why using Docker to run PostgreSQL?

Docker is an open platform providing the ability to package and run an application in an isolated environment called a container. Containers are lightweight and contain everything needed to run an application, so you do not need to rely on what is currently installed on the host.

Which is exactly what we want in order to manage several version of PostgreSQL without having to install them. Note that we will see how to do it for PostgreSQL, but you can do the same with any database.

You can find instructions to install Docker here. You will also need psql to interact with the database; see here for the installation.

Run your PostgreSQL container

Once you have installed Docker, you can run a container with PostgreSQL using its official image.

docker run --name my-postgres -p 5432:5432 \
  -e POSTGRES_PASSWORD=my-password \
  -e POSTGRES_USER=myself \
  -e POSTGRES_DB=my-db \
  postgres:12

This command will start a container using the image postgres:12 (not specifying the version use the latest one) named my-postgres with a port forward from your localhost port 5432 to the container port 5432. You might want to change your localhost port if you already have PostgreSQL installed on your computer.

We also provide several environment variables to configure our database: POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DB. Defining the user and database names is not mandatory, if you dont they will default to postgres.

We can now connect to the database using psql.

psql --host localhost --port 5432 --username myself --dbname my-db

You will then be asked for a password, type my-password, and you will then access the interactive console.

Our database is currently empty. Let's create a table with a few entries.

CREATE TABLE persons (id SERIAL PRIMARY KEY, name VARCHAR(100));
INSERT INTO persons (name)
  VALUES ('Sheldon'), ('Leonard'), ('Penny'), ('Howard'), ('Rajesh');

We can run a query to check that the table and its lines were created.

SELECT * FROM persons;

 id |  name
----+---------
  1 | Sheldon
  2 | Leonard
  3 | Penny
  4 | Howard
  5 | Rajesh
(5 rows)

Great! We now have a functioning PostgreSQL database without any installation.

Manage data persistence

We do have a problem, though.

While we can easily stop and start again our container with docker stop my-postgres and docker start my-postgres, there will be cases we will want to create a new one with the same database, for instance, if our 5432 port is not available.

Creating a new container will not allow us to access the database that we just created, as it is isolated in our container.

The solution to this is to store the database outside of the container, and the easiest way to do so is to use docker volumes.

You can create a new volume with the following command.

docker volume create my-postgres-db

We can then stop and remove our current container, and create a new one, this time linking the database files location to our newly created volume with the command -v my-postgres-db:/var/lib/postgresql/data.

docker stop my-postgres
docker rm my-postgres
docker run --name my-postgres \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=my-password \
  -e POSTGRES_USER=myself \
  -e POSTGRES_DB=my-db \
  -v my-postgres-db:/var/lib/postgresql/data \
  postgres:12

Create some data like we did before and stop the container. You can now remove it and create a new one. When you attach to it, you will see that your data is still here!

If you are curious to know where the database is stored on your computer, you can use the inspect command.

docker inspect my-postgres-db

[
  {
    "CreatedAt": "2021-05-19T22:00:00+02:00",
    "Driver": "local",
    "Labels": {},
    "Mountpoint": "/var/lib/docker/volumes/my-postgres-db/_data",
    "Name": "my-postgres-db",
    "Options": {},
    "Scope": "local"
  }
]

Final thought

I hope I have convinced you that managing several versions of PostgreSQL (or even just one) can be neat and easy thanks to Docker.

I am talking about PosgreSQL in this article because it is the SQL database I mostly use, but you can do this with any database. Docker is a fantastic tool when it comes to using software without any need to install it.

Definitely a software worth having in your toolbox!