Аннотация

В данной статье представлена инструкция по установке и первичной настройке СУБД PostgreSQL на Astra Linux Special Edition x.7. Установка незащищенной версии выполняет с подключенным компонентом astra-ce расширенного (extended) репозитория, установка защищенной версии выполняется без подключения этого компонента. На момент написания данной статьи в составе Astra Linux Special Edition РУСБ.10015-01 (очередное обновление 1.7) доступны следующие версии СУБД PostgreSQL:

версия 11.10-astra.se5 - защищенная версия в основном репозитории (на установочном диске);
версия 11.10-astra.se13 - защищенная версия в основном репозитории (оперативное обновление №1);
версия 11.12-astra.ce5 - незащищенная версия в компоненте astra-ce расширенного репозитория до оперативного обновления 1.7.2;
версия 14+240astra4 - - незащищенная версия в компоненте astra-ce расширенного репозитория начиная с оперативного обновления 1.7.2.

Внимание!

Программное обеспечение расширенного репозитория является сторонним по отношению к Astra Linux, не дорабатывается с точки зрения выполнения требований по безопасности информации и не проверяется при сертификации.
Подробнее см. Использование стороннего программного обеспечения в аттестованных информационных системах, функционирующих под управлением Astra Linux Special Edition.

При использовании программного обеспечения расширенного репозитория рекомендуется для дополнительной изоляции процессов осуществлять их запуск в изолированной программной среде (контейнере). При использовании для этих целей Docker-контейнеров их запуск целесообразно осуществлять от имени непривилегированного пользователя в rootless-режиме, а при включенном мандатном контроле целостности (МКЦ) с применением технологии запуска контейнеров на пониженном или выделенном уровне МКЦ. Подробнее см. Руководство по КСЗ в составе эксплуатационной документации используемого обновления.

При установке СУБД PostgreSQL из компонента astra-ce расширенного репозитория защищенная версия СУБД, доработанная для взаимодействия с КСЗ Astra Linux Special Edition будет заменена на стандартную версию СУБД..
Подробнее про структуру и использование репозиториев см. Репозитории Astra Linux Special Edition x.7: структура, особенности подключения и использования.

При установке незащищенной СУБД PostgreSQL версии 14 из компонента astra-ce расширенного репозитория 1.7.2 при наличии ранее установленной СУБД PostgreSQL версии 11 необходимо перед запуском нового кластера вручную удалить ранее установленные кластеры версии 11:

pg_dropcluster 11 <имя_старого_кластера> 

Что такое СУБД PostgreSQL

Общая информация

PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (СУБД). PostgreSQL базируется на языке SQL и поддерживает многие из возможностей стандарта SQL:2011.

Высокопроизводительные и надёжные механизмы транзакций и репликации;
Расширяемая система встроенных языков программирования: в стандартной поставке поддерживаются PL/pgSQL, PL/Perl, PL/Python и PL/Tcl; дополнительно можно использовать PL/Java, PL/PHP, PL/Py, PL/R, PL/Ruby, PL/Scheme, PL/sh и PL/V8, а также имеется поддержка загрузки модулей расширения на языке C;
Наследование;
Возможность индексирования геометрических (в частности, географических) объектов и наличие базирующегося на ней расширения PostGIS;
Встроенная поддержка слабоструктурированных данных в формате JSON с возможностью их индексации;
Расширяемость (возможность создавать новые типы данных, типы индексов, языки программирования, модули расширения, подключать любые внешние источники данных).
Триггеры - Триггеры определяются как функции, инициируемые DML-операциями. Например, операция INSERT может запускать триггер, проверяющий добавленную запись на соответствия определённым условиям. При написании функций для триггеров могут использоваться различные языки программирования (см. выше);
правила и представления - Механизм правил (англ. rules) представляет собой механизм создания пользовательских обработчиков не только DML-операций, но и операции выборки. Основное отличие от механизма триггеров заключается в том, что правила срабатывают на этапе разбора запроса, до выбора оптимального плана выполнения и самого процесса выполнения. Правила позволяют переопределять поведение системы при выполнении SQL-операции к таблице. Хорошим примером является реализация механизма представлений (англ. views): при создании представления создается правило, которое определяет, что вместо выполнения операции выборки к представлению система должна выполнять операцию выборки к базовой таблице/таблицам с учётом условий выборки, лежащих в основе определения представления. Для создания представлений, поддерживающих операции обновления, правила для операций вставки, изменения и удаления строк должны быть определены пользователем;
Индексы - В PostgreSQL имеется поддержка индексов следующих типов: B-дерево, хеш, GiST, GIN, BRIN, Bloom. При необходимости можно создавать новые типы индексов. Индексы в PostgreSQL обладают следующими свойствами:
- возможен просмотр индекса не только в прямом, но и в обратном порядке — создание отдельного индекса для работы конструкции ORDER BY ... DESC не нужно;
- возможно создание индекса над несколькими столбцами таблицы, в том числе над столбцами различных типов данных;
- индексы могут быть функциональными, то есть строиться не на базе набора значений некоего столбца/столбцов, а на базе набора значений функции от набора значений;
- индексы могут быть частичными, то есть строиться только по части таблицы (по некоторой её проекции); в некоторых случаях это помогает создавать намного более компактные индексы или достигать улучшения производительности за счёт использования разных типов индексов для разных (например, с точки зрения частоты обновления) частей таблицы;
- планировщик запросов может использовать несколько индексов одновременно для выполнения сложных запросов.
Многоверсионность (MVCC) - PostgreSQL поддерживает одновременную модификацию БД несколькими пользователями с помощью механизма Multiversion Concurrency Control (MVCC). Благодаря этому соблюдаются требования ACID и практически отпадает нужда в блокировках чтения;
Типы данных:
- Численные типы:
  - Целые;
  - С фиксированной точкой;
  - С плавающей точкой;
  - Денежный тип (отличается специальным форматом вывода, а в остальном аналогичен числам с фиксированной точкой с двумя знаками после запятой);
  - Символьные типы произвольной длины;
