Найти застойные процессы, возможно, остановленные блокировкой
Этот запрос ищет в представлении pg_stat_activity активные процессы с wait_event или wait_event_type, отличные от non-NULL.
SELECT
pid,
datname,
usename,
application_name,
client_addr,
client_port,
to_char (now (), 'YYYY-MM-DD HH24:MI:SS') as now,
to_char (now () - xact_start, 'DD HH24:MI:SS MS') as xact_time,
to_char (now () - query_start, 'DD HH24:MI:SS MS') as query_time,
state,
to_char (now () - state_change, 'DD HH24:MI:SS MS') as state_time,
wait_event,
wait_event_type,
left (query, 40)
FROM
pg_stat_activity
WHERE
state != 'idle'
and pid != pg_backend_pid ()
ORDER BY
query_time desc;
Найти процессы с событием в ожидании, включая PID, содержащие начальную блокировку
Этот запрос работает с представлениями pg_stat_activity и pg_locks, отображает pid-ы, состояние, wait_event и режим блокировки, а также блокирующие pid-коды.
WITH sos AS (
SELECT array_cat(array_agg(pid),
array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
FROM pg_locks
WHERE NOT granted
)
SELECT a.pid, a.usename, a.datname, a.state,
a.wait_event_type || ': ' || a.wait_event AS wait_event,
current_timestamp-a.state_change time_in_state,
current_timestamp-a.xact_start time_in_xact,
l.relation::regclass relname,
l.locktype, l.mode, l.page, l.tuple,
pg_blocking_pids(l.pid) blocking_pids,
(pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(case when locktype='transactionid' then 1 else array_length(pg_blocking_pids(l.pid),1)+1 end,0),a.pid||'.0') lock_depth,
a.query
FROM pg_stat_activity a
JOIN sos s on (a.pid = any(s.pids))
LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
ORDER BY lock_depth;
Установить тайм-аут блокировки
Хорошей идеей может оказаться использование команды “set lock_timeout” внутри сессии, чтобы он отменял транзакции и по истечении времени снимал все блокировки.
ALTER SYSTEM SET lock_timeout = '10s';
Логинг
По умолчанию журнал медленных запросов неактивен, его можно включить командой log_min_duration_statement
ALTER database postgres SET log_min_duration_statement = '250ms';
Контроль записи в журнал типов операторов
Следите за тем, какие типы операторов записываются в журнал вашей базы данных.
ALTER DATABASE postgres SET log_statement = 'all';
Допустимые значения включают all, ddl, none, mod
Лог с ожиданием блокировки
Чтобы в логе выявить замедляющие запрос блокировки, необходимо использовать настройку сервера.
ALTER DATABASE postgres SET log_lock_waits = 'on';
Производительность
Установка временного ограничения запроса предотвращает выполнение запросов дольше указанного времени. Вы можете установить временное ограничение запроса на уровне базы данных, пользователя или сеанса. Мы рекомендуем установить глобальное временное ограничение в Postgres, а затем переопределять его для конкретных пользователей или сеансов, которым требуется большее разрешенное время для выполнения.
ALTER DATABASE mydatabase SET statement_timeout = '60s';
Используйте pg_stat_statements, чтобы найти запросы и процессы, которые используют больше всего ресурсов
SELECT
total_exec_time,
mean_exec_time as avg_ms,
calls,
query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Мониторинг подключений в Postgres
Этот запрос позволяет получать количество подключений в зависимости от типа.
SELECT count(*),
state
FROM pg_stat_activity
GROUP BY state;
Запрос размера конкретной таблицы
По этому запросу можно узнать размер таблицы
SELECT pg_relation_size('table_name');
– для удобочитаемости данные можно упростить информацию о размере таблицы до байтов через команду:
SELECT pg_size_pretty(pg_relation_size('table_name'));
Запрос всех данных о размере таблиц
После запуска запроса вы получите детальную информацию о каждой таблице с указанием её схемы, размера без индексов, размере индексов, суммарном размере таблицы и индексов, а также количестве строк в таблице.
SELECT relname AS relation,
pg_size_pretty (
pg_total_relation_size (C .oid)
) AS total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE nspname NOT IN (
'pg_catalog',
'information_schema'
)
AND C .relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size (C .oid) DESC
Наличие неиспользуемых индексов
Этот запрос поможет найти индексы, которые могут быть малоиспользуемы или неэффективны, основываясь на количестве сканирований и размере индекса относительно количества сканирований.
SELECT schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS "index size",
idx_scan as "index scans"
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
AND idx_scan < 50
AND pg_relation_size(relid) > 5 * 8192
ORDER BY
pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;
Приблизительные подсчеты таблицы
Этот SQL-запрос используется для получения приблизительного количества строк (кортежей) в указанной таблице в базе данных PostgreSQL. Полезно для больших таблиц, где выполнение «SELECT count(*)» приводит к снижению производительности.
SELECT reltuples::numeric as count FROM pg_class WHERE relname='table_name';
Создание индекса без блокировки
Данный SQL-запрос с помощью опции CONCURRENTLY позволяет создать индекс без блокировки таблицы для записей.
CREATE INDEX CONCURRENTLY foobar ON foo (bar);
PSQL
Отображение времени запроса в PSQL
Эта команда используется для включения или отключения отображения времени выполнения запросов в консоли или интерфейсе управления базами данных. *Обратите внимание, что это время выполнения запроса в оба конца*
\timing
Автоформатирование результатов запроса в psql
Когда используется эта команда – автоматически будет выбран формат вывода в зависимости от типа запроса. Например, для простых запросов будет использоваться удобочитаемый формат, а для сложных запросов — табличный формат.
Таким образом, команда «\x auto» упрощает визуализацию результатов запросов в MySQL, делая их более читаемыми и удобными для анализа.
\x auto
Редактируйте свои запросы psql по вашему выбору
Когда вы вводите эту команду в интерфейсе командной строки базы данных (например, в PostgreSQL или MySQL), текущий SQL запрос открывается в вашем предварительно настроенном текстовом редакторе, где вы можете вносить изменения.
После того как вы внесете необходимые изменения в SQL запросе и сохраните файл, интерфейс командной строки автоматически выполнит отредактированный SQL запрос. Команда «\e» облегчает редактирование сложных запросов или длинных скриптов, позволяя вам использовать удобный текстовый редактор для изменений.
\e
Установка значения для нулей
Будет отображать нулевые значения в виде любого указанного вами символа. Удобно для упрощения разбора нулевых значений по сравнению с пустым текстом.
\pset null 👻
Вы можете сохранить это в своем файле `.psqlrc` в качестве параметра по умолчанию
Сохраняйте историю запросов для каждой базы данных локально
Автоматически сохранит файл истории для каждой **DBNAME**.
\set HISTFILE ~/.psql_history- :DBNAME
Вы можете сохранить это в своем файле `.psqlrc` в качестве параметра по умолчанию
Показывать запросы, выполняемые внутренними командами psql
Добавьте параметр «-E» (или —echo-hidden) для psql в командной строке. Этот параметр будет отображать запросы, которые генерируются внутренними командами psql (например, «\dt mytable»). Это отличный способ узнать больше о системных каталогах или повторно использовать запросы, выданные psql, в вашем собственном инструменте.
psql -E
Возврат только данных
Добавьте параметры «-qtA» в psql в командной строке. Благодаря этим параметрам psql будет работать в автоматическом режиме («-q»), возвращая только кортежи («-t») без выравнивания («-A»). В сочетании с параметром «-c» для отправки одного запроса это может быть полезно, если вам нужно быстро получить только значения из запросов без лишней информации о столбцах.
psql -qtA
Получение результатов в HTML-таблице
Добавьте параметры «-qtH» в psql в командной строке. Благодаря этим параметрам psql будет работать в автономном режиме («-q»), возвращая только кортежи («-t») в HTML-таблицу («-H»). В сочетании с опцией «-c» для отправки одного запроса это поможет быстро встроить результат запроса в HTML-страницу.
psql -qtH
Поиск предыдущих запросов с помощью Ctrl + R
Сочетание клавиш Ctrl + R запустит сеанс поиска, и вы сможете начать вводить часть запроса или команды, чтобы найти и запустить его снова. Если вы помечаете конкретные запросы комментарием, это может помочь в дальнейшем поиске.
(reverse-i-search)
Очистить psql экран
Очистит экран в текущей сессии
\! clear
Повторное выполнение запроса с параметром watch
Эта команда позволяет наблюдать за изменениями данных в реальном времени, поскольку запрос будет автоматически выполняться через определенные интервалы времени.
После ввода команды необходимо указать интервал времени в секундах, через который будет выполняться повторное выполнение текущего запроса. Например, «\watch 5» означает, что текущий запрос будет выполняться каждые 5 секунд.
Команда «\watch» удобна для мониторинга изменений в данных или результатов запросов в реальном времени, особенно когда необходимо отслеживать динамические изменения или обновления в базе данных.
\watch
Показывать настройки, отличные от настроек по умолчанию
Выведет список параметров и значений для параметров конфигурации Postgres, которые были изменены по сравнению с параметрами по умолчанию.
\dconfig
Откат при ошибке в интерактивном режиме
В случае возникновения ошибки при выполнении запроса в рамках транзакции, psql будет автоматически откатывать изменения и переводить сессию в режим отката транзакции, что позволит вам взаимодействовать с интерфейсом psql для дальнейшей обработки ошибки или внесения исправлений. В этом режиме удобнее управлять ошибками во время выполнения транзакций в интерактивном режиме работы с базой данных.
\set ON_ERROR_ROLLBACK interactive
Экспортируйте CSV-файл непосредственно из psql
При указании значения `—csv` в запросе команда выполнит конкретный запрос и вернет CSV в стандартный вывод.
psql <connection-string> --csv -c 'select * from test;'
Запуск запроса из файла в psql
При вводе этой команды, psql прочитает содержимое указанного файла и выполнит SQL команды, которые в нем содержатся. Это удобно, когда у вас есть большой объем SQL кода или когда вам нужно выполнить серию команд из внешнего файла в интерактивной оболочке psql.
\i filename
Чистые границы в psql
После выполнения команды «\pset border 2», результаты запросов будут отображаться с двойной линией вокруг каждой ячейки. Это помогает лучше визуализировать данные в виде таблицы и делает вывод более удобным для чтения. Изменение стиля рамки при выводе результатов запросов может быть полезно для улучшения внешнего вида вывода данных и облегчения их анализа в интерфейсе командной строки psql.
\pset border 2
Вы можете сохранить это в своем файле `.psqlrc` в качестве параметра по умолчанию
Сохранение запросов в psqlrc
Добавьте эти примеры запросов в psqlrc для определения длительности выполнения запросов, коэффициента попадания в кэш, неиспользуемых индексов и размеров таблиц. Затем для выполнения внутри psql используйте :long_running, :cache_hit , :unused_indexes , :table_sizes.
\set long_running 'SELECT pid, now() - pg_stat_activity.xact_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > interval ''5 minutes'' ORDER by 2 DESC;'
\set cache_hit 'SELECT ''index hit rate'' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT ''table hit rate'' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;'
\set unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
\set table_sizes 'SELECT c.relname AS name, pg_size_pretty(pg_table_size(c.oid)) AS size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (''pg_catalog'', ''information_schema'') AND n.nspname !~ ''^pg_toast'' AND c.relkind=''r'' ORDER BY pg_table_size(c.oid) DESC;'
Установите стиль строки в unicode
Когда устанавливается этот параметр, символы Unicode используются для отображения линий вокруг таблицы с результатами запроса.
При использовании стиля линий Unicode, таблицы с результатами запросов становятся более читабельными и привлекательными, а символы Unicode обеспечивают более эстетичное отображение границ и разделителей в выводе данных.
\pset linestyle unicode
Вы можете сохранить это в своем файле `.psqlrc` в качестве параметра по умолчанию
SQL
Поиск искаженных данных
Этот SQL запрос выполняет извлечение статистики по процентам значений в колонках таблиц из системного каталога PostgreSQL. Данный запрос извлекает статистику по процентам значений в колонках таблиц, представляя результаты в формате, который показывает процентные значения для каждой колонки в каждой таблице, исключая системные таблицы и таблицы из информационной схемы.
SELECT starelid::regclass AS table_name,attname AS column_name,
(SELECT string_agg('',format(E''%s': %s%%
', v,ROUND(n::numeric*100, 2)))
FROM unnest(stanumbers1,stavalues1::text::text[])nvs(n,v)) pcts
FROM pg_statistic
JOIN pg_attribute ON attrelid=starelid
AND attnum = staattnum
JOIN pg_class ON attrelid = pg_class.oid
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
WHERE nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema'
\x\g\x
Замена нулей другими значениями
данный запрос вернет результат, где каждая запись будет содержать значение из столбца «id» и значение столбца «ip», которое будет заменено на ‘no IP’, если оно равно NULL. Это позволяет заменить отсутствующие IP-адреса на читаемую строку ‘no IP’ при выводе результатов запроса.
SELECT id,
coalesce(ip, 'no IP')
FROM logs;
Вы также можете указать два столбца перед вашим заменяющим значением, и функция будет использовать первое ненулевое значение.
Импортируйте схему с отображением внешней оболочки данных (FDW)
При выполнении этой команды, PostgreSQL будет импортировать все объекты (таблицы, представления, функции и т. д.) из схемы ‘public’ в текущую базу данных, где вы выполняете команду. Важно отметить, что для успешного выполнения команды «IMPORT FOREIGN SCHEMA» может потребоваться наличие правильных привилегий доступа к внешней схеме и объектам, которые вы пытаетесь импортировать.
IMPORT FOREIGN SCHEMA "public";
Вы можете IMPORT FOREIGN SCHEMA при сопоставлении внешней оболочки данных, чтобы избавить себя от необходимости создавать новую
Генерируйте данные с помощью generate_series
Данный запрос вернет результирующий набор данных, содержащий все даты, начиная с текущей даты минус три месяца и заканчивая текущей датой, с шагом в один день. Это может быть полезно, например, для создания временных рядов или анализа данных за определенный период времени. Может использоваться в предложении FROM или JOIN или CTE. Обычно используется при построении диаграмм и отчетов, в которых требуется заполнение всех дат.
SELECT * FROM generate_series(now() - '3 month'::interval, now(), '1 day');
Округление дат с помощью date_trunc
Дата будет сокращена до указанного уровня точности. В качестве примера можно привести следующие уровни точности: месяц, неделя, день, час, минута.
SELECT date_trunc('day', now());
Выполняйте математические вычисления во времени с интервалами
Вы можете добавлять или вычитать определенные промежутки времени из временной метки, задавая нужное значение в качестве интервала.
SELECT now() - '1 month'::interval;
Сделайте небольшую паузу в вашей сессии
Команда используется для создания задержки в выполнении запроса в PostgreSQL. В данном случае, функция pg_sleep() используется для приостановки выполнения запроса на указанное количество секунд. В примере запроса указана задержка в 2.5 секунды. Когда этот запрос будет выполнен, выполнение запроса будет приостановлено на 2.5 секунды, прежде чем вернуться к выполнению следующего запроса или операции. Этот тип запроса с задержкой может быть полезен в различных сценариях, таких как тестирование производительности, сценарии ожидания и тестирование параллельного выполнения запросов.
select pg_sleep(2.5);
Утилиты
Сделайте резервную копию или заархивируйте таблицу
Эта команда выполняет резервное копирование определенной таблицы из базы данных в файл с использованием утилиты `pg_dump` в PostgreSQL. Таким образом, только данные из указанной таблицы будут сохранены в указанный файл.
pg_dump -t my_table > test.sql
Близки к переполнению целого числа?
Этот запрос выявляет информацию о последних значениях последовательностей и процентном использовании данных в PostgreSQL.
SELECT
seqs.relname AS sequence,
format_type(s.seqtypid, NULL) sequence_datatype,
CONCAT(tbls.relname, '.', attrs.attname) AS owned_by,
format_type(attrs.atttypid, atttypmod) AS column_datatype,
pg_sequence_last_value(seqs.oid::regclass) AS last_sequence_value,
TO_CHAR((
CASE WHEN format_type(s.seqtypid, NULL) = 'smallint' THEN
(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)
WHEN format_type(s.seqtypid, NULL) = 'integer' THEN
(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)
WHEN format_type(s.seqtypid, NULL) = 'bigint' THEN
(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)
END) * 100, 'fm9999999999999999999990D00%') AS sequence_percent,
TO_CHAR((
CASE WHEN format_type(attrs.atttypid, NULL) = 'smallint' THEN
(pg_sequence_last_value(seqs.relname::regclass) / 32767::float)
WHEN format_type(attrs.atttypid, NULL) = 'integer' THEN
(pg_sequence_last_value(seqs.relname::regclass) / 2147483647::float)
WHEN format_type(attrs.atttypid, NULL) = 'bigint' THEN
(pg_sequence_last_value(seqs.relname::regclass) / 9223372036854775807::float)
END) * 100, 'fm9999999999999999999990D00%') AS column_percent
FROM
pg_depend d
JOIN pg_class AS seqs ON seqs.relkind = 'S'
AND seqs.oid = d.objid
JOIN pg_class AS tbls ON tbls.relkind = 'r'
AND tbls.oid = d.refobjid
JOIN pg_attribute AS attrs ON attrs.attrelid = d.refobjid
AND attrs.attnum = d.refobjsubid
JOIN pg_sequence s ON s.seqrelid = seqs.oid
WHERE
d.deptype = 'a'
AND d.classid = 1259;
Восстановить дамп/архив одной таблицы
Этот запрос создаст базу данных и связанные с ней данные из файла .sql. Необходимо запустить из папки с тестовым файлом.sql.
psql -f test.sql
Создание пользователя только для чтения
Уже создав определенную роль пользователя, вы можете использовать `pg_read_all_data`, чтобы предоставить доступ только для чтения ко всем таблицам.
GRANT pg_read_all_data TO username;
Отображение таблицы при помощи TOAST
Этот запрос извлекает информацию о таблицах и их связанных toast-таблицах, при условии, что у toast-таблицы есть хотя бы одна страница данных (технология хранения атрибутов с увеличенным размером).
SELECT
c.relname AS source_table_name,
c.relpages AS source_table_number_of_pages,
c.reltuples AS source_table_number_of_tuples,
c.reltoastrelid AS toast_table_oid,
t.relname AS toast_table_name,
t.relpages AS toast_table_number_of_pages,
t.reltuples AS toast_table_number_of_tuples
FROM
pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE
t.relpages > 0;
Поиск активных и неактивных пользователей
Включая их роли, состояние и номер подключения.
SELECT usename, state, count(1) cnt FROM pg_stat_activity WHERE usename is not null GROUP BY usename, state ORDER BY usename, state;
Завершить работу серверных систем конкретного пользователя («test»)
Этот SQL запрос использует команду для принудительного завершения всех активных сеансов (backend’ов), принадлежащих конкретному пользователю в базе данных.
WITH pids AS ( SELECT pid FROM pg_stat_activity WHERE usename='test' ) SELECT pg_terminate_backend(pid) FROM pids;
Отмена всех SQL-команд от определенного пользователя (“test”)
Этот запрос отменит каждый запущенный запрос, сделанный конкретным пользователем «test».
WITH pids AS ( SELECT pid FROM pg_stat_activity WHERE username='test' ) SELECT pg_cancel_backend(pid) FROM pids;
Оригинал доступен по ссылке
