Установка и использование PostgreSQL в CentOS 8

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

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

Следуя указаниям этого руководства, вы установите последнюю версию PostgreSQL на сервер CentOS 8.

Установка PostgreSQL

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

В DNF, используемом по умолчанию в CentOS 8 менеджере пакетов, модули представляют собой наборы пакетов RPM, которые вместе составляют крупное приложение. Это необходимо, чтобы сделать установку пакетов и их зависимостей более интуитивно понятной для пользователей.

В первую очередь просмотрите список доступных потоков для модуля postgresql с помощью команды dnf:

dnf module list postgresql

Output

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

В этом выводе вы можете видеть все три версии PostgreSQL из репозитория AppStream: 9.6, 10 и 12. Поток, который предоставляет Postgres версии 10, является потоком по умолчанию, о чем сигнализирует [d] в его конце.

Если вы хотите установить эту версию, запустите sudo dnf install postgresql-server и перейдите к следующему шагу. Однако, хотя версия 10 до сих пор поддерживается, в этом обучающем руководстве мы будем устанавливать версию 12, последний релиз на момент написания.

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

Чтобы активировать поток модуля для Postgres версии 12, запустите следующую команду:

sudo dnf module enable postgresql:12

При появлении запроса нажмите y, а затем ENTER, чтобы подтвердить, что вы хотите активировать поток:

Output

====================================================================
 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

При появлении запроса подтвердите установку, нажав y, а затем ENTER:

Output

. . .
Install  4 Packages

Total download size: 16 M
Installed size: 62 M
Is this ok [y/N]: y

После установки программного обеспечения мы выполним ряд шагов инициализации для подготовки нового кластера базы данных для PostgreSQL.

Создание нового кластера баз данных PostgreSQL

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

База данных template1 представляет собой шаблон сортировок, который используется для создания новых баз данных, все, что хранится в template1, даже объекты, которые вы добавляете самостоятельно, будут размещаться в новых базах данных при их создании. База данных postgres — это база данных по умолчанию, предназначенная для пользователей, утилит и сторонних приложений.

Пакет Postgres, установленный на предыдущем шаге, содержит удобный скрипт с именем postgresql-setup, который помогает администрированию кластеров баз данных на низком уровне. Для создания кластера баз данных запустите скрипт с помощью sudo и опции --initdb:

sudo postgresql-setup --initdb

Результат будет выглядеть следующим образом:

Output 

* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Теперь запустите службу PostgreSQL с помощью systemctl:

sudo systemctl start postgresql

Затем воспользуйтесь systemctl еще раз, чтобы активировать запуск службы при загрузке сервера:

sudo systemctl enable postgresql

Результат будет выглядеть следующим образом:

Output

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

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

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

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

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

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

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

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

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

sudo -i -u postgres

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

psql

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

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

\q

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

exit

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

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

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

sudo -u postgres psql

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

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

\q

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

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

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

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

createuser --interactive

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

sudo -u postgres createuser --interactive

Скрипт будет предлагать варианты на выбор и исходя из ваших ответов выполнять необходимые команды Postgres для создания пользователя в соответствии с вашими спецификациями. Для этого обучающего руководства создайте роль sammy и предоставьте ей права суперпользователя, введя y после соответствующего запроса:

Output

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

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

man createuser

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

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

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

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

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

createdb sammy

sudo -u postgres createdb sammy

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

Теперь, когда мы создали новую базу данных, мы выполним вход в нее с помощью новой роли.

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

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

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

sudo adduser sammy

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

sudo -i -u sammy
psql

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

sudo -u sammy psql

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

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

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

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

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

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

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

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

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

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

Теперь, когда у вас есть таблица, вы можете ввести в нее данные.

В качестве примера добавьте строги swing (качели) и slide (горка), вызвав таблицу, куда вы хотите добавить данные, указав столбцы и предоставив данные для каждого столбца:

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)

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

Теперь, когда вы добавили и удалили элементы в таблице, вы можете попробовать добавить и удалить столбцы.

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

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

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:

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

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

SELECT * FROM playground;

Вы увидите следующее:

Output

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