PostgreSQL — одна из самых популярных и мощных систем управления реляционными базами данных (СУБД). У неё открытый исходный код, плюс она абсолютно бесплатная. Система помогает создавать и хранить базы данных, а также работать с ними на языке SQL (Structured Query Language). Без СУБД это будет невозможно, поскольку технически БД — это просто файлы в определённом формате.
PostgreSQL используется как для небольших проектов, так и для крупных корпоративных приложений благодаря своей надёжности, расширяемости и поддержке обширного набора SQL-стандартов.
На первый взгляд кажется, что работа с PostgreSQL — непростая задача. Действительно, начинающим программистам синтаксис и команды системы могут быть непривычны, но со временем это чувство проходит. В этом руководстве мы собрали всё, что нужно для старта программирования в PostgreSQL — от установки до первых запросов.
В прошлом году PostgreSQL заняла четвёртую строчку в рейтинге самых популярных СУБД, уступив только Oracle, MySQL и Microsoft SQL Server. Вот несколько её преимуществ:
- Опенсорс. PostgreSQL — это СУБД с открытым исходным кодом. Это значит, что она доступна для использования, модификации и распространения без каких-либо лицензионных сборов.
- Надёжность. Эта система предоставляет несколько уровней защиты данных и механизмов восстановления после сбоев, что гарантирует минимальные потери данных и стабильную работу системы. Среди механизмов надежности — журналирование транзакций (Write-Ahead Logging, WAL) и поддержка транзакции ACID.
- Полная поддержка SQL-стандартов. PostgreSQL позволяет выполнять сложные запросы SQL, включая вложенные и агрегированные, поддерживает различные типы данных от базовых числовых и строковых типов до сложных JSON и XML, а также предлагает расширенные функции: оконные функции, общие табличные выражения (CTE) и множество встроенных функций для работы с датами, строками и географическими данными.
- Объектно-реляционная модель. СУБД поддерживает одновременно реляционный подход, то есть данные хранятся в виде записей, которые связаны друг с другом с помощью relations (отношения), и объектный подход, то есть данные представлены в виде объектов с атрибутами и методами, как в объектно-ориентированном программировании (ООП).
- Поддержка JSON и NoSQL. PostgreSQL предлагает средства для работы с JSON-документами, что позволяет использовать его как гибридную СУБД, объединяющую реляционные и NoSQL подходы. Она поддерживает два формата хранения JSON: JSON (текстовый) и JSONB (бинарный). JSONB оптимизирован для быстрого поиска и индексирования.
- Мощные средства индексирования. Среди типов индексов в этой системе управления БД — B-Tree, Hash, GIN и GiST, BRIN.
- Возможность использовать другие языки программирования. Postgre позволяет писать собственные функции и поддерживает больше языков программирования, чем остальные СУБД. Среди них — C, C++, Python, Java, PHP, Ruby, Lua и даже JavaScript (есть поддержка V8).
Кстати, на основе PostgreSQL создана отечественная СУБД Postgres Pro — в качестве замены Oracle. В рамках импортозамещения российским аналогом пользуются многие крупные компании и организации, например, Сбер, ФНС, Минфин, «Газпромнефть» и другие.
Как установить и запустить PostgreSQL
Перед тем как приступить к установке PostgreSQL, нужно определить, какая версия вам нужна. Каждая версия PostgreSQL включает обновления, улучшения и новые функции — выбор зависит от совместимости с вашими приложениями. Мы будем рассматривать установку и запуск на трёх операционных системах на примере последней 16 версии.
Для работы с PostgreSQL на Windows и macOS может понадобится графический интерфейс pgAdmin. В нем есть Query Tool, с помощью которого пишутся и выводятся SQL-запросы.
Установка и запуск на Windows
- Скачайте установочный файл с официального сайта PostgreSQL.
- Запустите установку. В появившемся мастере установки следуйте этим шагам:
- Выберите директорию установки и отметьте галочкой эти компоненты:
Компоненты для установки PostgreSQL
- Укажите данные и задайте пароль для суперпользователя (обычно это postgres):
Меню для ввода пароля
- Настройте порт для сервера (по умолчанию это 5432).
- Определите каталог для хранения данных.
3. После установки откройте pgAdmin и подключитесь к вашему новому серверу. Уже в этом интерфейсе вы будете создавать базы данных и таблицы и писать SQL-запросы.
Интерфейс pgAdmin
Установка на macOS
Установить СУБД на macOS можно тремя способами.
- С помощью интерактивного установщика от EDB. Его нужно скачать с официального сайта. Утилита уже включает в себя сервер PostgreSQL, pgAdmin и StackBuilder — менеджер пакетов, который можно использовать для загрузки и установки дополнительных инструментов и драйверов.
- Postgreapp. Это полнофункциональная программа для установки PostgreSQL, которая работает как стандартное приложение для Mac. Она включает в себя все необходимое для начала работы, даже популярное расширение PostGIS для геоданных.
- Через Homebrew. Homebrew — популярный пакетный менеджер для macOS. Убедитесь, что он установлен, и выполните команду:
После установки инициализируйте базу данных и запустите сервер:
initdb /usr/local/var/postgres
pg_ctl -D /usr/local/var/postgres start
Убедитесь, что сервер работает с помощью команды psql:
Установка на Linux
- Для установки на Ubuntu/Debian используйте команду в консоли:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Для CentOS/RHEL:
sudo yum install postgresql-server postgresql-contrib
На Ubuntu и Debian PostgreSQL автоматически инициализируется и запускается.
- На CentOS и RHEL выполните:
sudo postgresql-setup initdb
sudo systemctl start postgresql
На Linux тоже есть pgAdmin — его нужно скачать отдельно, однако он открывается не в приложении, а окне браузера. Однако большинство разработчиков, использующих эту операционную систему, работают с PostgreSQL в основном в консоли с помощью psql.
Основные команды SQL
SQL-команд очень много — о них можно почитать в официальной документации PostgreSQL. Но есть те, которыми вы будете пользоваться большую часть времени. Вот несколько основных команд:
CREATE — и все её вариации: CREATE DATABASE, CREATE TABLE, CREATE DOMAIN, CREATE USER и другие.
ALTER | DROP — изменить/удалить данные СУБД.
INSERT | UPDATE | DELETE/TRUNCATE — вставить/изменить/удалить данные в таблице.
SELECT, TABLE, WITH — получить строки из таблицы или представления.
Типы данных PostgreSQL
Прежде чем создавать базы данных и таблицы, нужно разобраться в основных типах данных этой СУБД. В PostgreSQL есть базовые и расширенные типы данных. Ниже рассмотрим основные (подробнее можно почитать в документации на русском языке).
Числовые типы
Числовые типы включают 2-, 4- и 8-байтные целые, 4- и 8-байтные числа с плавающей точкой, а также десятичные числа с задаваемой точностью.
Числовые типы данных PostgreSQL
Денежные типы
Здесь все проще, тип всего один.
Денежные типы данных
Символьные типы
В SQL есть два основных символьных типа: character varying(n) и character(n), где n — положительное число. Они могут хранить текстовые строки длиной до n символов.
Символьные типы данных
Типы даты/времени
Дата и время в PostgreSQL хранятся в целочисленном виде и считаются по Григорианскому календарю.
Типы времени/даты
Логический тип
В СУБД есть стандартный SQL-тип boolean, который может иметь состояния «true», «false» и «unknown» (оно представляется SQL-значением NULL).
Логический тип данных
Создаем первую таблицу в PostgreSQL
Для начала нужно завести базу данных:
CREATE DATABASE mydatabase
Это создаст новую базу данных с именем mydatabase. Затем подключаемся к нашей БД:
Теперь можно создавать таблицу. Синтаксис выглядит следующим образом:
CREATE TABLE table_name (
column1_name data_type [column_constraint],
column2_name data_type [column_constraint],
...
table_constraint
);
где
table_name: название новой таблицы.
column1_name, column2_name: названия столбцов в таблице.
data_type: тип данных столбца (например, INTEGER, VARCHAR(100), DATE).
column_constraint: опциональные ограничения на уровне столбца (например, NOT NULL, UNIQUE).
table_constraint: опциональные ограничения на уровне таблицы (например, PRIMARY KEY, FOREIGN KEY).
Рассмотрим пример создания таблицы employees с различными типами данных и ограничениями:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE NOT NULL,
salary NUMERIC(10, 2),
department_id INTEGER REFERENCES departments(id)
);
После выполнения этого SQL-запроса в вашей БД появится таблица employees с указанными столбцами и ограничениями (PRIMARY KEY, UNIQUE, NOT NULL и так далее).
Чтобы удалить базу данных или таблицу, понадобится команда drop:
drop database <database_name>
drop table <table_name>
Модификация таблиц
Если вы допустили ошибку при оформлении таблицы, PostgreSQL предоставляет мощный набор команд для изменения ее структуры без необходимости удаления.
Добавление столбца
ALTER TABLE employees ADD COLUMN bio TEXT;
Эта команда добавляет новый столбец bio типа TEXT в таблицу employees.
Удаление столбца
ALTER TABLE employees DROP COLUMN bio;
Удаляет столбец bio из таблицы employees.
Изменение типа данных столбца
ALTER TABLE employees ALTER COLUMN email TYPE VARCHAR(150);
Изменяет тип данных столбца email на VARCHAR(150).
Получение информации о таблице
Команда выводит информацию о структуре таблицы employees, включая список столбцов, их типы данных и ограничения.
Изменение данных в таблице
PostgreSQL также имеет много функций для модификации строк и колонок в таблице. Ниже несколько примеров.
Добавление новой записи
INSERT INTO employees (last_name, email) VALUES ('Ivanov', 'ivanivanov@example.com');
Вставляет новую запись в таблицу employees с указанными значениями для last_name и email.
Изменение существующих данных
UPDATE employees
SET email = 'ivan_ivanov@example.com'
WHERE last_name = 'ivanov';
Обновляет адрес электронной почты пользователя с фамилией ivanov.
Удаление записей
DELETE FROM employees
WHERE last_name = 'ivanov';
Удаляет записи из таблицы employees, где last_name равен ivanov.
Запросы с командой SELECT
Выбор всех записей из таблицы:
Фильтрация данных:
SELECT * FROM employees
WHERE email LIKE '%@example.com';
Сортировка данных:
SELECT * FROM employees
ORDER BY hire_date
DESC;
Команда сортирует записи по столбцу hire_date в порядке убывания.
Группировка данных:
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
Запрос вернет список департаментов и количество сотрудников в каждом из них.
Это наиболее простые запросы в PostgreSQL. Сложные будут включать, например, объединение данных через оператор JOIN (INNER JOIN, LEFT JOIN, RIGHT JOIN и другие).
Как оптимизировать выполнение запросов
С помощью индексов
Индексы играют важную роль в оптимизации выполнения запросов. PostgreSQL позволяет создавать индексы на одном или нескольких столбцах таблицы.
Создание индекса:
CREATE INDEX idx_username ON employees(last_name);
Этот индекс ускоряет поиск записей по last_name.
Использование уникальных индексов:
CREATE UNIQUE INDEX idx_unique_email ON employees(email);
Он обеспечивает уникальность значений в столбце email.
С помощью транзакций и блокировок
Транзакции позволяют группировать несколько операций в одну атомарную операцию. PostgreSQL также поддерживает механизмы блокировки для обеспечения согласованности данных.
Начало и завершение транзакции:
BEGIN;
UPDATE employees
SET email = 'ivan_updated@example.com'
WHERE last_name = 'ivanov';
COMMIT;
Откат транзакции:
BEGIN;
UPDATE employees
SET email = 'ivan_failed@example.com'
WHERE last_name = 'ivanov';
ROLLBACK;
PostgreSQL — мощная и многофункциональная СУБД. В этом руководстве мы рассмотрели основные аспекты установки, создания баз данных и выполнения простых SQL-запросов. На самом деле PostgreSQL не такая страшная, как кажется на первый взгляд. Главное — знать азы.
В прошлый раз я познакомил тебя
с
основами реляционных баз данных и SQL.
Теперь пришло время настроить рабочую среду. Ты будешь использовать
PostgreSQL в качестве своей СУБД, поэтому для начала ее нужно скачать и установить.
PostgreSQL поддерживает все основные операционные системы. Процесс установки прост, поэтому я постараюсь рассказать
о нем как можно быстрее.
Для Windows и Mac ты можешь загрузить установщик
с
веб-сайта EDB.
EDB больше не предоставляет пакеты для систем GNU/Linux. Вместо этого они рекомендуют вам использовать диспетчер
пакетов твоего дистрибутива.
Установщики включают в себя разные компоненты.
Вот самые важные из них:
- Сервер PostgreSQL (очевидно)
- pgAdmin, графический инструмент для управления базами данных
- Менеджер пакетов для загрузки и установки дополнительных инструментов и драйверов
Windows
Скачав установщик, запусти его как любой другой исполняемый файл. Процесс довольно прямолинеен,
но некоторые вещи все же заслуживают внимания.
Диалоговое окно «Выбрать компоненты» позволяет выборочно устанавливать компоненты.
Если у тебя нет веской причины что-то менять — оставляй все как есть.
По умолчанию PostgreSQL создает суперпользователя с именем postgres (воспринимай его как учетную запись
администратора сервера базы данных).
Во время установки тебе нужно будет указать пароль для суперпользователя (root).
Позже ты сможешь создать других пользователей и назначать им отдельные доступы и роли.
Мы вернемся к этому позже, а сейчас тебе понадобится учетная запись суперпользователя, чтобы начать использовать СУБД.
Чтобы запустить сервер разработки на твоем компьютере или localhost, необходимо
назначить ему порт.
Порт по умолчанию — 5432. Если ты устанавливаешь PostgreSQL впервые, то он скорее всего свободен.
Если окажется, что этот порт уже занят другим экземпляром PostgreSQL, ты можешь указать другое значение, например 5433.
После завершения установки ты сможешь запустить SQL Shell, поставляемый с Postgres.
Шаг за шагом ты выберешь сервер, какую базу данных использовать, порт, имя пользователя и пароль.
Используй данные, которые ты вводил на предыдущих шагах.
Поздравляю! Настройка для Windows завершена, и скоро мы начнем писать первые SQL запросы.
Ниже список вариантов установки для других операционных систем.
macOS
Для macOS у тебя есть разные варианты. Можно скачать установщик с сайта EDB и запустить его.
Кроме того, можно использовать Postgres.app, простое приложение для macOS.
После запуска у тебя появится сервер PostgreSQL, готовый к использованию.
Завершить работу сервера можно просто закрыв приложение.
Кроме того, ты также можете использовать Homebrew, менеджер пакетов для macOS.
GNU/Linux
Ты можешь найти PostgreSQL в репозиториях большинства дистрибутивов Linux. Установить его можно одним щелчком мыши
из выбранного графического диспетчера пакетов.
Альтернативно, можно использовать установку через терминал.
Ты можешь обратиться к документации твоего дистрибутива для получения дополнительных сведений.
Ubuntu
sudo apt-get install postgresql
Ubuntu PostgreSQL configuration guide
Fedora
sudo yum install postgresql-server postgresql-contrib
Fedora PostgreSQL configuration guide
openSUSE
sudo zypper install postgresql postgresql-server postgresql-contrib
openSUSE PostgreSQL configuration guide
Arch
sudo pacman -S postgresql
Arch PostgreSQL configuration guide
Запуск оболочки PostgreSQL
После установки PostgreSQL, нужно запустить оболочку(shell), с помощью которой ты получишь возможность управлять базой данных.
Открой терминал и введи:
psql — это оболочка Postgres, аргумент -U используется для указания пользователя.
Поскольку ты еще не создавал других
пользователей, ты войдешь в систему как суперпользователь postgres.
После этого нужно будет ввести пароль
суперпользователя, который ты выбрал во время установки.
Как только пароль установлен, база данных PostgreSQL готова к работе!
Если сервер PostgreSQL по какой-то причине не запускается, можешь попробовать запустить его вручную.
sudo systemctl start postgres
Понимание модели клиент-сервер
Я уже упоминал PostgreSQL Server как важный компонент базы данных. Но что такое сервер в этом контексте и зачем он нам нужен?
Для начала тебе необходимо понимать модель клиент-сервер.
Почти все СУБД (PostgreSQL, MySQL и другие) следуют клиент-серверной модели. В ней база данных находится на сервере, и клиент
отправляет запросы на сервер, который их обрабатывает.
Под клиентом здесь подразумевается бекэнд нашего приложения, а запросы в — это SQL операции, такие как SELECT, INSERT, UPDATE и DELETE.
Для разработки любого бекэнда, тебе нужен локальный сервер для экспериментов и тестирования.
Этот локальный сервер аналогичен удаленному, но работает прямо на твоем компьютере.
С точки зрения клиента удаленный и локальный сервер идентичны. После разработки и тестирования ты можешь заставить свой
продукт взаимодействовать с удаленным сервером вместо локального, просто изменив пару параметров.
Некоторые базы данных не используют эту модель, например SQLite, которая хранит все в простом файле на диске. Это хорошо
работает для небольших приложений, но для большинства реальных приложений тебе понадобится архитектура клиент-сервер.
Мета-команды PostgreSQL
Теперь, когда ты все настроил и готов приступить к работе с базой данных, осталось разобрать несколько мета-команд.
Это не SQL запросы, а команды специфичные для PostgreSQL.
В других системах управления базами данных есть их аналоги, но их синтаксис немного отличается.
Всем мета-командам предшествует обратная косая черта \, за которой следует фактическая команда.
Список всех баз данных
Чтобы получить список всех баз данных на сервере, ты можешь использовать команду \ l.
Ввод этой мета-команды в оболочке Postgres выведет:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
Это список всех имеющихся баз данных и служебная информация, такая как владелец базы данных, кодировка и права доступа.
На данный момент мы пока ничего не создали, а базы данных которые ты видишь на экране — создаются по умолчанию при установке Postgres.
- postgres — это просто пустая база данных.
- «template0» и «template1» — это служебные базы данных, которые служат шаблоном для создания новых баз.
Тебе пока не стоит беспокоиться о них. Если хочешь изучить все детали, то проверь официальную документацию.
Подключаемся к базе данных PostgreSQL
Некоторые команды SQL требуют, чтобы ты сначала вошел в базу данных (например, для создания новой таблицы).
Ты можешь выбрать, в какую базу данных входить, при запуске SQL Shell.
Когда ты находишься внутри оболочки (shell), то можешь использовать команду \c (или \connect), за которой следует имя
базы данных. Если бы у тебя была другая база данных под названием hello_world, то подключиться к ней можно было бы так:
Полностью в терминале у тебя получится что-то такое:
postgres=# \c hello_world
You are now connected to database "hello_world" as user "postgres".
hello_world=#
Обрати внимание, что приглашение оболочки изменилось с postgres на hello_world. Это значит, что теперь ты
подключен к базе данных hello_world, а не postgres.
Получить список всех таблиц в базе данных
Как и в случае со списком существующих баз данных, ты можешь получить список таблиц внутри конкретной базы данных
с помощью команды \dt.
Перед выполнением этой команды вам необходимо войти в базу данных.
Предположим, ты уже находишься внутри базы hello_world, и в ней есть таблица с именем my_table. Набрав \dt, ты
получишь следующее:
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | my_table | table | postgres
Ты можешь увидеть имя таблицы и некоторую другую информацию, такую как схема (мы обсудим схемы в более сложных
руководствах) и владельца.
Владелец (owner) — это пользователь, который создал таблицу.
Если ты создаешь других пользователей и используешь их для создания таблиц, то в последнем столбце будут именно они.
Список пользователей и ролей
Как ты уже знаешь, при установке Postgres создается суперпользователь с именем postgres.
Список всех пользователей базы данных можно вывести на экран используя команду \dg.
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Обрати внимание, что первый столбец называется — роль (role name).
И весь вывод на экран называется “список ролей” (List of roles), а не список пользователей.
В PostgreSQL пользователи и роли практически
одинаковы.
У ролей есть атрибуты, которые определяют их разрешения, такие как создание баз данных или даже создание
других новых ролей.
Любая роль с атрибутом LOGIN может рассматриваться, как пользователь.
Здесь мы видим только одну роль, суперпользователя по умолчанию.
В реальном мире все будет иначе, потому что использовать только суперпользователя все время опасно.
Вместо этого создают другие роли с меньшими привилегиями.
Это гарантирует, что никто не совершит нежелательных действий по ошибке.
Если у одной из ролей есть доступ только на чтение данных, то с помощью этой роли будет невозможно удалить таблицу или поле.
Твой первый SQL оператор
Наконец, мы все настроили и готовы к работе и знаем основные мета-команды, специфичные для PostgreSQL.
Теперь приступим к изучению языка запросов SQL.
Я покажу тебе несколько базовых примеров, чтобы разобраться в структурированном языке запросов и получить представление о SQL. А более подробно мы рассмотрим операции CRUD это в следующей статье.
Создание новой базы данных
Первое, что тебе нужно узнать при изучении баз данных, — это как создать базу данных.
Создать базу можно сделать с помощью команды CREATE DATABASE, за которой следует имя базы данных:
Команды и ключевые слова SQL обычно пишутся в верхнем регистре.
На самом деле это не является обязательным требованием, и обычно они нечувствительны к регистру.
То есть ты мог бы написать
И все сработало бы нормально.
Но при написании операторов SQL обычно предпочтительнее прописные буквы. Это
хорошая практика, потому что она может помочь тебе визуально отличить ключевые слова SQL от других частей оператора,
таких как имена таблиц и столбцов.
Заметь, что все стандартные команды в PostgreSQL должны заканчиваться точкой с запятой ;. Это часть стандарта.
Для мета-команд PostgreSQL точка с запятой не нужна.
Создание таблиц
После того как ты создал новую базу данных, можно приступать к созданию таблиц.
Но, для начала, подключимся к новой базе данных с помощью команды \c, за которой следует имя базы данных:
Теперь, когда ты подключился к базе данных (обратите внимание, что приглашение оболочки SQL теперь включает имя активной
базы данных), ты готов создать свою первую таблицу.
Таблица создается с помощью команды CREATE TABLE, за которой
следует список столбцов таблицы и их типы данных в круглых скобках:
CREATE TABLE products (
id INT,
name TEXT,
quantity INT
)
Это создаст таблицу под названием products, которая содержит 3 столбца:
idтипаINT(целое число)nameтипаTEXT(строка)quantityтакже типаINT
После создания таблицы перейдем к добавлению данных.
Вставка данных в таблицы PostgreSQL
Чтобы добавить данные в таблицу, используют команду INSERT INTO следующим образом:
INSERT INTO products (id, name, quantity) VALUES (1, 'first product', 20);
Посмотрим на команду INSERT INTO подробнее:
- Команда
INSERT INTOозначает, что вы собираетесь вставить новые данные products— это имя таблицы в базе данных, в которую ты хочешь вставить данные(id, name, quantity)— это список столбцов в нашей таблице, разделенных запятыми. Тебе не нужно указывать
все столбцы (иначе какой в смысл?). В некоторых случаях вы хотите выборочно вставлять данные в некоторые
столбцы. Остальные столбцы будут автоматически заполнены значениями по умолчанию.VALUES (1, 'first product', 20)— это фактические данные, которые будут вставлены в таблицу. «1» — этоid,
«first product» — этоname, «20» — этоquantity.
Выборка данных из SQL таблицы
Теперь, когда ты добавил в таблицу первую запись, ты можешь использовать SQL для
получения содержимого таблицы.
Выборка данных осуществляется с помощью команды SELECT, и это выглядит следующим
образом:
SELECT id, name, quantity FROM products;
Мы используем команду SELECT. За ней следует список столбцов, которые мы хотим получить.
Затем мы используем команду FROM, чтобы указать, из какой таблицы брать данные. На этот раз это таблица products.
id | name | quantity
---+---------------+----------
1 | first product | 50
Для ситуаций когда ты хочешь выбрать все столбцы которые есть в таблице, ты можешь поставить звездочку вместо списка полей.
Звездочка означает: выбрать все столбцы. Результат останется прежним.
Ты должен обратить внимание на то, как команда SELECT выбирает столбцы и строки. Столбцы указываются в виде списка и
разделяются запятыми. Затем команда переходит к выбору запрошенных строк.
Если условия не указаны (как в этом случае), будут выбраны все строки в таблице.
Позже мы увидим, как использовать условия с командой WHERE для создания эффективных запросов.
Обновление данных в PostgreSQL
Представь, что ты запустил свое потрясающее приложение для магазина и получили первый заказ на один из продуктов.
Первое, что нужно сделать — это обновить доступное количество в вашем инвентаре, чтобы в дальнейшем у вас не возникли
проблемы с отсутствием товара на складе.
Для обновления данных ты можешь использовать команду UPDATE:
UPDATE products SET quantity=49 WHERE id=1;
Давайте разберемся с тем как работает UPDATE.
Начинаем мы с ключевого слова UPDATE, за которым следует имя таблицы.
Затем мы используем SET, чтобы установить новые значения для наших столбцов.
После SET — пишем имена столбцов, которые
хотим обновить.
За ними — знак равенства и новое обновленное значение.
Также ты можешь обновить сразу несколько столбцов, разделив их запятыми:
UPDATE products SET name='new name', quantity=49 WHERE id=1;
Но стоп, какие строки обновляются этой командой?
Ты уже должны были догадаться об этом. Чтобы указать, какие строки
следует обновить новыми значениями, мы используем команду WHERE, за которым следует условие.
В этом случае мы сопоставляем строки, используя их столбец id, и обновляем строку с id 1.
Удаление данных из SQL таблицы
Теперь рассмотрим случай, когда ты прекратил продажу определенного продукта и захотел полностью удалить его из своей
базы данных.
Для этого можно использовать команду DELETE:
DELETE FROM products WHERE id=1;
Как и при обновлении данных, чтобы определить, какие именно строки мы хотим удалить, нам нужно условие WHERE.
Удаление таблиц в PostgreSQL
Если вдруг ты решил изменить структуру базы и для этого нужно удалить всю таблицу, то тебе подойдет команда DROP TABLE:
Это приведет к удалению всей таблицы products из базы данных.
Будь очень осторожен с командой DELETE!
Я бы не позавидовал тому, кто “случайно” удалит не ту таблицу из базы данных.
Удаление баз данных PostgreSQL
Точно так же ты можешь удалить из системы всю базу данных:
Заключение
Поздравляю, у тебя все получилось!
Ты установил и запустили PostgreSQL. Ты изучил основные команды SQL и проделали с ними несколько интересных вещей.
Эти несколько простых команд — основа, которую ты будешь использовать большую часть времени при взаимодействии с базами
данных, поэтому тебе следует пойти и потренироваться и изучить самостоятельно. В следующий раз мы погрузимся глубже и
обсудим
Базы данных, роли и таблицы в PostgreSQL
PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.
Содержание:
- Установка PostgreSQL 11 в Windows 10
- Доступ к PostgreSQL по сети, правила файерволла
- Утилиты управления PostgreSQL через командную строку
- PgAdmin: Визуальный редактор для PostgresSQL
- Query Tool: использование SQL запросов в PostgreSQL
Установка PostgreSQL 11 в Windows 10
Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.
В процессе установки установите галочки на пунктах:
- PostgreSQL Server – сам сервер СУБД
- PgAdmin 4 – визуальный редактор SQL
- Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
- Command Line Tools – инструменты командной строки
Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).
По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.
Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.
Доступ к PostgreSQL по сети, правила файерволла
Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.
Запустите командную строку от имени администратора. Введите команду:
netsh advfirewall firewall add rule name="Postgre Port" dir=in action=allow protocol=TCP localport=5432
- Где rule name – имя правила
- Localport – разрешенный порт
Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:
New-NetFirewallRule -Name 'POSTGRESQL-In-TCP' -DisplayName 'PostgreSQL (TCP-In)' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432
После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.
Совет. Для изменения порта в установленной PostgreSQL отредактируйте файл postgresql.conf по пути C:\Program Files\PostgreSQL\11\data.
Измените значение в пункте
port = 5432
. Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:
Restart-Service -Name postgresql-x64-11
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
- Запустите командную строку.
Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:
chcp 1251 - Перейдите в каталог bin выполнив команду:
CD C:\Program Files\PostgreSQL\11\bin
Основные команды PostgreSQL:
PgAdmin: Визуальный редактор для PostgresSQL
Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.
По умолчанию все созданные базы хранятся в каталоге base по пути C:\Program Files\PostgreSQL\11\data\base.
Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.
Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.
Query Tool: использование SQL запросов в PostgreSQL
Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.
- Выберите базу данных, в панели Tools откройте Query Tool
- Создадим таблицу сотрудников:
CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);
Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.
После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.
Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.
Здесь вы можете заполнить данные в таблице.
После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;
Установка PostgreSQL по шагам
1. Загружаем дистрибутив
Мы советуем скачивать актуальные версии на официальном сайте СУБД. Чтобы найти установщик PostgreSQL 15 для Windows нажимаем Download → Windows → Download the installer.
На сайте выбираем интересующую нас версию PostgreSQL и платформу Windows x86-64. Начинается загрузка установочного файла на компьютер.
2. Запускаем установочный файл
В ходе установки проводится проверка на наличие необходимых компонентов, например, Microsoft Visual C++. Если каких-то компонентов не хватает, они устанавливаются автоматически.
В открывшемся приветственном окне нажимаем Next.
3. Указываем каталог для установки
Выбираем путь, куда нужно установить PostgreSQL 15. По умолчанию предлагается C:\Program Files\PostgreSQL\15, его можно оставить.
4. Выбираем дополнительные компоненты
Вместе с СУБД можно установить следующие компоненты:
- PostgreSQL Server — это сам сервер баз данных PostgreSQL;
- pgAdmin 4 — приложение с графическим интерфейсом для администрирования СУБД;
- Stack Builder — утилита, которая поможет установить дополнительные библиотеки и инструменты в PostgreSQL;
- Command Line Tools — инструменты для работы с СУБД через командную строку.
5. Указываем каталог для файлов баз данных
Можно оставить путь по умолчанию C:\Program Files\PostgreSQL\15\data или выбрать свой. Здесь будет храниться вся информация, поэтому перед выбором каталога важно проверить, что на диске достаточно свободного места.
6. Устанавливаем пароль для системного пользователя
Рекомендуем выбирать пароль из 12 и более символов, со строчными и прописными буквами, цифрами и символами. Логин postgres устанавливается по умолчанию.
7. Выбираем порт и кодировку
Можно оставить порт по умолчанию 5432 или указать свой. Далее нажать Next.
В появившемся списке локалей выбираем подходящую для себя локализацию или оставляем значение по умолчанию [Default locale]. Нажимаем Next.
В будущем для новых баз данных на сервере можно будет указать конкретную кодировку.
8. Проверяем параметры и завершаем установку
В финальном окне выводится сводная информация об установке PostgreSQL 15: дополнительные компоненты, выбранные порт и каталог, логин. Если параметры указаны верно, нажимаем Next или возвращаемся для корректировок с помощью кнопки Back.
Все готово. Нажмите Next и дождитесь, когда необходимые компоненты установятся. Обычно процесс занимает не более 5 минут.
Одновременно с завершением процесса установки можно запустить компонент Stack Builder. Если запуск не требуется, снимаем флажок и нажимаем Finish.
Установка приложений с помощью Stack Builder
Запустить Stack Builder после окончания установки PostgreSQL можно с помощью команды:
C:\"Program Files"\PostgreSQL\15\bin\stackbuilder.exe
При этом путь будет зависеть от того, какие диск и каталог были выбраны во время установки СУБД.
В начальном окне Stack Builder выбираем СУБД, для которой будем устанавливать дополнения. Для дальнейшей загрузки необходимо подключение к Интернету.
Далее выбираем дополнительные приложения, которые нужны для работы с базой данных. При клике на любой из компонентов внизу окна появляется его краткое описание. Мы выбрали Migration Toolkit и PEM-HTTPD.
Следующим шагом указываем путь на локальной машине, куда нужно загрузить пакеты выбранных дополнений.
Дожидаемся загрузки файлов.
После этого скачанные приложения устанавливаются по очереди. Перезагружать компьютер следует только после того, как все дополнения из набора будут установлены.
Если устройство все-таки было перезапущено раньше, чем завершилась установка всех приложений, можно запустить инсталляцию с помощью .exe файла. Он находится в папке, которая была выбрана на предыдущем шаге. В нашем примере это папка C:\Program Files\PostgreSQL\additions.
После установки всех выбранных приложений Stack Builder сообщает об успешном завершении.
Запуск pgAdmin 4
pgAdmin 4 — это утилита с простым визуальным интерфейсом, которая помогает работать с PostgreSQL. Ее можно установить отдельно от СУБД, скачав дистрибутив на официальном сайте.
Запустить pgAdmin 4 на устройстве можно через поиск в панели Пуск.
Еще один способ запустить утилиту — ввести в командной строке:
C:\Users\Selectel>C:\"Program Files"\PostgreSQL\15\"pgAdmin 4"\bin\pgAdmin4
Сразу при запуске необходимо придумать мастер пароль. Он нужен для защиты сервера и последующей разблокировки сохраненных паролей.
Чтобы подключиться к серверу СУБД кликните на Servers в левой панели и в раскрывшемся списке выберите PostgreSQL 15. Нажмите на него правой кнопки мыши и выберите Connect Server.
Для подключения нужно ввести пароль пользователя postgres — его мы задавали на этапе установки PostgreSQL 15.
После аутентификации мы подключаемся к своему серверу PostgreSQL 15 и видим все объекты, которые на нем расположены.
Чтобы изменить язык в pgAdmin 4 нажмите в левом верхнем углу File → Preferences. Далее в открывшемся окне в разделе Miscellaneous выберите пункт User language и в выпадающем списке с доступными языками найдите Russian. Язык изменится после перезагрузки страницы.
Сетевой доступ к PostgreSQL
Чтобы сервер PostgreSQL был доступен другим устройствам по сети, необходимо добавить правила в файрволе Windows. Проще всего сделать это с помощью PowerShell или командной строки. При этом команды необходимо выполнять от имени администратора. Еще можно использовать графический интерфейс. Ниже рассмотрим каждый из способов.
PowerShell
New-NetFirewallRule -Name 'POSTGRESQL-PORT' -DisplayName 'Selectel Port PostgreSQL2' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432
Командная строка
netsh advfirewall firewall add rule name="Selectel Port PostgreSQL" dir=in action=allow protocol=TCP localport=5432
Графический интерфейс
В меню Пуск ищем брандмауэр и выбираем Брандмауэр Защитника Windows.
В левом меню кликаем на Дополнительные параметры.
Заходим в Правила для входящих подключений и в правой части окна выбираем Создать правило.
Устанавливаем настройки так, как показано на скринах ниже:
На шаге Профиль вы сами решаете, для каких профилей использовать новое правило. Мы оставим все профили включенными.
Задаем имя для нового правила и его описание.
После того, как мы добавили правило в файрволе Windows любым из описанных способов, оно появится в брандмауэре.
Смена порта для подключения к СУБД
Чтобы изменить порт подключения к PostgreSQL находим файл postgresql.conf. Путь к нему — C:\Program Files\PostgreSQL\15\data.
В этом файле ищем строку с указанием port=5432 и меняем номер порта на нужный. Для поиска нужной строки поможет Ctrl+F.
После этого перезапускаем службу postgresql-x64-15 используя встроенное в Windows приложение Службы. Его можно найти в меню Пуск или с помощью команды Win+R на клавиатуре и далее services.msc.
Еще postgresql-x64-15 можно перезапустить с помощью PowerShell. Для этого в консоли PowerShell нужно ввести команду:
Restart-Service-Name postgresql-x64-15
Проверка открытого порта
Проверить открыт ли указанный порт для входящего трафика можно с помощью пробного подключения по протоколу Telnet. Дальше рассмотрим по шагам, как включить этот протокол на Windows 11 и 10.
Telnet на Windows 11
- Через меню Пуск находим и открываем приложение Параметры.
- В открывшемся окне слева выбираем Приложения.
3. В окне Приложения открываем пункт Дополнительные компоненты.
4. В поисковой строке вводим Другие компоненты и открываем указанный параметр.
5. В открывшемся окне находим Клиент Telnet и включаем этот компонент, нажав кнопку ОК.
Telnet на Windows 10
- Через меню Пуск находим и открываем приложение Панель управления.
2. В открывшемся окне находим Программы и компоненты.
3. Кликаем слева на Включение или отключение компонентов Windows.
4. В открывшемся окне находим Клиент Telnet и включаем этот компонент, нажав кнопку ОК.
Теперь протокол Telnet включен и можно проверить открыт ли порт, который прослушивает PostgreSQL. Для этого в командной строке введем:
telnet <IP-address> <Port>
Где IP-address это IP-адрес сервера или его имя, а Port — номер порта, по которому будет производится подключение. Например,
C:\Users\Selectel>telnet 10.0.10.55 5432
Если подключение произошло успешно и порт открыт, будет отображаться пустая командная строка, а имя окна изменится на Telnet <IP-address>.
Если порт закрыт и подключение произвести не удалось, мы увидим сообщение о сбое.
Управление PostgreSQL через командную строку
Чтобы управлять СУБД с помощью командной строки, необходимо открыть ее от имени администратора.
Далее перейти в каталог \bin, так как все основные инструменты PostgreSQL лежат именно в этой папке:
C:\Users\Selectel>cd C:\Program Files\PostgreSQL\15\bin
С помощью параметра -V можно проверить версию СУБД, которая была установлена:
C:\Program Files\PostgreSQL\15\bin>psql -V
Для корректного отображения русских символов в Windows 10 и 11 советуем сменить кодировку на Windows-1251:
C:\Program Files\PostgreSQL\15\bin>chcp 1251
Чтобы создать новую базу данных есть команда:
C:\Program Files\PostgreSQL\15\bin>createdb -U postgres SelectelDatabase
Где:
createdb — сама команда создания новой базы данных;
-U — параметр для указания имени пользователя;
postgres — имя пользователя;
SelecterDatabase — название новой базы данных.
Теперь проверим была ли создана новая база данных. Введем команду с параметром -l, который выводит список доступных баз данных:
C:\Program Files\PostgreSQL\15\bin>psql -U postgres -l
Получаем список:
Чтобы создать нового пользователя воспользуемся командой:
C:\Program Files\PostgreSQL\15\bin>createuser -U postgres TRex
Где:
postgres — пользователь с правами создавать других пользователей;
TRex — имя нового пользователя.
После этого необходимо ввести пароль пользователя postgres, который инициирует команду.
Чтобы сразу изменить пароль для новой роли, можно использовать команду:
postgres=# ALTER ROLE “TRex” WITH PASSWORD 'password';
В команде вместо ‘password’ указать новый пароль.
При вводе SQL запросов важно не забывать ставить ; в конце запроса. Enter без ; в конце строки просто сделает переход на новую строку. Поэтому, если вы забыли ввести ; и нажали Enter, не нужно снова печатать всю строку. Достаточно ввести ; и запрос отправится на сервер.
Чтобы изменить права для новой роли, используется команда ALTER ROLE. Параметр CREATEDB дает право на создание базы данных:
postgres=# ALTER ROLE "TRex" CREATEDB;
Для просмотра ролей и их описания используется команда:
postgres=# \du
Теперь пользователь TRex имеет права на создание базы данных:
Для выхода из postgres можно использовать команду \q или exit.
С помощью командной строки можно также создавать таблицы и заполнять их данными. Для этого сначала подключимся к базе данных SelectelDatabase:
C:\Program Files\PostgreSQL\15\bin>psql -U "postgres" "SelectelDatabase"
Создадим таблицу с цветами Colors, где первое поле idcolor будет автоматически заполняться порядковым номером, а второе поле namecolor содержит наименование цвета:
SelectelDatabase=# CREATE TABLE Colors SelectelDatabase-# (idcolor serial PRIMARY KEY, SelectelDatabase(# namecolor varchar(255) NOT NULL);
Теперь добавим в созданную таблицу данные:
Ввод:
SelectelDatabase=# INSERT INTO Colors (namecolor) VALUES ('black');
Вывод:
INSERT 0 1
Ввод:
SelectelDatabase=# INSERT INTO Colors (namecolor) VALUES ('red');
Вывод:
INSERT 0 1
Ввод:
SelectelDatabase=# INSERT INTO Colors (namecolor) VALUES ('white');
Вывод:
INSERT 0 1
Ввод:
SelectelDatabase=# INSERT INTO Colors (namecolor) VALUES ('blue');
Вывод:
INSERT 0 1
Выведем на экран содержимое таблицы Colors и увидим, что данные успешно записались:
SelectelDatabase=# SELECT * FROM Colors;
Таблица:
С помощью команды \dt можно посмотреть весь список существующих таблиц в базе данных:
SelectelDatabase=# \dt
Все таблицы:
Команда \d позволяет вывести информацию о конкретной таблице. Так, указав таблицу Colors, мы видим параметры с которыми создана данная таблица:
SelectelDatabase=# \d 'Colors'
Параметры таблицы:
Таблицу можно удалить с помощью функции DROP:
SelectelDatabase=# DROP TABLE Colors;
Query Tool — это редактор SQL запросов в pgAdmin 4 с удобным графическим интерфейсом. На русском редактор называется Запросник.
Открыть запросник можно, нажав на одну из указанных на скриншоте кнопок:
Появляется пустое стартовое окно запросника.
Покажем как работает запросник на примере. Для этого создадим таблицу из пяти полей, где будут указаны рейсы самолетов. Первое поле задается автоматически порядковым номером, второе — точка вылета, третье — точка прилета, четвертое — дата и время отправления, пятое — дата и время прибытия самолета.
Вводим запрос:
CREATE TABLE "flight"
(
idflight SERIAL PRIMARY KEY,
Apoint VARCHAR(255) NOT NULL,
Bpoint VARCHAR(255) NOT NULL,
Atime timestamp NOT NULL,
Btime timestamp NOT NULL
);
INSERT INTO "flight" (Apoint, Bpoint, Atime, Btime) VALUES
('Saint-Petersburg', 'Novosibirsk', '2022-11-24 10:05:00', '2022-11-24 14:15:00'),
('Saint-Petersburg', 'Samara', '2022-11-21 16:45:00', '2022-11-21 18:00:00'),
('Saint-Petersburg', 'Moscow', '2022-11-20 13:20:00', '2022-11-20 14:25:00'),
('Saint-Petersburg', 'Netherlands', '2022-11-23 02:30:00', '2022-11-23 05:15:00'),
('Saint-Petersburg', 'Tashkent', '2022-11-27 06:25:00', '2022-11-27 10:25:00');
Чтобы отобразить введенную информацию выполним команду:
SELECT * FROM "flight";
Получаем вот такую таблицу с рейсами:
С использованием SQL запроса можно отсортировать данные в таблице по определенным условиям. Например укажем, что дата вылета должна быть после полуночи 22 ноября 2022 года и точкой прилета не должен быть Ташкент.
SELECT * FROM "flight" WHERE
"atime" > '2022-11-22 00:00:00' AND
"bpoint" != 'Tashkent';
Выполнив запрос, получим два рейса, которые подходят под заданные условия:
Заключение
В этой инструкции мы рассмотрели, как установить PostgreSQL на устройство с десктопной версией ОС Windows 10 и 11, а также:
- установили дополнительные приложения с помощью утилиты Stack Builder;
- создали правила файрвола для доступа к PostgreSQL по сети;
- поработали с СУБД через командную строку Windows;
- установили и русифицировали pgAdmin 4;
- рассмотрели работу запросника Query Tool.
#статьи
-
0
Знакомимся с СУБД, устанавливаем её на компьютер и пишем первые запросы.
Иллюстрация: Colowgee / Stable Diffusion / Олег Бартунов / Postgres Professional / freepik / Colowgee для Skillbox Media
Пишет об истории IT, разработке и советской кибернетике. Знает Python, JavaScript и немного C++, но предпочитает писать на русском.
PostgreSQL — самая популярная система управления базами данных в мире. Об этом говорят не только результаты опросов разработчиков и предпринимателей, но и количество вакансий, в которых владение ею указывается среди необходимых навыков.
Из этой статьи вы узнаете, чем так хороша PostgreSQL, научитесь её устанавливать в Windows и Linux и создадите базу данных с двумя связанными таблицами. А заодно погрузитесь в историю этого замечательного проекта.
Содержание:
- Что такое PostgreSQL и для чего она нужна
- Достоинства и недостатки PostgreSQL
- POSTGRES -> PostgreSQL: история проекта
- Как установить Postgres в Windows и Linux
- Как создать и связать две таблицы
- Что почитать и где следить за новостями
PostgreSQL — это свободно распространяемая объектно-реляционная система управления базами данных (СУБД) с открытым исходным кодом, написанном на языке C.
«Объектно-реляционная» означает, что PostgreSQL поддерживает концепции, присущие как реляционным базам данных, так и объектно-ориентированным языкам программирования (объекты, классы, наследование и другие). Далее мы рассмотрим, как эта поддержка реализуется и какие преимущества даёт при разработке приложений.
В своих продуктах PostgreSQL используют такие IT-гиганты, как Huawei, Alibaba, «Инстаграм»* и Yahoo. Согласно исследованию Stack Overflow, в котором приняло участие 48 тысяч профессиональных разработчиков, в 2022 году 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. Если вы хотите поскорее установить её и попрактиковаться, то можете сразу переходить к следующему разделу.
В 1985 году, когда SQL ещё не был мировым стандартом, группа инженеров Калифорнийского университета Беркли под руководством профессора Майкла Стоунбрейкера начала разработку реляционной СУБД POSTGRES. В основе проекта лежали наработки, которые Стоунбрейкер сделал во время работы над INGRES — одной из первых реляционных СУБД.
Фото: Wikimedia Commons
К 1988 году команда опубликовала ряд научных статей, описывающих язык запросов POSTQUEL, который лежал в основе POSTGRES. Название недвусмысленно намекало на то, что новый язык более современный и продвинутый, чем SQL. Саму POSTGRES называли «постреляционной СУБД» — её создавали для того, чтобы преодолеть ставшие тогда очевидными ограничения SQL.
Первая версия инновационной СУБД вышла в 1989 году, но уже в 1992-м, после нескольких обновлений, проект закрыли. К счастью, исходный код POSTGRES распространялся по лицензии BSD. Выпускники Беркли Эндрю Ю и Джоли Чену продолжили её развивать, заменив язык POSTQUEL на SQL, который на тот момент уже стал международным стандартом. Новая инкарнация сперва получила имя Postgres95 (по году создания), а затем PostgreSQL. С тех пор вышло 28 версий СУБД.
Сегодня проект поддерживает довольно небольшая по нынешним меркам команда разработчиков. Во главе команды стоит управляющий комитет (Core Team) — члены комитета принимают решения по развитию и выпуску новых версий Postgres. Разработчики делятся на обычных (contributors) и основных (major contributors). Кроме того, небольшая группа разработчиков (commiters) имеет право вносить изменения в исходный код.
В число основных разработчиков входят и три программиста из России: Олег Бартунов, Фёдор Сигаев и Александр Коротков.
Фото: Postgres Professional
На момент публикации статьи последней стабильной версией является PostgreSQL 15, а 29 июня 2023 года стала доступна вторая бета-версия PostgreSQL 16.
Установщик для любой операционной системы есть на официальном сайте PostgreSQL в разделе Latest Releases. На главной странице сайта нажмите кнопку Download и выберите свою операционную систему:
Далее мы рассмотрим процесс установки для Windows и Linux.
Нажмите на иконку Windows в разделе Downloads, и вы попадёте на страницу установщика PostgreSQL. Установщик включает в себя следующие утилиты:
- сервер PostgreSQL,
- pgAdmin4 — графический инструмент для управления базой данных,
- менеджер пакетов Stack Builder.
Перейдите на страницу выбора версии установщика, нажав на ссылку Download the installer. Перед вам откроется список доступных файлов — выбирайте последнюю версию (на скриншоте это версия 15.3). Обратите внимание на разрядность скачиваемого файла — она должна совпадать с разрядностью вашей ОС.
Запустите установщик. В приветственном окне нажмите Далее и выберите путь установки или оставьте папку по умолчанию.
Важно!
Если будете самостоятельно выбирать путь установки, не создавайте для этого папку — иначе получите сообщение об ошибке: «The chosen installation directory exists and is non-empty. Please choose a different directory». Просто впишите название папки в строку, например: «PostgreSQL/15», а установщик сам её создаст.
Затем нужно будет выбрать компоненты. По умолчанию галочки стоят во всех пунктах, но устанавливать всё необязательно. Главное для нас — сервер Postgres и pgAdmin. Также рекомендуем оставить инструменты командной строки, потому что они наверняка понадобятся для администрирования БД.
Далее нужно выбрать каталог, в котором будут храниться созданные вами базы данных. В рамках обучения можно оставить путь по умолчанию.
На следующем шаге вас попросят придумать пароль администратора — он понадобится для подключения к серверу PostgreSQL и входа в pgAdmin 4. Не забудьте его записать, чтобы не пришлось всё сносить и устанавливать заново 
Затем установщик предложит выбрать язык и ещё раз покажет настройки, с которыми будет установлена PostgreSQL. Нажимайте «Далее», пока не начнётся процесс установки — он займёт несколько минут. По окончании в меню «Пуск» появится папка с компонентами и документацией:
Проверим, всё ли работает. Запустите pgAdmin и кликните на Servers в левом верхнем углу. Вас попросят ввести пароль пользователя — это тот, пароль, который вы задавали во время установки.
Если пароль введён верно, вы увидите такой дашборд:
Чтобы проверить, подключён ли сервер, давайте спросим у него версию СУБД командой SELECT VERSION ():
Если в нижней области экрана появился ответ, значит, сервер подключён и готов принимать запросы. С чем вас и поздравляем!
Как и все программы в ОС 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 14.8 (Ubuntu 14.8-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04.1) 11.3.0, 64-bit.
Другими словами, сервер 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. Готово! Вы создали автоматически генерируемый первичный ключ.
Попробуйте заполнить остальные поля самостоятельно.
Подсказка 1
Для текстовых полей с переменной, но ограниченной длиной подходят типы character varying и varchar.
Подсказка 2
В PostgreSQL есть два типа чисел с плавающей точкой: real и double precision.
В результате у вас должен получиться такой набор полей:
Во второй таблице (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» — Салахаддин Джуба.
Небольшой комментарий к последнему пункту. Хоть «Изучаем PostgreSQL 10» и посвящена десятой версии Postgres (на момент публикации статьи вышла бета-версия 16-й), в ней изложены настолько фундаментальные принципы, что она остаётся актуальной и по сей день.
Следить за новостями можно на официальном сайте или в Twitter-аккаунте PostgreSQL (второй способ с VPN удобнее). Наконец, задать технические вопросы можно в чате русскоязычного сообщества в Telegram: https://t.me/pgsql.
* Решением суда запрещена «деятельность компании Meta Platforms Inc. по реализации продуктов — социальных сетей Facebook и Instagram на территории Российской Федерации по основаниям осуществления экстремистской деятельности».
Веб-разработка с нуля
Создайте 3 приложения и сделайте первый шаг к карьере веб-разработчика. Для обучения не нужны опыт и знания в IT.
Учитесь бесплатно →
Бесплатный курс: «Веб-разработка с нуля»
Начать учиться