PostgreSQL: все, что нужно знать для быстрого старта

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

Кроме того, информация обновляется настолько часто, что ее корректировка требует выделенных решений, оптимизированных под обработку и хранение конкретных видов информации. Для решения этой задачи было разработано специальное ПО — системы управления базами данных (СУБД). Впоследствии базы данных стали использоваться и на сайтах: например, для регистрации пользователей или онлайн-оплаты.

Многие облачные провайдеры предлагают готовые решения с предустановленными СУБД. Например, в Рег.ру можно заказать сервер с настроенной облачной PostgreSQL — СУБД , о которой пойдет речь.

Что такое PostgreSQL

PostgreSQL — это объектно-реляционная система управления базами данных. Она написана на языке C и распространяется свободно. СУБД PostgreSQL работает со стандартным языком запросов SQL, а также с его дополненной версией — PL/pgSQL.

Изначально СУБД носила название Postgres и с самого начала была некоммерческой. Она разрабатывалась в Калифорнийском университете в Беркли с 1986 года по 1994 год. За это время в Постгрес были добавлены правила, процедуры, пользовательские типы и многие другие компоненты, которые считаются основными.

В 1995 году началась разработка новой версии Постгрес, которая получила имя Postgres95. В этом варианте СУБД произошло глобальное изменение: первичный язык запросов POSTQUEL был заменен на популярный SQL.

Со временем Postgres95 была доработана до привычной PostgreSQL. Разработка этой версии вышла за пределы Калифорнийского университета в Беркли и была передана в руки программистов сообщества.

Часто кроме названия PostgreSQL можно встретить вариант Postgres или Постгрес. Несмотря на то, что так называлась первоначальная версия СУБД, это имя также используется для неформального обозначения PostgreSQL.

Особенности PostgreSQL

Любая СУБД имеет уникальные особенности, и с не исключение. Какими преимуществами она обладает:

1. Масштабируемость. PostgreSQL позволяет распределить входящие запросы между несколькими инстансами — экземплярами (репликами) БД. Также зачастую на облачных решениях с предустановленным Постгрес можно в любой момент добавить дополнительные ресурсы.
2. Кроссплатформенность. СУБД Постгрес можно установить практически на любую известную операционную систему. В число поддерживаемых ОС входят дистрибутивы Linux (Ubuntu, Debian, CentOS и другие), macOS, Windows 10 и 11.
3. Безопасность. PostgreSQL позволяет шифровать данные на разных уровнях, а также выбирать средства защиты информации от несанкционированного доступа. В состав Постгрес входит большое количество инструментов безопасности: например, LDAP, SSPI, Kerberos, GSSAPI и многие другие. Эти технологии можно использовать для создания дополнительных опций безопасности а также регулировать настройки доступа к объектам базы данных на разных уровнях: от самой БД до столбцов в ней.
4. Поддержка различных типов данных. PostgreSQL может работать не только со стандартными типами данных, которые существуют в большинстве популярных языков, но и со специфическими типами. Среди них можно встретить геометрические расчеты, сетевые адреса, типы полнотекстового поиска и многие другие.
5. Расширяемость. Вы можете написать собственные функции для Постгрес на Python, PHP, Java и Ruby. Также для загрузки доступны готовые модули на языке C из репозитория PGXN.
6. Открытый код. Постгрес распространяется по лицензии BSD. Это значит, что проект можно использовать, редактировать и распространять среди других пользователей бесплатно. Также разработчики могут модифицировать оригинальный код, скачав его с официального сайта.
7. Активное сообщество. Пользователи и разработчики собственного ПО еженедельно получают новости о Postgres из рассылок. Также участники сообщества делятся опытом, ведут дискуссии и помогают в решении вопросов разработки.
8. Поддержка NoSQL. PostgreSQL поддерживает форматы XML, JSON и JSONB. Это позволяет записать JSON-документ в базу данных, не разбирая его — от этого производительность сервера БД становится выше.

Также PostgreSQL имеет два недостатка:

1. Требовательность к ресурсам. Так как PostgreSQL способна работать со сложными запросами и большими объемами данных, она может требовать больше ресурсов в сравнении с другими популярными СУБД. В моменты высокой нагрузки особенно активно расходуется оперативная память (ресурсы ОЗУ) и процессорное время.
2. Сложная настройка. Так как СУБД обладает гибким функционалом для настройки, для работы с ней потребуется знание архитектуры и понимание параметров. Кроме того, необходимо освоить основы языка SQL. Все это может создать сложности в PostgreSQL для начинающих пользователей.

Типы данных в PostgreSQL

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

• числовые типы, которые включают в себя целые числа, числа с плавающей точкой и десятичные числа с задаваемой точностью;
• геометрические типы состоят из точек, прямых, отрезков, прямоугольников, многоугольников, путей и окружностей;
• двоичный тип включает в себя двоичную строку переменной длины;
• денежный тип хранит сумму с фиксированной дробной частью;
• дата/время содержит набор типов даты и времени SQL. Все даты будут указаны согласно григорианскому календарю;
• логический тип может иметь один из трех статусов: true, false или unknown;
• типы перечислений представляют собой статический упорядоченный набор данных. Это могут быть дни недели или месяцы в календарном порядке;
• диапазонные типы. Название полностью отражает их суть: это диапазоны значений определенного типа данных. Например, это могут быть временные интервалы, диапазоны дат, цен и многое другое.
• символьные типы представлены строками переменной длины (ограниченными и неограниченными), а также строками фиксированной длины с пробелами;
• составные типы представляют собой соотношения списка имен полей и типов данных;
• массивы. Этот тип позволяет обозначить колонки таблицы в базе данных как многомерные массивы — то есть в виде массива, элементы которого также представлены массивами;
• идентификаторы объектов — важный тип данных, который представляет собой первичный ключ для многих системных таблиц;
• типы для описания сетевых адресов позволяют хранить адреса IPv4 и IPv6, а также MAC-адреса.

