База данных postgresql расширение

Этот раздел описывает формат хранения на уровне файлов и каталогов.

Файлы конфигурации и файлы данных, используемые кластером базы данных, традиционно хранятся вместе в каталоге данных кластера, который обычно называют PGDATA (по имени переменной среды, которую можно использовать для его определения). Обычно PGDATA находится в /var/lib/pgsql/data . На одной и той же машине может находиться множество кластеров, управляемых различными экземплярами сервера.

В каталоге PGDATA содержится несколько подкаталогов и управляющих файлов, как показано в Таблице 65.1. В дополнение к этим обязательным элементам конфигурационные файлы кластера postgresql.conf , pg_hba.conf и pg_ident.conf традиционно хранятся в PGDATA , хотя их можно разместить и в другом месте.

Таблица 65.1. Содержание PGDATA

Для каждой базы данных в кластере существует подкаталог внутри PGDATA /base , названный по O >pg_database . Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги.

Каждая таблица и индекс хранятся в отдельном файле. Для обычных отношений, эти файлы получают имя по номеру файлового узла таблицы или индекса, который содержится в pg_class . relfilenode . Но для временных отношений, имя файла имеет форму t BBB _ FFF , где BBB — идентификатор серверного процесса сервера, который создал данный файл, а FFF — номер файлового узла. В обоих случаях, помимо главного файла (также называемого основным слоем), у каждой таблицы и индекса есть карта свободного пространства (см. Раздел 65.3), в которой хранится информация о свободном пространстве в данном отношении. Имя файла карты свободного пространства образуется из номера файлового узла с суффиксом _fsm . Также таблицы имеют карту видимости, хранящуюся в слое с суффиксом _vm , для отслеживания страниц, не содержащих мёртвых записей. Карта видимости подробнее описана в Разделе 65.4. Нежурналируемые таблицы и индексы имеют третий слой, так называемый слой инициализации, имя которого содержит суффикс _init (см. Раздел 65.5).

Внимание

Заметьте, что хотя номер файла таблицы часто совпадает с её O >не всегда; некоторые операции, например, TRUNCATE , REINDEX , CLUSTER и некоторые формы команды ALTER TABLE могут изменить номер файла, но при этом сохранят O >pg_class , в pg_class . relfilenode содержится ноль. Фактический номер файлового узла для них хранится в низкоуровневой структуре данных, и его можно получить при помощи функции pg_relation_filenode () .

Когда объём таблицы или индекса превышает 1 GB, они делятся на сегменты размером в один гигабайт. Файл первого сегмента называется по номеру файлового узла (filenode); последующие сегменты получают имена filenode.1, filenode.2 и т. д. При такой организации хранения не возникает проблем на платформах, имеющих ограничения по размеру файлов. (На самом деле, 1 ГБ — лишь размер по умолчанию. Размер сегмента можно изменить при сборке PostgreSQL , используя параметр конфигурации --with-segsize .) В принципе, карты свободного пространства и карты видимости также могут занимать нескольких сегментов, хотя на практике это маловероятно.

У таблицы, столбцы которой могут содержать данные большого объёма, будет иметься собственная таблица TOAST, предназначенная для отдельного хранения значений, которые слишком велики для хранения в строках самой таблицы. Основная таблица связывается с её таблицей TOAST (если таковая имеется) через pg_class . reltoastrelid . За подробной информацией обратитесь к Разделу 65.2.

Содержание таблиц и индексов рассматривается ниже (см. Раздел 65.6).

Табличное пространство делает сценарий более сложным. Каждое пользовательское табличное пространство имеет символическую ссылку внутри каталога PGDATA /pg_tblspc , указывающую на физический каталог табличного пространства (т. е., положение, указанное в команде табличного пространства CREATE TABLESPACE ). Эта символическая ссылка получает имя по O >PostgreSQL , как например PG_9.0_201008051 . (Этот подкаталог используется для того, чтобы последующие версии базы данных могли свободно использовать одно и то же местоположение, заданное в CREATE TABLESPACE .) Внутри каталога конкретной версии находится подкаталог для каждой базы данных, которая имеет элементы в табличном пространстве, названный по O >pg_default недоступно через pg_tblspc , но соответствует PGDATA /base . Подобным же образом, табличное пространство pg_global недоступно через pg_tblspc , но соответствует PGDATA /global .

