PostgreSQL

Материал из Национальной библиотеки им. Н. Э. Баумана
Последнее изменение этой страницы: 16:32, 21 января 2019.
PostgreSQL
Postgresql.png
Создатели: Стоунбрейкер, Майкл
Разработчики: сообщество PostgreSQL
Выпущена: 1995; 26 years ago (1995)
Предыдущий выпуск: 11.1 / 8 ноября 2018
Состояние разработки: Активное
Написана на: C
Операционная система: Windows, Linux, macOS, FreeBSD, OpenBSD, Solaris
Платформа: Кросс-платформенное программное обеспечение
Веб-сайт postgresql.org

PostgreSQL - это свободно распространяемая объектно-реляционная система управления базами данных (ORDBMS), наиболее развитая из открытых СУБД в мире и являющаяся реальной альтернативой коммерческим базам данных.[Источник 1]

История

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

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

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

О продукте

PostgreSQL поддерживается на всех современных Unix системах (34 платформы), включая наиболее распространенные, такие как Linux, FreeBSD, NetBSD, OpenBSD, SunOS, Solaris, DUX, а также под macOS. Начиная с версии 8.X PostgreSQL работает в "native" режиме под MS Windows NT, Win2000, WinXP, Win2003. Известно, что есть успешные попытки работать с PostgreSQL под Novell Netware 6 и OS2.

PostgreSQL неоднократно признавалась базой года, например, Linux New Media AWARD 2004, 2003 Editors' Choice Awards, 2004 Editors' Choice Awards. [Источник 3]

PostgreSQL используется как полигон для исследований нового типа баз данных, ориентированных на работу с потоками данных - это проект TelegraphCQ, стартовавший в 2002 году в Беркли после успешного проекта Telegraph (название главной улицы в Беркли).

Основные возможности и функциональность

Надежность

Надежность PostgreSQL является проверенным и доказанным фактом и обеспечивается следующими возможностями:

  • полное соответствие принципам ACID - атомарность, непротиворечивость, изолированность, сохранность данных.
  • многоверсионность (Multiversion Concurrency Control,MVCC) используется для поддержания согласованности данных в конкурентных условиях, в то время как в традиционных базах данных используются блокировки. MVCC означает, что каждая транзакция видит копию данных (версию базы данных) на время начала транзакции, несмотря на то, что состояние базы могло уже измениться. Это защищает транзакцию от несогласованных изменений данных, которые могли быть вызваны (другой) конкурентной транзакцией, и обеспечивает изоляцию транзакций.
  • наличие Write Ahead Logging (WAL) - общепринятый механизм протоколирования всех транзакций, что позволяет восстановить систему после возможных сбоев.
  • Point in Time Recovery (PITR) - возможность восстановления базы данных (используя WAL) на любой момент в прошлом, что позволяет осуществлять непрерывное резервное копирование кластера PostgreSQL.
  • Репликация также повышает надежность PostgreSQL. Существует несколько систем репликации, например, Slony (тестируется версия 1.1), который является свободным и самым используемым решением, поддерживает master-slaves репликацию.
  • Целостность данных является сердцем PostgreSQL. Помимо MVCC, PostgreSQL поддерживает целостность данных на уровне схемы - это внешние ключи (foreign keys), ограничения (constraints).
  • Открытость кодов PostgreSQL означает их абсолютную доступность для любого, а либеральная BSD лицензия не накладывает никаких ограничений на использование кода. [Источник 4]

Производительность

Производительность PostgreSQL основывается на использовании индексов, интеллектуальном планировщике запросов, тонкой системы блокировок, системе управления буферами памяти и кэширования, превосходной масштабируемости при конкурентной работе.

  • Поддержка индексов:
    • Стандартные индексы - B-tree, hash, R-tree, GiST (обобщенное поисковое дерево)
    • Частичные индексы (partial indices) - можно создавать индекс по ограниченному подмножеству значений, например, create index idx_partial on foo (x) where x > 0;
    • Функциональные индексы (expressional indices) позволяют создавать индексы используя значения функции от параметра, например, create index idx_functional on foo ( length(x) );
  • Планировщик запросов основывается на стоимости различных планов, учитывая множество факторов. Он предоставляет возможность пользователю отлаживать запросы и настраивать систему.
  • Система блокировок поддерживает блокировки на нижнем уровне, что позволяет сохранять высокий уровень конкурентности при защите целостности данных. Блокировка поддерживается на уровне таблиц и записей. На нижнем уровне, блокировка для общих ресурсов оптимизирована под конкретную ОС и архитектуру.
  • Управление буферами и кэширование используют сложные алгоритмы для поддержания эффективности использования выделенных ресурсов памяти.
  • Tablespaces (табличные пространства) для управления хранения данных на уровне объектов, таких как базы данных, схемы, таблицы и индексы. Это позволяет гибко использовать дисковое пространство и повышает надежность, производительность, а также способствует масштабируемости системы.
  • Масштабируемость основывается на описанных выше возможностях. Низкая требовательность PostgreSQL к ресурсам и гибкая система блокировок обеспечивают его шкалирование, в то время как индексы и управление буферами обеспечивают хорошую управляемость системы даже при высоких загрузках. [Источник 4]

