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

Системы управления реляционными базами данных являются ключевым компонентом многих веб-сайтов и приложений. Они предоставляют структурированный способ хранения, организации и доступа к информации.

PostgreSQL, или Postgres, — это система управления реляционными базами данных, которая предоставляет собой реализацию языка запросов SQL. Она соответствует стандартам и обладает множеством передовых функций, таких как надежность и параллельность операций без блокировки при чтении.

В этом обучающем руководстве мы рассмотрим, как установить Postgres на сервер Ubuntu 20.04. Также в нем содержится ряд инструкций для администрирования базы данных.

Для выполнения данного руководства вам потребуется один сервер Ubuntu 20.04 с конфигурацией, выполненной согласно руководству по первоначальной настройке сервера Ubuntu 20.04. После выполнения всех предварительных действий ваш сервер должен иметь пользователя без прав root с разрешениями sudo и базовый брандмауэр.

Используемые по умолчанию репозитории Ubuntu содержат пакеты Postgres, поэтому вы можете устанавливать их с помощью системы управления пакетами apt.

Если вы еще не сделали этого, обновите локальный индекс пакетов вашего сервера:

sudo apt update

После этого установите пакет Postgres вместе с пакетом -contrib, который содержит дополнительные утилиты и функциональные возможности:

sudo apt install postgresql postgresql-contrib

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

По умолчанию Postgres использует концепцию «ролей» для выполнения аутентификации и авторизации. В некоторых аспектах они напоминают обычные учетные записи в Unix, однако Postgres не делает различий между пользователями и группами и предпочитает использовать более гибкий термин “роль”.

После установки Postgres настроена на использование аутентификации ident, что значит, что выполняется привязка ролей Postgres с соответствующей системной учетной записью Unix/Linux. Если роль существует внутри Postgres, пользователь Unix/Linux с тем же именем может выполнить вход в качестве этой роли.

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

Существует несколько способов использования этой учетной записи для доступа к Postgres.

sudo -i -u postgres

psql

\q

sudo -u postgres psql

\q

К настоящему моменту у вас есть только роль postgres, настроенная внутри базы данных. Вы можете создавать новые роли из командной строки с командой createrole. Флаг --interactive будет запрашивать имя новой роли, а также попросит указать, будут ли у этой роли права суперпользователя.

Если вы выполнили вход в учетную запись postgres, то можете создать нового пользователя с помощью следующей команды:

createuser --interactive

Если же вы предпочитаете использовать sudo для каждой команды без переключения с обычной учетной записи, введите следующую команду:

sudo -u postgres createuser --interactive

Скрипт будет предлагать варианты на выбор и, исходя из ваших ответов, выполнять нужные команды Postgres для создания пользователя в соответствии с вашими спецификациями.

Output
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Вы можете получить дополнительный контроль с помощью дополнительных флагов. Посмотрите возможные варианты на странице man:

man createuser

В результате этих действий у вас будет новый пользователь, но вам предстоит еще добавить базы данных. В следующем разделе описан этот процесс.

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

Это означает, что если созданный вами в последнем разделе пользователь будет иметь имя sammy, эта роль попытается подключиться к базе данных, которая также называется «sammy» по умолчанию. Вы можете создать соответствующую базу данных с помощью команды createdb.

Если вы используете учетную запись postgres, необходимо ввести следующее:

createdb sammy

Если же вы предпочитаете использовать sudo для каждой команды без переключения с обычной учетной записи, введите следующую команду:

sudo -u postgres createdb sammy

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

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

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

sudo adduser sammy

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

sudo -i -u sammy
psql

Либо же вы можете сделать следующее:

sudo -u sammy psql

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

Если вы хотите, чтобы ваш пользователь подключился к другой базе данных, то вы можете сделать это с помощью следующей команды:

psql -d postgres

После входа вы можете проверить данные о текущем подключении:

\conninfo

Output
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

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

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

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

Вы можете узнать больше о том, как создавать и управлять таблицами в Postgres, здесь.

Для демонстрационных целей необходимо создать следующую таблицу:

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
);

Эта команда создает таблицу с описью оборудования для установки на игровых площадках. В первом столбце таблицы хранятся идентификационные номера оборудования последовательного типа. Эти номера представляют собой целые числа с автоматическим инкрементальным увеличением. В этом столбце также содержится константа PRIMARY KEY, и это означает, что значения в нем должны быть уникальными и ненулевыми.

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

Для двух из столбцов (equip_id и install_date) команда не указывает длину поля. Это связано с тем, что для некоторых типов данных не требуется заданная длина, поскольку длина или формат являются подразумеваемыми.

Вы можете просмотреть вашу новую таблицу, введя следующую команду:

\d

Output
                  List of relations
 Schema |          Name           |   Type   | Owner
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Ваша таблица игрового оборудования готова, но здесь есть что-то под названием playground_equip_id_seq с типом данных sequence. Это представление типа serial, который присвоен столбцу equip_id. Оно отслеживает следующий номер последовательности и создается автоматически для столбцов данного типа.

Если вы хотите только просмотреть таблицу без последовательности, можете ввести следующую команду:

\dt

Output
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | playground | table | sammy
(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;

Output
 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;

Output
 equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

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

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

ALTER TABLE playground ADD last_maint date;

Если вы снова просмотрите данные таблицы, то увидите, что новый столбец был добавлен, но в него не были введены данные:

SELECT * FROM playground;

Output
 equip_id | type  | color  | location  | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   |
(1 row)

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

ALTER TABLE playground DROP last_maint;

Эта команда удаляет столбец last_maint и любые значения внутри него, но оставляет все другие данные нетронутыми.

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

Вы можете обновить значения существующей записи, запросив нужную вам запись и установите для столбца значение, которое вы хотите использовать. Вы можете запросить запись swing (запрос выдаст все записи swing в вашей таблице) и изменить ее цвет на red. Это может быть полезно, если вы задали набору swing задание рисования:

UPDATE playground SET color = 'red' WHERE type = 'swing';

Вы можете убедиться, что операция прошла успешно, запросив данные еще раз:

SELECT * FROM playground;

Output
 equip_id | type  | color | location  | install_date
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2018-08-16
(1 row)

Как видите, ваша горка теперь имеет красный цвет.

Вы настроили PostgreSQL на сервере Ubuntu 20.04. Если вы хотите узнать больше о системе Postgres и способах ее использования, мы рекомендуем вам изучить следующие руководства:

• Сравнение систем управления реляционными базами данных
• Практика работы с запросами с помощью SQL