Описание:

Для чего нужны системы управления базами данных?

! Сегодня вы начинаете свой путь освоения работы с системами управления базами данных (СУБД). А может быть вы уже работали с данными системами и у вас большой опыт. В любом случае — вы пришли за новыми знаниями, а это значит, что придется собраться с силами и штурмовать доки, но мы постараемся максимально облегчить вам работу. В общем, прочь сомнения — приступим!

Как вы знаете, сегодня существует достаточно большое количество СУБД:

• SQLite
• Oracle
• MicrosoftSQL
• MySQL
• PostgreSQL
• etc.

Но... Задумывались ли вы когда-нибудь, зачем все это нужно? Зачем нужны системы управления базами данных если есть файлы?

Мы знаем, что файл — это структурированные данные. И эти данные мы можем уже считать базой данных. Простой, но, все же, базой данных.

Скажем, вот вам база данных сотрудников компании:

# Database.csv:
id , firstname , lastname , job , age , experience \n
1 , Вася , Иванов , DevOps , 32 , 8 \n
2 , Миша , Семенов , Программист , 24 , 2 \n
3 , test , name , _ пусто _ , _ пусто _  , _ пусто _ \n

Что мы можем с помощью файлов? А мы можем сделать следующее:

• сохранить данные по определенному правилу (используя какие-нибудь правила для строк и колонок: id , firstname , lastname , job , age , experience)
• быстро найти данные (с помощью grep например)
• изменить определенные данные (через sed -e s/pattern1/pattern2/)
• поддерживать указанный порядок данных при занесении новых данных (тот самый порядок, что выбрали в первом пункте)
• обеспечить безопасность (назначив правильные права на файл в системе)
• использовать резервное копирование (через rsync по крону раз в час)

И этот список можно продолжать ... Но... Все хорошо, пока мы не натыкаемся на ряд не совсем очевидных проблем. Что будет, если с этим файлом попытаются поработать сразу несколько пользователей? А что, если они будут работать по сети, особенно если файл будет очень большой? А что, если сеть отвалится, или сервер перестанет существовать? И как предоставить доступ к отдельным частям нашего файла или к отдельным столбцам? ...

Вот тут-то и приходят нам на помощь наши системы управления базами данных.

Какие основные проблемы они решают:

1. Многопользовательский доступ (Через создание внутренних пользователей, которые выполняют выделенную им роль в СУБД)
2. Разграничение прав доступа (Через систему RBAC - role based access control)
3. Общий доступ по сети (СУБД - "клиент-серверное" приложение!)
4. Абстракция работы с данными (используя язык запросов SQL)
5. Восстановление после сбоев (речь о правильном подходе работы с данными через набор транзакций с использованием логирования происходящих изменений)

Мы поняли для чего нужны подобные системы, но, в итоге, что же такое СУБД?

СУБД — комплекс программ, позволяющих создать базу данных (БД) и манипулировать данными в ней (вставлять, обновлять, удалять и выбирать) при этом обеспечивая безопасность, надежность хранения и целостность данных. Также СУБД, как правило, предоставляет средства для администрирования своей системы и управления конкретной БД.

Одной из таких СУБД является PostgreSQL.

Почему именно PostgreSQL?

Потому, что у PostgreSQL есть ряд иногда довольно значимых плюсов:

1. Открытый исходный код. Бесплатный проект. Наличие большого комьюнити.
2. Достаточно частые релизы. Разработчики "стараются держать руку на пульсе".
3. Как правило, подробная документация под все поддерживаемые версии.
4. Есть встроенные утилиты бэкапа (pg_dump).
5. Возможность работы в многоядерном и многопоточном режиме.
6. Великолепная поддержка репликации. (потоковая репликация: синхронная, асинхронная)
7. Наличие встроенных бенчмарков. (pgbench)
8. Наличие уже готовых качественных решений управления количеством соединений. (pgbouncer, pgpool)
9. Наличие бесплатного софта для управления отказоустойчивым кластером (Patroni).
10. Восстановление после сбоев вплоть до определенной транзакции. (point-in-time recovery)
11. Расширяемая RDBMS:
    • NoSQL возможности (Расширение: jsonbc)
    • memory-storage возможности (Расширение: tarantool)
    • менеджер очередей в PostgreSQL (Расширение: PgQ)
    • timeseries + SQL (Расширение: TimescaleDB)
    • и др.

Все это мы постараемся подробно разобрать в нашем практикуме.

А если говорить до конца, то PostgreSQL — это система управления объектно-реляционными базами данных => ORDBMS.

