Полезнейшее про PostgreSQL

Определение размеров таблиц в базе данных PostgreSQL

SELECT tableName, pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) as size
from pg_tables
where tableName not like 'sql_%'
order by size;

Можно так же вывести все таблицы, размером больше 10 Мб:

SELECT tableName, pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) as size
from pg_tables
where tableName not like 'sql_%' and pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) like '%MB%';

Блокировки БД по пользователям

select a.usename, count(l.pid) from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not(mode = 'AccessShareLock') group by a.usename;

Восстановить БД из дампа

psql --host=localhost --username=someuser -f /path/to/pgdumpall.sql

Запустить sql скрипт на БД

psql -h localhost -U someuser -d somedb -f /path/to/somefile.sql

Запустить sql скрипт на БД и перенаправить вывод в файл

psql -h localhost -U someuser -d somedb -f /path/to/scriptfile.sql -o /path/to/outputfile.txt

Выполнить sql запрос на БД

psql -U postgres -d pagila -c "CREATE TABLE test(some_id serial PRIMARY KEY, some_text text);"

Вывод в html формате

psql -h someserver -p 5432 -U someuser -d somedb -H -c "SELECT * FROM sometable" -o mydata.html

Переключение режима проверки ограничений (сonstraints)

Проеврка ограничений после каждого запроса:

SET CONSTRAINTS ALL IMMEDIATE;

Проверка ограничений в конце транзакции:

SET CONSTRAINTS ALL DEFERRED;

Переключение формата вывода бинарных данных в 'escape' формат (PostgreSQL 9)

ALTER DATABASE "database_name" SET bytea_output TO 'escape';

 

Статистика которую можно получить из базы данных

 

Таблица	Описание
pg_stat_activity	Каждая строка показывает: процесс сервера, OID базы данных, имя базы данных, ID процесса, OID пользователя, имя пользователя, имя приложения, адрес клиента и порт,время, текущею транзакцию, текущий запрос, статус процесса, текст запроса. Колонки показывающие данные текущего запроса доступны если параметр track_activities включен. Эти колонки доступны только для суперпользователя или пользователя владельца процесса.
pg_stat_bgwriter	One row only, showing cluster-wide statistics from the background writer: number of scheduled checkpoints, requested checkpoints, buffers written by checkpoints and cleaning scans, and the number of times the background writer stopped a cleaning scan because it had written too many buffers. Also includes statistics about the shared buffer pool, including buffers written by backends (that is, not by the background writer) and total buffers allocated.
pg_stat_database	Одна строка: OID базы данных, имя базы данных, количество процессов подключенных к базе, кол-во транзакций примененных и отмененных, количество прочитанных блоков, количество попаданий в буфер, количество выбранных, переданных, добавленных, обновленных и удаленных строк.
pg_stat_all_tables	Для каждой таблицы в текущей базе данных (включая TOAST таблицы): OID таблицы, схема и имя таблицы, количество последовательны просмотров, количество строк выбранных запросами, количество просмотров индексов (все индексы данной таблицы), количество строк выбранных через сканирование индексов, количество: пересечений строк, обновленных, удаленных строк, количество обновленных HOT строк, количество живых и мертвых строк, время последнего ручного vacuum, время последнего автоматического vacuum, время последнего ручного analyze, время последнего автоматического analyze.
pg_stat_sys_tables	То же что и pg_stat_all_tables, только системные таблицы
pg_stat_user_tables	То же что и pg_stat_all_tables, только пользовательские таблицы
pg_stat_all_indexes	Для каждого индекса текущей базы: OID таблицы и OID, схема, имя таблица и индекса, количество просмотров индекса, количество записей возвращенных при сканировании индекса, количество живых строк таблицы полученных простым сканированием индексов используя этот индекс.
pg_stat_sys_indexes	То же что и pg_stat_all_indexes, только системные таблицы
pg_stat_user_indexes	То же что и pg_stat_all_indexes, только пользовательские таблицы
pg_statio_all_tables	Для каждой таблицы текущей базы данных (включая TOAST таблицы), OID таблицы, схема и имя таблицы, количество блоков прочитанных с диска, количество попаданий в буфер, количество блоков прочитанных с диска и попавших в буфер для всех индексов таблицы, количество блоков прочитанных с диска и попавших в буфер that table's auxiliary TOAST table (if any), количество блоков прочитанных с диска и попавших в буфер для индекса TOAST таблиц.
pg_statio_sys_tables	То же что и pg_statio_all_tables, только системные таблицы
pg_statio_user_tables	То же что и pg_statio_all_tables, только пользовательские таблицы
pg_statio_all_indexes	Для каждого индекса текущей базы данных: OID таблицы и индекса, имя таблицы и индекса, количество блоков прочитанных с диска и попаданий в буфер.
pg_statio_sys_indexes	То же что и pg_statio_all_indexes, только системные таблицы
pg_statio_user_indexes	То же что и pg_statio_all_indexes, только пользовательские таблицы
pg_statio_all_sequences	Для каждой последовательности в текущей базе данных: OID последовательности, схема и имя последовательности, количество прочитанных блоков с диска и попаданий в буфер.
pg_statio_sys_sequences	То же что и pg_statio_all_sequences, только системные таблицы
pg_statio_user_sequences	То же что и pg_statio_all_sequences, только пользовательские таблицы
pg_stat_user_functions	For all tracked functions, function OID, schema, name, number of calls, total time, and self time. Self time is the amount of time spent in the function itself, total time includes the time spent in functions it called. Time values are in milliseconds.
pg_locks	Информация о блокировках в базе

 

Запросы мониторинга PostgreSQL

 

Количество подключений по пользователям:

select count(usename), usename from pg_stat_activity group by usename order by count(usename) desc;

Количество блокировок по пользователям:

select a.usename, count(l.pid) from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not(mode = 'AccessShareLock') group by a.usename;

Количество блокировок по пользователям и по типам:

select a.usename, count(l.pid), mode from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid group by a.usename, mode order by mode, count(l.pid);

Запросы с эксклюзивными блокировками:

select a.usename, a.current_query, mode from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where mode ilike '%exclusive%';

Пользователи блокирующие конкретную таблицу:

select a.usename, t.relname, a.current_query, mode from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid inner join pg_stat_all_tables t on t.relid=l.relation where t.relname = 'tablename';

Оригинал