How-To

Running Apps in Docker, Part 1: Instantiation and Using PostgreSQL

After his introductory series, Tom Fenton deploys a database on Docker and integrates it with other apps deployed from other Docker images.

• By Tom Fenton
• 01/18/2023

In my Introduction to Docker series, I covered the basics of what Docker is, how to install it and how to download images and start (instantiate) them. I also discussed networking and storage for Docker instances (running images).

While I stuck to just using a single Linux image in those articles, being able to deploy one or more images to create a multi-tiered application is where the real power of Docker comes into play. So, in this new series of articles, I will do that: deploy a database on Docker and then integrate it with other applications deployed from other Docker images.

There are many reasons why you would want to deploy an application in a Docker container rather than natively on the base OS, including isolation, ease of deployment and ease of upgrading. When multiple applications run natively on the same OS, they can interfere with one another. On the contrary, when each application runs in its own container, it is less likely to affect applications running in another container if another native or Docker instance has issues.

Because Docker applications are stored as images in a repository, they can be pulled down and deployed using just a few simple commands. As Docker is platform agnostic; identical images can be deployed on-premises, in the cloud, or on the Edge.

Instantiating PostgreSQL Instance
I will deploy the PostgreSQL database, a free, open source, relational database management system (RDBMS) whose origins date back to 1996. Since then, it has proven to be extremely reliable and performant. Although thousands of individuals and small businesses use PostgreSQL, it is also used by some of the largest corporations in the world and is the database for other applications. For example, VMware vCenter Server Appliance (VCSA) has an embedded PostgreSQL database. It also has a well-documented API that can be used to connect to it, and additionally has connectors that allow it to be used by most common programming languages, including Rust and Go.

Instantiating a PostgreSQL Image
Each example I provide below will be deployed on the Docker environment that I set up in my previous series of articles. Due to its agnostic nature, the following commands should work in other Docker environments, but they may require some minor tweaking. This article only touches on the basics of using PostgreSQL, but there is a list of free PostgreSQL tutorials located here.

Before deploying a PostgreSQL Docker image in my environment, I became root user, verified that the Docker engine was running, listed the Docker images, and examined my networking and volumes, as well as removed all unused instances, images, networks, and volumes, by entering:

  sudo bash
  systemctl status docker
  docker stats
  docker ps -a
  docker images
  docker network ls
  docker volume ls 
  docker system  prune --volumes
    

I listed PostgreSQL Docker images, downloaded the latest PostgreSQL image, instantiated (started) it and verified that it was started by entering:

  docker search postgres
  docker pull postgres


  docker run --name postgresql01 -e POSTGRES_USER=PostUser01 -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -v /data:/var/lib/postgresql/data -d postgres
  docker ps -a

While many of the switches in this last command should be obvious, others may not be. The name of the instance is postgresql01, and the network connection between the host and instance is -p 5432:5432. The -v specifies the location where the persistent data is saved, and the -d runs the container in the background if you disconnect from the console.

Using PostgreSQL
One of the ways to manage PostgreSQL is by using psql, its command-line interface.

I entered my PostgreSQL container, launched psql as the user I created when I instantiated it, listed the existing tables (three are created by default), created a new database (TDB01), connected to it, and ran psql commands to show the current date and the version of PostgreSQL I was using by entering:

  docker exec -it postgresql01 
  psql -U PostUser01
  \list
  CREATE DATABASE TDB01;
  \l
  \c TDB01
  SELECT current_date;
  SELECT version;

Following the example in the PostgreSQL tutorial, I created tables and worked with the database without any issues.

I deleted the database that I created (TDB01) by disconnecting from it, and removed (dropped) it by entering:

  DROPDB TDB01
  \q

Importing Data into PostgreSQL
There are many ways to import data into the database, but I wanted to import data from the devices that I was monitoring using Edge DX, an endpoint monitoring and remediation tool from ControlUp (a company that I work for). This data can be exported via a comma separated value (CSV) file.

The data that I extracted from Edge DX had six columns of data that contained information about the device and the applications which were installed on the device.

I created a new database (appsdata), connected to the database, created a table (apps) in the database, displayed the tables in the database, and then displayed the columns in the table by entering:

  CREATE DATABASE appsdata;
  \c appsdata
  CREATE TABLE Apps("_device_id" VARCHAR(40),"created_local" DATE,"device_name" VARCHAR(40),"name" VARCHAR(120),"publisher" VARCHAR(120) ,"version" VARCHAR(120));
  \dt+
  \d+ apps

I used SCP to transfer the CSV data from my laptop to the host system that Docker was running on. I then transferred it from the host machine to the Docker instance from the host by entering:

  docker cp /tmp/Apps.csv postgresql01:/tmp/Apps.csv

I copied the data from the CSV file into the apps table, displayed all the rows of the data, and displayed and counted the different applications (starting with the most-used app and then by name) by entering the following in psql:

  COPY apps FROM '/tmp/Apps.csv'  DELIMITER ','  CSV HEADER;
  SELECT * FROM apps;
  SELECT NAME, count (*) from apps05 GROUP BY NAME ORDER BY COUNT DESC;
  SELECT NAME, count (*) from apps GROUP BY NAME ORDER BY NAME;

Outputting Query Information to a File
I copied the output of my query in CSV format to a file in the /tmp directory, exited the database, and verified that the new file existed by entering:

  copy (select NAME, count (*) from apps04 GROUP BY NAME ORDER BY COUNT) to '/tmp/Appcount.txt' with csv;
  \q
  ls -l /tmp/A*

Here are PostgreSQL commands I used:

New Docker commands used in this article:

Conclusion
In this article, I showed you how to deploy PostgreSQL in a Docker container. I ran through a few psql commands to show its viability and explained how to perform Docker functions such as copying data from the Docker host to the container. Hopefully this gives you an idea for how easy it is to deploy a PostgreSQL database using a Docker image. This image can be deployed in the cloud or on the Edge as easily as it was my on-premises system. I deployed the latest PostgreSQL image, but I could have deployed older versions of PostgreSQL just as easily for regression testing.

In the next article, I will dive deeper into PostgreSQL and deploy additional Docker images for PostgreSQL management in order to demonstrate how multiple images interoperate with Docker containers.