Расширяемость

Расширяемость PostgreSQL означает, что пользователь может настраивать систему путем определения новых функций, агрегатов, типов,языков, индексов и операторов. Объектно-ориентированность PostgreSQL позволяет перенести логику приложения на уровень базы данных, что сильно упрощает разработку клиентов, так как вся бизнес логика находится в базе данных. Функции в PostgreSQL однозначно определяются названием, количеством и типами аргументов. [Источник 4]

Поддержка SQL

Кроме основных возможностей, присущих любой SQL базе данных, PostgreSQL поддерживает:

  • Очень высокий уровень соответствия ANSI SQL 92, ANSI SQL 99 и ANSI SQL 2003.
  • Схемы, которые обеспечивают пространство имен на уровне SQL. Схемы содержат таблицы, в них можно определять типы данных, функции и операторы. Используя полное имя объекта можно одновременно работать с несколькими схемами. Схемы позволяют организовать базы данных совокупность нескольких логических частей, каждая их которых имеет свою политику доступа, типы данных.
  • Subqueries - подзапросы (subselects), полная поддержка SQL92. Подзапросы делают язык SQL более гибким и зачастую более эффективным.
  • Outer Joins - внешние связки (LEFT,RIGHT, FULL)
  • Rules - правила, согласно которым модифицируется исходный запрос. Главное отличие от триггеров состоит в том, что rule работает на уровне запроса и перед исполнением запроса, а триггер - это реакция системы на изменение данных, т.е. триггер запускается в процессе исполнения запроса для каждой измененной записи (PER ROW). Правила используются для указания системе, какие действия надо произвести при попытке обновления view.
  • Cursors - курсоры, позволяют уменьшить трафик между клиентом и сервером, а также память на клиенте, если требуется получить не весь результат запроса, а только его часть.
  • Table Inheritance - наследование таблиц, позволяющее создавать объекты, которые наследуют структуру родительского объекта и добавлять свои специфические атрибуты.
  • Stored Procedures - серверные (хранимые) процедуры позволяют реализовывать бизнес логику приложения на стороне сервера. Кроме того, они позволяют сильно уменьшить трафик между клиентом и сервером.
  • Триггеры позволяют управлять реакцией системы на изменение данных (INSERT,UPDATE,DELETE), как перед самой операцией (BEFORE), так и после (AFTER). Во время выполнения триггера доступны специальные переменные NEW (запись, которая будет вставлена или обновлена) и OLD (запись перед обновлением).
  • Cluster table - упорядочивание записей таблицы на диске согласно индексу, что иногда за счет уменьшения доступа к диску ускоряет выполнение запроса. [Источник 4]

Типы данных

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

  • Булев тип
  • Численные типы
    • Целые
    • С фиксированной точкой
    • С плавающей точкой
    • Денежный тип (отличается специальным форматом вывода, а в остальном аналогичен числам с фиксированной точкой с двумя знаками после запятой)
  • Символьные типы произвольной длины
  • Двоичные типы
  • Типы «дата/время» (полностью поддерживающие различные форматы, точность, форматы вывода, включая последние изменения в часовых поясах)
  • Перечисление
  • Типы текстового поиска
  • Составные типы
  • HStore (расширение, добавляющее ключ-значение в PostgreSQL)
  • Массивы(различной длины и любого типа данных, включая текстовый и составной типы) размером до 1 Гбайта
  • Геометрические примитивы
  • Сетевые типы
  • XML-данные с поддержкой запросов XPath
  • UUID-идентификатор
  • JSON (начиная с версии 9.2) и более быстрый JSONB (начиная с версии 9.4)

