Install Postgresql Docker Container

/ Comments off

This file will have a configuration to create a PostgreSQL database in a Docker container and connect it to the container of the Spring Boot project to run as a multi-container application. Define the Docker Compose yml file. In the project directory, create a docker-compose.yml file. Setup the docker-compose.yml file with the following. Tutorial PostgreSQL - Docker Installation on Ubuntu Linux. Install the Docker service. Copy to Clipboard. Apt-get update apt-get install docker.io. Download the PostgreSQL docker image from the online repository. Copy to Clipboard. Docker pull postgres. List the Docker images installed on your system. Copy to Clipboard. With all databases data in the container. Looking on Postgres Official Docker Image I found the use of mapping a host’s folder with postgres data folder inside the container in order to maintain the data if you delete the container or want to use that data with another container for another project. May 25, 2015 Riga, Latvia / Tokyo, Japan / New York, USA. Joined May 25, 2015. Displaying 18 of 18 repositories. Install Docker Compose. By default, Docker Compose is not available in the CentOS 8 default repository. So you will need to download it from the Git repository. First, install the curl command with the following command: dnf install curl -y. Next, download the latest version of Docker Compose from the Git reposiotry using curl as shown below.

Introduction

This is the second of a two-part tutorial series explaining how to build and run a Python and PostgreSQL docker container. The container is being designed to uses the psycopg2 database adapter for PostgreSQL and Python programming language to interface with the database. Part two will also explain how to use Docker Compose and Dockerfile to customize a PostgreSQL-Python image and use the PythonPIP package manager to install the psycopg2 Postgres adapter.

Prerequisites for Docker

Docker
  • Docker must be properly installed on the machine or server running the programs. Elevate privileges must be enabled with access to a command prompt or UNIX terminal on the same machine or server.

Open a terminal or command-prompt window on the host machine and execute the following command to confirm Docker is installed and running:

docker --version

Note that the Docker engine version number must be compatible with the Docker Compose version number to avoid errors.

PostgreSQL table records for testing

Part one of this series created a Docker container with docker run and then entered into the container using the docker exec -it command. This was done in order to create a PostgreSQL table and sample records in a bind-mounted volume for persistent data.

If the steps in part one of this series have not yet been completed, a table must first be inserted and records added into the Docker container’s bind-mounted volume.

IDE for Python and YAML

Confirm access to a text editor or IDE that has both YAML and Python syntax as well as indentation support. While the examples in this tutorial use the Sublime IDE, other editors like Atom IDE or Visual Studio Code that uses the code command will work as well.

Docker-Compose file for Postgres

A Docker Compose file will be used to set up the container and specify such things as the bind-mounted volumes and port mapping.

Create a Docker-Compose file for the Postgres container

Change into root of the PostgreSQL-Docker project directory and create a new Docker compose file.

Following is an example of how to create a new file in a UNIX-based terminal using the touch command:

The following command can be used to edit the new file with the Sublime IDE:

subl docker-compose.yml

Note that the .yaml file extension can also be used in above command.

Docker-Compose version number

The Docker Compose version must be specified at the top of the YAML file. As shown in the following command, version '3.2' should work for the most recent stable releases of Docker:

NOTE: Typically, releases of Docker Engine 17 or newer will use a version string of '3.x' or greater in the Docker Compose file.

Postgres container and environment variables

At the same indentation level as the version number, the services: keys must be declared and the container name must be specified at one indentation level below the version number. This is shown in the following code:

services:
# container name
postgres
:
# build the image from Dockerfile
build
:
context
: ${PWD}
# bind mount volume for Postgres data
volumes
:
- $PWD/pg-data/:/var/lib/postgresql/data
- $PWD/python-app:/var/www/html
# open port so host machine can access
ports
:
- '5432:5432'
# configure Postgres environment variables
environment
:
- POSTGRES_USER=objectrocket
- POSTGRES_DB=some_db
- POSTGRES_PASSWORD=1234

The above YAML markup will instruct Docker to map the host port to the default Postgres port of 27017 and set the PostgreSQL environment variables. Note that the relative location for the Dockerfile must be specified under the context: key.

