Для тех, кто много работает с базами данных, есть простой и удобный инструмент для их проектирования – dbdiagram.io. Мы не разработчики, тем не менее, покажу, как этот сервис может быть полезен. У него удобная визуализация структуры базы данных. Намного удобнее, чем, к примеру, в привычном phpmyadmin.

Можно выгрузить обычный дам из СУБД без данных и загрузить его в dbdiagram. Получите наглядную схему базы. При желании, её можно отредактировать, или просто экспортнуть в виде картинки. Покажу на примере MySQL. Выгружаем дамп базы postfix без данных, только структуру:

# mysqldump --no-data -u dbuser -p postfix > ~/schema.sql

Передаём к себе на комп полученный дамп. В dbdiagram.io открываем Import ⇨ From MySQL ⇨ Upload .sql ⇨ Submit. Наблюдаем схему базы данных со всеми связями. Причём она сразу отформатирована по таблицам так, что все их наглядно видно.

При желании можно что-то изменить в структуре и выгрузить новый sql файл уже с изменениями. Если выбрать Export ⇨ To PNG, то сразу же получите аккуратную картинку со схемой, без лишних вопросов. Очень быстро и удобно, если нужно куда-то приложить схему базы данных.

⇨ 🌐 Сайт

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#mysql #postgresql

Расскажу про парочку инструментов, которые упростят обслуживание сервера PostgreSQL. Начну с наиболее простого - pgBadger. Это анализатор лога, который на его основе генерирует отчёты в формате html. На выходе получаются одиночные html файлы, которые можно просто открыть в браузере. Сделано всё аккуратно и добротно, легко настраивается, отчёты наглядные и информационные.

🔹Чтобы было что анализировать, необходимо включить логирование интересующих вас событий. Для разовой отладки это всё можно включать на ходу, либо постоянно через файл конфигурации postgresql.conf и перезапуск сервера. Он обычно хорошо прокомментирован. Вас будут интересовать параметры, начинающие с log_*. Они собраны все в отдельном блоке. Для примера я включил почти всё:

log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%m [%p] %q%u@%d '
log_lock_waits = on
log_temp_files = 0
log_timezone = 'Europe/Moscow'

Вся включенная статистика стала писаться в общий лог-файл /var/log/postgresql/postgresql-17-main.log. С ним и будем работать. Устанавливаем pgBadger:

# wget https://github.com/darold/pgbadger/archive/refs/tags/v13.1.tar.gz
# tar xzvf v13.1.tar.gz
# cd pgbadger-*
# apt install make
# make && make install

Анализируем лог файл:

# pgbadger /var/log/postgresql/postgresql-17-main.log

Тут же в директории, где его запускали, увидите файл out.html. Забирайте его к себе и смотрите. Там будет информация с общей статистикой сервера, информация по запросам и их типам, времени исполнения, подключениям, по пользователям, базам и хостам откуда подключались и много всего остального.

PgBadger удобен тем, что по сути это одиночный скрипт на Perl. Можно включить логирование в конфигурации, применить её через SELECT pg_reload_conf(); без перезапуска сервера СУБД. Пособирать некоторое время данные, забрать лог и анализировать его. Логирование отключить и снова перечитать конфиг. В итоге всё будет сделано без перезапуска сервера.

🔹Второй инструмент - PgHero, он показывает примерно то же самое, только в режиме реального времени и работает в виде веб сервиса. Для него уже надо создавать пользователя, настраивать доступ, отдельную базу. Немного другой подход. Надо будет дёргать сервер с СУБД.

Надо перейти в консоль и создать необходимые сущности:

# su postgres
# psql
> CREATE USER pghero WITH PASSWORD 'pgheropass';
> CREATE DATABASE pgherodb OWNER pghero;
> \q

Разрешаем этому пользователю подключаться. Добавляем в pg_hba.conf строку:

host pgherodb pghero 172.17.0.0/24 md5

172.17.0.0/24 - подсеть, из которой будет подключаться PgHero. В данном случае это Docker контейнер, запущенный на этом же хосте. PostgreSQL должен принимать запросы с локального IP адреса, к которому будет доступ из Docker сети. Можно добавить в конфиг postgresql.conf параметр:

listen_addresses = 'localhost,172.17.0.1'

Перезапускаем PotgreSQL:

# systemctl restart postgresql

Запускаем PgHero в Docker контейнере:

# docker run -ti -e DATABASE_URL=postgres://pghero:pgheropass@172.17.0.1:5432/pgherodb -p 8080:8080 ankane/pghero

Идём на порт севера 8080, где запущен контейнер и смотрим информацию о PostgreSQL. Если у вас не настроено расширение pg_stat_statements, которое использует PgHero для сбора статистики, то установите его. Для этого в конфигурацию postgresql.conf добавьте параметры:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_activity_query_size = 2048

Перезапустите Postgresql и выполните в консоли СУБД:

> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
> GRANT pg_read_all_stats TO pghero;

Теперь можно возвращаться в веб интерфейс и смотреть информацию. По умолчанию, пользователь pghero не будет видеть запросы других пользователей, если ему не дать права superuser. Это можно исправить, выдав ему набор прав и ролей из этой инструкции.

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#postgresql