Connecting to remote PostgreSQL DB (database) from a Docker container

Photo by Philippe Oursel on Unsplash

This blog demonstrates how to connect to an external remote PostgreSQL DB (database) from a docker container.

PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Docker is a set of platform as a service products that use OS-level virtualization to deliver software in packages called containers.

Step 1 — Install PostgreSQL DB instance.

I will be using a docker-compose approach. Docker Compose is a tool that helps us overcome this problem and easily handle multiple containers at once. The PostgreSQL will be installed on the host with IP: 192.168.0.123

dmi@dmi-VirtualBox:~/my_mysql_postgres_db_instances$ cat docker-compose.yaml
version: '3'

services:
  mysql_db_dima:
    image: mysql:5.7
    container_name: mysql_db_dima
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_ROOT_HOST: "%"
      MYSQL_DATABASE: my_mysql_db
      MYSQL_USER: my_db_user
      MYSQL_PASSWORD: 123456
    ports:
      - "3307:3306"
    volumes:
      - mysql_data:/var/lib/mysqlcl
  pg_db_dima:
    image: postgres:12
    container_name: postgres_db_dima
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: 123456
      POSTGRES_DB: postgres
    ports:
      - "5433:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data
volumes:
  mysql_data:
  pg_data:

And then

docker-compose up -d

The PostgreSQL DB Server is ready:

dmi@dmi-VirtualBox:~/my_mysql_postgres_db_instances$ psql -h localhost -p 5433 -U postgres -d postgres
Password for user postgres:
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1), server 12.7 (Debian 12.7-1.pgdg100+1))
Type "help" for help.

postgres=#

Step 2 — Create file runMe.sh

This executable shell will be run inside a Docker container.

#!/bin/bash
echo "$0: Start: $(date)"
echo "Viewing the PostgreSQL Client Version"
psql -Version
echo "Viewing the PostgreSQL Server Version"
export PGPASSWORD='123456'
psql -h 192.168.0.123 -p 5433 -U postgres -d postgres -c 'select version();'
echo "$0: End: $(date)"

Step 3 — Create Dockerfile

The Dockerfile includes instructions to create a container based on the Ubuntu version 22.04, then I add necessary packages, including psql client to be able to connect to PostgreSQL DB server.

FROM ubuntu:22.04
MAINTAINER Dmitry Romanoff
RUN apt-get update && apt-get install telnet -y
RUN apt-get install wget -y && apt-get install gnupg -y
RUN apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 7FCC7D46ACCC4CF8
RUN sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt jammy-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
RUN wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null
RUN apt-get update
RUN apt-get install postgresql-client -y
COPY runMe.sh runMe.sh
RUN chmod +x runMe.sh
ENTRYPOINT ["/runMe.sh"]

Step 4 — Create Docker image from the Dockerfile

docker build . -t from_doc_to_ext_db

Step 5 — Start container

docker run -it from_doc_to_ext_db

Step 6 — Check how the psql client installed inside the Docker container is connecting to the PostgreSQL DB (database) placed on an external remote host.

dmi@dmi-VirtualBox:~/from_docker_to_external_db$ docker run -it from_doc_to_ext_db
/runMe.sh: Start: Fri Dec  2 19:44:13 UTC 2022
Viewing the PostgreSQL Client Version
psql (PostgreSQL) 15.1 (Ubuntu 15.1-1.pgdg22.04+1)
Viewing the PostgreSQL Server Version
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

/runMe.sh: End: Fri Dec  2 19:44:13 UTC 2022
dmi@dmi-VirtualBox:~/from_docker_to_external_db$

Conclusion

In this blog I demonstrated how to connect to an external remote PostgreSQL DB from a docker container. It can be used to develop functionalities when it’s required to interact in a Docker container with a PostgreSQL DB database placed on an external / remote host. I believe this post can be useful for DevOps / R&D / Production / SRE / Software / Data Engineer(s) and anyone interested in this approach and technology.