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

postgresql logsЗачем вообще нужно логирование в базе данных? Для начала попробуйте ответить себе сами на этот вопрос.
У вас были когда-нибудь ситуации, когда кто-то изменил строку в таблице и на вопрос, почему система стала выдавать странные результаты, разработчики только разводили руками?
А была ли ситуация, когда кто-то случайно удалил таблицу и потом приходилось ее восстанавливать с бэкапа. Вы ведь делаете бэкапы, правда?
А иногда еще хочется посмотреть, кто соединился с базой. Ну и в добавок, когда что-то пойдет не так, логи могут помочь вам разобраться с этой ситуацией.

Надеюсь, каждый понимает насколько важно логирование в системе и почему ему всегда отводится такая большая роль.
Итак, что нужно чтобы включить логирование в 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.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

:D :) ^_^ :( :o 8) ;-( :lol: xD :wink: :evil: :p :whistle: :woot: :sleep: =] :sick: :straight: :ninja: :love: :kiss: :angel: :bandit: :alien: