PostgreSQL how to enable database logging - logging_collector

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';

postgresql_logging

 

sql> SHOW logging_collector;

postgresql_logging

 

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

postgresql_logging

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'

postgresql_logging

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';

postgresql_logging

sql> SHOW logging_collector;

postgresql_logging

Logging enabled.

 

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

postgresql_logging

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.

postgresql_logging


 

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

SIMILAR ARTICLES

PostgreSQL - How to determine which files on disk correspond to tables in a database
PostgreSQL - How to determine which files on disk correspond to tables in a database
POSTGRESQL
Read more
PostgreSQL - How to find out the size of tables in a database
PostgreSQL - How to find out the size of tables in a database
POSTGRESQL
Read more
PostgreSQL - How to check user privileges
PostgreSQL - How to check user privileges
POSTGRESQL
Read more