Troubleshooting PostgreSQL

In Chapter 11, Deciding on Useful Extensions, we learned about some useful extensions that are widely adopted and that can give your deployment a real boost. By way of a follow-up, you will now be introduced to PostgreSQL troubleshooting. The idea is to give you a systematic approach to inspecting and fixing your system to improve its performance and avoid common pitfalls. Having a systematic approach definitely pays off. Many users face similar issues, so it makes sense to take a look at the most common problems haunting people all over the world.

In this chapter, the following topics will be covered:

• Approaching an unknown database
• Inspecting pg_stat_activity
• Checking for slow queries
• Inspecting the log
• Checking for missing indexes
• Checking for memory and I/O
• Understanding noteworthy error scenarios

Keep in mind that many things can go wrong in the database, so it is important to professionally monitor it. To figure...

Approaching an unknown database

If you happen to administer a large-scale system, you might not know what the system is actually doing. Managing hundreds of systems implies that you won’t know what is going on with each of them.

The most important thing when it comes to troubleshooting boils down to a single word: data. If there is not enough data, there is no way to fix things. Therefore, the first step to troubleshooting is to always set up a monitoring tool, such as pgwatch2 (available at https://www.cybertec-postgresql.com/en/products/pgwatch2/), which gives you some insights into your database server. Without proper data collection, troubleshooting is basically impossible. Always keep in mind that without data, you will be clueless.

Once a reporting tool informs you about a situation that is worth checking, this means it has been proven useful for approaching the system in an organized way.

Inspecting pg_stat_activity

Firstly, let’s check the content of pg_stat_statements, and answer the following questions:

• How many concurrent queries are currently being executed on your system?
• Do you see similar types of queries showing up in the query column all the time?
• Do you see queries that have been running for a long time?
• Are there any locks that have not been granted?
• Do you see connections from suspicious hosts?

The pg_stat_activity view should always be checked first because it will give us an idea of what is happening in the system. Of course, graphical monitoring is supposed to give you a first impression of the system. However, at the end of the day, it really boils down to the queries that actually run on the server. Therefore, a good overview of the system, as provided by pg_stat_activity, is more than vital for tracking down issues.

To make it easier for you, I have compiled a couple of queries that I find useful for spotting...

Checking for slow queries

After inspecting pg_stat_activity, it makes sense to take a look at slow, time-consuming queries. Basically, there are two ways to approach this problem:

• Look for individual slow queries in the log
• Look for types of queries that take too much time

Finding single, slow queries is the classic approach to performance tuning. By setting the log_min_duration_statement variable to a desired threshold, PostgreSQL will start to write a logline for each query that exceeds this threshold. By default, the slow query log is off, as follows:

test=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
                         -1
 (1 row)

However, setting this variable to a reasonably good value makes perfect sense. Depending on your workload, the desired time may, of course, vary.

In...

Inspecting the log

If your system is in trouble, it makes sense to inspect the log to see what is going on. The important point is this: not all log entries are created equally. PostgreSQL has a hierarchy of log entries that range from DEBUG to PANIC.

For the administrator, the following three error levels are of great importance:

• ERROR
• FATAL
• PANIC

ERROR is used for problems such as syntax errors, permission-related problems, and more. Your log will always contain error messages. The critical factor is this—how often does a certain type of error show up? Producing millions of syntax errors is certainly not an ideal strategy for running a database server.

FATAL is scarier than ERROR; you will see messages such as could not allocate memory for shared memory name or unexpected walreceiver state. In other words, these error messages are already really scary and will tell you that things are going wrong.

Finally, there is PANIC. If you come across...

Checking for missing indexes

Once we are done with the first three steps, it is important to take a look at performance in general. As I have continually stated throughout this book, missing indexes are fully responsible for super-bad database performance, so whenever we face a slow system, it is recommended that we check for missing indexes and deploy whatever is needed.

Usually, customers ask us to optimize the RAID level, tune the kernel, or do some other fancy stuff. In reality, these complicated requests often boil down to a handful of missing indexes. In my opinion, it always makes sense to spend some extra time just checking whether all of the desired indexes are there. Checking for missing indexes is neither hard nor time-consuming, so it should be done all the time, regardless of the kind of performance problem that you face.

Here is my favorite query to get an impression of where an index may be missing:

SELECT schemaname, relname, seq_scan, seq_tup_read,
 ...

Checking for memory and I/O

Once we have found missing indexes, we can inspect the memory and I/O. To figure out what is going on, it makes sense to activate track_io_timing. If it is on, PostgreSQL will collect information about the disk wait time and present it to you.

Often, the main question asked by a customer is: if we add more disks, will it be faster? It is possible to guess what will happen, but in general, measuring is the better and more useful strategy. Enabling track_io_timing will help you gather the data to really figure this out.

PostgreSQL exposes the disk wait time in various ways. One way to inspect things is to take a look at pg_stat_database:

test=# \d pg_stat_database
    View "pg_catalog.pg_stat_database"
Column          | Type                     | Modifiers
--------...

Understanding noteworthy error scenarios

After going through the basic guidelines to hunt down the most common issues that you will face in your database, the upcoming sections will discuss some of the most common error scenarios that occur in the PostgreSQL world.

Facing clog corruption

PostgreSQL has a thing called the commit log (now called pg_xact; it was formally known as pg_clog). This tracks the state of every transaction on the system and helps PostgreSQL determine whether a row can be seen. In general, a transaction can be in four states:

#define TRANSACTION_STATUS_IN_PROGRESS    0x00
#define TRANSACTION_STATUS_COMMITTED      0x01
#define TRANSACTION_STATUS_ABORTED        0x02
#define TRANSACTION_STATUS_SUB_COMMITTED  0x03

The clog has a separate directory in the PostgreSQL database instance (pg_xact).

In the past, people have reported something called clog corruption...

Summary

In this chapter, we learned how to systematically approach a database system and detect the most common issues that people face with PostgreSQL. We learned about some important system tables, as well as some other important factors that can determine whether we will succeed or fail. It is especially important to keep an eye on table bloat and dangerous error messages. Note that depending on the type of workload, you might face different challenges. The most important ones have been covered in this chapter.

In the final chapter of this book, we will focus our attention on migrating to PostgreSQL. If you are using Oracle or some other database system, you might want to check out PostgreSQL. In Chapter 13, Migrating to PostgreSQL, we’ll discuss everything involved in this.

Questions

Let us try to test our knowledge now and take a look at some of the more common questions faced by users of PostgreSQL:

• Why do databases not administrate themselves?
• Does PostgreSQL encounter corruption often?
• Does PostgreSQL require constant care?

Answers to these questions can be found in the GitHub repository (https://github.com/PacktPublishing/Mastering-PostgreSQL-15-).