Postgresql logs linux where

Where are PostgreSQL logs files?

Posted by: Mohammed Semari | Published: September 6, 2016| Updated: December 10, 2016

When debugging a problem, it’s always frustrating to get sidetracked hunting down the relevant logs. PostgreSQL users can select any of several different ways to handle database logs, or even choose a combination. But especially for new users, or those getting used to an unfamiliar system, just finding the logs can be difficult. To ease that pain, here’s a key to help dig up the correct logs.

Where are log entries sent?

First, connect to PostgreSQL with psql, pgadmin, or some other client that lets you run SQL queries, and run this:

The log_destination setting tells PostgreSQL where log entries should go. In most cases it will be one of four values, though it can also be a comma-separated list of any of those four values. We’ll discuss each in turn.

SYSLOG

Syslog is a complex beast, and if your logs are going here, you’ll want more than this blog post to help you. Different systems have different syslog daemons, those daemons have different capabilities and require different configurations, and we simply can’t cover them all here. Your syslog may be configured to send PostgreSQL logs anywhere on the system, or even to an external server. For your purposes, though, you’ll need to know what “ident” and “facility” you’re using. These values tag each syslog message coming from PostgreSQL, and allow the syslog daemon to sort out where the message should go. You can find them like this:

Syslog is often useful, in that it allows administrators to collect logs from many applications into one place, to relieve the database server of logging I/O overhead (which may or may not actually help anything), or any number of other interesting rearrangements of log data.

EVENTLOG

For PostgreSQL systems running on Windows, you can send log entries to the Windows event log. You’ll want to tell Windows to expect the log values, and what “event source” they’ll come from. You can find instructions for this operation in the PostgreSQL documentation discussing server setup.

STDERR

This is probably the most common log destination (it’s the default, after all) and can get fairly complicated in itself. Selecting “stderr” instructs PostgreSQL to send log data to the “stderr” (short for “standard error”) output pipe most operating systems give every new process by default. The difficulty is that PostgreSQL or the applications that launch it can then redirect this pipe to all kinds of different places. If you start PostgreSQL manually with no particular redirection in place, log entries will be written to your terminal:

In these logs you’ll see the logs from me starting the database, connecting to it from some other terminal, and issuing the obviously erroneous command “select syntax error”. But there are several ways to redirect this elsewhere. The easiest is with pg_ctl’s -l option, which essentially redirects stderr to a file, in which case the startup looks like this:

Finally, you can also tell PostgreSQL to redirect its stderr output internally, with the logging_collector option (which older versions of PostgreSQL named “redirect_stderr”). This can be on or off, and when on, collects stderr output into a configured log directory.

So if you end see a log_destination set to “stderr”, a good next step is to check logging_collector:

In this system, logging_collector is turned on, which means we have to find out where it’s collecting logs. First, check log_directory. In my case, below, it’s an absolute path, but by default it’s the relative path “pg_log”. This is relative to the PostgreSQL data directory. Log files are named according to a pattern in log_filename. Each of these settings is shown below:

Documentation for each of these options, along with settings governing log rotation, is available here.

If logging_collector is turned off, you can still find the logs using the /proc filesystem, on operating systems equipped with one. First you’ll need to find the process ID of a PostgreSQL process, which is simple enough:

Then, check /proc/YOUR_PID_HERE/fd/2, which is a symlink to the log destination:

CSVLOG

The “csvlog” mode creates logs in CSV format, designed to be easily machine-readable. In fact, this section of the PostgreSQL documentation even provides a handy table definition if you want to slurp the logs into your database. CSV logs are produced in a fixed format the administrator cannot change, but it includes fields for everything available in the other log formats. For these to work, you need to have logging_collector turned on; without logging_collector, the logs simply won’t show up anywhere. But when configured correctly, PostgreSQL will create CSV format logs in the log_directory, with file names mostly following the log_filename pattern. Here’s my example database, with log_destination set to “stderr, csvlog” and logging_collector turned on, just after I start the database and issue one query:

Источник

Sysadminium

База знаний системного администратора

Журнал PostgreSQL. Настройка и анализ

В этой статье разберём журнал PostgreSQL, а именно как его настраивать, что в него можно записывать и как его анализировать.

Если помните при запуске PostgreSQL, мы указали файл журнала: pg_ctl start -l /home/postgres/logfile.