Функция pg_relation_filepath () показывает полный путь (относительно PGDATA ) для любого отношения. Часто это избавляет от необходимости запоминать многие из приведённых выше правил. Но следует помнить, что эта функция выдаёт лишь имя первого сегмента основного слоя отношения, т. е. возможно, понадобится добавить номер сегмента и/или _fsm , _vm или _init , чтобы найти все файлы, связанные с отношением.

Временные файлы (для таких операций, как сортировка объёма данных большего, чем может уместиться в памяти) создаются внутри PGDATA /base/pgsql_tmp или внутри подкаталога pgsql_tmp каталога табличного пространства, если для них определено табличное пространство, отличное от pg_default . Имя временного файла имеет форму pgsql_tmp PPP . NNN , где PPP — P > NNN служит для разделения различных временных файлов этого серверного процесса.

PostgreSQL
Тип	реляционная СУБД
Автор	Стоунбрейкер, Майкл[1]
Разработчик	сообщество PostgreSQL
Написана на	Си[4][5]
Операционная система	FreeBSD[6] , OpenBSD[6] , Linux[6] , macOS[6] , Solaris[6] и Microsoft Windows[6]
Первый выпуск	1996
Последняя версия	12.1 ( 14 ноября2019 ) [2]
Кандидат в релизы	12 RC 1 ( 26 сентября2019 ) [3]
Лицензия	лицензия PostgreSQL[d]
Сайт	postgresql.org​ (англ.)
Медиафайлы на Викискладе

Существует в реализациях для множества UNIX-подобных платформ, включая AIX, различные BSD-системы, HP-UX, IRIX, Linux, macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows.

Содержание

Поддержка стандартов, возможности, особенности [ править | править код ]

PostgreSQL базируется на языке SQL и поддерживает многие из возможностей стандарта SQL:2011 [8] [9] .

В PostgreSQL версии 9.5.3 есть следующие ограничения: [10]

Сильными сторонами PostgreSQL считаются:

• высокопроизводительные и надёжные механизмы транзакций и репликации;
• расширяемая система встроенных языков программирования: в стандартной поставке поддерживаются PL/pgSQL , PL/Perl , PL/Python и PL/Tcl ; дополнительно можно использовать PL/Java , PL/PHP , PL/Py , PL/R , PL/Ruby , PL/Scheme , PL/sh и PL/V8 , а также имеется поддержка загрузки модулей расширения на языке C[11] ;
• наследование;
• возможность индексирования геометрических объектов и наличие базирующегося на ней расширения PostGIS;
• встроенная поддержка слабоструктурированных данных в формате JSON с возможностью их индексации;
• расширяемость (возможность создавать новые типы данных, типы индексов, языки программирования, модули расширения, подключать любые внешние источники данных).

История [ править | править код ]

PostgreSQL создана на основе некоммерческой СУБД Postgres, разработанной как open-source проект в Калифорнийском университете в Беркли. К разработке Postgres, начавшейся в 1986 году, имел непосредственное отношение Майкл Стоунбрейкер, руководитель более раннего проекта Ingres, на тот момент уже приобретённого компанией Computer Associates. Название расшифровывалось как «Post Ingres», и при создании Postgres были применены многие ранние наработки.

Стоунбрейкер и его студенты разрабатывали новую СУБД в течение восьми лет с 1986 по 1994 год. За этот период в синтаксис были введены процедуры, правила, пользовательские типы и другие компоненты. В 1995 году разработка снова разделилась: Стоунбрейкер использовал полученный опыт в создании коммерческой СУБД Illustra, продвигаемой его собственной одноимённой компанией (приобретённой впоследствии компанией Informix), а его студенты разработали новую версию Postgres — Postgres95, в которой язык запросов POSTQUEL — наследие Ingres — был заменен на SQL.

Разработка Postgres95 была выведена за пределы университета и передана команде энтузиастов. Новая СУБД получила имя, под которым она известна и развивается в текущий момент — PostgreSQL.

Основные возможности [ править | править код ]

Функции [ править | править код ]

Функции являются блоками кода, исполняемыми на сервере, а не на клиенте БД. Хотя они могут писаться на чистом SQL, реализация дополнительной логики, например, условных переходов и циклов, выходит за рамки SQL и требует использования некоторых языковых расширений. Функции могут писаться с использованием одного из следующих языков:

• Встроенный процедурный язык PL/pgSQL, во многом аналогичный языку PL/SQL, используемому в СУБД Oracle;
• Скриптовые языки — PL/Lua, PL/LOLCODE, PL/Perl, PL/PHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl, PL/Scheme, PL/v8 (Javascript);
• Классические языки — C, C++, Java (через модуль PL/Java);
• Статистический язык R (через модуль PL/R).