Networking in Docker-Compose

The last part of the YAML mark sets up the network so the container will run on an IP address of 172.28.1.4:

networks:
node_net
:
ipv4_address
: 172.28.1.4

networks
:
node_net
:
ipam
:
driver
: default
config
:
- subnet
: 172.28.0.0/16

NOTE: Make certain to save the changes made here before moving on to the Dockerfile.

Dockerfile for Postgres

The Dockerfile for the container used to execute bash commands inside the container must be created.

Execute the following command to open an instance of Dockerfile in the Sublime text editor:

Docker

NOTE: The Dockerfile name cannot have a file extension after it.

Pull the Postgres image

The following commands will pull the latest image for postgres, if needed, from the Docker hub repository, including a “bare bones” distro of Debian Linux.

Execute the following code to install Python 3 and its PIP package manager:

FROM postgres:latest
# install Python 3
RUN apt-get update&&apt-get install-y python3 python3-pip
RUN apt-get-yinstall python3.7-dev
RUN apt-get install postgresql-server-dev-10gcc python3-dev musl-dev

Install the psycopg2 adapter

Now use pip3 to install the psycopg2 Python adapter for PostgreSQL. This will allow for the retrieval and modification PostgreSQL table data stored in the container’s volume.

Execute the following command to install the psycopg2 Python adapter:

# install psycopg2 library with PIP
RUN pip3 install psycopg2

Setup the PostgreSQL environment

In the final part of the Dockerfile, the following command is executed to set up the Postgres volumes, add the postgres admin role and expose the port for the Postgres service:

# add the 'postgres' admin role
USER postgres
# expose Postgres port
EXPOSE 5432
# bind mount Postgres volumes for persistent data
VOLUME ['/etc/postgresql', '/var/log/postgresql', '/var/lib/postgresql']

Now save all of the changes made to the file and then open a terminal window at the project’s root directory where the docker-compose.yml file is located.

Build the Postgres container

Execute the following command to spin up and build the Docker container using the specifications laid out in the Docker files:

NOTE: If a bind mount error is received because the specified port for Postgres is not accessible, use the sudo lsof -i -P -n grep 5432 command to search for the cause of the error and then pass the PID (Process ID) to the sudo kill -9 command to fix the error.

Create a Python script

While waiting for the container to build from Docker Hub’s postgres image, begin working on the Python script for the RESTful API calls to the container’s bind-mounted Postgres database.

Python script in the app folder

Execute the following code to create a new Python script in the python-app directory on the host machine that is bind-mounted to the container’s volume:

subl python-app/test-postgres.py

Now paste the following Python code into the .py script, making certain to save it in the directory specified under volumes: in the Docker Compose file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#!/usr/bin/python3
# -*- coding: utf-8 -*-
# import the connect library from psycopg2
from psycopg2 import connect
table_name ='some_table'
# declare connection instance
conn = connect(
dbname ='some_db',
user='objectrocket',
host ='172.28.1.4',
password ='1234'
)
# declare a cursor object from the connection
cursor = conn.cursor()
# execute an SQL statement using the psycopg2 cursor object
cursor.execute(f'SELECT * FROM {table_name};')
# enumerate() over the PostgreSQL records
for i, record inenumerate(cursor):
print('n',type(record))
print( record )
# close the cursor object to avoid memory leaks
cursor.close()
# close the connection as well
conn.close()

The results should resemble the following:

Enter the Postgres Container

The container should run as a foreground process in the terminal window after it has finished building and installing Python. Open a new terminal window, or a new tab in the terminal, and use the below docker exec command to connect to the container interactively as explained in part one of this tutorial series. However, the container ID must first be obtained by using docker ps. Once the ID is obtained, use the first three digits of the alpha-numeric ID to execute the following interactive TTY session with the running container:

Once inside of the container, input python3 -V and psql -V to verify that both Python 3 and Postgres are working properly, as shown here:

Execute a pycopg2 Python script in the container

Navigate to the /var/www/html directory inside the container where the Python script should be visible. Now use the python3 command to execute the script from the terminal.

