Chapter 8: Monitoring and Diagnosis

In this chapter, you will find recipes for some common monitoring and diagnosis actions that you will want to perform inside your database. They are meant to answer specific questions that you often face when using PostgreSQL.

In this chapter, we will cover the following recipes:

• Overview of PostgreSQL monitoring
• Cloud-native monitoring
• Providing PostgreSQL information to monitoring tools
• Real-time viewing using pgAdmin
• Checking whether a user is connected
• Checking whether a computer is connected
• Repeatedly executing a query in psql
• Checking which queries are running
• Monitoring the progress of commands and queries
• Checking which queries are active or blocked
• Knowing who is blocking a query
• Killing a specific session
• Detecting an in-doubt prepared transaction
• Knowing whether anybody is using a specific table
• Knowing when a table was last used
• Usage of disk space by temporary...

Overview of PostgreSQL monitoring

Databases are not isolated entities. They live on computer hardware using CPUs, RAM, and disk subsystems. Users access databases using networks. Depending on the setup, databases themselves may need network resources to function in any of the following ways: performing some authentication checks when users log in, using disks that are mounted over the network (not generally recommended), or making remote function calls to other databases.

This means that monitoring only the database is not enough. At a minimum, you should also monitor everything directly involved in using the database. This means knowing the following:

• Is the database host available? Does it accept connections?
• How much of the network bandwidth is in use? Have there been network interruptions and dropped connections?
• Is there enough RAM available for the most common tasks? How much of it is left?
• Is there enough disk space available? When will you run...

Cloud-native monitoring

Prometheus is the tool of choice from the Cloud Native Computing Foundation, so we'll discuss it here. Prometheus is an open source monitoring and alerting toolkit that allows multiple types of systems to feed it monitoring data. An open source Prometheus exporter is available for PostgreSQL, though this is not always needed. For example, EDB's Cloud Native Postgres Operator integrates a Prometheus exporter into the Kubernetes operator to provide better security and avoid the need for a separate component in your architecture.

Data from Prometheus is displayed using Grafana. Data from Prometheus can also be stored inside a database and there are various options there for storing data inside PostgreSQL or other systems:

Figure 8.1 – Grafana view of PostgreSQL metrics

Remember that the key to successful monitoring is not the tool you use but what information you display with it.

Providing PostgreSQL information to monitoring tools

PostgreSQL exposes a huge amount of information for monitoring. To expose that information securely, make sure your user has the predefined (default) pg_monitor role, which will give you all you need. Some sources say to expose the full contents of pg_stat_activity and similar restricted views, but be careful how and when you do this. Monitoring is important but so is security.

It's best to use historical monitoring information when all of it is available from the same place and on the same timescale. Most monitoring systems are designed for generic purposes while allowing application and system developers to integrate their specific checks with the monitoring infrastructure. This is possible through a plugin architecture. Adding new kinds of data inputs to them means installing a plugin. Sometimes, you may need to write or develop this plugin, but writing a plugin for something such as Cacti is easy. You just...

Real-time viewing using pgAdmin

You can also use a GUI tool such as pgAdmin, which we discussed for the first time in Chapter 1, First Steps, to get a quick view of what is going on in the database.

Getting ready

pgAdmin4 no longer requires an extension to access PostgreSQL fully, so there is no need to install adminpack, as was required in earlier editions. If you use pgAdmin3, you may still want to install the adminpack extension in the destination database by issuing the following command:

CREATE EXTENSION adminpack;

This extension is a part of the additionally supplied modules of PostgreSQL (also known as contrib).

How to do it…

This section illustrates the pgAdmin tool.

Once you have connected to the database server, a window similar to the one shown in the following screenshot will be displayed, where you can see a general view, plus information about connections, overall activity, and...

Checking whether a user is connected

Here, we will show you how to learn whether a certain database user is currently connected to the database.

Getting ready

If you are logged in as a superuser, you will have full access to monitoring information.

How to do it…

Issue the following query to see whether the user bob is connected:

SELECT datname FROM pg_stat_activity WHERE usename = 'bob';

If this query returns any rows, then that means that bob is connected to the database. The returned value is the name of the database that the user is connected to.

How it works…

PostgreSQL's pg_stat_activity system view keeps track of all running PostgreSQL backends. This includes information such as the query that is being currently executed, or the last query that was executed by each backend, who is connected, when the connection, the transaction, and/or the query were started, and so on...

Checking whether a computer is connected

Often, several different processes may connect as the same database user. In that case, you may want to know whether there is a connection from a specific computer.

How to do it…

You can get this information from the pg_stat_activity view as it includes the connected clients' IP address, port, and hostname (where applicable). The port is only needed if you have more than one connection from the same client computer and you need to do further digging to see which process there connects to which database. Run the following command:

SELECT datname, usename, client_addr, client_port,
       application_name FROM pg_stat_activity
WHERE backend_type = 'client backend';

The client_addr and client_port parameters help you look up the exact computer and even the process on that computer that has connected to the specific database. You can...

Repeatedly executing a query in psql

Sometimes, we want to execute a query more than once, repeated at regular intervals; in this recipe, we will look at an interesting psql command that does exactly that.

How to do it…

The \watch meta-command allows psql users to automatically (and continuously) re-execute a query. This behavior is similar to the watch utility of some Linux and Unix environments.

In the following example, we will run a simple query on pg_stat_activity and ask psql to repeat it every 5 seconds. You can exit at any time by pressing Ctrl + C:

gabriele=> SELECT count(*) FROM pg_stat_activity;
 count
-------
     1
(1 row)
gabriele=> \watch 5
Watch every 5s     Tue Aug 27 21:47:24 2013
 count
-------
     1
(1 row)
<snip>

There's more…

For further information about the psql...

Checking which queries are running

In this section, we will show you how to check which query is currently running.

Getting ready

You must make sure that you are logged in as a superuser or as the same database user you want to check out. Also, ensure that the track_activities = on parameter is set (which it normally should be, being the default setting). If not, check the Updating the parameter file recipe in Chapter 3, Server Configuration.

How to do it…

To see which connected users are running at this moment, just run the following code:

SELECT datname, usename, state, backend_type, query
       FROM pg_stat_activity;

This will show normal users as "client backend," but it will also show various PostgreSQL worker processes that you may not want to see. So, you could filter this using WHERE backend_type = 'client backend'.

On systems with a lot of users, you...

Monitoring the progress of commands

PostgreSQL 14 now has a growing list of commands that have a "progress bar" – in other words, they provide information to show intermediate progress information for active commands.

Getting ready

Using the earlier recipes, identify the active processes that concern you:

SELECT pid, query
FROM pg_stat_activity
WHERE state = 'active';

If the query column indicates that they are one of the following actions, then we can look at detailed progress information for them:

• Maintenance commands: ANALYZE, VACUUM, VACUUM FULL/CLUSTER
• Index commands: CREATE INDEX, REINDEX
• Backup/replication: BASE BACKUP
• Data load/unload: COPY

At this time, SELECT statements don't provide detailed progress information.

How to do it…

Each type of command has specific progress information, so you must look in the view that's appropriate to the type of command.

All commands show...

Checking which queries are active or blocked

Here, we will show you how to find out whether a query is running or waiting for another query.

Getting ready

Using the predefined (default) pg_monitor role, you will have full access to monitoring information.

How to do it…

Follow these steps to check if a query is waiting for another query:

1. Run the following query:

   SELECT datname, usename, wait_event_type, wait_event, backend_type, query
   FROM pg_stat_activity
   WHERE wait_event_type IS NOT NULL
   AND wait_event_type NOT IN ('Activity', 'Client');

2. You will receive the following output:

   -[ RECORD 1 ]---+-----------------
   datname         | postgres
   usename         | gianni
   wait_event_type | Lock
   wait_event      | relation
   backend_type    | client backend
   query   ...

Knowing who is blocking a query

Once you have found out that a query is being blocked, you need to know who or what is blocking it.

Getting ready

If you are logged in as a superuser, you will have full access to monitoring information.

How to do it…

Perform the following steps:

1. Write the following query:

   SELECT datname, usename, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by, backend_type, query
   FROM pg_stat_activity
   WHERE wait_event_type IS NOT NULL
   AND wait_event_type NOT IN ('Activity', 'Client');

2. You will receive the following output:

   -[ RECORD 1 ]---+-----------------
   datname         | postgres
   usename         | gianni
   wait_event_type | Lock
   wait_event      | relation
   blocked_by      | {18142}
   backend_type    | client backend...

Killing a specific session

Sometimes, the only way to let the system continue as a whole is by surgically terminating some offending database sessions. Yes, you read that right: surgically.

In this recipe, you will learn how to intervene, from gracefully canceling a query to brutally killing the actual process from the command line.

How to do it…

Once you have figured out the backend you need to kill, try to use pg_cancel_backend(pid), which cancels the current query, though only if there is one. This can be executed by anyone who is a member of the role whose backend is being canceled.

If that is not enough, then you can use pg_terminate_backend(pid), which kills the backend. This works even for client backends that are idle or idle in a transaction.