Полный список типов данных и их описания можно найти в официальной документации PostgreSQL.

Как установить PostgreSQL

В этом разделе мы рассмотрим установку Постгрес на ОС семейства Linux и Windows.

Linux

В качестве примера в этой инструкции мы опишем установку на ОС Ubuntu. Однако этот алгоритм применим и к Debian.

Чтобы установить Постгрес:

1. Подключитесь к серверу по протоколу SSH.
2. Обновите пакеты:

3. Установите PostgreSQL и подпакет contrib:

4. Добавьте публичный ключ репозитория pgAdmin4 APT:

5. Создайте конфигурационный файл репозитория:

6. Установите pgAdmin:

7. Перезапустите службу Apache с помощью скрипта:

8. В брандмауэре разрешите доступ для Apache:

9. Запустите брандмауэр:

10. Проверьте, добавлена ли служба Apache в список разрешенных:

Напротив службы Apache будет указано действие Allow.

11. Откройте браузер и в адресной строке введите

Вместо 123.123.123.123 укажите IP-адрес вашего сервера.

12. Укажите логин и пароль, которые вы вводили на шаге 7. Затем нажмите Login.

Windows

1. Подключитесь к серверу по протоколу RDP.
2. Перейдите на официальный сайт PostgreSQL.
3. Выберите операционную систему Windows:

4. Кликните по ссылке Download the installer:

5. В строке с именем нужной вам версии нажмите на значок загрузки:

6. По окончании загрузки запустите установочный файл.
7. В стартовом окне нажмите Next:

8. Выберите директорию для установки и кликните Next:

9. Оставьте список компонентов без изменений. Затем нажмите Next:
10. Выберите директорию для хранения файлов баз данных. Затем кликните Next:

11. Введите пароль суперпользователя (root-пароль). После этого нажмите Next:

12. Укажите порт для работы PostgreSQL. Затем кликните Next:

13. Выберите географическое положение вашего будущего кластера PostgreSQL и нажмите Next:

14. Проверьте параметры установки. Если они верны, кликните Next:

15. По окончании установки нажмите Finish:

Начало работы с PostgreSQL

Чтобы начать работу с БД в консоли PostgreSQL, необходимо знать базовые команды. В этом разделе мы разберем основные действия, которые могут потребоваться при работе с базой данных.

Команды PostgreSQL можно выполнить как в Linux, так и на Windows. В этом помогут специальные инструменты. Их выбор будет зависеть от операционной системы:

• терминал, если вы работаете с Linux;
• PowerShell, если вы работаете с Windows.

Открытие консоли Postgres

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

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

В качестве postgres указано имя суперпользователя.

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

Для создания базы данных выполните команду:

Вместо new_db укажите имя базы данных.

Если вы хотите создать базу данных и сразу назначить ее владельца, выполните эту команду:

Где:

new_db — имя базы данных,
new_role — название роли.

Создание пользователя

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

Вместо new_user укажите логин нового пользователя.

Если вы хотите назначить пароль новому пользователю сразу при создании, выполните команду в следующем виде:

Где:

• new_user — логин нового пользователя,
• user_password — пароль нового пользователя.

Создание таблицы

Чтобы создать таблицу, используйте следующую команду:

Где:

• new_table — название таблицы;
• address, id, service, day — названия столбцов новой таблицы;
• varchar, int, real, date — типы данных, которые будут храниться в каждом из столбцов.

Изменение настроек базы данных

Чтобы изменить настройки базы данных, используйте команду ALTER DATABASE:

Где:

• new_db — имя базы данных;
• ACTION — действие, которое нужно выполнить (переименовать, скопировать и др.);
• value — значение (новое имя, место копирования и др.)

Ниже мы привели примеры команды ALTER DATABASE, которые можно применить на практике.

Переименовать базу данных:

Где:

• new_db — имя базы данных;
• ACTION — действие, которое нужно выполнить (переименовать, скопировать и др.);
• value — значение (новое имя, место копирования и др.)

Ниже мы привели примеры команды ALTER DATABASE, которые можно применить на практике.

Переименовать базу данных:

Где:

• old_db — текущее имя базы данных;
• RENAME TO — действие, которое нужно выполнить;
• new_db — новое имя базы данных.

Передать базу данных во владение другому пользователю:

Где:

• new_db — имя базы данных;
• OWNER TO — действие, которое нужно выполнить;
• new_user — имя пользователя, которому нужно передать базу данных.

Закрытие консоли Postgres

Чтобы закрыть консоль Постгрес, используйте следующую команду:

Полный список команд вы можете найти в официальной документации PostgreSQL.

Подведем итоги. PostgreSQL — бесплатное решение с гибкой конфигурацией и возможностью создания собственного функционала. Оно подходит для крупных проектов с большими объемами данных. Однако для работы с ним необходимо иметь навыки администрирования и владеть языком SQL на базовом уровне. Поэтому мы рекомендуем эту СУБД только для опытных пользователей.