Кроме того, пользователи могут создавать свои собственные типы данных, которые обычно можно полностью индексировать через инфраструктуру индексирования PostgreSQL - GiST, GIN, SP-GiST. К ним относятся типы данных географической информационной системы (ГИС) из проекта PostGIS для PostgreSQL.

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

Начиная с PostgreSQL 9.2, может использоваться тип данных, представляющий диапазон данных, которые называются типами диапазонов. Это могут быть дискретные диапазоны (например, все целые значения от 1 до 10) или непрерывные диапазоны (например, любой момент времени между 10:00 и 11:00). Доступные типы доступных диапазонов включают диапазоны целых чисел, большие целые числа, десятичные числа, отметки времени (с часовым поясом и без него) и даты.

Пользовательские типы диапазонов могут быть созданы для обеспечения доступности новых типов диапазонов, таких как диапазоны IP-адресов, с использованием типа inet в качестве базы или диапазонов с плавающей точкой, используя тип данных float в качестве базы. Типы диапазонов поддерживают включенные и исключительные границы диапазона, используя символы и () соответственно. (например, представляет все целые числа, начиная с 4 включительно, но не включая 9.) Типы диапазонов также совместимы с существующими операторами, используемыми для проверки наложения, сдерживания, права и т. д. [Источник 4]

Наследование

Таблицы могут наследовать свои характеристики от "родительской" таблицы. Данные в дочерних таблицах будут существовать в родительских таблицах, если данные не выбираются из родительской таблицы, с помощью ключевого слова ONLY, т.е. SELECT * FROM ONLY parent_table;. Добавление столбца в родительскую таблицу будет причиной того, что столбец появится в дочерней таблице.

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

По состоянию на 2010, эта функция поддерживается не полностью, но, в частности, таблица ограничений в настоящее время не наследуемая. Все проверочные ограничения и ненулевые ограничения на родительской таблице автоматически наследуются его детьми. Другие типы ограничений (уникальные, первичный ключ, и иностранные ключевые ограничения) не наследуются.

Наследование обеспечивает способ отображения особенности иерархий обобщения, изображенных на сущность-связь диаграммах (ERD) непосредственно в базе данных PostgreSQL[Источник 4]

Функции запроса

  1. Операции
  2. Полнотекстовый поиск
  3. Просмотры
  4. Материализованные взгляды
  5. Обновляемые виды
  6. Рекурсивные взгляды
  7. Внутренний, внешний (полный, левый и правый) и крест- соединения
  8. Суб выбирает
  9. Коррелированные подзапросы
  10. Регулярные выражения
  11. Общие выражения таблиц и записываемые общие табличные выражения
  12. Зашифрованные соединения через TLS (текущие версии не используют уязвимый SSL, даже с этой конфигурацией)
  13. Домены
  14. Точки сохранения
  15. Двухфазное принятие
  16. TOAST ( метод хранения с ограниченными возможностями ) используется для прозрачного хранения больших атрибутов таблицы (таких как большие вложения MIME или XML-сообщения) в отдельной области с автоматическим сжатием.
  17. Встроенный SQL реализован с использованием препроцессора. Код SQL сначала записывается в C-код. Затем код запускается через препроцессор ECPG, который заменяет SQL на вызовы в библиотеку кода. Затем код может быть скомпилирован с использованием компилятора C. Встраивание работает также с C++, но не распознает все конструкции C++. [Источник 4]

Поддержка стандартов, возможности, особенности

PostgreSQL PostgreSQL поддерживает большинство возможностей стандарта SQL: 2011, ACID-совместимая и транзакционная (включая большинство DDL утверждения) избегает проблемы блокировки с помощью механизма Многоверсионное управление параллельным доступом (MVCC), обеспечивает иммунитет к «грязному» чтению и полую сериализационность; управляет комплексными SQL запросами используя множество индексированных методов, которые недоступны в других базах данных; имеет обновляемые представления и материализованные представления, триггеры, внешние ключи; поддерживает функции и хранимые процедуры, и другие возможности расширения, и имеет множество расширений, написанных третьими лицами. В дополнение к возможности работы с основными фирменными и с открытым исходным кодом базами данных, PostgreSQL поддерживает миграцию из них, путем своей обширной поддержки стандарта SQL и доступных инструментов миграции. Фирменные расширения в базах данных, таких как Oracle можно эмулировать с помощью встроенных и сторонних расширений совместимости с открытым исходным кодом. Последние версии также обеспечивают репликацию самой базы данных для доступности и масштабируемости.

