blog

How to Monitor PostgreSQL Running Inside a Docker Container: Part Two

Thiago Lopes

Published

This is the second part of the multi-series How to Monitor PostgreSQL Running Inside a Docker Container. In Part 1, I presented an overview of docker containers, policies and networking. In this part we will continue with docker configuration and finally enable monitoring via ClusterControl.

PostgreSQL is an old school, open-source database whose popularity is still increasing, being widely used and accepted on most cloud environments of today. 

When it’s used inside of a container it can be easily configured and managed by Docker, using different tags for the creation and shipped to any computer in the world with Docker installed, but this is all about Docker.

Now we will discuss about PostgreSQL, and to start, let’s list their six main processes running simultaneously inside of a container by the OS user called “postgres”, which is a different one from the “postgres” user inside of the database, that one is a super user.

Remember that the blue arrow in the images is displaying commands entered inside of a container.

$ ps auxww
PostgreSQL main processes

PostgreSQL main processes

The first process on the list is the PostgreSQL server, and the others are started by him. Their duties are basically to analyze what is going on in the server, being sub-processes performing statistics inputs, writing logs, and these kinds of things.

We will use ClusterControl to monitor the activity inside of this container having the PostgreSQL server, and to do so we will need to have SSH installed in order to connect them safely.

Secure Shell Server (SSH)

To collect the information about the PostgreSQL container, nothing is better than SSH. It gives remote access for one IP address to another, and this all the ClusterControl needs to perform the job.

SSH needs to be downloaded from the repository, and to do so we must be inside of the container.

$ docker container exec -it postgres-2 bash
$ apt-get update && apt-get install -y openssh-server openssh-client
Installing SSH in the container "postgres-2"

Installing SSH in the container “postgres-2”

After install we’ll edit the configuration, start the service, setup a password for the root user, and finally leave the container:

$ sed -i 's|^PermitRootLogin.*|PermitRootLogin yes|g' /etc/ssh/sshd_config
$ sed -i 's|^#PermitRootLogin.*|PermitRootLogin yes|g' /etc/ssh/sshd_config
$ service ssh start
$ passwd
$ exit
Configuring the SSH in the "postgres-2" container, part 1/2

Configuring the SSH in the “postgres-2” container, part 1/2

Monitoring with ClusterControl

ClusterControl has an in-depth integration system able to monitor all the processes of PostgreSQL in real-time, also coming with a library of Advisors to keep the data secure, track the database performance, and of course providing alerts when anomalies happens.

With SSH configured, ClusterControl can monitor the OS hardware activity and give insights about both the database and the outside layer.

We will run be running a new container, and publishing it to the port 5000 of our computer, then we will be able to access the system through our browser.

$ docker container run --name s9s-ccontrol --network bridge-docker -p 5000:80 -d severalnines/clustercontrol
Running the container "s9s-ccontrol" for Severalnines ClusterControl

Running the container “s9s-ccontrol” for Severalnines ClusterControl

Once deployed, only remains the SSH configuration, and we have good news, because we are in a User-Defined Bridge Network, we can use DNS!

$ docker container exec -it s9s-ccontrol bash
$ ssh-copy-id postgres-2
Configuring the SSH in the "postgres-2" container, part 2/2

Configuring the SSH in the “postgres-2” container, part 2/2

After enter “yes” and specify the password provided earlier, it’s possible to access the “postgres-2” container as root using SSH:

$ ssh postgres-2
Checking the SSH connection successfully

Checking the SSH connection successfully

This new color, light blue, will be used in the following to represent the activity inside of the database. In the example above we have accessed the “postgres-2” container from “s9s-ccontrol”, but it’s still the root user. Keep your attention and criticism with me.

So the next step is going to the browser and access http://localhost:5000/clustercontrol/users/welcome/ and register an account, or if you already have one, visit http://localhost:5000/clustercontrol/users/login.

