How to Log All PostgreSQL Queries

1. Overview of PostgreSQL Queries
2. Locate the Configuration File
3. Locate Data Directory Path
4. Configure PostgreSQL & Generate Log Output
5. Restart Your PostgreSQL
6. Verify the Log Generation

PostgreSQL is popular in the market for its robustness, speed, reliability, and high performance. In addition, with PostgreSQL, logging has been made relatively easy by altering just a few configuration settings.

These settings can be changed in memory, temporarily enabling logging for only that specific client session. However, Postgres can log all queries to work permanently.

This article will teach us how to log all PostgreSQL queries and update configuration files.

Overview of PostgreSQL Queries

PostgreSQL is famous for being a highly stable database application accessible for its users to use, modify and implement whatever they require. As a result, it is the primary database for many analytical and web applications worldwide.

SQL is a structured query language that stores and manipulates data in databases. SQL queries help perform various data operations in relational database management systems everywhere.

Through this powerful language, we can perform several operations that range from executing complex queries to fetching data and inserting, updating, and deleting records in a database.

It also allows you to create new objects in a database and set permission tables, views, procedures, functions, etc.

As for the Postgres log all queries functionality, you need to enable logging within PostgreSQL by altering the configuration settings and restarting the server.

Locate the Configuration File

If you cannot locate the postgresql.conf configuration file, a simple method is available to find its location. First, connect to the postures client and execute the following command.

postgres=# SHOW config_file;

After executing the command, we get the path to the postgresql.conf file. Proceed to open the file with a text editor (we are using nano) using the following command and start changing the settings.

$ nano /etc/postgresql/version/main/postgresql.conf

Locate Data Directory Path

Make sure you confirm the path of your Postgres installation data directory. Then, retrieve the data directory like you retrieved the configuration file.

postgres=# SHOW data_directory;

It will provide you with a path for the data directory. However, the directory and configuration files are in the same installation path. Therefore, copy the data directory and configuration directory for later use.

Configure PostgreSQL & Generate Log Output

Open the postgresql.conf file and scroll down to the section that says ERROR REPORTING AND LOGGING. Here you’re likely to see several configuration settings commented.

Among these settings, some are critical: logging_collector and log_destination. In addition, there are some recommended settings you should set.

However, you can alter them to cater to your own needs. The recommended settings are as follows:

• log_destination = 'csvlog'

• logging_collector =on

• log-directory = 'pg_log'

• log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

• log_min_error_statement = error

• set log_min_duration_statement= 0 as a parameter to log every statement

• This step is optional. Run the following code:

  SELECT set.config('log_statement','all, true );
  

After setting the values to the variables as instructed above, reload the PostgreSQL configuration. It can be done by the following:

/usr/pgsqlversion/bin/pg_ctl reload -D /var/lib/pgsql/version/data/

With these settings, we have uncommented the log_filename setting to produce timestamps and a proper name for the log files. We are also getting Postgres to generate logs in the CSV format, outputting them to the pg_log directory within the data directory.

In your data/postgresql.conf file, change the setting log_statement to 'all'. Once you’re done doing this, there are a few other settings to verify.

Ensure your log_destination, logging_collector, and variables are turned on. You need to ensure the log_directory exists inside the data directory and that the Postgres user can write to it.

Restart Your PostgreSQL

The final step to Postgres logs all queries is to restart the PostgreSQL service. It is crucial that when you continue, the settings in the configuration file you have altered can take effect.

However, a Postgres restart may be different in various systems. Typically, a Unix system has a command that does the job. The command looks something like this:

$ service postgresql restart

Verify the Log Generation

Once you have restarted the Postgres system, your changes’ effects should occur. After the restart, logging should begin immediately. To ensure this happens, proceed to the data/pg_log directory of your specific Postgres installation.

A few steps ago, we asked you to save the data directory path; this is where you put it to use. Next, navigate to that directory by adding /pg_log at the end to get to the log directory.

$ cs /var/lib/postgresql/version/main/pg_log

Proceed to list the files.

$ ls -l

A log file will be created following the previous Postgres service restart. These automatically generated log files are made for Postgres to log all queries by changing a few configuration settings.

Over time, log files tend to grow a lot and might ultimately kill your machine. To prevent this from happening, write a bash script that will delete logs and restart the postgresql server for your safety.

Another approach when you don’t want to write a bash script but want the log files to overwrite monthly, perform a simple procedure to do so: 'log_filename='postgresql-%d.log'.

It will not overwrite after every restart but append each day and overwrite every month. All of the above will log solutions from the default database of postgreSQL.

To proceed to log others as well, you need to start with their solution and then run the following:

ALTER DATABASE databasename
SET log_statement = 'all';

While navigating through the Postgres log, all queries process may seem confusing; this article is designed to make things easier. We have provided a step-by-step process so you can be well on your way to logging queries in PostgreSQL.