PostgreSQL является кросплотформенной и работает на множестве операционных систем, включая Linux, FreeBSD, macOS, Solaris, и Microsoft Windows. Начиная с Mac OS X 10.7 Lion Server, PostgreSQL это стандартная база данных по умолчанию, и клиентские инструменты PostgreSQL идут в комплекте с настольной версией. Подавляющее большинство дистрибутивов Linux имеет PostgreSQL доступным в поддерживаемых пакетах.

PostgreSQL разработан PostgreSQL Global Development Group, разнообразной группой из многих компаний и отдельных вкладчиков. Это свободное и открытое программное обеспечение, распространяемое по условиям Лицензии PostgreSQL, разрешительной лицензии свободного программного обеспечения.

Поскольку СУБД PostgreSQL выпускается под либеральной лицензией, её можно бесплатно использовать, модифицировать и распространять для любых целей, включая личные, коммерческие или академические.

На данный момент (версия 9.4.5), в PostgreSQL имеются следующие ограничения:[Источник 5]

Максимальный размер базы данных Нет ограничений
Максимальный размер таблицы 32 Тбайт
Максимальный размер записи 1,6 Тбайт
Максимальный размер поля 1 Гбайт
Максимум записей в таблице Нет ограничений
Максимум полей в записи 250—1600, в зависимости от типов полей
Максимум индексов в таблице Нет ограничений

Сильными сторонами 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-совместимых модулей [Источник 4];
  • поддержка со стороны многих языков программирования: C\C++, Java, Perl, Python, Ruby, ECPG, Tcl, PHP и других.
  • наследование;
  • легкая расширяемость.

Разработка

PostgreSQL развивается силами международной группы разработчиков (PGDG), в которую входят как непосредственно программисты, так и те, кто отвечают за продвижение PostgreSQL (Public Relation), за поддержание серверов и сервисов, написание и перевод документации, всего на 2005 год насчитывается около 200 человек. Другими словами, PGDG - это сложившийся коллектив, который полностью самодостаточен и устойчив. Проект развивается по общепринятой среди открытых проектов схеме, когда приоритеты определяются реальными нуждами и возможностями. При этом, практикуется публичное обсуждение всех вопросов в списке рассылке, что практически исключает возможность неправильных и несогласованных решений.

Это относится и к тем предложениям, которые уже имеют или рассчитывают на финансовую поддержку коммерческих компаний.

Цикл работой над новой версией обычно длится 10-12 месяцев (сейчас ведется дискуссия о более коротком цикле 2-3 месяца) и состоит из нескольких этапов. [Источник 1]

Другие функции хранения

Ограничения ссылочной целостности, включая ограничения внешнего ключа, ограничения столбцов и проверки строк Двоичное и текстовое хранилище больших объектов Табличные Сравнение столбцов Онлайн-резервная Восстановление по времени, реализованное с использованием записи на основе записи Обновление на месте с помощью pg_upgrade за меньшее время простоя (поддерживает обновления с 8.3.x и выше) [Источник 1]

Установка и настройка

В данном разделе представлена инструкция по установки и настройке PostgreSQL для разных ОС [Источник 6]

Установка

Если установка происходит на macOS, то процесс установки можно запустить командой: [Источник 7]

brew install postgresql

На Linux СУБД устанавливается так:

sudo apt-get install postgresql postgresql-contrib

После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:

postgres --version

Инструкция по установке в цифровом формате

Настройка

Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL. [Источник 7]

Необходимо ввести следующую команду:

psql postgres (для выхода из интерфейса используйте \q)

Этой командой запускается утилита psql. Хотя есть много сторонних инструментов для администрирования PostgreSQL, нет необходимости их устанавливать, т. к. psql удобен и отлично работает.

