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.
Latest comments (0)