Then “Import Existing Server/Cluster” and enter the earlier configuration. The tab “PostgreSQL & TimescaleDB” must be selected. In the field “SSH User” for this demonstration just type “root”. Then finally enter the “Cluster Name”, and it can be any name that you want, is simply who will hold as many necessaries PostgreSQL containers that you want to import and monitor.

Importing the "postgres-2" database, part 1/2

Importing the “postgres-2” database, part 1/2

Now it’s time to enter the information about the PostgreSQL container, the User “postgres”, Password “5af45Q4ae3Xa3Ff4” and the desired containers. It’s extremely important to remember that the SSH service must be active inside of the PostgreSQL container.

Importing the "postgres-2" container, part 2/2

Importing the “postgres-2” container, part 2/2

After press the “Import” button, ClusterControl will start to manage the PostgreSQL container “postgres-2” inside of the Cluster called “PostgreSQL”, and it will inform when the process of importing is done.

Log about the process of importing the container "postgres-2"

Log about the process of importing the container “postgres-2”

Once finished, the system will be shown under the Clusters tab, our most recently created Cluster and different options separated in sections

Our first visualization step will be in the Overview option.

PostgreSQL Cluster imported successfully

PostgreSQL Cluster imported successfully

As you can imagine, our database is empty and we don’t have any chaos here for our fun, but the graphic still works being engaged in a small scale containing statistics about the SQL and Database processes.

Displaying statistics about the SQL and Database activity

Displaying statistics about the SQL and Database activity

Real World Scenario Simulation

To give some action, I’ve created a CSV file using Python, exploring the GitHub repository of Socratica, who provides amazing courses on YouTube, and they make those files available for free.

In summary, the created CSV file have 9 millions, 999 thousands and 999 records about persons, each one containing ID, first name, last name, and birthday. The size of the file is 324 MB:

$ du -s -h persons.csv
Checking the size of the CSV file

Checking the size of the CSV file

We will copy this CSV file into the PostgreSQL container, then copy it again but this time into the database, and finally check the statistics in ClusterControl.

$ docker container cp persons.csv postgres-2:/persons.csv
$ docker container exec -it postgres-2 bash
$ du -s -h persons.csv
$ su - postgres
$ psql
Transfering the CSV file to the container and entering in the database

Transfering the CSV file to the container and entering in the database

Ok, so we are in the database now, as the super user “postgres”, please note the different colors in the arrows.

Now, we must create the database, table, and populate it with the data contained in the CSV file, and finally check if everything is working fine.

$ CREATE DATABASE severalnines;
$ c severalnines;
$ CREATE TABLE persons (id SERIAL NOT NULL, first_name VARCHAR(50), last_name VARCHAR(50), birthday DATE, CONSTRAINT persons_pkey PRIMARY KEY (id));
$ COPY persons (id, first_name, last_name, birthday) FROM '/persons.csv' DELIMITER ',' CSV HEADER;
Connecting to the new database and importing the CSV file

Connecting to the new database and importing the CSV file

This process takes some minutes to complete.

Ok, so now let’s enter some queries:

Queries in the database "severalnines"

Queries in the database “severalnines”

If you look at ClusterControl now, some movement in the statistics about the hardware happened:

Displaying statistics about the CPU inside of ClusterControl

Displaying statistics about the CPU inside of ClusterControl

An entire section to monitor the queries is provided with ease of use UI:

Displaying statistics about the Queries inside of ClusterControl

Displaying statistics about the Queries inside of ClusterControl

Statistics about the PostgreSQL database serve the best DBAs to perform their entire potential on their main duties, and ClusterControl is a complete system to analyze every activity happening at real time, giving information based on all the data collected from the database processes.

With ClusterControl the DBA also can easily extend their skills using a full set of tools to create Backups locally or in the Cloud, Replications, Load Balancers, integrations with services, LDAP, ChatOps, Prometheus, and so much more.

Conclusion

Through this article, we’ve been configuring PostgreSQL inside of Docker, and integrating with ClusterControl using User-Defined Bridge Network and SSH, simulating a scenario populating the database with a CSV file, and then doing an overall quick check in the ClusterControl user interface.

Subscribe below to be notified of fresh posts