Thank you for reading this post, don't forget to subscribe!
PostgreSQL – это реляционная система управления базами данных (СУБД), основанная на языке запросов SQL. Она отвечает всем стандартам, подходит для поддержки приложений любого масштаба и имеет множество дополнительных функций (надёжные транзакции, параллелизм без блокировки прав на чтение и многое другое).
1: Установка PostgreSQL
Пакет PostgreSQL можно загрузить из стандартного репозитория CentOS 8, который называется AppStream. Здесь вы найдете множество доступных версий. Чтобы выбрать необходимую версию, вы должны активировать соответствующую коллекцию пакетов (коллекции называются потоками модулей).
В DNF (стандартном менеджере пакетов CentOS 8) модулями называются специальные наборы RPM-пакетов, которые вместе составляют большее приложение. Такой подход делает установку пакетов и их зависимостей более интуитивным.
Запросите список доступных потоков для модуля postgresql с помощью dnf:
dnf module list postgresql
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 12 client, server PostgreSQL server and client module
В этом выводе показано, что в AppStream доступны три версии PostgreSQL: 9.6, 10 и 12. Поток, который предоставляет версию 10, является потоком по умолчанию (на это указывает [d]). Если вы хотите установить эту версию, вы можете просто запустить команду:
sudo dnf install postgresql-server
и перейти к следующему разделу мануала. Хотя версия 10 все еще поддерживается, в этом мануале мы установим версию 12, последний релиз на момент написания этой статьи.
Чтобы установить PostgreSQL версии 12, вы должны включить поток модулей этой версии. При включении этого потока вы переопределите поток по умолчанию и сделаете все пакеты, связанные с включенным потоком, доступными в системе. Обратите внимание: в системе можно включить только один поток любого из существующих модулей.
Чтобы включить поток Postgres для версии 12, выполните следующую команду:
sudo dnf module enable postgresql:12
При появлении запроса нажмите y, а затем Enter, чтобы подтвердить действие, после чего вы увидите:
====================================================================
Package Architecture Version Repository Size
====================================================================
Enabling module streams:
postgresql 12
Transaction Summary
====================================================================
Is this ok [y/N]: y
Включив поток для версии 12, вы можете установить пакет postgresql-server, после чего у вас появится PostgreSQL 12 и все зависимости этой СУБД.
sudo dnf install postgresql-server
Подтвердите установку:
. . .
Install 4 Packages
Total download size: 16 M
Installed size: 62 M
Is this ok [y/N]: y
Установка PostgreSQL завершена. Теперь можно ознакомиться с её функциями и создать новый кластер.
2: Создание кластера PostgreSQL
Для начала нужно создать новый кластер базы данных PostgreSQL. Кластер баз данных – это набор баз, которые управляются одним экземпляром сервера. Чтобы создать кластер, нужно создать ряд каталогов, в которые будут помещены данные БД, создать таблицы и базы данных template1 и postgres.
База данных template1 необходима для создания новой БД. Все, что хранится в ней, будет помещено в новую базу данных при ее создании. postgres – это база данных по умолчанию, предназначенная для работы с пользователями, утилитами и сторонними приложениями.
Пакет Postgres, который мы установили на предыдущем этапе, поставляется с удобным скриптом postgresql-setup, который берет на себя низкоуровневое администрирование кластера базы данных.
Чтобы создать кластер базы данных, запустите скрипт с параметром —initdb:
sudo postgresql-setup --initdb
Вы увидите следующий вывод:
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
Теперь запустите сервис PostgreSQL с помощью systemctl:
sudo systemctl start postgresql
Затем нужно включить сервис, чтобы добавить его в автозагрузку:
sudo systemctl enable postgresql
Вы увидите такой вывод:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
PostgreSQL запущена и работает. Теперь давайте посмотрим, как работает Postgres и чем эта СУБД отличается от аналогичных систем, которые вы, вероятоно, использовали ранее.
3: Роли и базы данных PostgreSQL
По умолчанию PostgreSQL использует так называемые роли. Они чем-то похожи на учётные записи пользователей или группы в Unix-подобных системах. Только система PostgreSQL не различает пользователей и группы, она объединяет их под понятием «роли».
Во время установки PostgreSQL использует модель аутентификации ident, которая связывает роли PostgreSQL со стандартными пользователями Unix/Linux. Открыть существующую роль можно при помощи связанной с ней учётной записи Linux.
Во время установки был создан Linux-пользователь postgres, который связан со стандартной ролью системы. Можно использовать этот аккаунт, чтобы подключиться к PostgreSQL.
Существует несколько способов получить доступ к PostgreSQL при помощи этого аккаунта.
Пользователь postgres
Чтобы перейти в сессию пользователя postgres, введите:
sudo -i -u postgres
Чтобы получить доступ к командной строке, введите:
psql
Команда выполнит вход и откроет доступ к управлению СУБД.
Чтобы закрыть командную строку PostgreSQL, наберите:
\q
Это вернёт вас в командную строку Linux. Чтобы вернуться в сессию обычного пользователя sudo, введите:
exit
Прямой доступ к командной строке PostgreSQL
Также можно получить доступ к командной строке PostgreSQL, не изменяя при этом сессии пользователя. Для этого нужен доступ к команде sudo.
В последнем примере вы перешли к командной строке Postgres, сначала переключившись на пользователя postgres, а затем запустив psql. Чтобы получить доступ к командной строке Postgres за один шаг, запустите команду psql как пользователь postgres с правами sudo:
sudo -u postgres psql
Эта команда выполнит автоматический вход в PostgreSQL без промежуточной оболочки bash.
Чтобы закрыть эту командную строку, введите:
\q
4: Создание роли
На данный момент в СУБД есть только одна роль, postgres. Чтобы создать новую роль, используйте команду createrole. Чтобы создать роль в интерактивном режиме, добавьте флаг —interactive.
В сессии пользователя postgres создать нового пользователя можно с помощью команды:
createuser --interactive
В сессии другого пользователя можно использовать команду sudo, чтобы не переходить в сессию postgres:
sudo -u postgres createuser --interactive
Скрипт запросит данные о пользователе. Затем в зависимости от полученных данных он выполнит все необходимые команды, чтобы создать отвечающего требованиям пользователя.
Enter name of role to add: 8host
Shall the new role be a superuser? (y/n) y
У этого скрипта есть много различных флагов. Ознакомиться со списком доступных опций можно на странице мануала:
man createuser
5: Создание базы данных
По умолчанию роль Postgres запрашивает одноименную базу данных. Потому следует создать такую БД для нового пользователя.
Только что вы создали нового пользователя (в мануале он условно называется 8host). Для создания БД используется команда createdb.
В сессии пользователя postgres создать новую БД можно с помощью команды:
createdb 8host
В сессии другого пользователя можно использовать команду sudo, чтобы не переходить в сессию postgres:
sudo -u postgres createdb 8host
6: Аутентификация новой роли PostgreSQL
Чтобы выполнить ident аутентификацию PostgreSQL, нужно иметь пользователя системы Linux, чье имя совпадает с именем роли и БД.
Если у вас нет такого пользователя, создайте его в Linux с помощью команды adduser (для этого необходимы права sudo).
sudo adduser 8host
После этого можно перейти в сессию нового пользователя и подключиться к БД:
sudo - sudo -i -u 8host
psql
Или же подключиться к БД напрямую:
sudo -u 8host psql
Если все предыдущие настройки были выполнены правильно, команда автоматически подключится к БД.
Чтобы подключиться к другой БД, нужно указать её имя:
psql -d postgres
Чтобы получить информацию о текущем подключении, введите:
\conninfo
You are connected to database «8host» as user «8host» via socket in «/var/run/postgresql» at port «5432».
Это может быть полезно при подключении к нестандартным базам данных или пользователям.
7: Создание и удаление таблиц
Вы уже знаете, как подключиться к системе баз данных PostgreSQL. Теперь вы можете изучить некоторые основные задачи по управлению Postgres.
Попробуйте создать таблицу для хранения данных; для примера таблица может описывать оборудование детской площадки. Синтаксис этой команды выглядит так:
CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);
Как видите, для таблицы нужно выбрать имя, определить столбцы, тип столбцов и максимальную длину поля. Опционально можно также установить ограничения для каждого столбца.
Для примера создайте простую таблицу:
CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);
Данная таблица описывает всё оборудование детской площадки. Таблица начинается со столбца ID, который указывает порядковый номер записи. Этот столбец был ограничен по PRIMARY KEY, что значит, что значения должны быть уникальными и не должны быть нулём.
Длина поля столбцов equip_id и install_date не ограничена; дело в том, что задавать длину некоторых столбцов необязательно, так как она автоматически задаётся типом данных.
Столбцы type и color не могут быть пустыми. Столбец location ограничен восемью возможными значениями. Столбец date указывает дату установки оборудования.
Просмотреть таблицу можно при помощи команды:
[codesyntax lang="php"]
|
1 2 3 4 5 6 7 |
\d List of relations Schema | Name | Type | Owner -------+-------------------------+----------+------- public | playground | table | 8host public | playground_equip_id_seq | sequence | 8host (2 rows) |
[/codesyntax]
playground_equip_id_seq имеет тип данных sequence. Это представление типа данных serial, установленного для столбца equip_id. Этот тип определяет следующий номер в последовательности.
Чтобы просмотреть таблицу вне этого типа данных, введите:
[codesyntax lang="php"]
|
1 2 3 4 5 6 |
\dt List of relations Schema | Name | Type | Owner -------+------------+-------+------- public | playground | table | 8host (1 row) |
[/codesyntax]
8: Добавление, запрос и удаление данных
Теперь попробуйте добавить в таблицу данные.
Для этого нужно вызвать целевую таблицу, назвать столбцы и ввести данные, которые нужно добавить. К примеру, чтобы добавить горку (slide) и качели (swing), нужно ввести:
INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');
Добавляя данные в таблицу, помните:
- Имя таблицы не нужно брать в кавычки.
- Значения столбцов необходимо взять в кавычки.
- В столбец equip_id не нужно добавлять данные; его значения генерируются автоматически.
Просмотрите добавленные данные:
SELECT * FROM playground;
equip_id | type | color | location | install_date
---------+-------+--------+-----------+--------------
1 | slide | blue | south | 2017-04-28
2 | swing | yellow | northwest | 2018-08-16
(2 rows)
Как видите, столбец equip_id был заполнен автоматически. Чтобы удалить значение (к примеру, slide), используйте команду:
DELETE FROM playground WHERE type = 'slide';
Проверьте таблицу:
SELECT * FROM playground;
equip_id | type | color | location | install_date
---------+-------+--------+-----------+--------------
2 | swing | yellow | northwest | 2018-08-16
(1 row)
Теперь значения slide нет в таблице.
9: Добавление и удаление столбцов
PostgreSQL позволяет изменять количество столбцов таблицы после её создания.
Чтобы добавить столбец, используйте команду:
ALTER TABLE playground ADD last_maint date;
Просмотрите таблицу, и вы увидите новый столбец (пока что пустой):
SELECT * FROM playground;
equip_id | type | color | location | install_date | last_maint
---------+-------+--------+-----------+--------------+------------
2 | swing | yellow | northwest | 2018-08-16 |
(1 row)
Удалить столбец также можно с помощью одной команды. Например, чтобы удалить столбец last_maint, введите:
ALTER TABLE playground DROP last_maint;
Это удаляет столбец last_maint и все найденные в нем значения, но оставляет все другие данные без изменений.
10: Обновление данных в таблице
Существующие записи в таблице можно редактировать.
Чтобы обновить значение записи, нужно запросить эту запись и указать столбец, значение которого нужно изменить. Для примера попробуйте обновить значение записи swing; замените yellow в столбце color новым значением (например, red).
UPDATE playground SET color = 'red' WHERE type = 'swing';
Просмотрите таблицу, чтобы убедиться, что данные обновились:
SELECT * FROM playground;
equip_id | type | color | location | install_date
----------+-------+-------+-----------+--------------
2 | swing | red | northwest | 2010-08-16
(1 row)
Как видите, значение было успешно обновлено на red.