We can also use these logs to identify the potential performance bottlenecks and troubleshooting, especially when using an ORM.
In this guide, we will cover the process of configuring the PostgreSQL server to log the queries to a given file.
Step 1: Edit the PostgreSQL Configuration File
The first step is to locate and edit the PostgreSQL configuration file. The PostgreSQL configuration is called “postgresql.conf’.
The exact location of this file depends on your target system, the installed PostgreSQL version, and the installation method.
Some familiar places you can check for the location of the PostgreSQL configuration file include the following:
Linux
- Debian/Ubuntu: /etc/postgresql/<version>/main/postgresql.conf
- Red Hat/CentOS: /var/lib/pgsql/<version>/data/postgresql.conf
- Arch Linux: /var/lib/postgres/data/postgresql.conf
MacOS (Homebrew Installation)
- /usr/local/var/postgres/postgresql.conf
Windows
- Binary Installer: C:\Program Files\PostgreSQL\<version>\data\postgresql.conf
- Stack Builder: C:\Program Files (x86)\PostgreSQL\<version>\data\postgresql.conf
NOTE: Replace the <version> placeholder with the specific version of the PostgreSQL server that is installed on your system.
Once you locate the configuration file for your PostgreSQL installation, open the file with your text editor. In some cases, you may need administrative permissions to make changes to the configuration file.
Step 2: Enable the Query Logging
We need to allow the logging_collector to be in the configuration file to enable the query logging.
Locate the entry that starts with “#logging_collector” and remove the “#” symbol at the beginning of the line to uncomment the line.
Next, change the value of the logging_collector from “off” to “on”.
Then, locate the entry that defines the path to the PostgreSQL log directory as #log_directory = ‘log’ and remove the “#” symbol to uncomment the entry.
Optionally, you can change the “log” directory to a different path on the filesystem. Ensure that the specified directory is writable by the Postgres user.
Once completed, save the changes to the file.
Step 3: Specify the Log Format
The next step is to configure the log format. Although this step is not required, it is beneficial as it allows the logs to be more readable and customized to your needs.
By default, PostgreSQL logs the queries in plain text format. However, if you wish to change the log format, you can edit the log_line_prefix entry in the configuration.
In the configuration file, uncomment the line #Line_line_prefix entry by removing the preceding “#” symbol.
Next, modify the value inside the single quotes to specify your desired log format. PostgreSQL supports the following variables:
%u = user name
%d = database name
%r = remote host and port
%h = remote host
%b = backend type
%p = process ID
%t = timestamp without milliseconds
%m = timestamp with milliseconds
%n = timestamp with milliseconds (as a Unix epoch)
%i = command tag
%e = SQL state
%c = session ID
%l = session line number
%s = session start timestamp
%v = virtual transaction ID
%x = transaction ID (0 if none)
%q = stop here in non-session processes
Once satisfied with your log format, you can save the changes to the configuration file.
Once you configured the query logging, the logging directory, and the logging format, you can restart the PostgreSQL server to apply the changes.
Step 4: Verify the Query Logging
Once you restart the PostgreSQL server, you can verify that the query logs are written to the specified file by navigating to the log directory which is defined in the configuration file.
Locate the file with a name that is similar to postgresql-<date>.log.
You can open the files with a text editor or process them with a log processor such as Elasticsearch and more.
Conclusion
You successfully enabled the query logging in PostgreSQL. You can now monitor and analyze the logged queries to gain insights into your database activity.