Если нужна помощь, введите \help (или -h) в psql-терминале. Появится список всех доступных параметров справки. Вы можете ввести \help [имя команды], если вам нужна помощь по конкретной команде. Например, если ввести \help UPDATE в консоли psql, отобразится синтаксис команды update.

 1 Description: update rows of a table
 2 [ WITH [ RECURSIVE ] with_query [, ...] ]
 3 UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
 4     SET { column_name = { expression | DEFAULT } |
 5           ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
 6           ( column_name [, ...] ) = ( sub-SELECT )
 7         } [, ...]
 8     [ FROM from_list ]
 9     [ WHERE condition | WHERE CURRENT OF cursor_name ]
10     [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Для начала необходимо проверить наличие существующих пользователей и баз данных. Выполните следующую команду, чтобы вывести список всех баз данных:

\list или \l
Рисунок 1 - Результат выполнения операции \l

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

Чтобы вывести список всех пользователей, выполните команду \du. Атрибуты пользователя postgres говорят нам, что он суперпользователь.

Рисунок 2 - Результат выполнения операции \du

Основные операции с БД

Чтобы выполнять базовые действия в СУБД, нужно знать язык запросов к базе данных SQL.

Создание базы данных

Для создания базы данных используется команда: [Источник 7]

create database

В приведенном ниже примере создается база данных с именем proglib_db.

Рисунок 3 - Создание базы данных с именем proglib_db

Если забыть точку с запятой в конце запроса, знак «=» в приглашении postgres заменяется на «-». Это зачастую указывает на то, что необходимо завершить (дописать) запрос.

Рисунок 4 - вывод ошибки при создании базы данных

На рисунке 4 видно сообщение об ошибке из-за того, что в нашем случае база уже создана.

Создание нового юзера

Для создания пользователя существует команда:

create user

В приведенном ниже примере создается пользователь с именем author.

Рисунок 5 - Создание пользователя с именем author

При создании пользователя отобразится сообщение CREATE ROLE. Каждый пользователь имеет свои права (доступ к базам, редактирование, создание БД / пользователей и т. д.). Вы могли заметить, что столбец Attributes для пользователя author пуст. Это означает, что пользователь author не имеет прав администратора. Он может только читать данные и не может создать другого пользователя или базу.

Можно установить пароль для существующего пользователя. [Источник 7]

С этой задачей справится команда \password:

postgres=#\password author

Чтобы задать пароль при создании пользователя, можно использовать следующую команду:

postgres=#create user author with login password 'qwerty';

Удаление базы или пользователя

Для этой операции используется команда drop: она умеет удалять как пользователя, так и БД. [Источник 7]

drop database <database_name>
drop user <user_name>

Данную команду нужно использовать очень осторожно, иначе удаленные данные будут потеряны, а восстановить их можно только из бэкапа (если он был).

Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:

psql [database_name] [user_name]

Войдем в базу proglib_db под пользователем author. Выполним команду \q, чтобы выйти из текущей БД, а затем выполните следующую команду:

Рисунок 6 - Вход в базу данных proglib_db

Источники

  1. 1,0 1,1 1,2 Что такое PostgreSQL? // Sternberg Astronomical Institute Moscow University. Дата обновления: 01.01.2005. URL: http://www.sai.msu.su/~megera/postgres/talks/what_is_postgresql.html (дата обращения: 17.01.2019).
  2. PostgreSQL // Википедия. [2019-2019]. Дата обновления: 04.07.2018. URL: https://ru.wikipedia.org/wiki/PostgreSQL (дата обращения: 17.01.2019).
  3. 2004 Editors' Choice Awards | Linux Journal // Linux Journal, LLC. [2019-2019]. Дата обновления: 01.08.2004. URL: https://www.linuxjournal.com/article/7564 (дата обращения: 17.01.2019).
  4. 4,0 4,1 4,2 4,3 4,4 4,5 4,6 4,7 PostgreSQL 11.1 Documentation // PostgreSQL. [1996-2019]. URL: https://www.postgresql.org/docs/current/ (дата обращения: 17.01.2019).
  5. About // PostgreSQL. [1996-2019]. URL: https://www.postgresql.org/about/ (дата обращения: 17.01.2019).
  6. Введение в PostgreSQL // Metanit. [2012-2019]. Дата обновления: 15.03.2018. URL: https://metanit.com/sql/postgresql/1.1.php (дата обращения: 17.01.2019).
  7. 7,0 7,1 7,2 7,3 7,4 Работа с PostgreSQL: от полного нуля до полного просветления // Библиотека программиста. [2016-2018]. URL: https://proglib.io/p/learn-postgresql (дата обращения: 22.12.2018).