DOCKER: POSTGRESQL & PGADMIN

What is docker?

As a developer, you have encountered the situation where your software/application runs smoothly on your laptop, but problem arises when you move that same application to another machine, or you need to run different versions of the same software on one machine and you don’t want to mess with the configurations on your machine, this is where Docker comes in.

Docker provides container technology which helps isolate applications from their underlying infrastructure, meaning a containerized application is sure to run smoothly on any machine that supports the container’s runtime environment without worrying too much about hardware configuration.

In this article will be looking at:

1. Installation of Docker and Docker compose on Ubuntu

2. Setting up postgres and pgAdmin containers:

• Introduction to volumes (data persistence)
• Docker networks
• Environment variables

1a. INSTALLATION OF DOCKER

Prerequisite:

• Ubuntu

Step 1: update the OS by running this command at the terminal:

sudo apt-get update

Step 2: Install Docker:

sudo apt-get install docker.io

Step 3: Start docker:

sudo systemctl start docker

Step 4: check the status of the docker service, whether it is running:

sudo systemctl status docker

Step 5: Enable the docker service to start on system reboot:

sudo systemctl enable docker

We have successfully installed docker

1b. INSTALLATION OF DOCKER COMPOSE

Docker vs Docker Compose: Docker is a container technology whiles Docker Compose is a tool for defining and running multiple docker container applications.

Step 1: Installing docker compose:

sudo apt install docker-compose

We have successfully installed docker compose.

2. POSTGRES AND PGADMIN CONTAINERS

After installing docker and docker compose, We are now going to create postgres and pgadmin containers, we will do that by creating a yml file which will contain our yml script to create these containers. I have created a folder on the desktop called docker (you can create your folder anywhere and name it whatever), which contains my docker-compose.yml file.

When a yml file isnt explicitly referenced in a docker compose command, the default file ( docker-compose.yml ) is used. so, for the sake of this blog, we will use docker-compose.yml.

Step 1: change directory to the docker folder and create the docker-compose.yml file

This is the content of my docker-compose.yml file. (indentation is very important in the writing of yml files.)

version: “2.3”
services:
  postgres-db:
    image: postgres:12
    mem_limit: 2048MB
    mem_reservation: 1G 
    environment:
      POSTGRES_USER: db_user
      POSTGRES_PASSWORD: unbreakable_password
    ports:
    - “5555:5432”
    networks:
    - pg_pgadmin_network
    volumes:
    - db-vol:/var/lib/postgresql/data
  pgadmin4-demo:
    image: dpage/pgadmin4
    mem_limit: 2048MB
    mem_reservation: 1G
    environment:
      PGADMIN_DEFAULT_EMAIL: gmail@gmail.com
      PGADMIN_DEFAULT_PASSWORD: unbreakable_gmail_password
    ports:
    - “7777:80”
    networks:
    - pg_pgadmin_network
  networks:
   pg_pgadmin_network:
     driver: bridge
  volumes:
    db-vol:

Step 2: Open a terminal and change directory to the folder containing the docker-compose.yml file (mine is the docker folder).

Step 3: At the terminal, we run this command:

sudo docker-compose up

We have successfully created our postgres and pgAdmin containers, and we can now connect to them.

CONNECTING TO OUR CONTAINERS

Enter the url of the pgAdmin container (http://localhost:7777/) in a browser and you should see the pgAdmin login page.

The username and password for the pgAdmin are the environment variables we included in the yml script for the postgres container
PGADMIN_DEFAULT_EMAIL: gmail@gmail.com
PGADMIN_DEFAULT_PASSWORD: unbreakable_gmail_password

We have successfully logged in to the pgAdmin

CONNECTING TO THE POSTGRESQL CONTAINER

From the pgAdmin interface, we can connect to the postgres database either using the:

1. postgres container name

2. ip of the postgres container

USING THE CONTAINER NAME

Right click on servers, click on create and click on server.

name of the container: postgres-db
username: db_user
password: unbreakable_password
port: 5432

We have successfully connected to the postgres database.

We can now use our postgres database.

Lets create a simple table:

CREATE TABLE user_accounts (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);

We have successfully created a table

CONNECT USING THE IP OF THE CONTAINER

to connect using the IP of the container, we need to get the IP of the container, we get the IP of the container by getting the ID of the container and run docker inspect on that ID to get the IP.

sudo docker container ls

sudo docker inspect cb5370688c6a

We now have the IP of the postgres container: (172.29.0.2)

We should be able to connect to the postgres database with the IP.

we should see our previously created table.

CONNECTING TO THE POSTGRESQL CONTAINER FROM OUTSIDE

We were able to connect to the postgres database using the above methods because both containers were attached to the same network — pg_pgadmin_network, hence both containers could communicate with each other.

We won’t be able to connect to the postgres database from outside the container using the same method. To connect to the postgres database from outside the containers (say from another machine), we use the ip of the host (the machine running the docker containers) and the port we mapped to the postgresql container (in the yml script)

Lets connect from outside the containers. I am connecting with sqlectron. We provide similar details, what changes are the server address and port. This time around we are using the hostname or ip address of the host, and port 5555 (from our yml file)

We have successfully connected to the database from outside the containers and we can see the table previously created.

VOLUMES

By default, when a docker container is deleted, data stored in/by the container is lost. In order to be to save (persist) data or to share data between containers, We create and attach volumes to containers. Volumes are normal directories that exist on the host and ‘mapped’ to the container. Volumes created can be found at: /var/lib/docker/volumes/

OVERVIEW OF THE DOCKER-COMPOSE.YML FILE

• VERSION: version of the compose file
• SERVICES: defines the services we are creating (postgres-db and pgadmin4-demo)
• IMAGE: image to use for creating the container
• MEM_LIMIT: A strict upper limit to the amount of memory that can potentially be used by a Docker container.
• MEM_RESERVATION: This should be set as the bare minimum amount of memory that an application needs to run properly.
• POSTGRES_USER: This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name
• PORTS: “5555:5432” —
  5555 = port on the host.
  5432 = port in the postgresql container

Hence 5555 maps to 5432 in the postgresql container.

• NETWORKS:
  PG_PGADMIN_NETWORK:
• DRIVER: BRIDGE
  a bridge network uses a software bridge which allows containers connected to the same bridge network to communicate, while providing isolation from containers which are not connected to that bridge network.
• VOLUMES:
  - DB-VOL:/VAR/LIB/POSTGRESQL/DATA

Volumes are normal directories that exist on the host and ‘mapped’ to the container.

• PGADMIN_DEFAULT_EMAIL & PGADMIN_DEFAULT_PASSWORD: username and password for PgAdmin
• PORTS:
  - “7777:80”
  7777 = port on the host.
  80 = port in the pgadmin container

Hence 7777 maps to 80 in the pgAdmin container.