PostgreSQL допускает использование функций, возвращающих набор записей, который далее можно использовать так же, как и результат выполнения обычного запроса.

Функции могут выполняться как с правами их создателя, так и с правами текущего пользователя.

Иногда функции отождествляются с хранимыми процедурами, однако между этими понятиями есть различие. С девятой версии возможно написание автономных блоков, которые позволяют выполнять код на процедурных языках без написания функций, непосредственно в клиенте.

Триггеры [ править | править код ]

Триггеры определяются как функции, инициируемые DML-операциями. Например, операция INSERT может запускать триггер, проверяющий добавленную запись на соответствия определённым условиям. При написании функций для триггеров могут использоваться различные языки программирования (см. выше).

Триггеры ассоциируются с таблицами. Множественные триггеры выполняются в алфавитном порядке.

Правила и представления [ править | править код ]

Механизм правил (англ. rules ) представляет собой механизм создания пользовательских обработчиков не только DML-операций, но и операции выборки. Основное отличие от механизма триггеров заключается в том, что правила срабатывают на этапе разбора запроса, до выбора оптимального плана выполнения и самого процесса выполнения. Правила позволяют переопределять поведение системы при выполнении SQL-операции к таблице. Хорошим примером является реализация механизма представлений (англ. views ): при создании представления создается правило, которое определяет, что вместо выполнения операции выборки к представлению система должна выполнять операцию выборки к базовой таблице/таблицам с учётом условий выборки, лежащих в основе определения представления. Для создания представлений, поддерживающих операции обновления, правила для операций вставки, изменения и удаления строк должны быть определены пользователем.

Индексы [ править | править код ]

В PostgreSQL имеется поддержка индексов следующих типов: B-дерево, хеш, GiST, GIN, BRIN, Bloom. При необходимости можно создавать новые типы индексов. Индексы в PostgreSQL обладают следующими свойствами:

• возможен просмотр индекса не только в прямом, но и в обратном порядке — создание отдельного индекса для работы конструкции ORDER BY . DESC не нужно;
• возможно создание индекса над несколькими столбцами таблицы, в том числе над столбцами различных типов данных;
• индексы могут быть функциональными, то есть строиться не на базе набора значений некоего столбца/столбцов, а на базе набора значений функции от набора значений;
• индексы могут быть частичными, то есть строиться только по части таблицы (по некоторой её проекции); в некоторых случаях это помогает создавать намного более компактные индексы или достигать улучшения производительности за счёт использования разных типов индексов для разных (например, с точки зрения частоты обновления) частей таблицы;
• планировщик запросов может использовать несколько индексов одновременно для выполнения сложных запросов.

Многоверсионность (MVCC) [ править | править код ]

PostgreSQL поддерживает одновременную модификацию БД несколькими пользователями с помощью механизма Multiversion Concurrency Control (MVCC). Благодаря этому соблюдаются требования ACID и практически отпадает нужда в блокировках чтения.

Типы данных [ править | править код ]

PostgreSQL поддерживает большой набор встроенных типов данных:

• Численные типы

• Целые
• С фиксированной точкой
• С плавающей точкой
• Денежный тип (отличается специальным форматом вывода, а в остальном аналогичен числам с фиксированной точкой с двумя знаками после запятой)

Более того, пользователь может самостоятельно создавать новые требуемые ему типы и программировать для них механизмы индексирования с помощью GiST.

Пользовательские объекты [ править | править код ]

PostgreSQL может быть расширен пользователем для собственных нужд практически в любом аспекте. Есть возможность добавлять собственные:

• Преобразования типов
• Типы данных
• Домены (пользовательские типы с изначально наложенными ограничениями)
• Функции (включая агрегатные)
• Индексы
• Операторы (включая переопределение уже существующих)
• Процедурные языки

Наследование и партицирование [ править | править код ]

Таблицы могут наследовать характеристики и наборы полей от других таблиц (родительских). При этом данные, добавленные в порождённую таблицу, автоматически будут участвовать (если это не указано отдельно) в запросах к родительской таблице.

В PostgreSQL 10 был добавлен механизм партицирования таблиц. Партицирование предназначено для разделения одной таблицы на несколько, так называемые партиции. Партицирование схоже с наследованием, но имеет более дружелюбный к пользователю синтаксис и более строгие ограничения, что позволяет выполнять дополнительные оптимизации при планировании запросов.

Прочие возможности [ править | править код ]

