Инструменты пользователя

Инструменты сайта


postgresql

Различия

Здесь показаны различия между двумя версиями данной страницы.

Ссылка на это сравнение

postgresql [2018/07/09 14:50]
postgresql [2018/11/05 23:17] (текущий)
Строка 1: Строка 1:
 +====== PostgreSQL ======
 +
 +{{htmlmetatags>​
 +metatag-keywords=(PostgreSQL,​ epp)
 +metatag-description=(В статье практические примеры ускорения (tuning) PostgreSQL. Индексирование,​ резервное копирование БД, хранимые процедуры,​ функции,​ триггеры PostgreSQL.)
 +}}
 +
 +{{ ::​postgresql-helps.png?​nolink&​400 |}}
 +
 +Homepage: [[http://​www.postgresql.org|PostgreSQL]]
 +  * [[Запросы для анализа PostgreSQL]]
 +
 +  * Tuning PostgreSQL [[postgresql.conf]]
 +  * [[Резервное копирование и восстановление в PostgreSQL]]. WAL. Как бэкапить данные в PostgreSQL?
 +  * [[Репликация PostgreSQL]]:​ Bucardo, [[SkyTools]]:​ Londiste и т.д.
 +  * [[Индекс в базах данных]]:​ INDEX, REINDEX. HOT.
 +  * [[Типы данных PostgreSQL]]. [[http://​postgresql.ru.net/​manual/​ddl-constraints.html#​AEN2599|CONSTRAINT Ограничения целостности]].
 +  * [[Хранимые процедуры,​ функции и триггеры]]
 +  * [[EXPLAIN]]
 +  * [[Табличные пространства PostgreSQL]]
 +
 +  * **[[pg_hba.conf]]** - настройка политики доступа к базам данных и идентификации пользователей
 +  * **pg_config** - эта утилита выводит параметры (например,​ с какими параметрами скомпилирован PostgreSQL) конфигурации текущей установленной версии PostgreSQL. Находится в пакете postgresql-devel (для ОС [[Fedora]]) ​ postgresql-server-dev-8.3(для ОС Debian 5).
 +  * **pg_ctl** является утилитой для запуска,​ остановки,​ перезапуска,​ перезагрузки конфигурационных файлов,​ информирования о состоянии сервера PostgreSQL, или отправки сигналов PostgreSQL- процессу. Показать статус севера:<​file>​
 +# sudo -u postgres pg_ctl status -D /​var/​lib/​pgsql/​data</​file>​
 +  * **[[postgresql.conf]]** - настройки сервера.
 +
 +**PostgreSQL** (произносится «Постгре-Эс-Кю-Эль»,​ коротко называется «Постгрес») - свободная объектно-реляционная система управления базами данных ([[СУБД]]). Использует порт 5432/​tcp/​udp. PostgreSQL использует только один механизм хранения данных под названием **Postgres storage system** (система хранения Postgres), в котором транзакции и внешние ключи полностью функциональны,​ в отличии от [[MySQL]], в котором InnoDB и BDB являются единственными типами таблиц,​ которые поддерживают транзакции.
 +
 +По умолчанию PostgreSQL настроен так, что каждый локальный пользователь может подсоединиться к базе совпадающей по названию с регистрационным именем клиента,​ при условии что такая база данных уже создана.
 +
 +<note tip>​Все объекты (таблицы,​ индексы ...) базы данных в PostgreSQL хранятся в каталоге data/​base/​OID,​ т.е. названием каталога содержащего БД, будет не имя БД (как в [[MySQL]]), а номер (OID) БД.</​note>​
 +
 +**MVCC** - одна из ключевых технологий доступа к данным,​ которая используется в PostgreSQL. Она позволяет осуществлять параллельное чтение и изменение записей (tuples) одних и тех же таблиц без блокировки этих таблиц. Чтобы иметь такую возможность,​ данные из таблицы сразу не удаляются,​ а лишь помечаются как удаленные. Изменение записей осуществляется путем маркировки этих записей как удаленных,​ и созданием новых записей с измененными полями. Таким образом,​ история изменений одной записи сохраняется в базе данных и доступна для чтения другими транзакциями. Этот способ хранения записей позволяет параллельным процессам иметь неблокирующий доступ к записям,​ которые были удалены или изменены в параллельных незакрытых транзакциях. Техника,​ используемая в этом подходе,​ относительно простая. У каждой записи в таблицы есть системные скрытые поля xmin, xmax.
 +  * **xmin** - хранит номер транзакции,​ в которой запись была создана.
 +  * **xmax** - хранит номер транзакции,​ в которой запись была удалена или изменена.
 +Перед началом выборки данных PostgreSQL сохраняет снапшот текущего состояния БД. На основании данных снапшота,​ полей xmin, xmax осуществляется фильтрация записей.
 +
 +====== pg_hba.conf идентификация пользователей ======
 +**pg_hba.conf** - настройка политики доступа к базам данных и идентификации пользователей сервера [[PostgreSQL]].
 +
 +В этом файле описываются клиентские компьютеры сети, с которых разрешен доступ к [[SQL]] серверу [[PostgreSQL]],​ а также методы идентификации клиентов. Этот файл может содержать два вида записей:​
 +  - Запись вида "​host"​. host <имя базы данных> ​ <​маска адреса>​ <​способ авторизации доступа>​ [аргумент для авторизации]
 +  - Запись типа "​local"​. Эта запись определяет авторизацию доступа к базе данных локальных пользователей. Идентично "​host",​ за исключением того, что IP- адрес и маска адреса опущены за ненадобностью.
 +
 +Параметр <​способ авторизации доступа>:​
 +  * **trust** в этом режиме авторизации доступа не производится. Соединение считается доверительным.
 +
 +<​note>​Каждая запись в файле pg_hba.conf должна полностью умещаться в одной строке. Перенос записей на другую строку запрещен.</​note>​
 +
 +**Примеры записей pg_hba.conf**:​
 +  * Разрешить всем пользователям доступ с любого хоста к всем базам данных по логину и паролю<​file>​
 +# TYPE  DATABASE ​       USER            ADDRESS ​                ​METHOD
 +host    all             ​all ​            ​all ​                    md5
 +</​file>​
 +  * Разрешить пользователю(dbuser) доступ к базе данных(mother) с любого хоста по логину и паролю <​file>​
 +host   ​mother ​   dbuser ​          ​all ​                  md5
 +</​file>​
 +
 +===== Кодировка БД PostgreSQL и locale =====
 +PostgreSQL поддерживает только общую для всех баз кластера кодировку,​ которая должна совпадать с локальной кодировкой ([[locale]]),​ иначе не будут работать строковые функции сортировки,​ upper/lower и т.п. [[|Локаль]] общая для всех процессов сервера - соответственно он не может создать две базы в разных кодировках - кодировка всегда одна для всего сервера и всех его БД.
 +
 +Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):<​file>​
 +postgres=# show server_encoding;​
 + ​server_encoding ​
 +-----------------
 + UTF8
 +(1 row)
 +</​file>​
 +
 +Т.е. создать базу в другой кодировке можно, но тогда в ней будут неправильно работать функции обработки строк и сортировка строк.
 +
 +Указывать список кодировок нужно не для createdb (create database), а для подключения клиента к серверу (client_encoding),​ если кодировка символов которую ожидает программа-клиент не совпадает с её (программы-клиента) текущей системной локалью,​ с которой она была запущена.
 +====== Клиенты администрирования PostgreSQL ======
 +  * [[pgAdmin]] - GUI для PostgreSQL. **Лучший графический клиент** из существующих.
 +  * **Tora**. Homepage: [[http://​www.torasql.com|Tora]] - графический инструмент для проектировщиков баз данных и администраторов. Tora позволяет просматривать схемы баз данных,​ составлять и отлаживать PL /​SQL-запросы,​ управлять дисками,​ следить за откатами и просматривать вывод [[SQL]]. Благодаря Qt4 может непосредственно работать с [[PostgreSQL]] и [[MySQL]], а с другими СУБД — через ODBC.<​file>​
 +# aptitude install tora odbc-postgresql
 +</​file>​
 +  * [[phpPgAdmin]] - веб- приложение с открытым кодом, написанное на языке [[PHP]] и представляющее собой веб- интерфейс для администрирования СУБД PostgreSQL.
 +  * **OpenOffice и SDBC**. Для прямого доступа из OpenOffice к PostgreSQL без промежуточного уровня в виде ODBC/JDBC драйверов разрабатывается postgresql-sdbc драйвер.<​file>​
 +# aptitude install openoffice.org-sdbc-postgresql
 +</​file>​
 +  * **[[http://​www.dbvis.com|DbVisualizer]]**
 +====== psql ======
 +**psql** - PostgreSQL interactive terminal.
 +  * [[Дополнительные возможности клиента psql]]
 +  * [[Пример. Создать БД, пользователя,​ таблицу,​ установить права.]]
 +  * [[wpru>​PL/​pgSQL]] ( (Procedural Language/​PostGres Structured Query Language) - процедурное расширение языка SQL, используемое в СУБД PostgreSQL.
 +  * [[Как в PostgreSQL сохранить данные в формате csv]]?
 +
 +В директории /​usr/​share/​doc/​postgresql* можно найти дополнительную информацию по запуску.
 +  * Подключиться к локальному серверу.<​file>​
 +$ sudo -u postgres psql
 +psql (8.4.5)
 +Type "​help"​ for help.
 +
 +postgres=#
 +</​file>​
 +  * Подключиться к удаленному серверу.<​file>​
 +$ psql -h 192.168.1.20 -U postgres
 +</​file>​
 +  * В psql узнать подробности об определённом SQL команде можно при помощи \h (ключ \? выводит помощь по мета-командам),​ например для drop table:<​file>​
 +postgres=# \h drop table
 +Command: ​    DROP TABLE
 +Description:​ remove a table
 +Syntax:
 +DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
 +</​file>​
 +  * Пример выполнения SQL запроса из командой строки<​file>​
 +# sudo -u postgres psql -d mybd --command="​select * from pg_stat_user_indexes where schemaname='​public';"​
 +</​file>​
 +  * **Как в PostgreSQL посмотреть список пользователей?​**<​file>​
 +   ​postgres=#​ select * from pg_shadow;
 +   ​postgres=#​ \du
 +</​file>​
 +  * **Вывести список баз данных:​**<​file>​
 +   ​postgres=#​ select * from pg_database;​
 +или
 +   ​postgres=#​ \l
 +Из командной строки:​
 +   # psql -U postgres -A -q -t -c "​select datname from pg_database"​
 +или:
 +   # psql -l
 +</​file>​
 +  * **Вывести название текущей базы данных:​**<​file>​
 +SELECT current_database();​
 +</​file>​
 +  * **Вывести имя (идентификатор) текущего пользователя:​**<​file>​
 +SELECT current_user;​
 +</​file>​
 +  * **Вывести список таблиц активной базы данных (если после ключа \d указать имя таблицы - будет выведена структура таблицы):​**<​file>​
 +postgres=# \dt
 +или
 +select * from pg_tables; # будут выведены все таблицы,​ в том числе и системные
 +select tablename from pg_tables where schemaname='​public';​
 +</​file>​
 +  * **Удалить БД** testbd777<​file>​
 +# sudo -u postgres dropdb testbd777
 +</​file>​
 +  * **Залить(восстановить) данные в БД c именем test777**<​file>​
 +# sudo -u postgres psql test777 < lost_cdr.sql
 +</​file>​
 +  * **regexp_split_to_table**. В текстовом поле ipaddr хранятся IP адреса разделенные ; т.е. 1 строка - много IP. Запрос выводит все IP в столбик т.н. 1 строка - 1 IP<​file>​
 +SELECT regexp_split_to_table(ipaddr,​ E';'​) as ip FROM peers;
 +</​file>​
 +  * **Вывести версию Postgresql**:​ <​file>​
 +postgres=# SELECT version();
 + ​PostgreSQL 8.4.8 on x86_64-redhat-linux-gnu, ​
 +compiled by GCC gcc (GCC) 4.4.5 20101112 (Red Hat 4.4.5-2), 64-bit
 +</​file>​
 +  * **Пример экранирования** одинарной кавычки в слове Кот-д'​Ивуар. В [[PHP]] для экранирования спецсимволов используется функция pg_escape_string().<​file>​
 +$ psql -U postgres -d mydb --command="​INSERT INTO countries (english,​russian) ​
 +VALUES ('​Ivory Coast',​ '​Кот-д''​Ивуар'​);"​
 +</​file>​
 +  * **Для смены активной базы данных в psql, применяется ключ \с**
 +  * **Вывести время запуска сервера и uptime**<​file>​
 +SELECT pg_postmaster_start_time();​
 +   ​pg_postmaster_start_time ​   ​
 +-------------------------------
 + ​2013-08-21 14:​20:​45.451467+00
 +
 +SELECT current_timestamp - pg_postmaster_start_time() AS uptime;
 +</​file>​
 +
 +===== Посмотреть и удалить активные запросы =====
 +Если запрос запущен из интерфейса pgsql, то завершение работы сервера не поможет - запрос все равно продолжит свое выполнение,​ необходимо вызывать функцию [[http://​www.postgresql.org/​docs/​8.4/​interactive/​functions-admin.html|pg_cancel_backend]].<​file>​
 +select * from pg_stat_activity;​ # посмотреть все запросы
 +select * from pg_stat_activity WHERE current_query like '​SELECT%';​ # посмотреть все SELECT запросы
 +select * from pg_stat_activity WHERE current_query like '​INSERT%';​
 +
 +# снять все активные select запросы
 +SELECT pg_cancel_backend(procpid) as x FROM pg_stat_activity WHERE current_query like '​SELECT%';​
 +
 +# снять запрос VACUUM
 +SELECT pg_cancel_backend(procpid) as x FROM  pg_stat_activity WHERE current_query like '​VACUUM%';​
 +</​file>​SELECT запросы можно снимать из ОС командой kill<​file>​
 +# ps auxww | grep ^postgres
 +...
 +postgres 15724 97.7 11.3 2332996 1871476 ?     ​Rs ​  ​07:​50 ​  1:53 postgres: postgres mybd 127.0.0.1(53624) SELECT
 +...
 +# kill 15724
 +</​file>​
 +<note important>​procpid содержит PID процесса,​ которому можно сделать kill при необходимости. Например PID можно узнать запросом(отсортируем по длительности выполнения)<​file>​
 +select datname,​procpid,​now()-query_start as duration,​current_query from pg_stat_activity order by duration DESC;
 +</​file></​note>​
 +
 +
 +====== Транзакции в PostgreSQL ======
 +В PostgreSQL [[транзакция]] - это список команд [[SQL]], которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.
 +
 +PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.
 +
 +Пример запуска транзакции из файла delprices.sql,​ которая удаляет в БД test777 из таблиц prices и ratesheets строки с id=2<​file>​
 +# nano delprices.sql
 +BEGIN;
 +DELETE FROM prices WHERE ratesheet_id=2;​
 +DELETE FROM ratesheets WHERE id=2;
 +COMMIT;
 +</​file>​Выполним транзакцию для test777:<​file>​
 +# sudo -u postgres psql -l
 +# sudo -u postgres psql test777 < delprices.sql
 +</​file>​
 +
 +====== Мониторинг,​ логи, размер БД PostgreSQL ======
 +  * [[Размер базы данных PostgreSQL]],​ таблицы,​ столбца,​ количество строк. **Мониторинг использования диска.**
 +===== Лог файлы =====
 +**Лог файлы** PostgreSQL находятся в директории pg_log, для Fedora полный путь /​var/​lib/​pgsql/​data/​pg_log. Детализация лог файлов настраивается в [[postgresql.conf#​error_reporting_and_logging|postgresql.conf]].
 +===== Мониторинг =====
 +  * [[zabbix?&#​zabbix_i_postgresql|Zabbix и PostgreSql]]
 +  * [[http://​www.postgresql.org/​docs/​8.4/​interactive/​monitoring-stats.html|Monitoring Database Activity]]
 +  * [[http://​pgfouine.projects.postgresql.org/​|pgFouine - a PostgreSQL log analyzer]]
 +  * bucardo.org [[check_postgres]] - Perl cкрипт для мониторинга более 20 параметров,​ определяющих состояние СУБД PostgreSQL.
 +
 +Текущую активность базы данных легко оценить с помощью команды ps, для вывода в реальном времени (с задержкой 1 секунда) можно использовать утилиту [[watch]]:
 +<​file>​
 +# watch -n 1 'ps auxww | grep ^postgres'​
 +postgres 14164  0.0  0.0 188492 ​ 5296 ?        S    Dec13   0:46 /​usr/​bin/​postmaster -p 5432 -D /​var/​lib/​pgsql/​data
 +postgres 14166  0.0  0.0 159904 ​ 1264 ?        Ss   ​Dec13 ​  0:05 postgres: logger process ​                         ​
 +postgres 14168  0.0  0.1 188636 27208 ?        Ss   ​Dec13 ​  0:49 postgres: writer process ​                         ​
 +postgres 14169  0.0  0.0 188492 ​ 1348 ?        Ss   ​Dec13 ​  0:23 postgres: wal writer process ​                     ​
 +postgres 14170  0.0  0.0 188804 ​ 1752 ?        Ss   ​Dec13 ​  0:17 postgres: autovacuum launcher process ​            
 +postgres 14171  0.0  0.0 160176 ​ 1468 ?        Ss   ​Dec13 ​  0:45 postgres: stats collector process ​                
 +postgres 21596  0.0  0.1 190228 30476 ?        Ss   ​Dec27 ​  0:58 postgres: postgres mbill 127.0.0.1(37047) idle    ​
 +postgres 21597  0.0  0.0 189716 ​ 5672 ?        Ss   ​Dec27 ​  0:00 postgres: postgres mbill 127.0.0.1(37048) idle
 +</​file> ​   ​
 +Так как для каждого клиента создаётся своя копия процесса postmaster, то это позволяет подсчитать число активных клиентов. Статусная строка даёт информацию о состоянии клиента. Фразы writer process, stats buffer process и stats collector process соответствуют системным процессам,​ запущенным самим PostgreSQL при старте. Пользовательские процессы имеют статусную строку вида:<​file>​
 +postgres: «пользователь» «база» «хост» «статус»
 +</​file>​«пользователь»,​ «база» и «хост» соответствуют имени пользователя «пользователь» подсоединявшегося к базе «база» с компьютера «хост». «статус» может принимать следующие параметры:​
 +  * idle - ожидание команды от клиента,​
 +  * idle in transaction - ожидание команды от клиента внутри транзакции (между BEGIN и окончанием [[транзакция|транзакции]]),​
 +  * [[SQL]]- команда - выполняется эта команда,​ например,​ SELECT,
 +  * waiting - ждём когда разблокируется занятая другим процессом таблица. Для уточнения из-за чего возникла блокировка,​ нужно анализировать представление pg_locks.
 +===== Views сборщик статистики =====
 +**Представления (Views) сборщика статистики.**
 +  * [[http://​www.postgresql.org/​docs/​8.4/​interactive/​monitoring-stats.html|Statistics Collector (Standard Statistics Views)]]: описание системных таблицах,​ собирающих информацию об активности базы данных.
 +
 +Если в [[postgresql.conf]] разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.
 +
 +Информация собранная "​статистическим сборником"​ может оказаться полезной для оценки эффективности базы данных и запросов. Из этих представлений можно узнать,​ например
 +  * Для каких таблиц стоит создать новые индексы (индикатором служит большое количество полных просмотров и большое количество прочитанных блоков).
 +  * Какие индексы вообще не используются в запросах. Их имеет смысл удалить,​ если, конечно,​ речь не идёт об индексах,​ обеспечивающих выполнение ограничений PRIMARY KEY и UNIQUE.
 +  * Достаточен ли объём буфера сервера.
 +  * Возможен "​дедуктивный"​ подход,​ при котором сначала создаётся большое количество индексов,​ а затем неиспользуемые индексы удаляются.
 +
 +<note warning>​Для [[http://​www.postgresql.org/​docs/​8.4/​static/​monitoring-stats.html|сброса системной статистики]](файл pgstat.stat) в ноль применяется команда<​file>​
 +select pg_stat_reset();​
 +</​file></​note>​
 +
 +
 +**Стандартные Statistics Views**. Вывести все представления каталога<​file>​
 +select schemaname,​viewname,​viewowner from pg_views where schemaname='​pg_catalog';​
 +</​file>​
 +  * **pg_stat_activity** - Каждая строка показывает:​ процесс сервера,​ OID базы данных,​ имя базы данных,​ ID процесса,​ OID пользователя,​ имя пользователя,​ имя приложения,​ адрес клиента и порт,​время,​ текущею транзакцию,​ текущий запрос,​ статус процесса,​ текст запроса. Колонки показывающие данные текущего запроса доступны если параметр track_activities включен. Эти колонки доступны только для суперпользователя или пользователя владельца процесса.<​file>​
 +select * from pg_stat_activity;​
 +# Вывести запросы отсортированные по длительности выполнения
 +select datname,​client_addr,​now()-query_start as duration,​current_query from pg_stat_activity order by duration DESC;
 +</​file>​
 +  * pg_stat_bgwriter
 +  * pg_stat_database -  Одна строка на кажду БД. Выводит OID базы данных,​ имя базы данных,​ количество процессов подключенных к базе(**numbackends**),​ кол-во транзакций примененных(**xact_commit**) и отмененных(**xact_rollback**),​ количество прочитанных блоков(**blks_read**),​ количество попаданий в буфер(**blks_hit**),​ количество выбранных(**tup_returned**),​ переданных(**tup_fetched**),​ добавленных(**tup_inserted**),​ обновленных(**tup_updated**) и удаленных строк(**tup_deleted**).<​file>​
 +select * from pg_stat_database;​
 +</​file>​**Вывести соотношение hit/read**. При выполнении запроса PostgreSQL сначала смотрит,​ есть ли нужные в запросе данные в разделяемой памяти ([[postgresql.conf|shared buffers]]). Если они найдены,​ засчитывается hit, если нет - делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем,​ верно правило:​ чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь,​ однако,​ искусственно завысить настройку shared_buffers,​ которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить,​ что fread может быть довольно быстрым,​ если данные находятся в дисковом кеше ОС.<​file>​
 +select datname, case when blks_read = 0 then 0 else blks_hit/​blks_read end as ratio from pg_stat_database;​
 +</​file>​
 +  * **pg_stat_all_tables** - Для каждой таблицы в текущей базе данных (включая TOAST таблицы):​ OID таблицы(**relid**),​ схема(**schemaname**) и имя таблицы(**relname**),​ количество последовательны просмотров(**seq_scan**),​ количество строк выбранных запросами(**seq_tup_read**),​ количество просмотров индексов (все индексы данной таблицы)(**idx_scan**),​ количество строк выбранных через сканирование индексов(**idx_tup_fetch**),​ количество:​ пересечений строк(**n_tup_ins**),​ обновленных(**n_tup_upd**),​ удаленных строк(**n_tup_del**),​ количество обновленных HOT строк(**n_tup_hot_upd**),​ количество живых(**n_live_tup**) и мертвых строк(**n_dead_tup**),​ время последнего ручного vacuum, время последнего автоматического vacuum, время последнего ручного analyze, время последнего автоматического analyze. Запросы для активной БД:<​file>​
 +select * from pg_stat_all_tables where schemaname='​public';​
 +select relname,​last_vacuum,​last_autovacuum,​last_analyze,​last_autoanalyze from pg_stat_all_tables where schemaname='​public';​
 +</​file>​
 +  * pg_stat_sys_tables - То же что и pg_stat_all_tables,​ только системные таблицы.
 +  * pg_stat_user_tables - То же что и pg_stat_all_tables,​ только пользовательские таблицы. **Статистика seq scan/index scan**. Список по таблицам:​ какое количество запросов к ним было выполнено посредством последовательного просмотра;​ какое количество запросов было выполнено с использованием индексов;​ а также отношение этих двух чисел. Позволяет оценить,​ все ли нужные индексы созданы в данной таблице. Если ваши таблицы содержат более нескольких тысяч рядов, последовательный просмотр будет выполняться медленнее просмотра индекса,​ поэтому в идеальном случае seqscan-ов в таких таблицах быть не должно. Если у вас они все же есть, анализируйте запросы к таким таблицам и создавайте соответствующие индексы. При этом важно не перестараться:​ чем больше индексов по колонкам таблицы,​ тем дороже становятся операции обновления данных. **Также не забывайте,​ что после создания индекса таблице нужно делать ANALYZE, иначе планировщик запросов не заметит изменений в структуре таблицы.**<​file>​
 +select relname,​seq_scan,​idx_scan,​ case when idx_scan = 0 then 100 else seq_scan/​idx_scan end as ratio from pg_stat_user_tables order by ratio desc;
 +select relname,​seq_scan,​idx_scan,​ case when idx_scan = 0 then 100 else seq_scan/​idx_scan end as ratio,​n_live_tup,​n_dead_tup from pg_stat_user_tables order by ratio desc;
 +</​file>​**Количество модификаций,​ произошедших в таблице**. Список по таблицам:​ какое количество записей в них было добавлено,​ изменено и удалено с момента последнего сброса статистики. Администратор БД должен представлять,​ какие таблицы являются самыми нагруженными в текущей базе данных,​ а также каково соотношение между различными типами модифицирующих запросов к ним.<​file>​
 +select relname,​n_tup_ins,​n_tup_upd,​n_tup_del from pg_stat_user_tables order by n_tup_ins desc;
 +</​file>​
 +  * **pg_stat_all_indexes** - Поможет оценить эффективность и частоту использования индексов при реальной работе. Для каждого индекса текущей базы: OID таблицы и OID, схема, имя таблица и индекса,​ количество просмотров индекса(**idx_scan**),​ количество записей возвращенных при сканировании индекса(**idx_tup_read**),​ количество живых строк(idx_tup_fetch) таблицы полученных простым сканированием индексов используя этот индекс.<​file>​
 +select * from pg_stat_all_indexes;​
 +</​file>​
 +  * pg_stat_sys_indexes - То же что и pg_stat_all_indexes,​ только системные таблицы.
 +  * pg_stat_user_indexes - То же что и pg_stat_all_indexes,​ только пользовательские таблицы.<​file>​
 +select * from pg_stat_user_indexes where schemaname='​public';​
 +</​file>​**Статистика по индексам**. Список по индексам:​ сколько записей из индекса были использованы в запросах по этому индексу;​ сколько рядов при этом получилось достать из родительской таблицы;​ разность этих двух чисел. Суть данной статистики проста:​ если у вас большая разница read-ов и fetch-ей, значит индекс устарел и ссылается на уже несуществующие данные,​ т.е. не всякий просмотр индекса и чтение из него соответствующего указателя на данные из таблицы (read) вызывает чтение самих данных из таблицы (fetch). В этом случае необходимо перестроить данный индекс,​ чтобы он соответствовал реальным данным в таблице.<​file>​
 +select relname,​indexrelname,​idx_tup_read,​idx_tup_fetch,​(idx_tup_read-idx_tup_fetch) as diff, CASE WHEN idx_tup_read=0 THEN 0 ELSE (idx_tup_read::​float4-idx_tup_fetch)/​idx_tup_read END as r FROM pg_stat_user_indexes ORDER BY r desc;
 +</​file>​
 +  * 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 - Значения времени указано в миллисекундах.
 +
 +  * [[pg_locks]] - информация о блокировках в базе данных.
 +===== Уровни блокировок таблиц =====
 +  * [[http://​www.postgresql.org/​docs/​8.4/​interactive/​explicit-locking.html|Explicit Locking]] - документация о типах блокировок в PostgreSQL.
 +Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу,​ пользуется нормальным доступом;​ последствия блокировки распространяются только на других пользователей,​ пытающихся получить доступ к заблокированной таблице.
 +<note tip>​Блокировка не означает отказа в доступе. С точки зрения пользователя,​ подключенного к базе данных и пытающегося обратиться к заблокированному ресурсу,​ блокировка приводит к задержке,​ но не к отказу в предоставлении доступа. Пользователю приходится ожидать либо завершения заблокированной команды пользователем,​ либо снятия блокировки с таблицы.</​note>​
 +
 +Некоторые команды [[SQL]] автоматически устанавливают блокировку для выполнения своих функций;​ в таких случаях PostgreSQL всегда выбирает минимально необходимый уровень блокировки. После завершения транзакции блокировка немедленно снимается.
 +
 +Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими,​ следует явно задать нужный режим.
 +<note tip>​Блокировка таблиц возможна только в транзакциях. Выполнение команды LOCK TABLE вне транзакционного блока не приводит к ошибке,​ но установленная блокировка немедленно снимается. Транзакция создается командой BEGIN; команда COMMIT фиксирует изменения в базе данных и снимает блокировку.</​note>​
 +
 +Ситуация взаимной блокировки (**deadlock**) возникает в там случае,​ когда каждая из двух транзакций ожидает снятия блокировки другой транзакцией. Хотя PostgreSQL распознает взаимные блокировки и завершает их командой ROLLBACK, это все равно причиняет определенные неудобства. Приложения не должны сталкиваться с проблемой взаимных блокировок,​ поэтому проектируйте их так, чтобы объекты всегда блокировались в одинаковом порядке.
 +
 +  * **ACCESS SHARE MODE**. Устанавливается автоматически командой SELECT для таблиц,​ из которых производится выборка данных. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровня ACCESS EXCLUSIVE MODE.
 +  * ROW SHARE MODE. Устанавливается автоматически командами SELECT, содержащими секцию FOR UPDATE или FOR SHARE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
 +  * ROW EXCLUSIVE MODE. Устанавливается автоматически командами UPDATE, INSERT и DELETE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и CREATE INDEX. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней SHARE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
 +  * SHARE UPDATE EXCLUSIVE MODE . Устанавливается автоматически командами VACUUM (без FULL), ANALYZE и CREATE INDEX CONCURRENTLY.
 +  * SHARE MODE. Устанавливается автоматически командами CREATE INDEX (без CONCURRENTLY). В заблокированных таблицах запрещается выполнение команд INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW EXCLUSIVE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
 +  * SHARE ROW EXCLUSIVE MOOE. Специальный режим блокировки,​ практически идентичный режиму EXCLUSIVE MODE, но допускающий установку параллельных блокировок уровня ROW SHARE MODE.
 +  * EXCLUSIVE MODE. Запрещает выполнение команд INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE и VACUUM, а также команд SELECT с секцией FOR UPDATE. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW SHARE MODE, ROW EXCLUSIVE MODE, SHARE MODE, SHARE ROW EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
 +  * ACCESS EXCLUSIVE MODE. Устанавливается автоматически командами ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц запрещаются любые команды или параллельные блокировки любого уровня.
 +===== Automatic Vacuuming =====
 +  * [[http://​www.postgresql.org/​docs/​8.4/​static/​sql-vacuum.html|VACUUM -- garbage-collect and optionally analyze a database]]
 +  * [[http://​www.postgresql.org/​docs/​8.4/​static/​app-vacuumdb.html|vacuumdb -- garbage-collect and analyze a PostgreSQL database]]
 +  * [[http://​www.postgresql.org/​docs/​8.4/​static/​sql-analyze.html|ANALYZE -- collect statistics about a database]]
 +  * [[http://​www.sql.ru/​forum/​actualthread.aspx?​tid=812939&​pg=-1|Vacuum Full без полного лока таблицы]]
 +
 +**Автоматическая сборка мусора (Automatic Vacuuming)**.
 +
 +**Синтаксис VACUUM:​**<​file>​
 +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
 +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
 +</​file>​
 +
 +**Синтаксис ANALYZE:​**<​file>​
 +ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]
 +</​file>​
 +
 +Кроме сборки мусора (VACUUM) производится ещё и анализ (ANALYZE). Периодическое выполнение команды ANALYZE необходимо для нормального функционирования планировщика. Собранная с помощью этой команды статистика позволяет значительно ускорить выполнение [[SQL]]- запросов. То есть, если не хочется настраивать автоматическую сборку мусора,​ то в любом случае её придётся делать только теперь в ручную. Процесс обычной сборки мусора в PostgreSQL (VACUUM без приставки FULL) не блокирует таблиц и может выполняться в фоне, не мешая выполнению запросов.
 +Регулярное исполнение команд VACUUM и ANALYZE обязательно. Это необходимо по той причине,​ что иначе не получится заново использовать дисковое пространство,​ которое занимают ранее удалённые или изменённые строки и не удастся обновить статистику для планировщика запросов. И то и другое отрицательно сказывается на эффективности использования ресурсов и производительности запросов. Начиная с версии PostgreSQL 8.1 сервер может самостоятельно автоматически запускать ещё один системный процесс,​ который,​ соответственно,​ так и называется autovacuum daemon. Все настройки для этого процесса хранятся в [[postgresql.conf]]. К значениям этих параметров следует отнестись крайне внимательно. Если по каким-то причинам демон было решено не запускать,​ то в любом случае необходимо производить сборку мусора и набор статистики в ручную.
 +
 +Основным средством физического и аналитического сопровождения баз данных в PostgreSQL является команда SQL **VACUUM** и ее аналог — сценарий **vacuumdb**. Оба средства выполняют две общие функции:​
 +  - удаление всех данных,​ оставшихся в результате отмены транзакций и других операций,​ оставляющих временные данные;​
 +  - анализ операций с базами данных,​ по результатам которого PostgreSQL конструирует более эффективные запросы.
 +
 +Синтаксис VACUUM:
 +<​file>​
 +=# \h VACUUM
 +Command: ​    ​VACUUM
 +Description:​ garbage-collect and optionally analyze a database
 +Syntax:
 +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
 +VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
 +
 +</​file>​
 +
 +При наличии необязательного ключевого слова ANALYZE PostgreSQL анализирует структуру данных во всех полях всех таблиц (или только заданной таблицы,​ если она указана),​ после чего эта информация используется оптимизатором запросов для более эффективного планирования. Ключевое слово ANALYZE также позволяет ограничить анализ отдельными полями.<​file>​
 +# sudo -u postgres psql mybd
 +mybd=# VACUUM ANALYZE VERBOSE;
 +</​file>​
 +<​note>​**Команда ANALYZE** служит для обновления информации о распределении данных в таблице. Эта информация используется оптимизатором для выбора наиболее быстрого плана выполнения запроса. Обычно команда используется в связке VACUUM ANALYZE. Если в базе есть таблицы,​ данные в которых не изменяются и не удаляются,​ а лишь добавляются,​ то для таких таблиц можно использовать отдельную команду ANALYZE. Также стоит использовать эту команду для отдельной таблицы после добавления в неё большого количества записей.</​note>​
 +
 +Практика показала,​ что без более-менее регулярных запусков vacuum full analyze производительность системы постепенно падает,​ причем чем дальше,​ тем больше. Разница между vacuum и vacuum full заключается в том, что full физически переписывает на диске всю таблицу таким образом,​ чтобы в ней не оставалось "​дырок"​ от удаленных или обновленных записей. Но его недостаток в том, что во время работы таблица полностью блокируется(включая и select запросы),​ что может привести к проблемам на популярном сервере - начнет скапливаться очередь запросов,​ ожидающих доступа к базе, каждый запрос требует памяти,​ память кончается,​ начинается запись в [[swap]], из-за отсутствия памяти сам vacuum тоже начинает использовать swap и все начинает работать очень-очень медленно. Простой VACUUM (Без FULL) просто восстанавливает пространство и делает его доступным для повторного использования. Эта форма команды умеет работать параллельно с обычными чтение и запись таблицы,​ без монопольной блокировки.
 +
 +Чтобы определить необходимость использования индекса для какой-либо таблицы,​ PostgreSQL должен иметь статистику по этой таблице. Эта статистика собирается при использовании VACUUM ANALYZE или просто ANALYZE. Используя статистику,​ оптимизатор узнает о том как много строк в таблице и если он должен использовать индексы,​ то он может принимать лучшие решения. Статистика также влияет на определение оптимального порядка соединений таблиц и метода соединения. При изменении содержимого таблицы должен периодически выполнятся сбор статистики.
 +
 +<​file>​
 +# vacuumdb -U postgres --verbose --analyze --full --dbname=mydb
 +# crontab
 +# Запуск "​vacuumdb"​ каждый день в 22:01
 +1 22 * * *  root vacuumdb -U postgres --quiet --analyze --dbname=mydb &
 +</​file>​
 +
 +====== Получение id добавленной записи в PostgeSQL ======
 +**lcr_id** - колонка автоинкрементная (lcr_id ​   | integer | not null default nextval('​df_lcr_list_lcr_id_seq'::​regclass)). Запрос возвращает значение колонки lcr_id для вставленной записи,​ в этом случае id записи.
 +<​file>​
 +INSERT INTO df_lcr_list (datestart,​dateend) VALUES (20120429,​20120429) RETURNING lcr_id;
 +</​file>​
 +
 +====== Системные таблицы pg_ ======
 +  * [[http://​www.postgresql.org/​docs/​8.4/​interactive/​catalogs.html|PostgreSQL 8.4 System Catalogs]]
 +  * [[http://​postgresql.ru.net/​manual/​ddl-system-columns.html|Системные колонки]]:​ oid, tableoid, xmin, cmin, xmax, cmax, ctid
 +Системные таблицы(System Catalogs) PostgreSQL начинаются с префикса pg_.
 +
 +^№^Имя таблицы^Назначение таблицы^^^^
 +|1|pg_aggregate|aggregate functions||||
 +|2|pg_am|index access methods||||
 +|3|pg_amop|access method operators||||
 +|4|pg_amproc|access method support procedures||||
 +|5|pg_attrdef|column default values||||
 +|6|pg_attribute|table columns ("​attributes"​)||||
 +|7|pg_authid|authorization identifiers (roles)||||
 +|8|pg_auth_members|authorization identifier membership relationships||||
 +|9|pg_cast|casts (data type conversions)||||
 +|10|[[pg_class]]|tables,​ indexes, sequences, views ("​relations"​)||||
 +|11|pg_constraint|check constraints,​ unique constraints,​ primary key constraints,​ foreign key constraints||||
 +|12|pg_conversion|encoding conversion information||||
 +|13|pg_database|databases within this database cluster|Хранятся имена доступных баз данных|||
 +|14|pg_depend|dependencies between database objects||||
 +|15|pg_description|descriptions or comments on database objects|В таблице хранятся описания объектов,​ для которых была применена функция COMMENT (расширение PostgreSQL). Например COMMENT ON TABLE mytable IS '​Эта строка будет сохранена в системной таблице pg_description.';​|||
 +|16|pg_enum|enum label and value definitions||||
 +|17|pg_foreign_data_wrapper|foreign-data wrapper definitions||||
 +|18|pg_foreign_server|foreign server definitions||||
 +|19|pg_index|additional index information||||
 +|20|pg_inherits|table inheritance hierarchy||||
 +|21|pg_language|languages for writing functions||||
 +|22|pg_largeobject|large objects||||
 +|23|[[pg_listener]]|asynchronous notification support|Используется механизмом LISTEN/​NOTIFY. pg_listener существует в версиях PostgreSQL меньше 9.|||
 +|24|pg_namespace|schemas||||
 +|25|pg_opclass|access method operator classes||||
 +|26|pg_operator|operators||||
 +|27|pg_opfamily|access method operator families||||
 +|28|pg_pltemplate|template data for procedural languages||||
 +|29|pg_proc|functions and procedures||||
 +|30|pg_rewrite|query rewrite rules||||
 +|31|pg_shdepend|dependencies on shared objects||||
 +|32|pg_shdescription|comments on shared objects||||
 +|33|pg_statistic|planner statistics||||
 +|34|pg_tablespace|tablespaces within this database cluster||||
 +|35|pg_trigger|triggers|Триггеры хранятся в системной таблице pg_trigger, что позволяет получить информацию о существующих триггерах на программном уровне.|||
 +|36|pg_ts_config|text search configurations||||
 +|37|pg_ts_config_map|text search configurations'​ token mappings||||
 +|38|pg_ts_dict|text search dictionaries||||
 +|39|pg_ts_parser|text search parsers||||
 +|40|pg_ts_template|text search templates||||
 +|41|pg_type|data types||||
 +|42|pg_user_mapping|mappings of users to foreign servers||||
 +^^Представления (View) ^Назначение^^^^
 +|43|pg_cursors|open cursors||||
 +|44|pg_group|groups of database users||||
 +|45|pg_indexes|indexes||||
 +|46|[[pg_locks]]|currently held locks|Содержит информацию о блокировках. [[postgresql#​urovni_blokirovok_tablic|Уровни блокировок таблиц]].|||
 +|47|pg_prepared_statements|prepared statements||||
 +|48|pg_prepared_xacts|prepared transactions||||
 +|49|pg_roles|database roles||||
 +|50|pg_rules|rules||||
 +|51|pg_settings|parameter settings||||
 +|52|pg_shadow|database users|Существует для обратной совместимости,​ она имитирует каталог,​ который существовал в PostgreSQL до версии 8.1.|||
 +|53|pg_stats|planner statistics||||
 +|54|pg_tables|tables||||
 +|55|pg_timezone_abbrevs|time zone abbreviations||||
 +|56|pg_timezone_names|time zone names||||
 +|57|pg_user|database users|Информативный характер о пользователях,​ пароль содержится в таблице pg_shadow|||
 +|58|pg_user_mappings|user mappings||||
 +|59|pg_views|views||||
 +====== Partitioning (Партицирование) ======
 +  * [[http://​romantelychko.com/​blog/​520/​|Партицирование (partitioning) больших таблиц PostgreSQL]]
 +  * [[http://​www.postgresql.org/​docs/​8.4/​interactive/​ddl-partitioning.html|8.4 Partitioning]]
 +
 +**Partitioning (партицирование,​ секционирование).**
 +====== Ссылки ======
 +  * [[http://​www.postgresql.org/​docs/​manuals/​|PostgreSQL Documentation]]
 +  * [[http://​postgresql.leopard.in.ua|А. Ю. Васильев aka leopard "​Работа с PostgreSQL: настройка и масштабирование"​]]
 +  * [[http://​postgresql.ru.net/​docs/​overview.html|Краткий обзор возможностей PostgreSQL]]
 +  * [[http://​www.hardline.ru/​selfteachers/​Info/​Programming/​SQL/​menu.html|Иллюстрированный самоучитель по PostgreSQL]]: ​ Дж. Уорсли,​ Дж. Дрейк "​PostgreSQL. Для профессионалов."​
 +  * [[http://​postgresmen.ru/​articles/​view/​38|Инструкция по оптимизации PostgreSQL 8.x]]
 +  * [[http://​wiki.etersoft.ru/​PostgreSQL/​Optimum|Оптимизация работы Postgre SQL]]
 +  * [[http://​postgresmen.ru/​articles/​view/​48|PostgreSQL:​ аналитика для DBA]]
 +  * [[http://​postgresmen.ru/​articles/​view/​39|Библиотека полезных функций для PostgreSQL]]
 +  * [[http://​samoucka.ru/​document29476.html|Иллюстрированный самоучитель по PostgreSQL]]:​ date_part()
 +  * [[http://​habrahabr.ru/​post/​188096/​|Отказоустойчивый кластер Master-Slave на PostgreSQL]]
 +
 +  * PostgreSQL [[FAQ]] на русском языке
 +     * postgresql.ru.net [[http://​postgresql.ru.net/​docs/​FAQ_Russian.html#​q6.40|Как много дискового пространства в базе данных нужно для сохранения данных из обычного текстового файла?​]]
 +     * postgresql.ru.net [[http://​postgresql.ru.net/​docs/​FAQ_Russian.html#​q6.41|Почему мои запросы работают медлено?​ Почему они не используют мои индексы?​]]
 +     * postgresql.ru.net [[http://​postgresql.ru.net/​docs/​FAQ_Russian.html#​q6.51|Что такое OID?]]
 +     * [[http://​postgresql.ru.net/​docs/​FAQ_Russian.html#​q5.35|Почему PostgreSQL использует так много памяти?​ ]]
 +
 +  * [[http://​wiki.postgresqlrussia.org/​index.php/​%D0%9F%D1%80%D0%BE%D0%B5%D0%BA%D1%82%D1%8B-%D1%81%D0%BF%D1%83%D1%82%D0%BD%D0%B8%D0%BA%D0%B8_PostgreSQL|Проекты-спутники PostgreSQL]]
 +  * [[http://​postgresmen.ru/​articles/​view/​33|Regexps in PostgreSQL / Регулярные выражения в PostgreSQL]]
 +  * [[http://​stackoverflow.com/​questions/​7785419/​in-memory-table-in-postgresql|in-memory table in PostgreSQL]] ​
  
загрузка...