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

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

Реляционные базы данных являются одним из основных компонентов большинства сайтов и приложений. Они необходимы для структурирования и хранения данных.

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

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

Требования

Для работы вам нужен сервер Ubuntu 18.04, настроенный по этому мануалу.

Установка PostgreSQL

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

Сначала нужно обновить индекс пакетов системы, а затем можно загрузить пакеты PostgreSQL и contrib (он предоставляет дополнительные утилиты):

sudo apt update
sudo apt install postgresql postgresql-contrib

Установка программы завершена. Теперь можно ознакомиться с её функциями и особенностями.

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

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

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

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

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

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

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

sudo -i -u postgres

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

psql

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

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

\q

Это вернёт командную строку postgres в Linux.

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

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

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

sudo -u postgres psql

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

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

\q

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

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

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

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

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

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

createdb 8host

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

sudo -u postgres createdb 8host

Аутентификация новой роли PostgreSQL

Чтобы выполнить ident аутентификацию PostgreSQL, нужно иметь пользователя системы Linux, чье имя совпадает с именем роли и БД.

Если у вас нет такого пользователя, создайте его в Linux с помощью команды adduser (для этого необходимы права sudo).

sudo adduser 8host

После этого можно перейти в сессию нового пользователя и подключиться к БД:

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".

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

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

Попробуйте создать таблицу для хранения данных; для примера таблица может описывать оборудование детской площадки. Синтаксис этой команды выглядит так:

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

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

Столбцы type и color не могут быть пустыми. Столбец location ограничен восемью возможными значениями. Столбец 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)

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

Теперь попробуйте добавить в таблицу данные.

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

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

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)

Чтобы удалить столбец, введите:

ALTER TABLE playground DROP last_maint;

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

Записи в таблице можно редактировать.

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

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

Заключение

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

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

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