The Python code should return the PostgreSQL records stored in the container’s volume. Assuming Python and the psycopg2 adapter are working properly on the host machine, those records should be accessible outside of the container by changing the script’s host to localhostas shown in the following screenshot:

Now type exit into the terminal to leave the container and then input docker-compose down from the root directory of the project to shut down the container.

Conclusion

This was part two of a tutorial series explaining how to build and run a Python and PostgreSQL docker container. This second part of the series covered how to use Docker Compose and Dockerfile to customize a PostgreSQL-Python image and use the PythonPIP package manager to install the psycopg2 Postgres adapter to make API calls to a Postgres database in Python. It is critical to remember that the Dockerfile name cannot have a file extension after it when executing the command to open an instance of Dockerfile in the Sublime text editor.

I just started this as fun activity and checking remote database connectivity in a single board computing environment and how docker can make single board computing easy and more usable in real IOT projects. I am glad that everything worked as expected and still few things to be explored ! I am not connecting my sensor now, that will be step 2 to log sensor data on PostgreSQL hosted on Pi. So really less hack this time :).

I am just planning SSH (putty or techtia which ever I get) to the Raspberry Pi from another Windows PC for better control on the Raspberry Pi. Once the PostgreSQL container is up and running I will connect the instance from Azure Data Studio and then we will try some basic PSQL bash command from SSH terminal.

I am trying to put every detail step to install PostgreSql on Raspberry Pi running on Debian Linux. Lets create a folder named postgres1:

Create postgres_data folder inside postgres1 folder:

Install Postgresql Docker Container


Create the docker-compose.yml using any editor … I am using Nano which come out of box with Debian Linux. VI or VIM anything can be used but VIM require separate installation on the PI..

I created a 'docker-compose.yml' file like below .. There are many things we can change I liked these attributes in my file as I need them.

Now after writing the docker compose file Postgres1 the directory on Pi should look like below:

I prefer a previous postgres installation Config cleanup if there is something already. My approach was finding the config file and just remove that from specific location ..

Now use “sudo docker-compose up” .. It will take few minutes to create the container as per the docker compose file and should be okay if you get same response like below.

Meantime lets talk about bridge network and volume little bit - the purpose I am using now should work with default network on the compose but in future I want to add new services under same network and keep isolated from other default networks on the docker. As a result I want my networks to work as single bridge network as OSI data link layer component for the PostgreSql instance. My volume is located at '/var/lib/postgresql/data' which we can not read out side of docker process. I am using volume as this is best persistence storage mechanism in docker however I would love to play with this latter for back up, replication and mount (not included in this step 1 document).

Docker

Lets come back to the terminal .. Sometime you can expect the terminal frozen after starting the server but just starting a new SSH session should solve the problem.

Now we can see the PostgreSql instance is running in my Raspberry Pi in a docker container.

Connection to Raspberry PI remote Postgres database from Azure Data Studio:

I just installed Azure data Studio on a Windows PC and installed the PostgreSQL plugin for Azure Data Studio. Now I will use Azure Data Studio to remotely connect the PostgreSQL database running on Raspberry PI using TCP port 5432. Let's to verify TCP connection in postgresql.conf file.

Using above command I tracked down the file and and then just a quick check whether the listen_address =”*” or not. I was expecting a * there not 127.0.0.1 (fake) .. and it is *.

Great .. I am connected now. Running a basic SQL query on 'master' database from azure data studio.

Install postgresql docker container list

Now I will create a new super user 'postgres' which I would use from psql command line .. I just logged in in azure data studio and ran the sql.

PSQL Command from SSH terminal:

Now I am going to use psql command and list down all the postgres databases present in my raspberry pi. Run this from from you ssh terminal connecting Raspberry pi.

Then login as the created super user 'postgres'.. My host is localhost for psql because I am just doing ssh and port I opened 5432 as per the docker compose file.

This should allow me to see the available databases using 'l' or we can create new databases also using psql as we logged in as superuser.

Remove container:

Install Postgresql Docker Container Model

Finally, I will use same docker compose file to remove the container from my raspberry pi. This just to do clean up what I did now …

Install Postgresql Inside Docker Container