
Easier working with PostgreSQL – part III. database connection monitor

In this installment of the series about useful tools for working with PostgreSQL, we will discuss a tool that will certainly appeal to database administrators – about the interactive activity monitor of the pg_activity server. Database administration requires checking from time to time what is happening in a given system at a given moment. Of […]
In this installment of the series about useful tools for working with PostgreSQL, we will discuss a tool that will certainly appeal to database administrators – about the interactive activity monitor of the pg_activity server.
Database administration requires checking from time to time what is happening in a given system at a given moment. Of course, in the event of a crisis, nothing can replace a well-designed early response mechanism informing about the occurrence of a potentially undesirable state in the database system. However, in order to find out what the situation looks like, you often have to reach for previously prepared administrative scripts or write appropriate instructions ad hoc after connecting to the server.
Many tools (from graphic to terminal) can be used to monitor EuroLinux system parameters, such as top
, ps
, whether htop
. They allow you to quickly explore the system status without having to write your own scripts parsing information in the directory /proc
. A similar convenience is the program pg_activity
which clearly draws handfuls of the program htop
and tries to provide the most useful information to the administrator in a similar way.
Pg_activity installation
The program code was written in Python and is shared using GitHub on an Open Source license similar to MYTH (which can be read on our blog).
Installation can be carried out easily using the command pip3
.
$ pip3 install pg_activity
To isolate installations, it may be a good idea to use a virtual environment that can be created and activated as follows:
$ python3 -m venv pgactivityenv $. pgactivityenv /bin/activate
Thanks to this, we will be sure that the application will not change the state or libraries in system paths during testing.
Using pg_activity
As you can see in the screenshot above, after starting the application we are greeted with a clear screen with a short summary at the top and a list of detailed connections at the bottom.
Individual columns correspond to the following information:
PID
– process number that is responsible for this session;DATABASE
– name of the database to which the connection took place;APP
– displays the name of the application connecting to the database (SET application_name
). It is worth mentioning here that the use of this mechanism in
significantly facilitates the maintenance of an application using PostgreSQL;USER
– database user who made the connection;CLIENT
– column suggests how the connection was made. In this caselocal
is responsible for the connection made by socket;TIME
– process operation time;W
– information about whether a given session is waiting for some lock;state
– information on the status of the transaction;Query
– query that is currently being executed.
In addition, views are also available to look at queries that are currently waiting for some resource to be released. To do this, just press the key F2
or 2
.
Similarly, we can view blocking queries by going to a dedicated view that we activate by pressing the key F3
or 3
.
Information on interactive use of the application can be obtained by pressing the key h
which presents a help screen.
Using this tool during everyday work definitely makes it easier to monitor the status of the database. It allows you to develop the habit of browsing connected processes, with particular emphasis on those that assume blockades. In the case of tables that constitute the so-called hot path, blocking monitoring is even necessary, because setting them up will significantly affect the speed of applications using the database.
Summary
I hope that the program presented in this text will enrich your list of useful tools for working with PostgreSQL and, above all, that it will be useful in everyday work. I encourage you to read the previous guides in this series, which links to below.