badcasedaily1 7 часов назад

Текстовая обработка в PostgreSQL: grep, sed, awk

Простой

6 мин

872

Обзор

Привет, Хабр.

Всё, что пишет PostgreSQL в /var/log/postgresql/, можно разбирать, фильтровать, анализировать и автоматизировать. Не нужно вручную листать гигабайты логов — научимся вытягивать полезные данные и работать с конфигами в пару команд.

Поговорим о:

grep — быстро ищем ошибки, медленные запросы, аномалии
sed — редактируем конфиги PostgreSQL, комментируем, заменяем настройки
awk — превращаем сырые логи в отчёты

grep

grep — это команда для поиска строк в файлах по шаблону. Она быстро находит нужные строки в логах, конфигурациях и других текстовых файлах.

Базовый синтаксис:

grep "шаблон" файл

Если шаблон найден, grep выведет все совпадения.

Когда PostgreSQL пишет ошибки, они обычно содержат слова ERROR, FATAL или PANIC. Найдём их:

grep "ERROR" /var/log/postgresql/postgresql.log

Это покажет все строки с ошибками.

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

grep -B3 -A3 "ERROR" /var/log/postgresql/postgresql.log

Здесь:

‑B3 — захватить 3 строки перед ошибкой
‑A3 — захватить 3 строки после ошибки

Бывают ошибки разного уровня: PANIC, FATAL, ERROR. Найдём их сразу все:

grep -E "PANIC|FATAL|ERROR" /var/log/postgresql/postgresql.log

Флаг ‑E включает расширенные регулярки, а | значит «или».

Если в postgresql.conf включен log_min_duration_statement, PostgreSQL пишет в логах запросы вида:

LOG: duration: 2500 ms statement: SELECT * FROM users WHERE active = false;

Чтобы найти все запросы, выполнявшиеся дольше 2 секунд:

grep "duration: [2-9][0-9][0-9][0-9] ms" /var/log/postgresql/postgresql.log

А если ищем запросы дольше 5 секунд:

grep -E "duration: ([5-9][0-9]{3}|[1-9][0-9]{4,}) ms" /var/log/postgresql/postgresql.log

Теперь видим только самые тяжёлые запросы.

sed

sed — это инструмент поточного редактирования текста. Он позволяет:

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

