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

  • Определение размеров таблиц в базе данных PostgreSQL
  • Блокировки БД по пользователям
  • Восстановить БД из дампа
  • Запустить sql скрипт на БД
  • Запустить sql скрипт на БД и перенаправить вывод в файл
  • Выполнить sql запрос на БД
  • Вывод в html формате
  • Переключение режима проверки ограничений (сonstraints)
  • Переключение формата вывода бинарных данных в ‘escape’ формат (PostgreSQL 9)
  • Статистика которую можно получить из базы данных
  • Запросы мониторинга 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’;

select relation::regclass, mode, a.usename, granted, pid from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not mode = ‘AccessShareLock’ and relation is not null;

Реклама

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s