Postgres Guru | Базы данных

🤖 Встроенные средства мониторинга в PostgreSQL. Поздравляем с Днем Системного Администратора всех причастных!!! Интересно, а день DBA есть или нет? 🤔 Сегодня поговорим о полезных средствах мониторинга в PostgreSQL, которые доступны нам из коробки (ну или почти). Чем полезно знать о таких средствах? ✅ Не требуют сторонних решений для базового анализа; ✅ Показывают реальную картину без искажений; ✅ Доступны мгновенно без настройки; ✅ Покрывают 80% потребностей в мониторинге. pg_stat_activity (Показывает все текущие подключения с детализацией. Пример запроса, который покажет нам "зависшие" запросы: SELECT    pid,   usename,   application_name,   client_addr,   state,   query,   query_start,   age(now(), query_start) AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC; pg_stat_statements (детектор медленных запросов). Сначала надо включить расширение командой: CREATE EXTENSION pg_stat_statements; и добавить в shared_preload_libraries, после чего перезапустить службу PostgreSQL. Пример запроса, который покажет нам ТОП-10 самых ресурсоемких запросов: SELECT    query,   calls,   total_exec_time,   mean_exec_time,   rows/calls AS avg_rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; pg_stat_bgwriter (статистика ввода-вывода). Пример запроса: SELECT    checkpoints_timed,   checkpoints_req,   buffers_checkpoint,   buffers_clean,   buffers_backend FROM pg_stat_bgwriter; Если запрос показал высокий checkpoints_req, то у нас нехватка max_wal_size, если большие buffers_backend - нехватка shared_buffers. pg_stat_progress_vacuum (реальный прогресс AUTOVACUUM). Пример запроса: SELECT * FROM pg_stat_progress_vacuum; Если на нужна более детальная информация, то можем использовать вот такой навороченный запрос: SELECT p.pid, now() - a.xact_start AS duration, coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting, CASE WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound' WHEN a.query ~*'^vacuum' THEN 'user' ELSE 'regular' END AS mode, p.datname AS database, p.relid::regclass AS table, p.phase, pg_size_pretty(p.heap_blks_total *  current_setting('block_size')::int) AS table_size, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned, pg_size_pretty(p.heap_blks_vacuumed *  current_setting('block_size')::int) AS vacuumed, round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct, round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct, p.index_vacuum_count, round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a using (pid) ORDER BY now() - a.xact_start DESC; pg_stat_database (покажет статистику по базам данных): Пример запроса: SELECT    datname,   numbackends,   xact_commit + xact_rollback AS transactions,   tup_returned AS selects,   tup_inserted AS inserts,   tup_updated AS updates,   tup_deleted AS deletes,   blks_hit * 100 / (blks_hit + blks_read) AS cache_hit_ratio FROM pg_stat_database WHERE datname NOT LIKE 'template%'; Здесь ключевая метрика: cache_hit_ratio (процент попадания в кэш) < 99% - нужно увеличивать shared_buffers. В один пост все встроенные средства мониторинга в PostgreSQL не поместятся, по этому будем развивать тему в будущем. На этом все! До связи! #pgmonitor

#pgmeme

🤖 Малоизвестные системные представления PostgreSQL. Сегодня поговорим о не очень известных системных представлениях (view) в PostgreSQL (и не только). Эти представления помогут нам более глубоко изучить события, происходящие в нашей СУБД и заглянуть в ее скрытые уголки. 1️⃣ Представления для анализа безопасности pg_stat_ssl Представление добавлено еще в версию PostgreSQL 9.5. И предоставляет информацию о SSL-соединениях для каждого процесса. Пример запроса: SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid(); А если, мы соединим это представление с pg_stat_activity, то получим более расширенную информацию по SSL соединениям: SELECT backend_start,ssl,version,datname as "Database name", usename as "User name", ssl, client_addr, application_name, backend_type,* FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid ORDER BY backend_start DESC,ssl; pg_stat_gssapi Аналогично для GSSAPI-аутентификации. Представление было добавлено в PostgreSQL 12. Пример запроса: SELECT * FROM pg_stat_gssapi WHERE pid = pg_backend_pid(); 2️⃣ Представления для анализа WAL (Write-Ahead Log) pg_stat_wal Представление было добавлено в версию PostgreSQL 14 и показывает статистику по активности WAL: количество сгенерированных записей, буферизованных записей, количество FPI (Full Page Images) и т.д. Пример запроса: SELECT * FROM pg_stat_wal; pg_walfile_name_offset Это фактически не представление, а системная функция, которая преобразует LSN (Log Sequence Number) в имя файла WAL и смещение внутри файла. SELECT pg_walfile_name_offset(pg_current_wal_lsn()); 3️⃣ Специализированные представления для анализа памяти pg_stat_slru Представление было добавлено в версию PostgreSQL 13 и показывает статистику по использованию SLRU (Simple Least Recently Used) кэшей, которые используются для subtransactions, multixact и других внутренних структур. Пример запроса: SELECT * FROM pg_stat_slru; pg_shmem_allocations Представление появилось в версии PostgreSQL 13 и показывает распределение разделяемой памяти. Представлением могут воспользоваться только для суперпользователей или с правом pg_read_all_stats. Пример запроса: SELECT * FROM pg_shmem_allocations; На этом пока все! Если тема интересна, то будем развивать ее в будущих постах. До связи! #pgmonitor

🔬 Поддержка составных (композитных) первичных ключей в PostgreSQL. Сегодня с вами поговорим подробнее про составные первичные ключи. Составной первичный ключ (Composite Primary Key) — это комбинация двух или более столбцов, которые вместе уникально идентифицируют каждую строку в таблице. В отличие от простого первичного ключа (одного столбца), составной ключ гарантирует уникальность только в сочетании всех указанных полей. Составной PK гарантирует, что комбинация значений уникальна, но отдельные столбцы могут повторяться. Когда использовать составные PK? ✅ Связующие таблицы (many-to-many). Например, order_items связывает заказы (orders) и товары (products); ✅ Таблицы с естественным составным ключом. Например, версионность данных, где уникальность определяется ID + датой; ✅ Когда бизнес-логика требует уникальности комбинации полей. Например, расписание врачей: врач_id + дата_приема + время_начала. Когда лучше использовать простой PK (serial/bigserial)? ❌ Если есть частое изменение ключевых полей (PK лучше не менять); ❌ Для JOIN-операций простой PK будет быстрее. Составной первичный ключ создается следующим образом: При создании таблицы: CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT NOT NULL, PRIMARY KEY (order_id, product_id) ); Или так, если таблица уже существует: ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id); Внешние ключи (FOREIGN KEY) и составные PK. Если нужно сослаться на составной PK из другой таблицы, мы можем это сделать, либо сославшись на часть составного PK: CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(id) ); либо на весь составной PK: CREATE TABLE shipments ( shipment_id SERIAL PRIMARY KEY, order_id INT, product_id INT, FOREIGN KEY (order_id, product_id) REFERENCES order_items(order_id, product_id) ); Индексы и производительность. 📌 PostgreSQL автоматически создаёт уникальный B-tree индекс для составного PK; 📌 Порядок столбцов в PK важен для запросов с WHERE и JOIN; 📌 Лучше ставить чаще используемые столбцы первыми. Ограничения: ❌ Нельзя напрямую использовать SERIAL/BIGSERIAL для всего составного первичного ключа. Вместо этого, необходимо создать отдельный столбец с SERIAL/BIGSERIAL и включить его в состав первичного ключа вместе с другими столбцами; ❌ Изменение значений PK возможно, но не рекомендуется (нарушает ссылочную целостность); ❌ Размер ключа - не более ~2700 байт (зависит от версии PostgreSQL). Вывод: Составные первичные ключи в PostgreSQL — мощный инструмент для: 📌 Связей many-to-many (корзины заказов, теги); 📌 Сложных бизнес-правил (уникальные комбинации); 📌 Оптимизации запросов (если правильно выбрать порядок столбцов). На этом все! До связи! #pgbase