Объектно-реляционная СУБД (ORDBMS) — реляционная СУБД, (то есть СУБД, использующая реляционную модель данных внутри, об этом поговорим позже), поддерживающая некоторые технологии, присущие объектно-ориентированным системам, и реализующая объектно-ориентированный подход. Он основывается на объектах, их свойствах и типах, и возможности наследования свойств объектов при создании новых сущностей.

Отлично! Разобрались для чего нужна СУБД и почему PostgreSQL, но есть еще один вопрос, как она работает?
Давайте для начала установим нашу СУБД PostgreSQL, а уже в следующих заданиях посмотрим, как она устроена.

Установка postgresql

Систему управления базами данных можно разделить на два главных компонента: сервер (как набор внешних интерфейсов) и набор клиентов.

Сервер — это и есть СУБД.
Клиентами же являются различные сторонние приложения, написанные программистами, или встроенные приложения, поставляемые вместе с СУБД.

Готовые пакеты — предпочтительный способ установки https://www.postgresql.org/download/.

Linux (Red Hat, CentOS, Debian, Ubuntu и другие)

• входит в дистрибутив ОС
• репозиторий (yum, apt) или пакеты RPM, DEB

FreeBSD, OpenBSD

• пакеты из Ports and Packages Collection

Mac OS X

• менеджер пакетов brew

Windows

• сертифицированный установщик https://www.postgresql.org/download/windows/

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

Первое, что нам нужно сделать, — это добавить официальный репозиторий postgre, чтобы на момент установки мы получили последнюю версию.

Для CentOS

Установите репозиторий RPM:

$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Отключаем встроенный PostgreSQL модуль:

$ sudo dnf -qy module disable postgresql

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

$ sudo dnf install -y postgresql13-server

Можете также проинициализировать базу данных и включить автозапуск:

$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
$ sudo systemctl enable postgresql-13
$ sudo systemctl start postgresql-13

Чтобы проверить состояние сервиса, напишите следующее:

$ systemctl status postgresql-13

Для Ubuntu

$ sudo apt-get install wget ca-certificates

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Затем добавьте репозиторий PostgreSQL, набрав:

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Обновите список пакетов:

$ sudo apt-get update

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

$ apt-cache policy postgresql

postgresql:
  Installed: (none)
  Candidate: 13+223.pgdg18.04+1
  Version table:
     13+223.pgdg18.04+1 500
        500 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 Packages
     10+190ubuntu0.1 500
        500 http://ru.archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages
        500 http://ru.archive.ubuntu.com/ubuntu bionic-security/main amd64 Packages
     10+190 500
        500 http://ru.archive.ubuntu.com/ubuntu bionic/main amd64 Packages

Установите PostgreSQL:

$ sudo apt install postgresql postgresql-contrib

Проверьте версию установленного postgres:

$ psql --version
psql (PostgreSQL) 13.1 (Ubuntu 13.1-1.pgdg18.04+1)

Управление инстансами

Кластер баз данных — это набор баз данных, которыми управляет один экземпляр сервера. Понятие кластеров баз данных было введено разработчиками OS Ubuntu для упрощения работы с PostgreSQL, для этого был собран специальный пакет postgres-common.

По факту в этом контексте кластер баз данных - это просто отдельный инстанс PostgreSQL (отдельный postmaster) со своей рабочей директорией и своим окружением.

Чтобы создать отдельный инстанс, нужно создать каталог, в котором будут размещены данные баз данных, общие таблицы каталога и базы данных template1 и postgres.

Для этого есть команда pg_createcluster:

pg_createcluster [options] version name [--initdb options]

# Example
$ pg_createcluster --locale de_DE.UTF-8 --start 13 new_name_of_cluster

# Изменение локали на немецкий язык показано просто в качестве примера 

Все, что хранится в template1, будет помещено в новую базу данных при ее создании.

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

По умолчанию экземпляр PostgreSQL будет создан с именем main и он будет по умолчанию использовать порт 5432. Если Порт уже занят, то значение порта будет увеличиваться на 1 пока не будет найден свободный порт. Например, если порт 5432 занят, то будет использоваться порт 5433.

Чтобы посмотреть на существующие инстансы PostgreSQL в Ubuntu нужно использовать команду pg_lsclusters:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

Все файлы конфигурации, включая файл параметров PostgreSQL, находятся в каталоге /etc/postgresql.

$ tree /etc/postgresql/13/main/
/etc/postgresql/13/main/
├── conf.d
├── environment
├── pg_ctl.conf
├── pg_hba.conf
├── pg_ident.conf
├── postgresql.conf
└── start.conf

1 directory, 6 files

Пакет postgres-common в OS Ubuntu дает новую утилиту pg_ctlcluster, которая является оболочкой поверх pg_ctl:

$ pg_ctlcluster 13 main status
pg_ctl: server is running (PID: 5297)
/usr/lib/postgresql/13/bin/postgres "-D" "/var/lib/postgresql/13/main" "-c" "config_file=/etc/postgresql/13/main/postgresql.conf"

Интеграция с systemd позволяет легко запускать и останавливать инстанс:

$ systemctl status postgresql@13-main
● postgresql@13-main.service - PostgreSQL Cluster 13-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; indirect; vendor preset: enabled)
   Active: active (running) since Mon 2020-11-30 19:38:59 UTC; 30min ago
 Main PID: 5297 (postgres)
    Tasks: 7 (limit: 4631)
   CGroup: /system.slice/system-postgresql.slice/postgresql@13-main.service
           ├─5297 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresq
           ├─5308 postgres: 13/main: checkpointer 
           ├─5309 postgres: 13/main: background writer 
           ├─5310 postgres: 13/main: walwriter 
           ├─5311 postgres: 13/main: autovacuum launcher 
           ├─5312 postgres: 13/main: stats collector 
           └─5313 postgres: 13/main: logical replication launcher 

Nov 30 19:38:56 ubuntu-server-tmp systemd[1]: Starting PostgreSQL Cluster 13-main...
Nov 30 19:38:59 ubuntu-server-tmp systemd[1]: Started PostgreSQL Cluster 13-main.

$ systemctl stop postgresql@13-main
$ systemctl start postgresql@13-main

Подключение к СУБД PostgreSQL

Для работы с СУБД PostgreSQL существуют различные сторонние инструменты. Но один из инструментов входит в комплект PostgreSQL — это psql (интерактивный терминал).

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

$ su postgres
$ psql
postgres=# \?

Либо в командной строке ОС:

$ psql --help
$ man psql

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

$ psql -d база -U роль -h узел -p порт

$ psql -d postgres -U postgres -h localhost -p 5432

Для нового подключения в psql:

\c[onnect] база роль узел порт

Для получения информация о текущем подключении:

postgres-# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

К обязательным параметрам подключения относятся:

• база (по умолчанию, совпадает с именем пользователя ОС);
• роль (по умолчанию, совпадает с именем пользователя ОС);
• узел (по умолчанию, localhost);
• порт (по умолчанию, 5432) .

Значения по умолчанию позволяют пользователю postgres подключаться к PostgreSQL без указания параметров.

И еще одна метакоманда, которую полезно знать, это \q или quit — выход из psql.

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

Настройка входа через pg_hba.conf

Разберем, что такое pg_hba.conf.

pg_hba позволяет, по умолчанию, любому пользователю локальной системы подключаться к любой базе данных, используя любое имя пользователя баз данных, через Unix-сокеты (по умолчанию для локальных подключений). Аутентификация клиента управляется файлом конфигурации, который традиционно называется pg_hba.conf и хранится в каталоге данных кластера БД. HBA означает аутентификацию на основе хоста. Файл pg_hba.conf по умолчанию устанавливается, когда каталог данных инициализируется initdb. Однако можно разместить файл конфигурации аутентификации в другом месте.

Общий формат файла pg_hba.conf — это набор записей, по одной в строке. Пустые строки игнорируются, как и любой текст после символа комментария #. Записи не могут продолжаться через строки. Запись состоит из нескольких полей, разделенных пробелами и/или табуляциями. Поля могут содержать пробелы, если значение поля заключено в двойные кавычки. Заключение в кавычки одного из ключевых слов в поле базы данных, пользователя или адреса приводит к тому, что слово теряет свой специальный символ и просто соответствует базе данных, пользователю или хосту с этим именем.

Каждая запись определяет:

• тип соединения;
• диапазон IP-адресов клиента (если он актуален для типа соединения) или его dns-имя;
• имя базы данных;
• имя пользователя и метод аутентификации, который будет использоваться для соединений, соответствующих этим параметрам.

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

Запись может иметь один из семи форматов:

Формат 1:

Формат 2:

Формат 3:

Формат 4:

Формат 5:

Формат 6:

Формат 7:

Давайте рассмотрим создание нового пользователя и подключение к серверу на примере.
Введите следующие команды используя консоль ОС:

$ sudo -u postgres createuser --interactive
sudo -u postgres createuser --interactive
Введите имя новой роли: task01
Должна ли новая роль иметь полномочия суперпользователя? (y — да, n — нет) y

Либо с помощью psql подключимся к рабочему инстансу postgres и создадим пользователя.

$ su postgres
$ psql
postgres=# CREATE USER task01 WITH PASSWORD 'mypassword';
CREATE ROLE

Проверим созданные роли:

postgres=# \du
                                          Список ролей
 Имя роли |                                Атрибуты                                 | Член ролей 
