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

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

Из этой статьи вы узнаете, чем так хороша PostgreSQL, научитесь её устанавливать в Windows и Linux и создадите базу данных с двумя связанными таблицами. А заодно погрузитесь в историю этого замечательного проекта.

Содержание:

• Что такое PostgreSQL и для чего она нужна
• Достоинства и недостатки PostgreSQL
• POSTGRES -> PostgreSQL: история проекта
• Как установить Postgres в Windows и Linux
• Как создать и связать две таблицы
• Что почитать и где следить за новостями

Что такое PostgreSQL и для чего она нужна

PostgreSQL — это свободно распространяемая объектно-реляционная система управления базами данных (СУБД) с открытым исходным кодом, написанном на языке C.

«Объектно-реляционная» означает, что PostgreSQL поддерживает концепции, присущие как реляционным базам данных, так и объектно-ориентированным языкам программирования (объекты, классы, наследование и другие). Далее мы рассмотрим, как эта поддержка реализуется и какие преимущества даёт при разработке приложений.

Читайте также:

Система управления базами данных: что это такое и зачем она нужна

В своих продуктах PostgreSQL используют такие IT-гиганты, как Huawei, Alibaba, «Инстаграм»* и Yahoo. Согласно исследованию Stack Overflow, в котором приняло участие 48 тысяч профессиональных разработчиков, в 2022 году PostgreSQL была самой популярной СУБД.

Преимущества и недостатки PostgreSQL

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

Преимущества

Расширяемость и богатый набор типов данных. Помимо стандартных, в PostgreSQL есть типы для геометрических расчётов, сетевых адресов и полнотекстового поиска. Мощная система расширений позволяет добавлять новые возможности и типы данных. Кроме того, администратор может писать свои функции и процедуры на Python, PHP, Java, Ruby и многих других языках программирования, а также загружать модули на языке C из центрального репозитория PGXN.

Масштабируемость. Для повышения производительности и масштабируемости в PostgreSQL используются разные виды блокировок на уровне таблиц и строк; шесть видов индексов, среди которых B-дерево и обобщённое дерево поиска (GiST) для полнотекстового поиска; наследование таблиц — для быстрого создания таблиц на основе имеющейся структуры. Вы также можете анализировать скорость выполнения запросов с помощью команды explain, очищать диск от мусора командой vacuum и собирать статистику по таблицам с analyze.

Кросс-платформенность. PostgreSQL поддерживается всеми популярными операционными системами, среди которых различные дистрибутивы Linux и BSD, macOS, Windows, Solaris и другие. Интерфейсы для этой СУБД реализованы практически во всех языках программирования.

Безопасность. В PostgreSQL есть множество инструментов для защиты данных от злоумышленников: пароль, Kerberos, LDAP, GSSAPI, SSPI, PAM и другие. Она позволяет управлять доступом к объектам БД на нескольких уровнях — от базы данных до отдельных столбцов, а также шифровать данные на аппаратном уровне.

Возможности NoSQL. Помимо стандартных форматов, PostgreSQL поддерживает XML, а с девятой версии — JSON и JSONB. Последний позволяет не разбирать JSON-документ перед записью в базу данных, что существенно ускоряет его сохранение в БД.

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

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

Недостатки

И всё-таки добавим капельку дёгтя в нашу бочку мёда. Да, при всех достоинствах у этой прекрасной СУБД есть недостатки.

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

Высокое потребление ресурсов. PostgreSQL может потреблять больше ресурсов (памяти и процессорного времени) по сравнению с некоторыми другими СУБД. Особенно это заметно при работе с большими объёмами данных и сложными запросами.

Отсутствие некоторых функций. В сравнении с некоторыми коммерческими СУБД PostgreSQL может слегка отставать в функциональности.

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

Как POSTGRES превратилась в PostgreSQL: история проекта и комьюнити

В этом разделе мы кратко рассмотрим историю Postgres. Если вы хотите поскорее установить её и попрактиковаться, то можете сразу переходить к следующему разделу.

В 1985 году, когда SQL ещё не был мировым стандартом, группа инженеров Калифорнийского университета Беркли под руководством профессора Майкла Стоунбрейкера начала разработку реляционной СУБД POSTGRES. В основе проекта лежали наработки, которые Стоунбрейкер сделал во время работы над INGRES — одной из первых реляционных СУБД.