🔬 Смотрим на события ожидания удобно (представление pg_wait_events). Релиз PostgreSQL 17 принес нам много улучшений и нововведений. Среди них появилось новое представление системного каталога pg_wait_events. Это представление в простой и удобной форме покажет нам все запросы и процессы, которые чего-то жду, да еще и выдаст описание к каждому событию ожидания. Сами события ожидания классифицируются на несколько типов: События блокировки (Lock, LWLock) - транзакция ожидает блокировки, удерживаемой другим процессом (наши любимые 😅); События использования буфера (BufferPin) - процесс удерживает блокировку на буфере, предотвращая внесение изменений другими процессами; События ввода/вывода (I/O) - указывают на задержку при чтении или записи данных на диск; События репликации (WalSenderMain, WalReceiverMain) - связаны с задержками репликации. Самый простой запрос к представлению будет выглядеть так: SELECT * FROM pg_wait_events LIMIT 3; В ответ получим таблицу, состоящую из трех строк и трех колонок: type - тип события ожидания; name - название события ожидания; description - описание события ожидания. Для получения более подробной информации по событиям ожидания, это представление можно объединить в запросе с pg_stat_activity. Например вот так: SELECT psa.pid, psa.application_name, psa.wait_event, we.description FROM pg_stat_activity psa JOIN pg_wait_events we ON (psa.wait_event_type = we.type ANDpsa.wait_event = we.name); Получим в выводе еще дополнительные две колонки pid (pid процесса/запроса) и application name (имя приложения). А вот таким запросом мы можем вывести еще колонку с названием базы данных, именем пользователя , от которого запущен запрос и статус транзакции: SELECT a.datname, a.usename, a.state, a.wait_event_type, a.wait_event, b.description FROM pg_stat_activity a JOIN pg_wait_events b ON a.wait_event_type = b.type AND a.wait_event = b.name; Как видите , новое представление сильно нам облегчило отлов событий ожидания. Так что пользуемся! На этом все! До связи! #queries