Во многих конфигах параметры закомментированы (#). Сделаем их активными:

sed -i 's/^#//' /etc/postgresql/14/main/postgresql.conf

Теперь все настройки активны.

sed позволяет изменять параметры:

sed -i 's/^work_mem = .*$/work_mem = 128MB/' /etc/postgresql/14/main/postgresql.conf

Теперь PostgreSQL использует 128MB на сортировки и хеш‑джоины, а не 4MB, как обычно по дефолту.

Чтобы сделать конфиг читаемее, можно убрать пустые строки:

sed -i '/^$/d' /etc/postgresql/14/main/postgresql.conf

Иногда в postgresql.conf могут быть заданы ненужные параметры. Например, удалим все строки, где log_statement =:

sed -i '/^log_statement =/d' /etc/postgresql/14/main/postgresql.conf

Эта команда удалит все строки, начинающиеся на log_statement =.

А теперь допустим, мы хотим изменить max_connections на 200:

sed -i 's/^max_connections = .*$/max_connections = 200/' /etc/postgresql/14/main/postgresql.conf

Теперь PostgreSQL не задохнётся от нагрузки.

Некоторые параметры могут отсутствовать в конфиге, но нужно их добавить. Проверим, есть ли log_connections, и если нет — добавим:

grep -q '^log_connections' /etc/postgresql/14/main/postgresql.conf || echo 'log_connections = on' >> /etc/postgresql/14/main/postgresql.conf

Теперь PostgreSQL будет логировать все подключения.

Если параметр в конфиге закомментирован (#), sed его не изменит. Нужно раскомментировать строку и заменить значение:

sed -i 's/^#*\(log_duration =\).*/\1 on/' /etc/postgresql/14/main/postgresql.conf

Теперь включён лог длительности запросов.

Если в postgresql.conf случайно прописали параметр несколько раз:

sed -i '$!N; /^\(.*\)\n\1$/!P; D' /etc/postgresql/14/main/postgresql.conf

awk

awk — это как grep, только может считать, фильтровать, анализировать и даже делать статистику.

Найдём ТОП-10 самых медленных запросов. Если в логах есть строки вида:

LOG: duration: 3400 ms statement: SELECT * FROM transactions;

Найдём топ-10 самых долгих:

awk '/duration:/ {print $3, $6, $8}' /var/log/postgresql/postgresql.log | sort -nr | head -10

Разбираем:

/duration:/ — находим строки с duration:.
print $3, $6, $8 — выводим время выполнения и SQL‑запрос.
sort -nr — сортируем по убыванию времени.
head -10 — берём топ-10 самых долгих запросов.

Какие ошибки встречаются чаще всего?

awk '/ERROR/ {count[$0]++} END {for (line in count) print count[line], line}' /var/log/postgresql/postgresql.log | sort -nr | head -10

Выведет ТОП-10 самых частых ошибок.

Теперь реализуем SQL‑запросов по времени выполнения. Если в логах есть:

LOG: duration: 1200 ms statement: UPDATE users SET balance = balance - 100 WHERE id = 42;

А мы хотим получить все запросы дольше 2 секунд:

awk '/duration:/ {split($2, t, " "); if (t[1] > 2000) print $0}' /var/log/postgresql/postgresql.log

Теперь видим только долгие запросы.

Подсчёт запросов по типу:

awk '/statement:/ {type[$6]++} END {for (t in type) print t, type[t]}' /var/log/postgresql/postgresql.log

Выведет:

SELECT 154
INSERT 23
UPDATE 98
DELETE 17

Теперь видно, какие операции нагружают базу.

Если включён log_executor_stats, то в логах есть строки:

LOG: executor runtime: 450 ms

Вытащим среднее время выполнения запросов:

awk '/executor runtime:/ {sum+=$3; count++} END {if (count>0) print "Среднее время выполнения CPU:", sum/count, "мс"}' /var/log/postgresql/postgresql.log

Теперь можно понять, сколько ресурсов тратится на запросы.

Скрипт мониторинга PostgreSQL

Сделаем мониторинг логов PostgreSQL, который отлавливает ошибки, медленные запросы, а если что‑то идёт не так — сразу пинает в Telegram.

Сначала убеждаемся, что PostgreSQL вообще пишет нужные логи. Заходим в postgresql.conf и включаем вот это:

log_min_duration_statement = 1000  # Логируем запросы дольше 1 секунды  
log_statement = 'mod'              # Записываем только изменения (INSERT, UPDATE, DELETE)  
log_connections = on                # Фиксируем все подключения  
log_disconnections = on             # Фиксируем отключения  
log_executor_stats = on             # Логируем CPU-статистику

После изменений не забываем перезапустить сервер:

sudo systemctl restart postgresql

Теперь создаём сам скрипт, который будет искать ошибки, считать их, вытаскивать топ-5 медленных запросов и пинать в Telegram, если что‑то идёт не так.

Сохраняем вот это в /opt/postgresql_monitor.sh:

#!/bin/bash

LOG_FILE="/var/log/postgresql/postgresql.log"
ERROR_LOG="/var/log/pg_errors.log"
SLOW_LOG="/var/log/pg_slow_queries.log"
TELEGRAM_BOT_TOKEN="ваш_токен"
TELEGRAM_CHAT_ID="ваш_chat_id"
THRESHOLD_MS=5000  # Порог медленных запросов (5 секунд)

echo "=== PostgreSQL Log Monitor: $(date) ===" >> $ERROR_LOG

# Ищем ошибки
ERRORS=$(grep -E "ERROR|FATAL|PANIC" "$LOG_FILE" | tail -10)
if [[ ! -z "$ERRORS" ]]; then
    echo "Найдены ошибки:" >> $ERROR_LOG
    echo "$ERRORS" >> $ERROR_LOG
    curl -s -X POST "https://api.telegram.org/bot$TELEGRAM_BOT_TOKEN/sendMessage" \
        -d chat_id="$TELEGRAM_CHAT_ID" \
        -d text="PostgreSQL Ошибки: $ERRORS"
fi

# Медленные запросы
SLOW_QUERIES=$(awk -v threshold="$THRESHOLD_MS" '/duration:/ {split($2, time, " "); if (time[1] > threshold) print $0}' "$LOG_FILE" | tail -10)
if [[ ! -z "$SLOW_QUERIES" ]]; then
    echo "Медленные запросы:" >> $SLOW_LOG
    echo "$SLOW_QUERIES" >> $SLOW_LOG
    curl -s -X POST "https://api.telegram.org/bot$TELEGRAM_BOT_TOKEN/sendMessage" \
        -d chat_id="$TELEGRAM_CHAT_ID" \
        -d text="PostgreSQL Медленные запросы: $SLOW_QUERIES"
fi

# Подсчёт ошибок
ERROR_STATS=$(awk '/ERROR/ {count++} END {print count}' "$LOG_FILE")
echo "Общее количество ошибок: $ERROR_STATS" >> $ERROR_LOG

# Топ-5 медленных запросов
TOP_SLOW=$(awk '/duration:/ {print $3, $6, $8}' "$LOG_FILE" | sort -nr | head -5)
echo "Топ-5 медленных запросов:" >> $SLOW_LOG
echo "$TOP_SLOW" >> $SLOW_LOG

# Если логи раздулись больше 500MB — чистим
LOG_SIZE=$(du -m "$LOG_FILE" | cut -f1)
if [[ "$LOG_SIZE" -gt 500 ]]; then
    echo "Очистка логов PostgreSQL: размер $LOG_SIZE MB" >> $ERROR_LOG
    cat /dev/null > "$LOG_FILE"
fi

echo "=== Конец анализа ===" >> $ERROR_LOG

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

Выдаём права:

chmod +x /opt/postgresql_monitor.sh

Добавляем в cron, чтобы запускался каждые 5 минут:

echo "*/5 * * * * root /opt/postgresql_monitor.sh" >> /etc/crontab

Скрипт автоматически анализирует логи PostgreSQL и фиксирует ошибки. Он также отслеживает медленные запросы, записывает их в отдельные файлы и при необходимости отправляет уведомления в Telegram. Если размер логов превышает 500MB, скрипт очищает их, предотвращая захламление диска.

Можно добавить группировку ошибок, чтобы сразу видеть, какие именно сбои случаются чаще всего (например, duplicate key, out of memory, disk full). Если PostgreSQL пишет логи в JSON, можно научить скрипт их парсить и вытягивать нужные данные. При сбоях (FATAL: too many connections) полезно сразу перезапускать сервер, а для мониторинга подключить Grafana Loki + Prometheus.

А как вы используете эти методы в своем работе? Делитесь в комментариях.

20 февраля в Otus пройдёт онлайн-лекция на тему «Продуктовое мышление для бизнес-аналитика или как перестать мыслить требованиями».
Поговорим о том, почему продуктовое мышление важно для бизнес-аналитика и как оно влияет на профессиональный рост. Если тема интересна, записывайтесь.

Теги:

Хабы:

Текстовая обработка в PostgreSQL: grep, sed, awk

grep

sed

awk

Скрипт мониторинга PostgreSQL

Публикации

Информация