PostgreSQL logs are text files that give us all the information about what events are occurring in our database system. These valuable resources include who has access to which component, what queries are in progress, what settings have changed, troubleshooting problems, tracking performance, and their issues, and tracing the database activity.

This write-up will demonstrate how to log queries in Postgres.

How to Find the Location of Logs in PostgreSQL?

If we want our PostgreSQL to start making our log files, we must enable the logging_collector parameter. By doing so, your logs will start going into a default location specified by your OS. There are the default directories of some of the most commonly used operating systems:

● Windows: C:\Program Files\PostgreSQL\15\data\pg_log

● Debian-based system: /var/log/postgresql/postgresql-x.x.main.log. X.x.

● Red Hat-based system: /var/lib/pgsql/data/pg_log

We can also change the location directory of where log files are to be saved. This is done by enabling the log collector. We can specify our new custom directory, in which we want to save the log file, in the log_directory parameter.

How to Log Queries in PostgreSQL?

It is easy to enable logging temporarily in PostgreSQL by making some changes in the configuration settings and then restarting the server. But we will look at how to log files permanently. Below are the steps to log queries in PostgreSQL:

Step 1: Find Configuration Files

If we need to know where PostgreSQL.config is located, we have to connect it to the Postgres client(pgsql) for which the “SHOW config_file” statement is used.

SHOW config_file

This will give the location of the config file. In my case, it is:

Step 2: Locate the Data Directory Path

Now we have to find the path of the data directory. We will use the SHOW statement again:

SHOW data_directory

Executing the “SHOW” command will retrieve the data directory’s path. In our case, this retrieved path is:

It is possible that the configuration and the data directory are located on the same path as in my case. However, it might be different in some cases.

Step 3: Configure the Postgres to Generate Output Log

Now open the config file(the location of which you have traced earlier) with the text editor of your choice. Scroll down to the “REPORTING AND LOGGING” part. The most important sections in this part are log_destination and logging_collector. Given below are the recommended settings that are to be done if not present already:

In my case, it was, log_destination = 'stderr', in which I replaced ‘stderr’ with ‘csvlog’. Doing these settings will basically reflect that we are instructing Postgres to generate/create the logs in CSV format and show them to the log folder.

Step 4: Restart the Postgres Service

Now we have to restart the Postgres service, to reflect the changes on it. Performing the restart may differ from OS to OS. Since I am using Windows the way I did it is by ‘Services Manager’. For this purpose, search and open the Services from the taskbar and follow the screenshot attached below:

Note: To learn more ways to restart your Posgres service head over to the following Postgres guide.

Step 5: Check the Log Generation

Now that we have restarted the system, we will have to check the log generation because the protocol has to start immediately. To check this we have to scroll to the data directory/log of the Postgres installation. We have already found the path to the data directory earlier now just add ‘\log’ to navigate to the log directory. We have done this because we reflected the logs output to the “log” in step no 3.

C:\Program Files\PostgreSQL\15\data\log

You can clearly see that the log files in CSV format have been created in the directory.

So this is how queries are logged in PostgreSQL.

Conclusion

To log queries in Postgres, first, locate the config file, then locate the data directory path. After that, configure the configurations and direct the output to the log file. Finally, restart the Postgres service to check if the log files have been generated yet or not. In this article, we have seen a procedure to log queries in PostgreSQL.