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

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.