К 1988 году команда опубликовала ряд научных статей, описывающих язык запросов POSTQUEL, который лежал в основе POSTGRES. Название недвусмысленно намекало на то, что новый язык более современный и продвинутый, чем SQL. Саму POSTGRES называли «постреляционной СУБД» — её создавали для того, чтобы преодолеть ставшие тогда очевидными ограничения SQL.

Первая версия инновационной СУБД вышла в 1989 году, но уже в 1992-м, после нескольких обновлений, проект закрыли. К счастью, исходный код POSTGRES распространялся по лицензии BSD. Выпускники Беркли Эндрю Ю и Джоли Чену продолжили её развивать, заменив язык POSTQUEL на SQL, который на тот момент уже стал международным стандартом. Новая инкарнация сперва получила имя Postgres95 (по году создания), а затем PostgreSQL. С тех пор вышло 28 версий СУБД.

Сегодня проект поддерживает довольно небольшая по нынешним меркам команда разработчиков. Во главе команды стоит управляющий комитет (Core Team) — члены комитета принимают решения по развитию и выпуску новых версий Postgres. Разработчики делятся на обычных (contributors) и основных (major contributors). Кроме того, небольшая группа разработчиков (commiters) имеет право вносить изменения в исходный код.

В число основных разработчиков входят и три программиста из России: Олег Бартунов, Фёдор Сигаев и Александр Коротков.

На момент публикации статьи последней стабильной версией является PostgreSQL 15, а 29 июня 2023 года стала доступна вторая бета-версия PostgreSQL 16.

Как установить Postgres в Windows и Linux

Установщик для любой операционной системы есть на официальном сайте PostgreSQL в разделе Latest Releases. На главной странице сайта нажмите кнопку Download и выберите свою операционную систему:

Далее мы рассмотрим процесс установки для Windows и Linux.

Установка и запуск в Windows

Нажмите на иконку Windows в разделе Downloads, и вы попадёте на страницу установщика PostgreSQL. Установщик включает в себя следующие утилиты:

• сервер PostgreSQL,
• pgAdmin4 — графический инструмент для управления базой данных,
• менеджер пакетов Stack Builder.

Перейдите на страницу выбора версии установщика, нажав на ссылку Download the installer. Перед вам откроется список доступных файлов — выбирайте последнюю версию (на скриншоте это версия 15.3). Обратите внимание на разрядность скачиваемого файла — она должна совпадать с разрядностью вашей ОС.

Запустите установщик. В приветственном окне нажмите Далее и выберите путь установки или оставьте папку по умолчанию.

Затем нужно будет выбрать компоненты. По умолчанию галочки стоят во всех пунктах, но устанавливать всё необязательно. Главное для нас — сервер Postgres и pgAdmin. Также рекомендуем оставить инструменты командной строки, потому что они наверняка понадобятся для администрирования БД.

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

На следующем шаге вас попросят придумать пароль администратора — он понадобится для подключения к серверу PostgreSQL и входа в pgAdmin 4. Не забудьте его записать, чтобы не пришлось всё сносить и устанавливать заново :)

Затем установщик предложит выбрать язык и ещё раз покажет настройки, с которыми будет установлена PostgreSQL. Нажимайте «Далее», пока не начнётся процесс установки — он займёт несколько минут. По окончании в меню «Пуск» появится папка с компонентами и документацией:

Проверим, всё ли работает. Запустите pgAdmin и кликните на Servers в левом верхнем углу. Вас попросят ввести пароль пользователя — это тот, пароль, который вы задавали во время установки.

Если пароль введён верно, вы увидите такой дашборд:

Чтобы проверить, подключён ли сервер, давайте спросим у него версию СУБД командой SELECT VERSION ():

Если в нижней области экрана появился ответ, значит, сервер подключён и готов принимать запросы. С чем вас и поздравляем!

Установка и запуск в Linux на примере Ubuntu

Как и все программы в ОС Linux, PostgreSQL проще всего установить через терминал из официального репозитория вашего дистрибутива. Откройте Bash и актуализируйте репозитории в своей системе:

$ sudo apt update

Затем одной командой установите PostgreSQL:

$ sudo apt -y install postgresql

Флаг -y отключает подтверждение установки (Y/n).

Когда установка завершится, убедитесь, что служба PostgreSQL включена (enabled) и сервер готов принимать запросы (accepting connections):

