WittCodešŸ’»

Docker and Postgres

By

Learn how to use postgres and docker including creating a postgres docker container, connecting to a postgres docker container, and running SQL queries inside a postgres docker container.

Table of Contents šŸ“–

Why Dockerize Postgres?

A benefit of dockerizing postgres is that it avoids the headache of installing and configuring database servers. Docker containers are available that are specifically designed to support postgres databases, allowing us to skip all the installation. Also, using a docker container for postgres provides a consistent and isolated environment for which can be distributed and ran in different environments.

Docker Containers and Persistence

An issue with docker containers and postgres is that when a container is removed, the data is not persisted and is deleted. However, this is easily fixed by using docker volumes. A docker volume maps file systems on a host mahcine to file systems on a docker container, allowing for data to persist longer than the lifecycle of the container. Lets create a volume called my_postgres_volume.

docker volume create my_postgres_volume

Postgres Docker Image

Now lets pull the latest postgres docker image from Docker Hub using the command docker pull postgres.

docker pull postgres

Now lets create a docker container from the postgres image using the docker run command. The postgres image uses several environment variables to get working. However, the only variable that is required is POSTGRES_PASSWORD.

docker run --name my_postgres_container
-e POSTGRES_PASSWORD=toor
-e POSTGRES_USER=wittcode
-e POSTGRES_DB=my_db
-d
-v my_postgres_volume:/var/lib/postgresql/data
-p 8005:5432
postgres

  • --name gives the container a name, we are naming it my_postgres_container
  • POSTGRES_PASSWORD sets the superuser password, we are setting it to toor
  • POSTGRES_USER sets the superuser name, here we set it to wittcode
  • POSTGRES_DB sets the name for the database that is created when the image is started, we are setting it to my_db
  • -d runs the docker container in the background of the terminal
  • -v sets the docker volume, /var/lib/postgresql/data is where the database files for postgres are stored by default. We map this location on the docker container to our docker volume my_postgres_volume
  • -p maps the machine port number 8005 to the docker container port number 5432

Connecting to Postgres Container

Now lets connect to the postgres container and check the values we set. To do this, lets first get the container ID for the postgres container with docker ps.

docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES a2258c4836e0 postgres "docker-entrypoint.sā€¦" 15 minutes ago Up 15 minutes 0.0.0.0:8005->5432/tcp my_postgres_container

We can see that the container ID is a2258c4836e0. Lets connect to it with the docker exec command. The docker exec command runs a command in a running container. What we want to run is a bash shell so we supply the command to be ran as bash.

docker exec -it a2258c4836e0 bash

  • -i keeps the containers standard input open so it can accept commands
  • -t allocates a pseudo-TTY which makes the container look like a terminal connection session

Now lets connect to my_db as wittcode with the command psql -U wittcode my_db.

psql -U wittcode my_db

  • -U is the username to connect to the database as

Now lets create a table called users with a username column of varying characters up to length 100.

CREATE TABLE users(username VARCHAR(100));

Now lets insert the user wittcode into it and then select all rows from the users table.

INSERT INTO users VALUES ('WittCode'); 0 1 SELECT * FROM users; username

WittCode (1 row)

Verifying our Volume Worked

Finally, lets verify that our docker volume is working correctly. To do this, lets shut down our current container and remove it with the docker rm command.

docker stop my_postgres_container && docker rm my_postgres_container

Now lets create another container from the postgres image the same way we did before but we will call the container my_postgres_container_2.

docker run --name my_postgres_container_2
-e POSTGRES_PASSWORD=toor
-e POSTGRES_USER=wittcode
-e POSTGRES_DB=my_db
-d
-v my_postgres_volume:/var/lib/postgresql/data
-p 8005:5432
postgres

Now connect to the postgres database my_db as wittcode once more with psql -U wittcode my_db.

psql -U wittcode my_db

Finally, select all the rows from the users table.

SELECT * FROM users; username

WittCode (1 row)

This is demonstrates that even when we create a new container from the postgres image, the data will be taken from our host machine. Specifically, the my_postgres_volume docker volume. Therefore, the data is persisting longer than the lifecycle of the docker container.