Логирование в PostgreSQL

Зачем вообще нужно логирование в базе данных? Для начала попробуйте ответить себе сами на этот вопрос.

У вас были когда-нибудь ситуации, когда кто-то изменил строку в таблице и на вопрос, почему система стала выдавать странные результаты, разработчики только разводили руками?

А была ли ситуация, когда кто-то случайно удалил таблицу и потом приходилось ее восстанавливать с бэкапа. Вы ведь делаете бэкапы, правда?

А иногда еще хочется посмотреть, кто соединился с базой. Ну и в добавок, когда что-то пойдет не так, логи могут помочь вам разобраться с этой ситуацией.

Надеюсь, каждый понимает насколько важно логирование в системе и почему ему всегда отводится такая большая роль.

Итак, что нужно чтобы включить логирование в postgres?

Да собственно говоря, нужно всего лишь прописать настройку

logging_collector = on

и перезапустить сервер.

После этого в папке $PGDATA появится каталог pg_log, где будут находиться ваши логи.

По-умолчанию, формат лога postgresql-%Y-%m-%d_%H%M%S.log.

Файл как правило создается при запуске базы. После этого все логи будут идти в этот файл, если у вас не настроена ротация. О ротации логов читайте дальше. Я при первом знакомстве с базой думал, что такой формат будет производить запись в лог каждую секунду. Это неверно.

Обратите внимание на следующие параметры:

log_rotation_age = 1d

говорит, что нужно производить ротацию логов спустя 1 день.

log_rotation_size = 10MB

говорит, что нужно производить ротацию логово, как только размер файла превысит 10 Mb.

log_truncate_on_rotation = on

говорит, что нужно перезаписывать логи вместо добавления новых.

К примеру, если хотите хранить логи только недельной давности за каждый день, то можно указать

log_filename = postgres-%a.log
log_truncate_on_rotation = on
log_rotation_age = 1d

В общем случаем вам нужно определиться с тремя вещами:

• куда записывать
• когда записывать
• что записывать.

Первые два пункта кажутся более простыми, чем третий.

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

Можно предположить, что разумным будет начать со следующих событий:

• кто соединился с базой/кто отсоединился
• изменения в базе
• ну и хотелось бы иметь кастомизируемое сообщение

Первый пункт - это настройки

log_connections = on
log_disconnections = on

При этом в логе вы увидите что-то похожее на это:

LOG: connection received: host=::1 port=60722
LOG: connection authorized: user=miholeus database=parsers
LOG: connection received: host=::1 port=60724
LOG: connection authorized: user=miholeus database=neuronetwork

Второй пункт - это настройка:

log_statement = ‘mod'

Вообще имеется несколько различных значений: none (по-умолчанию), ddl, mod, all.

• ddl - логирует изменения схемы базы данных
• mod - тоже, что ddl + операции модификации данных над строками в таблице
• all - тоже, что mod + все select запросы к базе

Третий пункт - это настройка:

log_line_prefix = ‘%t <%d %u %r> %%'

t - это timestamp

d - имя базы данных

u - пользователь

r - удаленный хост и порт

% - символ процента

Параметров на самом деле больше, тут я привел лишь часть из них. Все параметры предваряются символом процента.

При этом в логе будет запись вида:

2016-03-23 21:57:16 MSK <test miholeus ::1(61080)> %LOG:  statement: ALTER TABLE "public"."t" DROP COLUMN "flag";

Итак, еще раз отмечу список настроек для логирования:

log_connections = on
log_disconnections = on
log_statement = ‘mod'
log_line_prefix = ‘%t <%d %u %r> %%'

Теперь поговорим о том, когда нужно логировать.

При наступлении какого-либо события Postgres умеет смотреть на уровень возникшей ошибки и принять действие по логированию, основываясь на этой информации.

Настройки, которые помогут в этом:

log_min_messages = warning
client_min_messages = notice
log_min_error_statement = error

Первая настройка (log_min_messages) говорит о том, какие сообщения должны попасть в лог сервера. Уровни ошибок можно устанавливать следующие:

DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, и PANIC.

Уровень левее включает в себя все ошибки уровня правее, т.е. левый крайний вообще будет все сообщения записывать, крайний справа - только фатальные ошибки.

Вторая настройка (client_min_messages) - говорит о том, какие сообщения могут быть отправлены клиенты. По умолчанию стоит уровень NOTICE.

Третья настройка (log_min_error_statement) - говорит о том, какие ошибочные sql запросы следует записывать в log. По умолчанию стоит уровень ERROR - все ошибочные запросы можно увидеть в логе.

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

log_min_duration_statement = 2000 # 2 секунды

При такой настройке все запросы, чье время выполнения превышает 2 секунды, окажутся в логе.

Есть еще возможность определить куда вести запись в логе:

• eventlog
• csvlog
• syslog
• stderr

Как выбрать то, что вам нужно?

Если вы используете Windows - то вариант для вас - eventlog. Подробнее читайте тут http://www.postgresql.org/docs/9.5/static/event-log-registration.html.

Если вы хотите потом эти логи загрузить куда-то - то лучше, наверно, будет использовать csvlog.

При этом можно создать такую таблицу

CREATE TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  PRIMARY KEY (session_id, session_line_num)
);

И импортировать потом логи таким образом

COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;

Если вы проповедуете идею централизованного логирования - то для вас подойдет syslog.

В противном же случае остается stderr.

В дальнейшем logging_collector, если он включен, цепляет сообщения из stderr и перенаправляет их в лог файлы.

Еще один важный момент - нужно проверять, что пользователь базы данных имеет права на запись в папку с логами. Иначе получите Permission denied и сервер не сможет запуститься.

Со временем логов может скопиться большое количество и нужно будет следить за тем, чтобы они не занимали слишком много места. Старые логи можно переносить на другие сервера, либо удалять, либо делать и то и то.

Для удаления можно пользоваться простой командой:

find ${logdir} -name «postgres.*» -mtime +10 -exec rm -f {} \;

Эта команда удалит все логи, созданные более 10 дней назад.

Каждая из 4х настроек имеет свои плюсы и минусы, и вам решать, что использовать. Я бы не рекомендовал использовать syslog, т.к. будут серьезные проблемы с производительностью.

Я обычно ставлю stderr, настраиваю ротацию и сбор/копирование логов на удаленный сервер.

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

log_checkpoints = on

записывает чекпоинты в логи, количество записанных буферов и время, которое потребовалось для этого.

log_autovacuum_min_duration = 250

Все действия автовакуума, которые заняли боле 250 ms, будут записаны в лог.

log_error_verbosity

указывает насколько детальной будет информация в логе.

Можно устанавливать уровни - TERSE, DEFAULT, VERBOSE.

Посмотреть свои текущие настройки можно командой:

select name, setting, short_desc from pg_settings where category like 'Reporting and Logging%';

Теперь, когда у вас есть логи, что делать дальше?

Хотелось бы иметь какую-то отчетность, правда? И желательно, что это происходило в автоматическом режиме.

Тут есть куча разных вариантов: от написания каких-то своих скриптов на основе утилит командной строки до автоматизированных систем.

Из общих систем можно рассмотреть logstash. Из специфичных для postgres - pgfouine (вроде как умер давно), pgbadger.

Их я постараюсь рассмотреть в следующих статьях.

Ну, и не забываем про csvlog настройку. Там практически из коробки получаем готовую систему, останется написать пару скриптов для построения красивых графиков на javascript.