Зачем вообще нужно логирование в базе данных? Для начала попробуйте ответить себе сами на этот вопрос.
У вас были когда-нибудь ситуации, когда кто-то изменил строку в таблице и на вопрос, почему система стала выдавать странные результаты, разработчики только разводили руками?
А была ли ситуация, когда кто-то случайно удалил таблицу и потом приходилось ее восстанавливать с бэкапа. Вы ведь делаете бэкапы, правда?
А иногда еще хочется посмотреть, кто соединился с базой. Ну и в добавок, когда что-то пойдет не так, логи могут помочь вам разобраться с этой ситуацией.
Надеюсь, каждый понимает насколько важно логирование в системе и почему ему всегда отводится такая большая роль.
Итак, что нужно чтобы включить логирование в 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.
Отличная простая статья
Где прописывать все эти настройки совершенно непонятно. В каком-то файле? В каком-то интерфейсе? logging_collector = on – где писать? Лог C:\Program Files\PostgreSQL\9.6\data\pg_log\postgresql-2017-09-14_000000 нашёл, но он пустой…