В этот журнал PostgreSQL записывает некоторые из своих действий. Настраивая журналирование мы можем задать:

• какие действия заносить в журнал;
• насколько подробно описывать эти действия;
• сколько будут хранится файлы журнала и как переключаться на другие файлы.

Если сейчас заглянуть в журнал и посмотреть последние строчки в которых есть слово FATAL, то можно увидеть следующее:

FATAL: terminating connection due to administrator command – это завершение процесса с помощью функции pg_terminate_backend(pid). Это мы проделывали на прошлом уроке.

За настройку журнала отвечают следующие опции:

• log_destination = можем указать один, или через запятую несколько приёмников:
  • stderr – поток ошибок (в обычном текстовом виде)
  • csvlog – формат CSV (только с коллектором)
  • syslog – писать ошибки в syslog (только для Linux)
  • eventlog – писать ошибки журнал событий Windows (только для Windows)
• logging_collector = (on или off). При включении позволяет собирать дополнительную информацию и никогда не теряет сообщения, в отличие от syslog. При этом запись можно вести в stderr или csvlog. Может стать узким местом, так как после каждого действия записывается журнальная запись и только потом совершается следующее действие.
• log_directory и log_filename – каталог и файл журнала. Следует указывать только если log_destination = stderr.

Что можем записывать в журнал?

В журнал может быть записано много полезной информации. Но по умолчанию почти весь вывод выключен, чтобы снизить нагрузку на диски. Вот что может писаться в журнал сообщений сервера:

• log_min_messages – минимальный уровень логирования. Допустимые значения: DEBUG5 – DEBUG1, INFO, NOTICE, WARNINF, ERROR, LOG, FATAL, PANIC. Каждый из перечисленных уровней включает все идущие после него. По умолчанию используется WARNINF;
• log_min_duration_statement – время в миллисекундах. Если команда выполнялась дольше этого времени, то по ней будет запись в журнале. Если установить равное нулю, то абсолютно все команды буду записаны в журнал;
• log_duration – (on или off) записывать время выполнения команд;
• application_name – (on или off) записывать имя приложения;
• log_checkpoints – (on или off) записывать информацию по контрольным точкам;
• log_(dis)connections – (on или off) записывать подключения к серверу и отключения от него;
• log_lock_waits – (on или off) записывать, если сеанс ожидает блокировку дольше, чем указано в deadlock_timeout;
• log_statement – (none, ddl, mod, all) записывать текст выполняемых команд:
  • none – отключено;
  • ddl – CREATE, ALTER, DROP;
  • mod – dll + INSERT, UPDATE, DELETE, TRUNCATE, COPY;
  • all – все команды (кроме команд с синтаксическими ошибками);
• log_temp_files – использование временных файлов. Если процессу не хватило оперативной памяти то он создаст временный файл на диске и будет его использовать. Если это случается то, например можно увеличить параметр workmem.
• и другое.

Ротация жарналов

Если писать все в 1 файл, то постепенно он вырастит до огромных размеров. Поэтому лучше использовать некоторую схему ротации. Следующими параметрами можно настроить ротацию, если мы используем log_destination=stderr:

• log_filename – может принять не просто имя файла, а маску имени. С помощью специальных символов можно указать текущее время;
• log_rotation_age – задает время переключения на следующий файл в минутах;
• log_rotation_size – задает размер файла, при котором нужно переключиться на следующий файл;
• log_truncate_on_rotation – если включить (on) то вы разрешите серверу перезаписывать уже существующие файлы. Если выключить (off) – то файл не будет перезаписываться, записи будут писаться в конец файла.

Комбинируя все выше перечисленное, можно настроить некоторую схему ротации. Например:

• log_filename = postgres-%H.log / log_rotation_age = 1h – 24 файла в сутки;
• log_filename = postgres-%a.log / log_rotation_age = 1d – 7 файлов в неделю.

Анализ журнала

Анализировать журнал можно средствами операционной системы, например: grep, awk и подобными. А также можно использовать pgBadger – это анализатор лога PostgreSQL, но он требует определённых настроек журнала.

Практика

Настроим журнал, чтобы он захватывал все запросы (log_min_duration_statement=0). Дополнительно в качестве префикса укажем “pid=%p“. Затем заставим сервер перечитать конфигурацию и выполним некоторый запрос (SELECT sum(random()) FROM generate_series(1,1000000)). Далее отключимся от сервера и посмотрим журнал:

Как видите, теперь в логах есть информация обо всех командах и время, которое они выполнялись!

Более подробно про настройку журнала можете почитать здесь.

Источник

How To Start Logging With PostgreSQL

By Juraj Holub
Last Validated on May 18 2021 · Originally Published on May 18, 2021 · Viewed 1.1k times

Introduction

This tutorial shows you how to configure and view different PostgreSQL logs. PostgreSQL is an open-source relational database based on SQL (Structured Query Language). PostgreSQL offers a complex logging daemon called logging collector. In general, the database is the basis of almost every backend, and administrators want to log this service.

In this tutorial, you will do the following:

• You will install the PostgreSQL server and view syslog records related to this service. Next, you will view the database custom log.
• You will connect to the PostgreSQL server and view metadata about the logging collector. You will enable this daemon.
• You will understand the most important PostgreSQL logging configuration setting. You will view, edit and reload server configuration.
• You will simulate some slow query and check this incident in the new log.

Prerequisites

• Ubuntu 20.04 distribution including the non-root user with sudo access.
• Basic knowledge of SQL languages (understanding of simple select query statement).
• Understanding of systemd and systemctl. All basics are covered in our How to Control Systemd with Systemctl tutorial.
• You should know the principle of rotation logs. You can consult our How to Control Journald with Journalctl tutorial.

Step 1 — Installing Server and Viewing Syslog Records

The PostgreSQL server is maintained by the command-line program psql . This program access the interactive terminal for accessing the database. The process of starting, running or stopping the PostgreSQL server is logged into syslog. These syslog records don’t include any information about SQL queries. It is useful for the analysis of the server.

First of all, let’s install the PostgreSQL server. Ubuntu 20.04 allows to install the PostgreSQL from default packages with the apt install (installation requires sudo privilege):

The first command will update Ubuntu repositories, and the second will download and install required packages for the PostgreSQL server.

Now, the server is installed and started. The process of server starting is recoded in syslog. You can view all syslog records related to the PostgreSQL with journalctl :

The option -u defines to show only syslog records related to service postgresql . You’ll see the program’s output appear on the screen:

The output shows the records about the first server start.

Step 2 — Viewing the Custom Server Log

Except for syslog records, PostgreSQL maintains its own log. This log includes much more detailed information than general syslog records, and it is widely adjustable. The log is stored in the default log directory for Linux systems ( /var/log ).

If you installed the PostgreSQL server, you can list the directory /var/log and find a new subdirectory postgresql with ls :

You’ll see the program’s output appear on the screen:

The output shows also directory postgresql . This directory contains by default single log postgresql-12-main.log . Let’s view the content of this file with cat :

You’ll see the program’s output appear on the screen:

The output shows that the file stores plain text records about the PostgreSQL server initialisation, and running. You can see that these records are much more detailed than the syslog records.

Step 3 — Connecting to Server and Checking Log Collector

By default, the PostgreSQL logs are maintained by the syslog daemon. However, the database includes a dedicated logging collector (daemon independent of syslog) that offers a more advanced log configuration specialized for logging the database.

First of all, let’s connect to the PostgreSQL server and check the logging configuration. You can connect to the PostgreSQL server as a user postgres (this user account is created by default within installation):

The command requires sudo because you are changing the user role. You will be redirected to PostgreSQL interactive terminal. Now, you can view system variables related to the logging configuration.

You can view the status of the log collector by executing the command show :

The command show displays the value of the system variable logging_collector . You will see the following output:

As you can see, the PostgreSQL log collector is by default disabled. We will enable it in the next step. Now, let’s disconnect from the server by executing the exit command:

You will be redirected back to the terminal.

Step 4 — Enabling the PostgreSQL Log Collector

The PostgreSQL server includes various system variables that specify the configuration of logging. All these variables are stored in the configuration file postgresql.conf . This file is by default stored in the directory /etc/postgresql/12/main/ . The following list explains the meaning of the most important server log variables:

• logging_collector : We already know this variable from the previous step. However, for completeness, it is included in this list because it is one of the most important log configuration settings.
• log_destination : Sets the destination for server log output.
• log_directory : It determines the directory in which log files will be created.
• log_filename : It sets the file names of the created log files.
• log_line_prefix : Each log record includes, besides the message itself, a header prefix with important metadata (for example, timestamp, user, process id, and others). You can specify the header fields in this variable.
• log_hostname : If this variable is disabled then the log will record only the IP address of clients. If it is enabled then the log will map the IP address to hostname. However, you should keep in mind that DNS translation cost resources.
• log_timezone : The variable holds geographical location. It converts the timestamp into the relevant local format.
• log_connections : If you enable this variable then the log will record all authorized connections, or attempts to the server. It could be beneficial for security auditing, but it could be also a heavy load for the server if you have thousands of clients.
• log_disconnections : This variable is complementary to the previous one. By enabling it, you set up to log all authorised disconnection. Typically, you want to enable only one of these two variables.
• log_statement : The variable determines which SQL statement will be logged.
• log_duration : It is the boolean variable. If it is enabled then all SQL statements will be recorded together with their duration. This setting could decrease database performance. However, it could be beneficial for determining slow queries.
• log_min_duration_statement : The variable is extension of previous setting. It specifies the minimal duration of SQL statement in a millisecond that will be logged.
• log_rotation_age : The integer value determines the maximal time period of minutes until log rotation.
• log_rotation_size : The value set the maximal size of the log file in kilobytes. If the log reaches this value, it will be rotated.

Each of these variables can be viewed through the psql terminal. If you want to view them, you can follow the previous step, where we already view the variable logging_collector . For further information about configuration variables see the official documentation.

Enabling Log Collector

You can enable log collector daemon by editing the postgresql.conf ( sudo required):

The file contains the following lines that hold configuration variables logging_collector and log_destination (by default commented out):

Uncomment both variables, set logging_collector to on and log_destination to stderr :

Now, you can save the file. You set up log destination to stderr because log collector read input from there. The configuration is now changed but the log daemon is not activated yet. If you want immediately apply the new configuration rules then you must restart the PostgreSQL server with systemctl ( sudo required):

Now, the PostgreSQL server reloads the configuration and enables a log collector. If you want to change any variable in the file postgresql.conf and immediately apply changes, you must restart the service.

Step 5 — Configuring Log Collector

Now, you will set up the variables described in the previous step. Keep in mind that each organisation has unique logging requirements. This tutorial shows you the possible setup, but you should configure values that match your use case. All these variables are stored in the file /etc/postgresql/12/main/postgresql.conf . If you want to change any of these variables, then edit this file and restart the PostgreSQL server as we did in the previous step.

Configuring Log Name, Directory and Rotation

The naming of logs becomes important if you manage logs from multiple services and servers. The log files created by the log collector are named by the regular expression determined in the variable log_filename . The name could include a constant string but also a formatted timestamp. The default log name is postgresql-%Y-%m-%d_%H%M%S.log . The pattern %Y-%m-%d_%H%M%S determines formatted timestamp:

• %Y : The year as a decimal number including the century.
• %m : The month as a decimal number (range 01 to 12).
• %d : The day of the month as a decimal number (range 01 to 31).
• %H : The hour as a decimal number using a 24-hour clock (range 00 to 23).
• %M : The minute as a decimal number (range 00 to 59).
• %S : The second as a decimal number (range 00 to 60).

The created file could be named, for example, as postgresql-2021-01-01_23:59:59:59.log .

The file-system directory of the log is determined by the variable log_directory . You should keep in mind that Linux typically stores all logs into the /var/log/ directory.

The log collector allows configuring log rotation. It is the same log rotation principle as the syslog logrotate but this rotation is maintained by PostgreSQL log controller daemon instead of syslog. If you do not know what is log rotation, you can read How to Manage Logs with Logrotate on Ubuntu 20.04. The log rotation is configured by following two values in the postgresql.conf :

• log_rotation_age : If the value is set to 0 then the log rotation is disabled. The default value is 1 day, but this value depends on your use case. The integer without units refers to the number of seconds.
• log_rotation_size : If the value is set to 0 then the log rotation is disabled, otherwise the automatic log file rotation will occur after a specified number of kilobytes.

You can view all these variables in the postgresql.conf :

The file contains the following lines that hold described configuration variables (by default commented out):

Now, you can close the file. You can potentially edit these values, but in such a case you need sudo access.

Configuring Log Structure

