postgres
29 commands
29 shown
Подключиться к базе данных
Интерактивная сессия psql к базе данных
psql -h localhost -U myuser -d mydb
Список баз данных
Все базы с размером и кодировкой
psql -U postgres -c '\l+'
Таблицы в схеме
Список таблиц в схеме public
psql -U myuser -d mydb -c '\dt public.*'
EXPLAIN ANALYZE запроса
План запроса с реальным временем и буферами
psql -U myuser -d mydb -c 'EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE status = $1;'
Медленные запросы
Топ 10 запросов с наибольшим суммарным временем выполнения
psql -U postgres -d mydb -c "SELECT round(total_exec_time::numeric,2) ms, calls, round((total_exec_time/calls)::numeric,2) avg_ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"
Активные запросы
Выполняющиеся запросы с длительностью и SQL
psql -U postgres -c "SELECT pid, now()-query_start AS duration, state, left(query,80) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC;"
Убить долгий запрос
Завершить все запросы выполняющиеся дольше 5 минут
psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE now()-query_start > interval '5 min' AND state = 'active';"
Размеры таблиц
Топ 20 таблиц по размеру включая индексы и TOAST
psql -U myuser -d mydb -c "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) size FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 20;"
Использование индексов
Какие индексы используются и как часто
psql -U myuser -d mydb -c "SELECT relname, indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes ORDER BY idx_scan DESC LIMIT 20;"
Неиспользуемые индексы
Индексы которые ни разу не использовались — кандидаты на удаление
psql -U myuser -d mydb -c "SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND NOT indisprimary ORDER BY pg_relation_size(indexrelid) DESC;"
Дамп базы данных
Дамп в custom-формате (быстрейший, поддерживает параллельное восстановление)
pg_dump -U myuser -d mydb -F c -f mydb.dump
Восстановить базу
Параллельное восстановление 4 потоками с удалением существующих объектов
pg_restore -U myuser -d mydb -j 4 --clean mydb.dump
Vacuum и analyze таблицы
Освободить мёртвые кортежи и обновить статистику для планировщика
psql -U myuser -d mydb -c 'VACUUM (VERBOSE, ANALYZE) orders;'
Лаг репликации
Состояние репликации и лаг в байтах по репликам
psql -U postgres -c "SELECT client_addr, state, sent_lsn, replay_lsn, (sent_lsn - replay_lsn) AS lag_bytes FROM pg_stat_replication;"
Блокировки
Активные блокировки с состоянием и запросом
psql -U postgres -d mydb -c "SELECT pid, relation::regclass, mode, granted, left(query,60) FROM pg_locks l JOIN pg_stat_activity a USING(pid) WHERE relation IS NOT NULL ORDER BY granted;"
Поиск долгих запросов
Показать активные запросы, работающие дольше 5 минут, начиная с самых долгих
SELECT pid, now()-query_start AS dur, state, query FROM pg_stat_activity WHERE state='active' AND now()-query_start > interval '5 min' ORDER BY dur DESC;
Завершить зависший backend
Принудительно завершить процесс по pid; note: сначала пробуйте pg_cancel_backend, чтобы отменить только запрос
SELECT pg_terminate_backend(<pid>); -- graceful first: SELECT pg_cancel_backend(<pid>);
Топ запросов по общему времени
pg_stat_statements: ранжирование запросов по суммарному времени выполнения
SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
EXPLAIN с буферами
Выполнить запрос с реальными таймингами и попаданиями в буферы, чтобы увидеть дисковый I/O
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ... ; -- shared hit/read shows cache vs disk I/O
Дерево блокировок
Показать процессы, ждущие блокировок, и какие pid их блокируют
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
Лаг репликации в байтах
Измерить лаг реплики в байтах и интервалы write/flush/replay
SELECT client_addr, state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes, write_lag, flush_lag, replay_lag FROM pg_stat_replication;
Самые большие базы
Размер каждой базы в человекочитаемом виде, начиная с самых больших
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;
Размер таблиц и индексов
Топ таблиц по полному размеру с учётом индексов и TOAST
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total, pg_size_pretty(pg_relation_size(oid)) AS table FROM pg_class WHERE relkind='r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 20;
Поиск неиспользуемых индексов
Индексы, которые ни разу не сканировались (idx_scan=0); кандидаты на удаление для освобождения места
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes WHERE idx_scan=0 ORDER BY pg_relation_size(indexrelid) DESC;
Проверка автовакуума
Найти таблицы с мёртвыми кортежами и давно не запускавшимся автовакуумом
SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;
Перестройка индекса без блокировки
Перестроить распухший индекс онлайн; note: нельзя выполнять внутри транзакции
REINDEX INDEX CONCURRENTLY <name>; -- note: cannot run inside a transaction block
Создание индекса онлайн
Создать индекс без блокировки записи; отслеживать через pg_stat_progress_create_index
CREATE INDEX CONCURRENTLY idx_<name> ON <table>(col); -- monitor: SELECT * FROM pg_stat_progress_create_index;
Параллельный дамп и восстановление
Параллельный дамп/восстановление в custom-формате; note: -j требует custom или directory формат, а не обычный SQL
pg_dump -Fc -j 4 -d <name> -f db.dump && pg_restore -j 4 -d <name> db.dump
Нагрузочный тест pgbench
Запустить 60-секундный нагрузочный тест: 32 клиента, 8 потоков, отчёт каждые 5с
pgbench -c 32 -j 8 -T 60 -P 5 -d <name>; -- init once: pgbench -i -s 100 <name>
no commands match