- Двоичные типы (включая BLOB);
- Типы «дата/время» (полностью поддерживающие различные форматы, точность, форматы вывода, включая последние изменения в часовых поясах);
- Булев тип;
- Перечисление;
- Геометрические примитивы;
- Интервалы (RANGE);
- Сетевые типы:
  - IP и IPv6-адреса;
  - CIDR-формат;
  - MAC-адрес;
  - UUID-идентификатор
  - XML-данные
- Массивы;
- JSON;
- Идентификаторы объектов БД;
- Псевдотипы;
- Пользовательские типы и механизмы индексирования (GiST();
- Пользовательские объекты: PostgreSQL может быть расширен пользователем для собственных нужд практически в любом аспекте. Есть возможность добавлять собственные:
  - Преобразования типов;
  - Типы данных;
  - Домены (пользовательские типы с изначально наложенными ограничениями);
  - Функции (включая агрегатные);
  - Индексы;
  - Операторы (включая переопределение уже существующих);
  - Процедурные языки;
  - Наследование и партицирование: Таблицы могут наследовать характеристики и наборы полей от других таблиц (родительских). При этом данные, добавленные в порождённую таблицу, автоматически будут участвовать (если это не указано отдельно) в запросах к родительской таблице. Партицирование предназначено для разделения одной таблицы на несколько, так называемые партиции. Партицирование схоже с наследованием, но имеет более дружелюбный к пользователю синтаксис и более строгие ограничения, что позволяет выполнять дополнительные оптимизации при планировании запросов;
- Соблюдение принципов ACID;
- Соответствие стандартам ANSI, SQL-92, SQL-99, SQL:2003, SQL:2011;
- Поддержка запросов с OUTER JOIN, UNION, UNION ALL, EXCEPT, INTERSECT и подзапросов;
- Последовательности;
- Контроль целостности;
- Репликация;
- Общие табличные выражения и рекурсивные запросы;
- Аналитические функции;
- Поддержка Юникода (UTF-8);
- Поддержка регулярных выражений в стиле Perl;
- Встроенная поддержка SSL, SELinux и Kerberos;
- Протокол разделяемых блокировок
- Подгружаемые расширения, поддерживающие SHA1, MD5, XML;
- Расширения для написания сложных выборок, отчётов и т. д. (API открыт);
- Средства для генерации совместимого с другими системами SQL-кода и импорта из других систем;

Оригинальная документация СУБД PostgreSQL

Документация на СУБД в защищенном исполнении PostgreSQL, входящую в состав Astra Linux Special Edition доступна на странице Astra Linux Special Edition РУСБ.10015-01 (очередное обновление 1.7). Эксплуатационная и дополнительная документация.

Оригинальная документация PostgreSQL доступна по ссылке: https://www.postgresql.org/docs/

Установка пакетов

Подключить репозитории:
1. основной репозиторий и актуальное оперативное обновление основного репозитория;
2. только для установки незащищенной версии PostgreSQL - актуальное оперативное обновление расширенного репозитория, включая компонент astra-ce. Для установки защищенной версии подключение компонента astra-ce расширенного репозитория недопустимо.
Обновить список пакетов:
sudo apt update
Опционально: установить актуальные обновления (подробнее см. статью Fly-astra-update и astra-update - инструменты для установки обновлений).
Проверить доступные версии:
apt policy postgresql-11
Установить пакет postgresql-11 старшей доступной версии:
sudo apt install postgresql-11
или установить пакет postgresql-11 указанной версии:
sudo apt install postgresql-11=<номер_версии>
Убедиться, что служба postgresql запустилась:
systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Fri 2021-09-10 12:48:20 MSK; 1min 26s ago
Main PID: 4338 (code=exited, status=0/SUCCESS)
   Tasks: 0 (limit: 4637)
   Memory: 0B
   CGroup: /system.slice/postgresql.service

Первичная настройка СУБД PostgreSQL

Выполнить вход в сессию служебного пользователя postgres:
sudo su - postgres
Работая в сессии служебного пользователя postgres:
1. Установить пароль администратора СУБД:
  psql -c "alter user postgres with password '<указать_пароль>'"
  - Вместо текста <пароль> указать устанавливаемый пароль;
  - Пароль заключается в одинарные кавычки;
  - Вся команда заключается в двойные кавычки.
2. Завершить работу в сессии служебного пользователя postgres:
  exit
Наcтроить удаленный доступ к СУБД, для чего в конфигурационном файле /etc/postgresql/11/main/postgresql.conf проверить и установить параметр listen_addresses:
1. Значение по умолчанию - служба postgresql подключена ко всем сетевым интерфейсам:
```
listen_addresses = '*'
```
2. Допустимые значения:
  1. Служба postgresql подключена ко всем сетевым интерфейсам IPv4:
    listen_addresses = '0.0.0.0'
  2. Служба postgresql подключена ко всем сетевым интерфейсам IPv6:
    listen_addresses = '::'
  3. Разделенный запятыми список IP-адресов сетевых интерфейсов, к которым будет подключена служба:
    listen_addresses = '192.168.1.2,10.0.0.2'
  4. Допускается использовать пустой список, тогда подключение будет возможно только через сокеты UNIX (подробнее см. документацию СУБД).
Если в конфигурацию были внесены изменения, то для того чтобы сделанные изменения вступили в силу перезапустить службу postgresql:
sudo systemctl restart postgresql
Проверить, к каким сетевым портам и интерфейсам подключена служба postgresql, можно командой:
ss -tunelp | grep uid:`id -u postgres`
tcp LISTEN 0 1024 0.0.0.0:5432 0.0.0.0:* uid:107 ino:32947 sk:5 <->
tcp LISTEN 0 1024 [::]:5432 [::]:* uid:107 ino:32948 sk:9 v6only:1 <->
Приведен вывод команды для службы, настроенной по умолчанию (параметр listen_addresses = '*', служба работает с портом 5432 на всех доступных сетевых интерфейсах IPv4 и IPv6);
Настроить активные сетевые экраны, разрешив доступ к сетевому порту postgresql (по умолчанию - порт 5432):
1. Для сетевого экрана ufw:
  sudo ufw allow 5432/tcp
2. Для сетевого экрана firewalld:
  sudo firewall-cmd --add-service=postgresql --zone=internal --permanent

Первичное тестирование работоспособности СУБД

Выполнить вход в сессию служебного пользователя postgres:
sudo su - postgres
Работая в сессии служебного пользователя postgres:
1. Добавить тестового пользователя СУБД test_user1:
  createuser test_user1
2. Добавить тестовую базу данных test_db, указав в качестве её владельца тестового пользователя:
  createdb test_db -O test_user1
3. Установить пароль тестового пользователя:
  psql -c "alter user test_user1 with password '<указать_пароль>'"
4. Подключиться к созданной тестовой базе данных и войти в сессию СУБД:
  psql test_db
  psql (11.12 (Debian 11.12-astra.ce5))
  Введите "help", чтобы получить справку.
  test_db=#
  
  Дальнейшие команды выполняются в сессии СУБД:
  1. Создать таблицу и добавить в нее данные:
    create table test_table ( id int,first_name text, last_name text );
    CREATE TABLE
  2. Добавить в таблицу данные:
    insert into test_table (id,first_name,last_name) values (2,'Иван','Иванов');
    INSERT 0 1
  3. Вывести табличные данные:
    select * from test_table;
    id | first_name | last_name
    ----+------------+-----------
    1 | Иван | Иванов
    (1 строка)
  4. Выйти из сессии СУБД:
    exit
5. Удалить тестовую базу данных:
  dropdb test_db
6. Завершить работу в сессии служебного пользователя postgres:
  exit

Дерево страниц

Расширенный репозиторий Astra Linux Special Edition x.7: установка и развертывание СУБД PostgreSQL