PostgreSQL: основы работы и руководство для начинающих
PostgreSQL — open source-решение, которое может составить конкуренцию коммерческим СУБД корпоративного уровня. Оно объединяет механизмы администрирования и аналитику. PostgreSQL может подстраиваться под задачи бизнеса, в чем главное конкурентное преимущество. Рассказываем, как установить и использовать систему.
Установка и первичная настройка PostgreSQL
Нужно выбрать, каким способом вы будете устанавливать СУБД и как потом работать — через командную строку или графические инструменты.
Выбор способа установки для вашей ОС
Для Windows используется официальный установщик от EnterpriseDB. Он загружает PostgreSQL с графическим интерфейсом pgAdmin и дополнительными инструментами. Способ подойдет, если не хотите разбираться с командной строкой.
На macOS PostgreSQL устанавливают через Homebrew. Это удобный способ, если вы уже используете терминал. Одна команда, и система сама все сделает. Альтернатива — установщик от EnterpriseDB. Используйте его, если нужен графический интерфейс «из коробки».
В Linux PostgreSQL устанавливается из официальных репозиториев системы. Это удобный вариант, поскольку для установки и обновления достаточно стандартных команд. Сервис сразу будет готов к работе.
Если хотите запустить PostgreSQL без установки на ОС, можете использовать Docker. Контейнер запустится одной командой. Этот вариант подходит для тестов, обучения и временных проектов. База данных будет изолированно работать, не затрагивая операционку.
После установки проверьте, что сервис работает. На Linux-системах используйте systemctl. В Windows для управления службами применяется стандартная консоль services.msc.
Посмотрите статус сервиса с помощью команды:
Альтернативный способ — утилита pg_isready, которая проверяет доступность сервера без указания дополнительных параметров:
Если служба отвечает, значит, система установилась и нормально работает.
Подключение к серверу: psql и альтернативы
Подключиться к PostgreSQL можно с помощью psql — терминального клиента, который входит в поставку решения. Он позволит работать с базой через sudo -u postgres psql из командной строки. Это оптимально для администраторов, поскольку можно обойтись без дополнительных инструментов.
Если нужен графический интерфейс, выбирайте pgAdmin 4, DBeaver или DataGrip. Эти инструменты облегчают работу с базами и аналитику благодаря наглядному представлению информации.
Фундамент: концепции и объекты базы данных
Перед началом работы с PostgreSQL, разберитесь, как устроена база изнутри. В системе все объекты организованы по иерархической структуре, благодаря чему проще поддерживать порядок в данных и управлять доступом.
Иерархия объектов
Система устроена по принципу «от большого к малому». Верхний уровень — это кластер, то есть запущенный сервер PostgreSQL. Он объединяет все базы данных и пользователей. Когда вы подключаетесь к PostgreSQL, то подключаетесь именно к нему.
Внутри кластера находятся базы данных — отдельные пространства для конкретных проектов и приложений. Они изолированы друг от друга, то есть таблицы одной базы нельзя просматривать из другой. Это особенно удобно, если на одном сервере хранятся данные разных систем.
Следующие в иерархии — схемы. Они помогают избежать хаоса в базе данных. Если таблиц много, схемы помогают распределить их по содержимому. Например, отдельно хранятся справочники, отчеты, данные приложений. Также они помогают контролировать безопасность — можно дать доступ пользователю к только к конкретной схеме, а не ко всей базе.
Нижний уровень в иерархии — таблицы. Именно там в виде строк и столбцов хранятся данные. С таблицами вы будете работать чаще всего — просматривать информацию, добавлять новые записи и редактировать существующие.
Организация данныхРабота с пользователями (ролями) и привилегиями
В PostgreSQL нет понятия USER — есть ROLE. Это пользователь, который может совершать действия в системе в соответствии с назначенными правами доступа.
Роль можно создать с помощью команд:
CREATE ROLE создает новую роль, LOGIN разрешает подключение к базе, PASSWORD — задает пользовательский пароль.
Чтобы задать права на подключение к базе данных (не к таблице или схеме), например, mydb, воспользуйтесь командой:
Владельцы баз данных и пользователи с привилегией CREATEDB автоматически получают право на подключение. Команда GRANT CONNECT требуется только для обычных пользователей, которым нужно предоставить доступ к чужой базе данных.
Назначение права на схему:
Назначение прав на таблицы (чтение, добавление, изменение, удаление):
Команда для отзыв прав доступа без удаления роли:
Создание и удаление базы данных
Для создания используется команда CREATE DATABASE. Например, она может выглядеть так: CREATE DATABASE myapp_db. Чтобы записи в базе хранились в читаемом виде, следует указывать формат кодировки. Обычно используется стандартный вариант с UTF-8. В команде это выглядит так: CREATE DATABASE myapp_db ENCODING 'UTF8';.
Удаляется база данных командой DROP DATABASE. Перед манипуляцией убедитесь, что нет активных подключений. Если есть, команда завершится с ошибкой.
Создание базы данныхРабота с данными: от таблиц до запросов
В PostgreSQL поддерживаются разные типы данных. Стандартные, как в других СУБД и уникальные, которые нужны для работы со сложными структурами.
Группа | Тип | Описание |
Основные | INTEGER | Целочисленный тип, который используется для хранения любых целых чисел без дробной части |
VARCHAR(n) | Строки ограниченной длины, где могут храниться короткие тексты, имена и логины | |
TEXT | Строки произвольной длины без ограничений по размеру | |
BOOLEAN | Логический тип данных, может быть true или false | |
DATE | Хранение даты без времени | |
TIMESTAMPTZ | Хранение даты и времени с по текущему часовому поясу | |
Специфичные | JSON | Хранение данных без оптимизации структуры |
ARRAY | Упорядоченный набор разных элементов | |
UUID | Хранение уникальных идентификаторов в 128-битном формате | |
HSTORE | Хранение в одном поле пар «ключ–значение» | |
Геопространственные типы | Типы данных для работы с координатами |
Создание и изменение таблиц
Таблица создается с помощью CREATE TABLE. Удобно сразу задать первичный ключ PRIMARY KEY и ограничения NOT NULL и UNIQUE, если они нужны. Ключ помогает идентифицировать записи, NOT NULL запрещает нулевые значения, UNIQUE — предотвращает дублирование данных.
Для изменения структуры таблицы применяется команда ALTER TABLE. Если хотите добавить столбцы, включите в нее опцию ADD COLUMN, удалить неактуальные — DROP COLUMN. Тип данных можно поменять с помощью ALTER COLUMN TYPE.
CRUD-операции: основы манипулирования данными
CRUD-операции — базовые действия с данными в таблицах. Они лежат в основе логики приложений, которые используют PostgreSQL. Действия и команды:
INSERT INTO — вставка данных. С помощью этой команды можно добавлять в таблицу новые строки, связывая столбцы и значения в них.
SELECT — команда для выборки данных. Чтобы получить нужные записи, задайте условия с помощью WHERE. Ограничить количество результатов можно с помощью опции LIMIT. Для сортировки используйте ORDER BY.
UPDATE — обновление данных. Команда позволяет изменить значение в строках таблицы. Обязательно задавайте условия с помощью WHERE, чтобы обновились отдельные записи, а не вся таблица.
DELETE — удаление данных. Чтобы удалить нужные записи, задайте условия с помощью WHERE, иначе удалятся все строки.
Чтобы безопасно работать с данными в PostgreSQL, используйте транзакции. Это последовательность операций, которые объединяются и выполняются одномоментно. Нужна совокупность команд — BEGIN, COMMIT и ROLLBACK. BEGIN начинает транзакцию, COMMIT — подтверждает изменения, ROLLBACK — позволяет откатиться назад при ошибке.
Продвинутые возможности для начинающих
Помимо работы с данными, освойте и другие действия для управления базами и безопасности. Коротко рассказываем о наиболее распространенных среди несложных.
Индексы для ускорения поиска
Индексы помогают находить записи, не просматривая всю таблицу. Можно создать их с помощью команды:
Работа с JSONB
PostgreSQL может хранить полуструктурированные данные. Для этого используется формат JSONB. Как его применить:
Основы агрегации и группировки
Чтобы подсчитать количество записей или сумму чисел, можно использовать агрегатные функции, такие, как COUNT(), SUM(), AVG(). Сгруппировать данные и фильтровать их по определенным условиям помогут команды GROUP BY и HAVING.
Резервное копирование и восстановление
Для безопасности стоит создать дамп базы данных:
Восстановить данные оттуда можно с помощью команды:
Мониторинг активности
Чтобы понять, какие запросы в базе выполняются, примените команду:
Размер базы данных можно узнать с помощью такой команды:
Распространенные ошибки новичков и их решение
При работе с PostgreSQL новички могут столкнуться с проблемами, связанными с ролями и права доступа. Их легко решить, если знать порядок действий. Распространенные ошибки:
Ошибка | Причина | Решение |
role "username" does not exist | В PostgreSQL нет такого пользователя или он не указан при подключении | Указать пользователя с помощью параметра -U username. Если пользователя нет, создать роль — CREATE ROLE username WITH LOGIN |
permission denied for table | Нет прав на чтение или изменение таблицы | Проверить права и выдать все недостающие привилегии одной командой, например, GRANT SELECT, INSERT, UPDATE ON table_name TO username |
Нет пароля пользователя postgres | Утерян доступ к административной учетке | Изменить метод аутентификации в конфигурационном файле pg_hba.conf, подключиться без пароля и задать новый командой ALTER ROLE postgres WITH PASSWORD 'new_password' |
Что изучать дальше для развития
Если освоили базовые возможности PostgreSQL, начните изучать продвинутые SQL-конструкции — оконные функции и рекурсивные запросы. Они позволяют работать с аналитическими выборками и иерархией данных. Посмотрите, какие расширения PostgreSQL можно использовать под ваши задачи.
Чтобы применять СУБД в production-окружении, разберитесь с механизмами надежности и обеспечения отказоустойчивости. В первую очередь нужны репликация, работа с журналами WAL и резервное копирование.
Посмотрите, с какими приложениями из вашей среды может интегрироваться PostgreSQL. Используйте только проверенные драйверы и библиотеки, ORM-фреймворки.
Чтобы упростить работу с базами данных, можно использовать облачный сервис Evolution Managed PostgreSQL от Cloud.ru.
Заключение
PostgreSQL позволяет хранить и обрабатывать данные без зависимости от сторонних сервисов. Освоив базовые и продвинутые возможности, вы сможете создавать безопасные приложения. Платформа интегрируется с инструментами разработки, масштабируется с бизнесом и справляется с большими объемами данных. Она подходит для проектов любого масштаба.