$ sudo systemctl is-enabled postgresql

$ sudo pg_isready

Терминал должен ответить так:

Теперь создадим базу данных и пользователя — чтобы работать с сервером через панель pgAdmin 4. Когда вы установили PostgreSQL, в вашей системе появилась учётная запись postgres — из-под неё можно управлять служебными процессами и создавать новых пользователей. Переключитесь на этого пользователя следующей командой:

$ sudo su - postgres

После этого в приглашение ко вводу появится префикс postgres:

Создавать новую БД будем в командной оболочке psql:

$ psql

Если приглашение для ввода изменилось на postgres=#, то вы находитесь в psql.

Теперь можно создать пользователя, под которым вы будете работать в pgAdmin, и пароль для него:

postgres=# CREATE USER anton WITH PASSWORD 'j8IIj_p0kZ';

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

Теперь создайте базу данных. Мы назвали её skillbox_test_db:

postgres=# CREATE DATABASE skillbox_test_db;

Чтобы пользователь, которого мы недавно создали, обладал всеми правами в новой БД, их нужно назначить:

GRANT ALL PRIVILEGES ON DATABASE skillbox_test_db to anton;

Если оболочка подтвердила выдачу прав словом GRANT, выйдите из неё, нажав комбинацию клавиш Ctrl + D.

Наконец, можно устанавливать pgAdmin. К сожалению, здесь тоже придётся немного повозиться. Программа не доступна из репозитория Ubuntu, поэтому придётся устанавливать её из репозитория поставщика — pgAdmin 4 APT.

Сначала установить открытый ключ GPG — он нужен, чтобы проверять подлинность репозитория:

$ curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

Затем создайте конфигурационный файл для репозитория — из него утилита apt будет получать информацию, необходимую для установки пакетов:

$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Наконец, установите pgAdmin 4:

$ sudo apt install pgadmin4

После выполнения команды у вас на компьютере будут все файлы, необходимые для запуска pgAdmin 4. Но перед началом работы нужно запустить скрипт, который подготовит pgAdmin к работе:

$ sudo /usr/pgadmin4/bin/setup-web.sh

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

Теперь вы можете запустить pgAdmin 4 по IP-адресу http://127.0.0.1/pgadmin4. В окне авторизации введите адрес электронной почты и пароль, которые указывали на предыдущем шаге. Если данные введены верно, то вы увидите такой интерфейс:

Напомним, что pgAdmin 4 — это клиент, который предоставляет удобный доступ к PostgreSQL. Чтобы работать с данными, нужно подключить его к серверу. Для этого кликните на иконку Add New Server:

Задайте серверу имя и перейдите к настройкам:

Здесь нужно заполнить три поля: host name (для работы на локальном компьютере пишем localhost), username и password. Имя пользователя и пароль вы задавали заранее:

Если вы всё ввели правильно, слева появится информация об имеющихся базах данных. Проверим подключение, запросив в Query Tool номер версии СУБД командой SELECT VERSION ():

В нашем случае ответ выглядит так:

Другими словами, сервер PostgreSQL успешно подключился к pgAdmin 4 и готов принимать от него запросы.

Как создать и связать две таблицы

Теперь, когда база данных подключена, давайте попрактикуемся с SQL-запросами. Представьте следующую ситуацию: менеджеры Skillbox попросили нас создать базу данных студентов, в которой будет видно, кто на каком курсе учится. Для простоты будем считать, что каждый студент может учиться только на одном курсе.

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

Создаём таблицы

Таблицу с курсами, как это ни удивительно, назовём Courses. Она будет состоять из следующих полей:

• course_id — уникальный идентификатор курса, который генерируется автоматически и не может быть равен NULL. Это поле будет выполнять роль первичного ключа (primary key);
• course_name — название курса. Размер поля не превышает 100 символов, а значение не может быть равно NULL;
• hours — количество часов в виде целого числа;
• price — стоимость курса в виде числа с плавающей точкой;
• is_open — хранит информацию о том, открыт или закрыт курс, и не может содержать NULL.

У каждого студента в таблице будет поле с идентификаторами курсов, на которые он записан. При этом набор id должен быть строго ограничен, а сохранение несуществующих id — запрещено.

Создать таблицу можно с помощью SQL-запроса в командной оболочке psql или в окне Query Tool (см. Установка и запуск PostgreSQL в Linux) pgAdmin:

CREATE TABLE courses(
	course_id integer NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	course_name character varying (100) NOT NULL, 
	hours integer, 
	price float,
	is_open boolean DEFAULT TRUE
);

Другой путь пролегает через кнопки и окна графического интерфейса. На боковой панели перейдите в раздел Schemas («Схемы») и кликните правой кнопкой мыши на разделе Tables («Таблицы»). Появится контекстное окно с приглашением создать новую таблицу — переходим:

Задайте таблице имя в разделе с общими настройками и перейдите в раздел Columns. Здесь можно создать и настроить необходимые поля.

Создадим и настроим поле course_id. Нажмите «+» в правом углу модального окна и в появившейся строке заполните параметры в соответствии с условиями: поле является первичным ключом и никогда не должно принимать значение NULL.

Осталось добавить автоматическую генерацию значений. Для этого нажмите на иконку карандаша (edit row) — откроется раздел с подробными настройками. Перейдите в раздел Constraints («Ограничения») и выберите тип IDENTIFY. Готово! Вы создали автоматически генерируемый первичный ключ.

Попробуйте заполнить остальные поля самостоятельно.

В результате у вас должен получиться такой набор полей:

Во второй таблице (students) будут храниться данные студентов и ссылки на курсы из таблицы courses:

• student_id — уникальный идентификатор студента (primary key);
• name — имя студента;
• middlename — отчество;
• lastname — фамилия;
• contacts — контакты (телефон, email, ссылки на соцсети и так далее);
• course — курсы (набор id из таблицы с курсами). Это поле выступает внешним ключом, который связывает таблицы между собой.

Так как у одного студента может быть несколько контактов, то для поля contacts подойдёт тип character varying[] (массив строк ограниченной длины).

Создадим новую таблицу и настроим столбцы, исходя из условий:

Сделаем так, чтобы столбец course ссылался на таблицу courses. Для этого перейдите во вкладку Constraints («Ограничения») → Foreign Key («Внешний ключ») и нажмите на +.

Задайте произвольное название ограничению — оно ни на что не влияет. Затем свяжите столбцы: в Local Column («Локальный столбец») укажите поле course, в References («Ссылается на») таблицу courses и в Referencing («Зависимый столбец») поле course_id. Готово! Вы создали и связали две таблицы.

О том же самом сервер можно было бы попросить на языке SQL:

CREATE TABLE students (
      student_id integer NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      name character varying (30) NOT NULL,
      middlename character varying (30) NOT NULL,
	  lastname character varying (30) NOT NULL,
      contacts character varying[],
      course integer,
    CONSTRAINT students_courses FOREIGN KEY (course) REFERENCES courses (course_id)
);

Что почитать и где следить за новостями

Часто опытные разработчики рекомендуют вместо книг читать «доку», и на то есть несколько причин:

• Официальная документация регулярно обновляется и, как правило, учитывает последние изменения в проекте. В то время как книги устаревают с выходом новых версий ПО.
• Документация всегда под рукой (в интернете или в pdf-формате) и организована в виде удобного справочника, что ускоряет поиск ответов на вопросы.
• И самое приятное — она бесплатная.

В случае с PostgreSQL официальное руководство лучше любой книги ещё и тем, что охватывает практически все вопросы, касающиеся установки, настройки и администрирования СУБД. В pdf-версии документа уже почти 3000 страниц! Ни одна книга не может похвастаться таким объёмом.

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

Вот несколько книг, на которые стоит обратить внимание:

• «PostgreSQL для начинающих» — П. Лузанов, Е. Рогов, И. Лёвшин.
• «PostgreSQL: основы языка SQL» — Е. П. Моргунов.
• «Оптимизация запросов в PostgreSQL» — Добровская Г., Новиков Б., Бейликова А.
• «Изучаем PostgreSQL 10» — Салахаддин Джуба.

Следить за новостями можно на официальном сайте или в Twitter-аккаунте PostgreSQL (второй способ с VPN удобнее). Наконец, задать технические вопросы можно в чате русскоязычного сообщества в Telegram: https://t.me/pgsql.

* Решением суда запрещена «деятельность компании Meta Platforms Inc. по реализации продуктов — социальных сетей Facebook и Instagram на территории Российской Федерации по основаниям осуществления экстремистской деятельности».