----------+-------------------------------------------------------------------------+------------
 postgres | Суперпользователь, Создает роли, Создает БД, Репликация, Пропускать RLS | {}
 root     | Суперпользователь, Создает роли, Создает БД                             | {}
 task01   | 

Для возможности подключиться к СУБД от созданного пользователя необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.

Для начала смотрим путь расположения данных для PostgreSQL:

$ ps aux | grep postgres | grep -- -D

либо

$ pg_ctlcluster 13 main status
/usr/lib/postgresql/13/bin/postgres "-D" "/var/lib/postgresql/13/main" "-c" "config_file=/etc/postgresql/13/main/postgresql.conf"

Нас интересует все, что после -D.

• -D путь расположения конфигурационных файлов.

Открываем pg_hba.conf:

$ vi /var/lib/pgsql/13/data/pg_hba.conf

Добавляем права на подключение нашему созданному пользователю task01:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             task01         127.0.0.1/32            md5

Важно понимать, что вы изменили настройки сервера, и теперь вам нужно как-то сказать ему об этом.
Файл pg_hba.conf прочитывается при запуске сервиса PostgreSQL, а также в тот момент, когда основной сервер получает сигнал SIGHUP. Если вы редактируете файл во время работы системы, необходимо послать сигнал процессу postmaster (используя pg_ctl reload, вызвав SQL-функцию pg_reload_conf(), выполнив kill -HUP или systemctl reload postgresql.service), чтобы он заново прочел обновлённый файл.

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

$ psql -U task01 postgres -h 127.0.0.1
Пароль пользователя task01: 
psql (13.1)
Введите "help", чтобы получить справку.

postgres=> 

На этом пока все, можете приступать к заданию!

Полезные ссылки:

• relational database
• postgresql download
• postgresql installation
• postgresql control
• pg_hba
• Установка postgresql из исходников
• Запуск сервера баз данных (настройка PostgreSQL в качестве системного сервиса)
• Как работает реляционная БД

Правила выполнения задания:

1. Время создания окружения занимает до 5 минут.
2. После нажатия кнопки «Начать выполнение» для вас будет создано окружение для выполнения задания, состоящее из одной или более виртуальнах машин с Ubuntu Linux. К ним будут выданы IP, логин и пароль для доступа по SSH, а также при необходимости — дополнительных ресурсов.
3. Также, если необходимо, вам могут быть выданы переменные, которые в задании указаны в фигурных скобках, — их надо будет подставить при выполнении задания (например, ${base_domain}).
4. После выполнения всех пунктов задания нажмите кнопку «Проверить выполнение», и в течение 3-5 минут скрипт проверит выполнение всех условий и выставит вам оценку.
5. В случае, если вы что-то забыли, можно исправить ошибку и отправить на проверку повторно (нажав кнопку «Проверить выполнение»).
6. После получения удовлетворительной оценки нажмите кнопку «Завершить задание», чтобы созданное окружение уничтожилось и вы могли приступить к следующему заданию.
7. Если у вас закончилось время (истек таймер) — окружение будет автоматически уничтожено и вам придется начать выполнение заново.
8. Также, если вы успешно сдали задание, но у вас остались вопросы — вы всегда сможете задать их куратору после проверки (используя кнопку «Задать вопрос куратору») или в чате практикума в любое удобное для вас время. Обращаем внимание — кураторы проверяют вопросы в течение 24 часов.

Задание:

1. На сервер PostgreSQL-CentOS добавьте официальный репозиторий PostgreSQL и установите СУБД PostgreSQL версии 11. (Port 5432)
2. На сервере PostgreSQL-CentOS создайте пользователя для управления PostgreSQL с именем pgsqlcentos и правами Superuser.
3. На сервер PostgreSQL-Ubuntu18 добавьте официальный репозиторий PostgreSQL и установите СУБД PostgreSQL версии 12. (Port 5432)
4. На сервере PostgreSQL-Ubuntu18 создайте пользователя для управления PostgreSQL с именем pgsqlubuntu18 и правами Superuser.
5. На сервер PostgreSQL-Ubuntu20 установите СУБД PostgreSQL версии 13.1 из исходных кодов PostgreSQL по шагам из документации предварительно установив на сервере компилятор gcc (install build-essential). Проверьте наличие утилиты psql в директории /usr/local/pgsql/bin/. Если утилиты psql там нет, то переместите ее в указанную директорию. (Port 5432)
6. На сервере PostgreSQL-Ubuntu20 создайте пользователя для управления PostgreSQL с именем pgsqlubuntu20 и правами Superuser.
7. Если уверены, что все сделали правильно, сдавайте задание на проверку.