You can run these functions as a superuser, or if the calling role is a member of the role whose backend pid is being signed (look for the usename field in the...

Detecting an in-doubt prepared transaction

While using a two-phase commit (2PC), you may end up in a situation where you have something locked but cannot find the backend that holds the locks. This recipe describes how to detect such a case.

How to do it…

Perform the following steps:

1. You need to look up the pg_locks table for those entries with an empty pid value. Run the following query:

   SELECT t.schemaname || '.' || t.relname AS tablename,
          l.pid, l.granted
          FROM pg_locks l JOIN pg_stat_user_tables t
          ON l.relation = t.relid;

2. The output will be something similar to the following:

    tablename |  pid  | granted
   -----------+-------+---------
       db.x   |       | t
       db.x...

Knowing whether anybody is using a specific table

This recipe will help you when you are in doubt about whether an obscure table is being used anymore, or if it has been left over from past use and is just taking up space.

Getting ready

Make sure that you are a superuser, or at least have full rights to the table in question.

How to do it…

Perform the following steps:

1. To see whether a table is currently in active use (that is, whether anyone is using it while you are watching it), run the following query on the database you plan to inspect:

   CREATE TEMPORARY TABLE tmp_stat_user_tables AS
          SELECT * FROM pg_stat_user_tables;

2. Then, wait for a while and see what has changed:

   SELECT * FROM pg_stat_user_tables n
     JOIN tmp_stat_user_tables t
       ON n.relid=t.relid
      AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)
       <> (t...

Knowing when a table was last used

Once you know that a table is not currently being used, the next question is, When was it last used?

Getting ready

You need to use a user with appropriate privileges.

How to do it…

PostgreSQL does not have any built-in last used information about tables, so you have to use other means to figure it out.

If you have set up a cron job to collect usage statistics, as described in the previous chapter, then it is relatively easy to find out the last date of change using a SQL query.

Other than this, there are two possibilities, neither of which give you reliable answers.

You can either look at the actual timestamps of the files that the data is stored in, or you can use the xmin and xmax system columns to find out the latest transaction ID that changed the table data.

In this recipe, we will cover the first case and focus on the date information in the table&apos...

Usage of disk space by temporary data

In addition to ordinary persistent tables, you can also create temporary tables. Temporary tables have disk files for their data, just as persistent tables do, but those files will be stored in one of the tablespaces listed in the temp_tablespaces parameter or, if not set, the default tablespace.

PostgreSQL may also use temporary files for query processing for sorts, hash joins, or hold cursors if they are larger than your current work_mem parameter setting.

So, how do you find out how much data is being used by temporary tables and files? You can do this by using any untrusted embedded language, or directly on the database host.

Getting ready

You have to use an untrusted language because trusted languages run in a sandbox, which prohibits them from directly accessing the host filesystem.

How to do it…

Perform the following steps:

1. First, check whether your database defines special tablespaces for...

Understanding why queries slow down

In production environments with large databases and high concurrent access, it might happen that queries that used to run in tens of milliseconds suddenly take several seconds.

Likewise, a summary query for a report that used to run in a few seconds may take half an hour to complete.

Here are some ways to find out what is slowing them down.

Getting ready

Any questions of the type why is this different today from what it was last week? are much easier to answer if you have some kind of historical data collection setup.

The tools we mentioned in the Providing PostgreSQL information recipe that can be used to monitor general server characteristics, such as CPU and RAM usage, disk I/O, network traffic, load average, and so on are very useful for seeing what has changed recently, and for trying to correlate these changes with the observed performance of some database operations.

Also, collecting historical statistics...

Analyzing the real-time performance of your queries

The pg_stat_statements extension adds the capability to track the execution statistics of queries that are run in a database, including the number of calls, total execution time, the total number of returned rows, and internal information on memory and I/O access.

It is evident how this approach opens up new opportunities in PostgreSQL performance analysis by allowing database admins to get insights directly from the database through SQL and in real time.

Getting ready

The pg_stat_statements module is available as a contrib module of PostgreSQL. The extension must be installed as a superuser in the desired databases. It also requires administrators to add the library to the postgresql.conf file, as follows:

shared_preload_libraries = 'pg_stat_statements'

This change requires restarting the PostgreSQL server.

Finally, to use it, the extension must be installed...

Investigating and reporting a bug

When you find out that PostgreSQL is not doing what it should, then it's time to investigate.

Getting ready

It is a good idea to make a full copy of your PostgreSQL installation before you start investigating. This will help you restart several times and be sure that you are investigating the results of the bug, and not chasing your tail by looking at changes that were introduced by your last investigation and debugging attempt.

Do not forget to include your tablespaces in the full copy.

How to do it…

Try to make a minimal repeatable test scenario that exhibits this bug. Sometimes, the bug disappears while doing this, but mostly, it is needed to make the process easy. It is almost impossible to fix a bug that you can't observe and repeat at will.

If it is about query processing, then you can usually provide a minimal dump file (the result of running pg_dump) of the specific tables, together...