• Соблюдение принципов ACID
• Соответствие стандартам ANSISQL-92, SQL-99,SQL:2003,SQL:2011
• Поддержка запросов с OUTER JOIN , UNION , UNION ALL , EXCEPT , INTERSECT и подзапросов
• Последовательности
• Контроль целостности
• Репликация
• Общие табличные выражения и рекурсивные запросы
• Аналитические функции
• Поддержка Юникода (UTF-8)
• Поддержка регулярных выражений в стиле Perl
• Встроенная поддержка SSL, SELinux и Kerberos
• Протокол разделяемых блокировок
• Подгружаемые расширения, поддерживающие SHA1, MD5, XML
• Расширения для написания сложных выборок, отчётов и т. д. (API открыт)
• Средства для генерации совместимого с другими системами SQL-кода и импорта из других систем
• Автономные блоки на доступных языках, а не только SQL

Качество исходного кода [ править | править код ]

Согласно результатам автоматизированного исследования различного ПО на предмет ошибок, проведённом в 2005 году, в исходном коде PostgreSQL было найдено 20 проблемных мест на 775 000 строк исходного кода (в среднем, одна ошибка на 39 000 строк кода) [12] . Для сравнения: MySQL — 97 проблем, одна ошибка на 8 000 строк кода; FreeBSD (целиком) — 306 проблем, одна ошибка на 2 500 строк кода; Linux (только ядро) — 950 проблем, одна ошибка на 800 строк кода.

Производные продукты [ править | править код ]

Лицензия PostgreSQL позволяет на его основе создавать различные, в том числе коммерческие, форки. Их известно несколько десятков [13] .

На базе PostgreSQL компанией EnterpriseDB были разработаны другие варианты этой СУБД, являющиеся платными для коммерческого использования — Postgres Plus (состоит целиком только из продуктов с открытыми исходными кодами; плата требуется только при необходимости приобретения коммерческой поддержки продукта) и Postgres Plus Advanced Server (расширение PostgreSQL специальными возможностями для обеспечения совместимости с Oracle Database) [14] . В комплекте поставки данных продуктов содержится набор ПО для разработчиков и администраторов баз данных:

• Postgres Studio — аналог phpPgAdmin;
• Postgres Plus Debugger — отладчик для кода на PL/pgSQL, интегрированный с предыдущим пакетом;
• Migration Studio — инструмент для автоматического преобразования баз данных из MySQL/Oracle в PostgreSQL

СУБД PostgreSQL в разных операционных системах имеет практически идентичную структуру каталогов. В данной статье рассматривается стандартная структура каталогов для ОС Ubuntu 12.04.

По умолчанию PostgreSQL устанавливается в папку /var/lib/postgresql/ /main. Основной каталог СУБД содержит подкаталоги с пользовательскими данными и служебной информацией.

postmaster.opts — файл, в котором сдержится командная строка с параметрами, с помощью которой была запущена СУБД.

На моем компьютере в этом файле хранится следующая строка

PG_VERSION — файл, содержащий основной номер версии СУБД (к примеру, 9.3)

base — каталог, содержащий каталоги баз данных (по каталогу на каждую БД)

Имена подкаталогов соответствуют OID'ам баз данных.

global — каталог, в котором хранятся глобальные таблицы (к примеру, pg_database)

pg_clog — каталог, в котором хранится информация о статусах транзакций.

На каждую транзакцию выделяется 2 бита. Статус транзакции может иметь следующие значения:

• транзакция стартовала,
• транзакция успешно завершена,
• транзакция отменена,
• подтранзакция успешно завершена.

Количество хранимых транзакций ограничено параметром autovacuum_freeze_max_age (максимальное значение

2 миллиарда), который в свою очередь регулирует работу автовакума. Поэтому максимальный размер файла может быть

pg_multixact — каталог, содержащий информацию, необходимую для координации работы параллельных транзакций (используется для хранения SHARED ROW LOCKS)

pg_notify — каталог, в котором хранится информация для поддержки работы LISTEN/NOTIFY

pg_serial — каталог, содержащий информацию о завершенных последовательных (serializable) транзакциях

pg_snapshots — каталог, в котором хранятся экспортированные снапшоты

Файлы создаются при вызове процедуры pg_export_snapshot () и существуют до окончания транзакции.

Ниже представлена последовательность действий, позволяющая понять, как появляются и исчезают файлы в этом каталоге.

Экспорт снапшота в транзакции

В каталоге появился файл, в котором содержится информация о снапшоте