🛠️ Мониторим ресурсы Linux через SQL (расширение pg_linux_stats). Раз у нас с предыдущим постом получился epic fail 😅, то сегодня поговорим об еще одном полезном расширении для PostgreSQL. Речь пойдет о расширении pg_linux_stats. И полезно оно в первую очередь для мониторинга. Оно позволяет нам мониторить ресурсы Linux с помощью SQL команд. Разработано оно с целью упрощения мониторинга ресурсов ОС Linux в небольших инфраструктурах, где использование таких систем мониторинга как Zabbix или Prometheus может быть избыточно. Хотя метрики, собранные с помощью этого расширения вы все равно можете туда передать. Официальный GitHub расширения: ➡️ https://github.com/s-hironobu/pg_linux_stats Суть расширения состоит в том, что оно позволяет вызывать такие утилиты Linux как vmstat, iostat, netstat и mpstat прямо из psql с помощью обычных SQL команд. Расширение поддерживает версии PostgreSQL 16 и 17. Вкратце напомним что делают, перечисленные выши утилиты Linux: vmstat - предоставляет обширную статистику по использованию виртуальной памяти, состоянию системных процессов, оперативной памяти, подкачке, количеству прерываний, активности ввода-вывода на уровне блочных устройств, а также активности диска и центрального процессора; iostat - выводит основные параметры ввода и вывода данных на диск, скорость записи и чтения данных, а также количество записанных или прочитанных данных; netstat - показывает сведения о сетевых соединениях и таблицах маршрутизации и данные о работе сетевых интерфейсов; mpstat - подробная статистика по использованию процессора. Все эти утилиты как правило уже входят в состав дистрибутива Linux. Единственное, утилиты mpstat может не оказаться в некоторых версиях Linux, по этому ее может потребоваться установить отдельно командой: # sudo apt install sysstat Установка самого расширения тривиальна и описана в документации, если кратко, то клонируем репозиторий расширения, собираем исходники, прописываем библиотеку расширения в shared_preload_libraries, перезапускаем службу PostgreSQL и устанавливаем расширение в нужную базу командой: CREATE EXTENSION pg_linux_stats; После этого можем вызывать утилиты Linux прямо из SQL. Например вот так: SELECT * FROM pg_vmstat(); Подробнее об использовании расширения читаем в документации. На этом все! До связи! #pgext

Предыдущий пост удалили, так как STRICT в итоге не был добавлен в финальный релиз вообще, не проверили иформацию до конца. Приносим извинения. У нас стоял какой-то тестовый релиз PostgreSQL 16, в котором эта фишка еще работала. В финальную версию ее не закомитили. Спасибо нашему подписчику за бдительность. В следующий раз будем внимательней 😁😅

#pgmeme

