Log Files and System Statistics

In Chapter 4, Handling Advanced SQL, you learned about advanced SQL and ways of viewing SQL in a different light. However, database work doesn’t only consist of hacking up fancy SQL. Sometimes, it is about keeping things running in a professional manner. To do that, it is highly important to keep an eye on system statistics, log files, and so on.

Monitoring is the key to running databases professionally. Fortunately, PostgreSQL has many features that can help you monitor your databases, and you will learn how to use them in this chapter.

In this chapter, you will learn about the following topics:

• Gathering runtime statistics
• Creating log files
• Gathering important information
• Making sense of database statistics

By the end of this chapter, you will be able to configure PostgreSQL’s logging infrastructure properly and take care of log files in the most professional way possible.

Gathering runtime statistics

The first thing you really have to understand is what features PostgreSQL’s onboard statistics have to offer and how to use them. In my opinion, there is no way to improve performance and reliability without collecting the necessary data to make prudent decisions.

This section will guide you through PostgreSQL’s runtime statistics and explain how you can extract more runtime information from your database setups.

Working with PostgreSQL system views

PostgreSQL offers a large set of system views that allow administrators and developers alike to take a deep look into what is really going on in their system. The trouble is that many people actually collect all this data but cannot make real sense of it. The general rule is this: there is no point in drawing a graph for something you don’t understand anyway. Therefore, the goal of this section is to shed some light on what PostgreSQL has to offer to hopefully make it easier for...

Creating log files

Now that we’ve taken a deep look at the system views provided by PostgreSQL, it’s time to configure logging. Fortunately, PostgreSQL provides us with an easy way to work with log files and helps people set up a good configuration easily.

Collecting logs is important because it can point to errors and potential database problems. In this section, you will learn how to configure logging properly.

The postgresql.conf file contains all the parameters you need so that you’re provided with all the necessary information.

Configuring the postgresql.conf file

In this section, we will go through some of the most important entries in the postgresql.conf file that we can use in order to configure logging and see how logging can be used in the most beneficial way.

Before we get started, I want to say a few words about logging in PostgreSQL in general. On Unix systems, PostgreSQL will send log information to stderr by default. However, stderr...

Summary

This chapter was all about system statistics. You learned how to extract information from PostgreSQL and how to use system statistics in a beneficial way. Without proper monitoring, achieving good and reliable operations is close to impossible. It is important to keep an eye on runtime parameters and database vitals to avoid trouble. This chapter has taught you a lot about PostgreSQL monitoring, which can be used to optimize your database.

The most important views were discussed in detail. Chapter 6, Optimizing Queries for Good Performance, which is the next chapter in this book, is all about query optimization. You will learn how to inspect queries and how they are optimized.

Questions

As in most of the chapters in this book, we will take a look at some of the key questions that will have arisen from what has just been covered:

• What kind of runtime statistics does PostgreSQL gather?
• How can I spot performance problems easily?
• How does PostgreSQL write log files?
• Does logging have an impact on performance?

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