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

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

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

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

Установка PostgreSQL

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

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

sudo apt update

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

sudo apt install postgresql postgresql-contrib

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

Использование ролей и баз данных в PostgreSQL

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

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

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

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

Переключение на учетную запись postgres

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

sudo -i -u postgres

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

psql

Теперь вы можете свободно взаимодействовать с СУБД по мере необходимости.

Закройте командную строку PostgreSQL с помощью следующей команды:

\q

В результате вы вернетесь в командную строку postgres в Linux.

Доступ к командной строке Postgres без переключения учетных записей

Также вы можете запустить необходимую вам команду с учетной записью postgres напрямую с помощью sudo.

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

sudo -u postgres psql

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

Вы снова сможете выйти из интерактивной сессии Postgres с помощью следующей команды:

\q

Многие варианты использования требуют использования сразу нескольких ролей Postgres. Ниже вы узнаете, как выполнить настройку в таких случаях.

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

К настоящему моменту у вас есть только роль 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 -u postgres createdb sammy

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

Открытие командной строки Postgres с новой ролью

Чтобы выполнить аутентификацию с помощью 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)
);

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

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

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)