🛠️ Представления с SECURITY INVOKER в PostgreSQL 15 Еще в PostgreSQL 15 появилась новая фишка - представления (views) с SECURITY INVOKER, которые меняют поведение проверки прав доступа. Давайте разберёмся, как это работает и зачем нужно. До версии PostgreSQL 15 все представления выполнялись с правами их создателя (SECURITY DEFINER). Это может быть небезопасно: Например, мы создали какое-то такое представление и дали на него права пользователю (user): CREATE VIEW admin_data AS SELECT * FROM sensitive_table; GRANT SELECT ON admin_data TO user; Если таблица sensitive_table принадлежит другому пользователю, то наш user получит доступ к данным с правами создателя представления, даже если у него нет прямых прав на таблицу. С PostgreSQL версии 15 можно явно указать, что представление должно выполняться с правами вызывающего (`SECURITY INVOKER`): Делается это так: CREATE VIEW user_specific_data WITH (security_invoker = true) AS SELECT * FROM private_data WHERE user_id = current_user_id(); Теперь: ✅ Если у пользователя нет прав на `private_data`, запрос к представлению не сработает; ✅ Нет риска эскалации привилегий. Когда использовать? 📌 Безопасность: Если важно, чтобы пользователи имели только свои права. 📌 Аудит: Действия выполняются от имени реального пользователя, а не создателя VIEW. Ограничения: ❌ Производительность: Проверка прав происходит при каждом обращении; ❌ Обратная совместимость: Старые приложения могут сломаться, если VIEW теперь требует больше прав. Вывод: SECURITY INVOKER - важный шаг в безопасности PostgreSQL. Используйте его для: ✅ Строгого контроля доступа; ✅ Избежания неявных привилегий. На этом все! До связи! #pgsec

🛠️ Подключаемся из PostgreSQL к MSSQL. Как нам всем известно у PostgreSQL есть так называемые модули Foreign Data Wrapper (FDW). Эти модули позволяют подключаться из PostgreSQL к сторонним базам данных, получать данные, создавать таблицы и т.д. FDW существуют для таких баз как MySQL, Oracle и еще много разных других. Среди FDW есть модуль, который позволяет подключаться к сторонним СУБД по протоколу TDS (Tabular Data Stream). К таким СУБД относится в том числе MSSQL. Модуль называется tds_fdw. Официальный GitHub расширения: ➡️ https://github.com/tds-fdw/tds_fdw?tab=readme-ov-file Рассмотрим сценарий, когда у нас PostgreSQL установлен на Ubuntu, MSSQL, соответственно, где то на Windows. Попробуем подключиться к MSSQL из PostgreSQL с помощью tds_fdw. Прежде чем устанавливать само расширение нам потребуется библиотека протокола TDS, одной из таких библиотек является FreeTDS: # sudo apt-get update # sudo apt-get install libsybdb5 freetds-dev freetds-common Также нам необходимо установить утилиты и зависимости нужные для сборки расширения: # sudo apt-get install gnupg gcc make Плюс нам понадобиться пакет postgresql-server-dev-XX, где XX версия вашей PostgreSQL. # sudo apt install postgresql-server-dev-XX После этого можем переходить к сборке расширения: # sudo apt-get install git git clone https://github.com/tds-fdw/tds_fdw.git # cd tds_fdw # make USE_PGXS=1 # sudo make USE_PGXS=1 install Далее устанавливаем расширение как обычно: CREATE EXTENSION tds_fdw; Прежде чем начинать манипулировать данными, нам нужно настроить подключение к серверу MSSQL по протоколу TDS. Для этого протоколу нужно знать порт, имя или IP адрес сервера MSSQL. Все эти параметры указываются в файле freetds.conf: # sudo nano /etc/freetds/freetds.conf Пример файла: [mssql01] host = 192.168.8.100 port = 1433 tds version = auto instance = MSSQL01 Здесь, думаю, все понятно по контексту, instance - название экземпляра MSSQL сервера. Сервер MSSQL должен быть доступен по портам TCP 1433 и UDP 1434. Теперь можем переходить к мапингу сервера MSSQL и пользователя в самой PostgreSQL. Создаем подключение к серверу: CREATE SERVER mssql_svr FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.8.100', port '1433', database 'tds_fdw_test', tds_version '7.1'); Сопоставляем пользователя MSSQL: CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username 'sa', password ''); Ну и наконец мы может перейти к сопоставлению и импорту таблиц: CREATE FOREIGN TABLE mssql_table ( id integer, data varchar) SERVER mssql_srv OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all'); Более того, мы можем импортировать всю схему из MSSQL. Таким образом нам не придется сопоставлять структуру каждой таблицы по отдельности: IMPORT FOREIGN SCHEMA msschema FROM SERVER mssql_srv INTO pgschema OPTIONS (import_default 'true'); Схемы msschema и pgschema должны уже существовать. Команда принимает следующие параметры: import_default — добавлять или нет выражение DEFAULT при описании столбцов таблиц (по умолчанию, false); import_not_null — добавлять или нет ограничение NOT NULL при описании столбцов таблиц (по умолчанию, true). Более подробно обо все параметрах можно почитать в официальной документации расширения, там все отлично и понятно описано. В итоге модуль tds_fdw поможет нам наладить коммуникацию PostgreSQL с MSSQL и даже провести миграцию на PostgreSQL. На этом все! До связи! #pgext