You can configure the structure of each log record by various configuration variables. Firstly, let’s set up a record header (information prefixed to each log line). The record prefix structure is determined by the variable log_line_prefix , which holds the printf style string. The following list shows the most important escape characters:

• %t : Timestamp without milliseconds ( %m is with miliseconds). If you want to configure timestamp format to a specific local time then you can set up variable log_timezone to chosen geographical location. For example America/New_York , Europe/Paris , or any other name from the IANA timezone database.
• %p : Process ID.
• %q : If it is non-session process then stop record at this point.
• %d : Name of database.
• %u : User name.
• %h : Remote hostname or IP address. By default, the IP address is recorded. You can set up DNS translation to hostname by enabling variable log_hostname to value on . However, this setting is usually too expensive because it might impose a non-negligible performance penalty.
• %a : Application name.
• %l : Numbering the records in each session (every session start from number 1).

The log_line_prefix with value ‘%t [%p] %q%d@%u, %h, %a, %l ‘ will hold, for example, following log record:

Once again, you can view all these variables in the postgresql.conf :

The file contains the following lines that hold described configuration variables:

As you can see, the DNS translation to the hostname is by default disabled, the default log line prefix record timestamp with milliseconds, process, user and IP address, and the timezone are set to geographical location preset from OS.

Configuring Log Collector to Record Selected SQL Commands

You can configure which type of action will be logged with the log collector. There are two boolean variables that enable logging of the following database actions:

• Logs each attempt, or successful connection to the database. This is by default disabled. You can enable it by setting the variable log_connections to on .
• Logs the duration of each completed SQL statement. By default it is disabled. You can enable it by setting the variable log_duration to the value on . If you want to log only slow queries then you can set the minimum execution time above which all statements will be logged. The variable log_min_duration_statement holds the minimal value as an integer in milliseconds.

Within the log_connections variable, there is also a log_disconnections variable that logs successful disconnections from the database. A database usually logs a large number of connection attempts, soo you want to enable just one of them to save resources.

At last, you can set up which SQL statements are logged. This setting determines variable log_statement , which can hold one of the following four values:

• none : The SQL statements logging is disabled.
• ddl : The log collector will log all data definition statements ( CREATE , ALTER , and DROP ).
• mod : Same as the ddl plus data-modifying statements ( UPDATE , INSERT , DELETE and others).
• all : All SQL statements are recorded.

Once again, you can view all these values in the postgresql.conf :

The file contains the following lines that hold described configuration variables:

As you can see, by default, all these database actions are not logged.

Step 6 — Viewing Collector Logs

If you set up all described variables in postgresql.conf and restart the server then you can view the content of the new logs.

For demonstration, we will use following postgresql.conf configuration:

Executing SQL Statement

First of all, let’s connect to the PostgreSQL server and execute some SQL statement. You can connect to the PostgreSQL server as a user postgres ( sudo required):

You will be redirected to PostgreSQL interactive terminal. Let’s execute some SQL statement that will be logged:

The command select call function pg_sleep() that fall asleep for 1 second (our configuration records every statement longer than 250ms).

Now, let’s disconnect from the server by executing the exit command:

You will be redirected back to the terminal.

Viewing Record of Executed SQL Statement in the Log

Now, let’s view the new collector log that holds a record of SQL statement executions. Our configuration specifies log directory to /var/log/postgresql . Let’s list the content of this directory with ls :

You’ll see the program’s output appear on the screen:

The output shows, within the default log file postgresql-12-main.log , a new log postgresql-2021-05-15_115637.log . You can validate that the name of the log match with the configuration string in variable log_filename .

Let’s view the content of this log with a cat (the sudo is required because this file is maintained by the system):

You’ll see the program’s output appear on the screen:

The output shows all records in this log. First records refer to the startup of the server. You can see that all records are in the format as specified in the variable log_line_prefix . You can view the last three records that hold information about the connection to the database through psql as a user postgres and executing command select pg_sleep(1) . The records include also a time of SQL statement execution.

As you can see, the logging collector with this configuration generates a relatively huge amount of records in a short time. You should find the best configuration that matches your use case.

Conclusion

In this tutorial, you installed the PostgreSQL server. You viewed the syslog records related to this service and the database custom log. You viewed the log collector configuration. You understood the meaning of the most important settings in the configuration file. At last, you enabled, configured and viewed a logging collector.

Источник