Установка и использование PostgreSQL в Ubuntu 20.04

Ubuntu | Amber | Комментировать запись

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

Читайте также: Краткий обзор реляционных систем управления базами данных

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

Данный мануал поможет установить PostgreSQL на сервер Ubuntu 20.04 и ознакомит вас с базовыми командами этой СУБД.

Требования

Для работы вам нужен сервер Ubuntu 20.04 с пользователем sudo и брандмауэром, настроенный по этому мануалу.

1: Установка PostgreSQL

Пакет PostgreSQL можно загрузить из стандартного репозитория Ubuntu   с помощью пакетного менеджера apt.

Обновите индекс локальных пакетов, если вы еще не сделали этого в текущей сессии:

sudo apt update

Затем установите пакет Postgres и -contrib, который добавляет некоторые нестандартные функции.

sudo apt install postgresql postgresql-contrib

Установка PostgreSQL завершена. Теперь можно ознакомиться с тем, как работает эта реляционная база данных.

2: Роли и базы данных PostgreSQL

По умолчанию PostgreSQL использует так называемые роли. Роли чем-то похожи на учётные записи пользователей или группы в Unix-подобных системах. Только система PostgreSQL не различает пользователей и группы, она объединяет их под понятием «роли».

Во время установки PostgreSQL использует модель аутентификации ident, которая связывает роли PostgreSQL со стандартными пользователями Unix/Linux. Открыть существующую роль можно при помощи связанной с ней учётной записи Linux.

При установке СУБД система создала Linux-пользователя postgres, который связан со стандартной ролью Postgres. Вы можете использовать этот аккаунт, чтобы подключиться к PostgreSQL.

Существует несколько способов получить доступ к PostgreSQL при помощи этого аккаунта.

Пользователь postgres

Чтобы перейти в сессию пользователя postgres, введите:

sudo -i -u postgres

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

psql

Команда выполнит вход и откроет доступ к управлению СУБД.

Чтобы закрыть командную строку PostgreSQL, наберите:

\q

Прямой доступ к командной строке PostgreSQL

Также можно получить доступ к командной строке PostgreSQL, не изменяя при этом сессии пользователя. Для этого нужен доступ к команде sudo.

В последнем примере вы перешли к командной строке Postgres, сначала переключившись на пользователя postgres, а затем запустив psql. Чтобы получить доступ к командной строке Postgres за один шаг, запустите команду psql как пользователь postgres с правами sudo:

sudo -u postgres psql

Эта команда выполнит автоматический вход в PostgreSQL без промежуточной оболочки bash.

Чтобы закрыть эту командную строку, введите:

\q

3: Создание роли

На данный момент в СУБД есть только одна роль, 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

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

По умолчанию роль Postgres запрашивает одноименную базу данных. Давайте попробуем создать такую БД для нового пользователя.

Только что вы создали нового пользователя (в мануале он условно называется 8host). Для создания БД используется команда createdb.

В сессии пользователя postgres создать новую БД можно с помощью команды:

createdb 8host

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

sudo -u postgres createdb 8host

5: Аутентификация новой роли 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".

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

6: Создание и удаление таблиц

Вы уже умеете подключаться к системе баз данных 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)
);

Как видите, для таблицы нужно выбрать имя, определить столбцы, тип столбцов и максимальную длину поля. Опционально можно также установить ограничения для каждого столбца.

Читайте также: Управление таблицами PostgreSQL

Для примера создайте простую таблицу, которая описывает оборудование детской площадки:

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, то есть значения должны быть уникальными и не должны быть нулём.

Столбцы type и color не могут быть пустыми. Столбец location ограничен, он имеет только восемь доступных значений. Последняя строка создает столбец date, который указывает дату установки оборудования.

Длина поля столбцов equip_id и install_date не ограничена; дело в том, что задавать длину некоторых столбцов необязательно, так как она автоматически задаётся типом данных.

Просмотреть таблицу можно при помощи команды:

\d
List of relations
Schema |          Name           |   Type   | Owner
-------+-------------------------+----------+-------
public | playground              | table    | 8host
public | playground_equip_id_seq | sequence | 8host
(2 rows)

playground_equip_id_seq имеет тип данных sequence. Это представление типа данных serial, установленного для столбца equip_id. Этот тип определяет следующий номер в последовательности.

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

\dt
List of relations
Schema |    Name    | Type  | Owner
-------+------------+-------+-------
public | playground | table | 8host
(1 row)

7: Добавление, запрос и удаление данных

Давайте попробуем вставить данные в таблицу. Для этого нужно вызвать целевую таблицу, назвать столбцы и ввести данные, которые нужно добавить в них. К примеру, чтобы добавить горку (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 больше нет в таблице.

8: Добавление и удаление столбцов

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 и все найденные в нем значения, но оставит все другие данные без изменений.

9: Обновление данных в таблице

Существующие записи в таблице можно редактировать.

Чтобы обновить значение, нужно запросить эту запись и указать столбец, значение которого нужно изменить. Для примера попробуйте обновить значение записи 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 | 2018-08-16
(1 row)

Как видите, значение было успешно обновлено на red.

Заключение

Теперь на сервере Ubuntu 20.04 у вас есть установка PostgreSQL, готовая к использованию. Также вы выучили основные команды для работы с сервером.

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

• Использование ролей и управление доступом в PostgreSQL
• Защита PostgreSQL на сервере Ubuntu
• Запрос данных PostgreSQL при помощи команды select