PostgreSQL how to enable database logging - logging_collector

Colleagues hello to all.

In today's article, we'll talk about how to enable logging in a PostgreSQL database. A background process is responsible for logging in Postgresql, which collects various information about the database robot and writes this information somewhere. The information can be completely different, various errors, attempts to authorize clients, etc. After installing the DBMS and creating the database itself, the logging process in the database is turned off by default.

Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR

Article content:

1. Check if logging is enabled.
2. Create a directory for the log logs.
3. Enable logging.
4. Check.

1. Checking whether logging is enabled.

In order to check whether logging is enabled in the database, you can use two queries.

sql> SELECT name, setting FROM pg_settings WHERE name = 'logging_collector';

sql> SHOW logging_collector;

As you can see in the screenshots, the value of the  logging_collector variable is off, which means that logging is not carried out in the database.

2. Create a log directory.

Create a directory for log files near the database itself. I have my database in  /app/postgresql/pgdatabase/data and I will create a directory for the log files here called log.

$. mkdir -p /app/postgresql/pgdatabase/data/log

Be sure to create a directory under the postgres account.

3. Enable logging.

In order for us to enable logging, we need to change the values ​​of the logging_collector variable in the main database configuration file called postgresql.conf.

Find the  logging_collector variable and change the values ​​from off to on.

Find the  log_directory variable and set the values '/app/postgresql/pgdatabase/data/log'. The variable is responsible for where we will store all the log files.

Find the  log_filename variable and set the values ​​to 'postgresql-%Y-%m-%d.log'. The variable is responsible for how we will name the log files.

logging_collector = on
log_directory = '/app/postgresql/pgdatabase/data/log'
log_filename = 'postgresql-%Y-%m-%d.log'

Be sure to restart the database.

4. Check.

Now let's check everything, execute requests.

sql> SELECT name, setting FROM pg_settings WHERE name = 'logging_collector';

sql> SHOW logging_collector;

Logging enabled.

You can also check which processes are running associated with the PostgreSQL database.

Logger and stats collector processes are responsible for logging. If logging is not enabled, then these processes will not happen.

After enabling logging, the first log file appeared in our directory.

Thank you all, I hope that my article helped you in some way.