Перейти к основному содержимому

Управление реляционными СУБД

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Управление РСУБД​

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

Перед этой главой полезны основы: СУБД, восстановление после сбоя (WAL при старте) и роль БД в организации. Для managed-сервисов — отдельно Администрирование БД в облаке. Резервные копии и PITR в SQL-разделе: Резервное копирование и восстановление PostgreSQL.

В отличие от встраиваемых СУБД (например, SQLite), управление серверными РСУБД предполагает наличие выделенной инфраструктуры, регулярного обслуживания и глубокого понимания внутренних механизмов системы.

Роль администратора и эволюция эксплуатации​

Администратор баз данных (DBA) отвечает за доступность, целостность, производительность и безопасность сервера данных. Разработчик пишет запросы и миграции; владелец данных утверждает смысл полей — см. роли в организации.

Как менялась эксплуатация​

ЭпохаТипичная картинаФокус DBA
Файлы и настольные БДОбщие каталоги, Access, dBaseРезервная копия файла, права на папку
Выделенный сервер СУБДOracle / SQL Server / PostgreSQL on-premУстановка, патчи, бэкап, тюнинг, права
DevOps и автоматизацияIaC, CI/CD миграций, контейнерыСкрипты, мониторинг, SRE-онколл вместе с приложением
Облако и managedRDS, Cloud SQL, Azure SQLSLA провайдера, параметры, PITR, shared responsibility — облако
Платформа данныхDWH, lakehouse, стримингDBA + инженер данных: ETL, качество, каталог

Сегодня редко существует "только DBA в башне из слоновой кости" — чаще совместная ответственность с разработкой (миграции, EXPLAIN в CI) и с безопасностью (секреты, шифрование). Критерии выбора СУБД на старте проекта — в жизненном цикле БД; ниже — лицензии и "железо" конкретных продуктов.

Инструменты администрирования​

КлассНазначениеПримеры
Графический клиентСхема, SQL, права, планыpgAdmin, SSMS, SQL Developer, MySQL Workbench, DBeaver (универсальный)
Терминал / CLIСкрипты, автоматизация, прод без GUIpsql, mysql, sqlcmd, sqlplus — см. терминальные клиенты
Облачная консольСоздание инстанса, snapshot, параметрыAWS RDS, Azure Portal, Yandex Cloud
Мониторинг и алертыCPU, I/O, replication lag, блокировкиPrometheus + Grafana, pg_stat_statements, AWR (Oracle), Query Store (SQL Server)
Резервное копированиеРасписание, проверка restorepg_dump, RMAN, BACKUP DATABASE, Velero + volume snapshot
Миграции схемыВерсионируемый DDL в CIFlyway, Liquibase, Alembic

Один инструмент редко закрывает всё:

  • DBA живёт в CLI + мониторинг + runbook;
  • а GUI берут для обзора схемы;
  • разбора плана.

Play ITЗагрузка интерактивного демо…

Play ITЗагрузка интерактивного демо…

Подготовка к установке​

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

Выбор платформы​

Выбор конкретной РСУБД — стратегическое решение, основанное на совокупности факторов. Ни одна из систем не является "универсальной", и оптимальный выбор зависит от контекста — типа приложения, требований к отказоустойчивости, бюджета, кадрового состава и регуляторных ограничений.

Сводная матрица критериев на этапе проектирования (нагрузка, HA, команда, регуляторика) — в роли БД в организации. Здесь — детали по лицензиям, ОС и ресурсам отдельных продуктов.

Лицензирование. PostgreSQL распространяется по лицензии PostgreSQL License, близкой к MIT — разрешено свободное использование, модификация и коммерческая эксплуатация без ограничений. Oracle Database в стандартной поставке предоставляет бесплатную редакцию Express Edition (XE) с жёсткими ограничениями по объёму памяти и CPU; основные редакции (Standard, Enterprise) требуют платных лицензий, стоимость которых зависит от числа ядер процессора и типа поддержки. Microsoft SQL Server также имеет бесплатную редакцию Express, а платные редакции (Standard, Enterprise) лицензируются по числу ядер или на основе модели CAL (Client Access License). MySQL доступен в двух редакциях: Community Edition под лицензией GPL (свободное использование без поддержки) и Enterprise Edition — коммерческая версия с расширенными инструментами и технической поддержкой от Oracle.

Поддержка операционных систем. PostgreSQL официально поддерживает Linux, Windows, macOS, FreeBSD и другие Unix-подобные системы; развёртывание на Linux считается наиболее производительным и предпочтительным в промышленной эксплуатации. Oracle Database традиционно имеет наилучшую поддержку на Linux и Solaris, полноценный порт для Windows существует, но в некоторых сценариях (например, RAC на Windows) возможны ограничения. Microsoft SQL Server исторически работал только на Windows, однако начиная с версии 2017 поддерживает Linux (Red Hat, SUSE, Ubuntu), хотя некоторые компоненты (например, SSIS, SSRS, SSAS) остаются доступны только на Windows. MySQL одинаково хорошо работает на всех основных платформах, включая облачные и встраиваемые ОС.

Требования к "железу". PostgreSQL эффективно масштабируется как вертикально (увеличение ресурсов одного сервера), так и горизонтально (шардинг, репликация), но требует адекватного объёма оперативной памяти для буферизации и сортировки. Oracle Database, особенно в конфигурации RAC или при использовании In-Memory Option, предъявляет высокие требования к памяти и CPU, а также к стабильности дисковой подсистемы. Microsoft SQL Server Enterprise Edition может использовать до 12 TB ОЗУ и 240 логических процессоров, но даже Standard Edition требует значительных ресурсов при нагрузке. MySQL наиболее лёгок в базовой конфигурации, однако при включении InnoDB, кэшировании и репликации требования сближаются с PostgreSQL.

Сообщество и экосистема. PostgreSQL обладает крупным, активным, независимым сообществом разработчиков и пользователей; документация высокого качества, поддержка распространяется через коммерческие компании и форумы. Oracle и Microsoft обеспечивают официальную поддержку по контрактам, но сообщества пользователей, хоть и велики, в большей степени ориентированы на корпоративных клиентов. MySQL, несмотря на принадлежность Oracle, сохраняет сильное open-source сообщество, особенно вокруг Percona и MariaDB-форков.

Планирование ресурсов​

Ресурсное планирование — это расчёт, основанный на профиле нагрузки, объёме данных, требованиях к времени отклика и стратегии резервного копирования.

Выделение вычислительного окружения. Рекомендуется изолировать СУБД от других сервисов. Использование выделенного физического сервера обеспечивает максимальную предсказуемость производительности и упрощает диагностику. Виртуальная машина (ВМ) допустима при условии гарантии выделения ресурсов (CPU reservation, memory reservation) и низкой конкуренции за I/O. Контейнеризация (Docker, Podman) уместна для тестовых и разработческих сред, а также в микросервисных архитектурах, но требует особой осторожности в production: нельзя смешивать слои хранения контейнера и данных СУБД, необходимо обеспечить постоянное хранилище (volume) с гарантией durability и производительности, а также учитывать ограничения на shared memory и IPC.

Планирование дискового пространства. Объём необходимо рассчитывать с учётом роста, служебных файлов и операций обслуживания. Составляющие:

  • Пространство под операционную систему (минимум 20–30 ГБ для Linux, 40–60 ГБ для Windows).
  • Пространство под установку СУБД (от 1 до 5 ГБ в зависимости от компонентов).
  • Пространство под данные (основной объём; учитывается коэффициент роста, фрагментация, индексы — они могут занимать до 50–100% объёма таблиц).
  • Пространство под логи транзакций (WAL в PostgreSQL, Redo Logs в Oracle, Transaction Log в MS SQL, Binary Log в MySQL): критически важно расположить их на отдельном физическом диске или RAID-массиве (предпочтительно SSD с высокой устойчивостью к записи); объём должен покрывать не менее 24–48 часов интенсивной записи при штатной нагрузке.
  • Пространство под архивные логи (если включено архивирование WAL/Redo/Transaction Log).
  • Пространство под резервные копии: полные бэкапы хранятся отдельно от основного сервера, но при использовании инкрементальных или дифференциальных стратегий необходимо место для временного хранения и сбора цепочек восстановления.
  • Пространство под временные файлы (сортировка, хеширование, временные таблицы): может временно достигать значительных объёмов при сложных запросах.

Файловая система и монтирование. На Linux рекомендуются XFS или ext4. XFS предпочтителен для больших томов и высоконагруженных операций записи благодаря эффективной обработке больших файлов и журналированию метаданных. ext4 проще в диагностике и хорошо зарекомендовал себя в большинстве сценариев. Для обоих файловых систем при монтировании рекомендуется использовать опцию noatime (или relatime), исключающую запись времени последнего доступа к файлу — это снижает нагрузку на диск без потери функциональности. На Windows используется NTFS с включённым журналированием метаданных (по умолчанию). Важно отключить антивирусную проверку в реальном времени для каталогов данных и логов СУБД — это может вызывать задержки и блокировки.

Пользователь операционной системы. Служба СУБД никогда не должна запускаться от имени суперпользователя (root в Linux, LocalSystem/NT AUTHORITY\SYSTEM в Windows). Создаётся отдельный непривилегированный пользователь (например, postgres, oracle, mssql, mysql), которому выдаются права только на необходимые каталоги (данные, логи, бинарники). Это соответствует принципу минимальных привилегий и ограничивает потенциальный ущерб при компрометации процесса СУБД.

Безопасность до установки​

Безопасность должна закладываться на этапе планирования, а не добавляться после инцидента.

Настройка брандмауэра. По умолчанию все порты закрыты. Открываются только те, которые действительно необходимы:

  • PostgreSQL: 5432 (основной порт; может быть изменён);
  • Oracle Database: 1521 (порт по умолчанию для Listener; может быть настроен на другой);
  • Microsoft SQL Server: 1433 (порт по умолчанию для именованного экземпляра MSSQLSERVER); для именованных экземпляров используется SQL Server Browser на UDP-порту 1434;
  • MySQL: 3306 (порт по умолчанию).

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

Сетевая архитектура. Рекомендуется выделять отдельную VLAN или подсеть для:

  • Взаимодействия между узлами кластера (репликация, синхронизация состояния);
  • Доступа систем резервного копирования;
  • Административного доступа (отделённого от трафика приложений).

Для критически важных систем применяется принцип "зона доверия": сервер БД находится в защищённой внутренней сети, к которой нет прямого доступа из внешней среды; доступ к данным осуществляется только через промежуточные сервисы (API-шлюзы, приложения).

Стратегия управления учётными данными. На этапе подготовки определяется:

  • Где и как хранятся пароли суперпользователей (например, в защищённом менеджере паролей, недоступном для автоматизированных систем);
  • Как будут создаваться и ротироваться пароли для сервисных аккаунтов;
  • Будет ли использоваться централизованная аутентификация (LDAP, Active Directory, Kerberos);
  • Какие требования к сложности паролей и сроку их действия.

Эти правила фиксируются в регламенте эксплуатации, а не оставляются на усмотрение администратора.

Установка СУБД​

Установка — это не просто запуск инсталлятора. Это процесс приведения программного комплекса в состояние, пригодное для дальнейшей настройки и эксплуатации. Ошибки на этом этапе (например, использование дефолтных путей в /tmp, запуск от root, пропуск инициализации) могут сделать дальнейшую эксплуатацию затруднительной или небезопасной.

PostgreSQL​

Основной способ установки PostgreSQL на Linux — использование официальных репозиториев, поддерживаемых сообществом (например, https://www.postgresql.org/download/linux/). Это гарантирует получение актуальных версий и автоматическое разрешение зависимостей. Установка пакета postgresql-server (или аналогичного, в зависимости от дистрибутива) размещает бинарные файлы, но не инициализирует кластер — это ключевой момент, отличающий PostgreSQL от многих других СУБД.

Инициализация кластера — создание структуры каталогов данных, системных таблиц, шаблонных баз (template0, template1) и учётной записи суперпользователя postgres. Выполняется вручную через initdb или скрипт-обёртку пакета. Важно явно указать кодировку, локаль и каталог данных (-D), чтобы избежать неожиданного поведения в будущем. Без инициализации служба PostgreSQL не запустится.

# RHEL/CentOS
sudo postgresql-setup --initdb

# Ubuntu/Debian (пример)
sudo -u postgres /usr/lib/postgresql/16/bin/initdb \
  -D /var/lib/postgresql/16/main \
  --encoding=UTF8 --locale=en_US.UTF-8

После инициализации службу запускают стандартными средствами ОС (Linux) или через "Службы" в Windows:

sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo systemctl status postgresql

При первом запуске автоматически создаётся системная база postgres и пользователь postgres с аутентификацией по умолчанию через peer (Linux) или без пароля (локально, Windows). Это состояние требует немедленной настройки безопасности.

Oracle Database​

Установка Oracle Database — многоэтапный процесс, разделённый на два логических компонента: Database Software и Database Instance.

Установка Database Software выполняется через Oracle Universal Installer (OUI) — графический или консольный инсталлятор. OUI копирует бинарные файлы в указанный ORACLE_HOME, настраивает переменные окружения (ORACLE_BASE, ORACLE_HOME, PATH) и создаёт базовую структуру каталогов. На этом этапе СУБД ещё не функционирует как сервер — это лишь "коробка инструментов".

Создание базы данных — отдельная операция. Стандартный способ — DBCA (мастер — тип БД, ASM/ФС, SGA/PGA, архивный режим, учётные записи SYS/SYSTEM); DBCA создаёт spfile.ora/init.ora и файлы данных. Ручной путь через SQL*Plus в production почти не используется:

CONNECT / AS SYSDBA
CREATE DATABASE ...

Запуск и остановка экземпляра управляются через SQL*Plus или, в конфигурациях RAC, через srvctl:

CONNECT / AS SYSDBA
STARTUP
-- ...
SHUTDOWN IMMEDIATE

Microsoft SQL Server​

Установка SQL Server начинается с запуска setup.exe. Инсталлятор предлагает выбрать компоненты — основной — Database Engine Services, но также могут потребоваться SQL Server Replication, Full-Text and Semantic Extractions, Machine Learning Services и другие. Ключевой выбор — тип экземпляра:

  • По умолчанию (MSSQLSERVER) — слушает стандартный порт 1433;
  • Именованный (например, PROD) — требует указания имени при подключении и, по умолчанию, использует динамические порты (назначаются SQL Server Browser).

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

На этапе настройки учётных записей необходимо создать логин для встроенной роли sysadmin. По умолчанию SQL Server может использовать аутентификацию Windows (Integrated Security), но для гибкости часто включают смешанный режим (Windows + SQL-аутентификация) и задают сложный пароль для учётной записи sa.

После завершения установки служба SQL Server (MSSQLSERVER) автоматически запускается. Её состояние и параметры (включая привязку к портам) контролируются через SQL Server Configuration Manager — специализированный инструмент, входящий в поставку.

MySQL​

Установка MySQL на Linux обычно выполняется через пакетный менеджер; на Windows — GUI-инсталлятором Oracle. После установки служба mysqld запускается автоматически.

# Ubuntu/Debian
sudo apt update && sudo apt install -y mysql-server
sudo systemctl enable --now mysql

# RHEL/CentOS
sudo yum install -y mysql-community-server
sudo systemctl enable --now mysqld

Первый запуск инициализирует системные таблицы в каталоге данных (обычно /var/lib/mysql), генерирует временный пароль для пользователя root (в логах, например, /var/log/mysqld.log) и создаёт базовые привилегии.

Критически важный шаг — запуск mysql_secure_installation:

sudo mysql_secure_installation

Скрипт предлагает:

  • Сменить временный пароль root;
  • Удалить анонимных пользователей;
  • Запретить удалённый вход под root;
  • Удалить тестовую базу test;
  • Обновить привилегии.

Пропуск этого шага оставляет СУБД в уязвимом состоянии. После его выполнения MySQL готова к дальнейшей настройке.

MySQL Workbench — отдельный графический клиент и инструмент администрирования, устанавливаемый дополнительно. Он не является частью сервера и не влияет на его работу.

Конфигурация​

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

Ключевые конфигурационные файлы​

Каждая СУБД имеет свои файлы настройки, но их назначение универсально — управление ресурсами, сетевым взаимодействием, журналированием и безопасностью.

PostgreSQL использует два основных файла:

  • postgresql.conf — сотни параметров (память, параллелизм, журналы). После правок — pg_ctl reload или перезапуск (зависит от параметра: postmaster vs sighup).
  • pg_hba.conf — Host-Based Authentication: кто и как может подключаться. Порядок правил важен.
pg_ctl reload -D /var/lib/postgresql/16/main

Oracle Database полагается на параметрические файлы:

  • spfile.ora (бинарный) или init.ora (текстовый) — параметры экземпляра (SGA_TARGET, PROCESSES, DB_NAME и др.). Динамические изменения — ALTER SYSTEM с SCOPE=BOTH или SPFILE.
ALTER SYSTEM SET sga_target = 4G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 4G SCOPE=BOTH;
  • listener.ora — конфигурация Listener’а, процесса, принимающего входящие соединения и передающего их в экземпляр.
  • tnsnames.ora — клиентский файл, содержащий псевдонимы для подключения (упрощает указание host:port/service_name).

Microsoft SQL Server исторически использовал файл sqlservr.ini, но сейчас основная конфигурация выполняется через:

  • SQL Server Management Studio (SSMS) — графический интерфейс с разделом "Свойства сервера";
  • Системные хранимые процедуры, например sp_configure, которые изменяют параметры в памяти (RECONFIGURE применяет без restart) или требуют restart.

MySQL использует my.cnf (Linux/macOS) или my.ini (Windows). Файл может быть фрагментирован: глобальные настройки в /etc/my.cnf, пользовательские — в ~/.my.cnf. Параметры группируются по секциям: [mysqld] — для сервера, [client] — для клиентов.

Критически важные параметры​

Некоторые настройки оказывают фундаментальное влияние на стабильность и безопасность.

Слушающие адреса. По умолчанию многие СУБД слушают только localhost, что блокирует удалённые подключения.

  • В PostgreSQL: listen_addresses = 'localhost' → listen_addresses = '*' (или конкретный IP).
  • В MySQL: bind-address = 127.0.0.1 → bind-address = 0.0.0.0.
  • В Oracle: listener.ora должен содержать HOST = 0.0.0.0 или конкретный IP.
  • В MS SQL: в Configuration Manager включаются протоколы (TCP/IP) и указывается порт (статический вместо динамического).

Изменение этих параметров без настройки pg_hba.conf (Pg), GRANT (MySQL), logins (MS SQL) или sqlnet.ora (Oracle) делает СУБД доступной, но не аутентифицирует клиентов — это создаёт угрозу.

Память. Неправильное распределение памяти — частая причина деградации производительности или сбоев.

  • PostgreSQL — shared_buffers (буфер кэша ядра, ~25% ОЗУ), work_mem (память на операцию сортировки/хеширования, на запрос), maintenance_work_mem (для VACUUM, CREATE INDEX).
  • Oracle — SGA_TARGET/SGA_MAX_SIZE (разделяемая память — кэш буферов, shared pool), PGA_AGGREGATE_TARGET (приватная память процессов).
  • MS SQL: max server memory (верхний лимит ОЗУ, используемой буферным пулом; обязательно ограничивать, чтобы не "съесть" всю память ОС).
  • MySQL — innodb_buffer_pool_size (главный кэш InnoDB, до 70–80% ОЗУ при dedicated-сервере), key_buffer_size (для MyISAM, если используется).

Настройка "по максимуму" без учёта других процессов на сервере приводит к своппингу и падению производительности.

Журналирование. Обеспечивает восстанавливаемость после сбоев.

  • PostgreSQL: wal_level = replica (минимум для репликации и PITR), archive_mode = on + archive_command (копирование WAL-файлов).
  • Oracle: переключение в режим ARCHIVELOG — обязательное условие для горячих бэкапов и PITR:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
  • MS SQL — выбор Recovery Model — FULL (полное журналирование, необходим для log backup и PITR), SIMPLE (автоматическая усечка лога, PITR невозможен).
  • MySQL: log_bin = ON (включение бинарных логов), binlog_format = ROW (рекомендуется для репликации и PITR).

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

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

  • В PostgreSQL autovacuum автоматически выполняет VACUUM (освобождение места от удалённых строк) и ANALYZE (обновление статистики для оптимизатора). Отключать его нельзя; регулируются параметры агрессивности (autovacuum_vacuum_scale_factor).
  • В Oracle автоматические задания (DBMS_SCHEDULER) управляют сбором статистики (GATHER_STATS_JOB), очисткой SYSAUX. sqlcmd -S localhost -U sa -P "StrongPass!" -Q "SELECT @@VERSION"
UPDATE STATISTICS
  • В MySQL innodb_file_per_table = ON (по умолчанию с 5.6) позволяет эффективно освобождать место при удалении таблиц; OPTIMIZE TABLE физически дефрагментирует, но блокирует таблицу.

Работа с терминальными клиентами​

Терминальные утилиты​

Терминальные утилиты — основной инструмент администрирования. Они незаменимы при отсутствии графического интерфейса (сервер без GUI), в процессах автоматизации (скрипты, CI/CD), при диагностике (например, когда GUI "висит" из-за блокировки) и для точного контроля над выполнением команд.

PostgreSQL: psql
Это не просто "клиент для SQL". Это полноценная среда с поддержкой:

  • Мета-команд (начинаются с \) — \l — список БД, \c dbname — подключение к БД, \d tablename — описание таблицы, \du — список ролей, \x — расширенный вывод, \timing — включение отображения времени выполнения.
  • Переменных (\set var 'value'), условного выполнения (\if), редактирования запросов.
  • Режима непосредственного выполнения (psql -c "SELECT 1"), что позволяет интегрировать SQL в shell-скрипты.
psql -c "SELECT 1"

psql поддерживает .psqlrc — файл инициализации, где можно настроить prompt, подключить расширения, задать параметры подключения.

Oracle: sqlplus и sqlcl
sqlplus — классический клиент, работающий в текстовом режиме. Он позволяет:

  • Подключаться как к локальному экземпляру (/ AS SYSDBA), так и удалённо (user/pass@//host:port/service);
  • Выполнять SQL и анонимные блоки PL/SQL;
  • Управлять форматом вывода (SET LINESIZE, SET PAGESIZE, COLUMN ... FORMAT);
  • Выполнять скрипты (@script.sql).

sqlcl (SQL Developer Command Line) — современная замена, написанная на Java, с поддержкой автодополнения, цветного синтаксиса, JavaScript-скриптов и REST-вызовов. Но sqlplus остаётся стандартом де-факто в скриптах и документации.

Microsoft SQL Server: sqlcmd — утилита командной строки для Windows/SQL-аутентификации, скриптов и автоматизации:

sqlcmd -S localhost -U sa -P "StrongPass!" -Q "SELECT @@VERSION"
sqlcmd -S localhost -d AppDB -i deploy.sql

В интерактивном режиме: :setvar name value, :connect server\instance.

Основное применение — автоматизация развёртывания и обслуживания.

MySQL: mysql
Интерактивный клиент с поддержкой:

  • Истории команд, автодополнения;
  • Выполнения SQL-скриптов (mysql < dump.sql);
mysql < dump.sql
  • Вывода в различные форматы (--table, --batch, --xml);
  • Настройки через .my.cnf в домашнем каталоге.

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

Подключение и настройка доступа​

Настройка доступа — баланс между удобством и безопасностью. Частая ошибка — разрешить доступ "для всех", а потом пытаться "закрыть дыры".

Локальное подключение​

По умолчанию все СУБД позволяют подключаться локально с минимальными проверками:

  • PostgreSQL использует peer (Linux) или ident (Windows) — аутентификация по имени ОС-пользователя. Пользователь postgres ОС может подключиться без пароля к БД postgres.
  • Oracle позволяет подключиться как / AS SYSDBA локально через IPC, если пользователь входит в группу dba.
  • MS SQL разрешает Windows-аутентификацию для членов локальной группы Administrators.
  • MySQL в некоторых дистрибутивах настраивает auth_socket для root@localhost, позволяя вход без пароля через Unix-сокет.

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

Настройка удалённого подключения​

Удалённый доступ требует явного разрешения на двух уровнях: сеть и аутентификация.

PostgreSQL:

  1. В postgresql.conf: listen_addresses = 'IP_сервера' (не * без необходимости);
  2. В pg_hba.conf — правило доступа (пример):
host app_db app_user 192.168.1.100/32 scram-sha-256

Oracle:

  1. В listener.ora: убедиться, что HOST не localhost;
  2. Перезагрузить Listener:
lsnrctl reload
  1. На клиенте — запись в tnsnames.ora:
APPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-server)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = appdb)))

MS SQL:

  1. В SQL Server Configuration Manager → Сетевые настройки SQL Server → Протоколы → включить TCP/IP;
  2. В свойствах TCP/IP указать статический порт (например, 1433);
  3. В брандмауэре открыть этот порт;
  4. В SSMS → Свойства сервера → Безопасность → разрешить SQL Server и Windows-проверку подлинности (если нужно).

MySQL:

  1. В my.cnf: bind-address = 0.0.0.0 (осторожно!) или конкретный IP;
  2. Перезапустить mysqld;
  3. Создать пользователя и выдать права:
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'StrongPass!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'192.168.1.100';
FLUSH PRIVILEGES;

Обратите внимание: в MySQL 'app_user'@'%' разрешает доступ с любого хоста, что крайне нежелательно в production.

Инструменты для проверки​

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

# проверка порта (telnet или netcat)
nc -zv db-server 5432
nc -zv db-server 1433

# аутентифицированное подключение
psql -h db-server -U app_user -d app_db
sqlplus app_user/pass@//db-server:1521/appdb

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

Создание БД и пользователей​

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

Создание базы данных​

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

PostgreSQL — кластер из нескольких баз; новая БД копируется из шаблона template1. Oracle — логическая изоляция через схемы пользователей. SQL Server — отдельные файлы на каждую БД. MySQL — DATABASE и SCHEMA синонимичны.

-- PostgreSQL
CREATE DATABASE app_db OWNER app_user;

-- Oracle (схема = пользователь)
CREATE USER app_user IDENTIFIED BY "StrongPass!";

-- Microsoft SQL Server
CREATE DATABASE AppDB;
CREATE LOGIN app_user WITH PASSWORD = 'StrongPass!';
CREATE USER app_user FOR LOGIN app_user;

-- MySQL
CREATE DATABASE app_db;
CREATE USER 'app_user'@'app-host' IDENTIFIED BY 'StrongPass!';

Создание пользователей и ролей​

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

PostgreSQL — роли и пользователи (CREATE ROLE ... LOGIN). Oracle — CREATE USER + отдельные привилегии и роли. MS SQL — логины на сервере, пользователи в БД. MySQL — GRANT с привязкой к хосту.

-- PostgreSQL
CREATE ROLE app_user LOGIN PASSWORD 'StrongPass!' NOSUPERUSER NOCREATEDB;

-- Oracle
CREATE USER app_user IDENTIFIED BY "StrongPass!";
GRANT CREATE SESSION, CREATE TABLE TO app_user;
CREATE ROLE app_role;
GRANT SELECT ANY TABLE TO app_role;
GRANT app_role TO app_user;

-- MS SQL (в контексте базы AppDB)
ALTER ROLE db_datareader ADD MEMBER app_user;

-- MySQL
GRANT SELECT, INSERT ON app_db.* TO 'app_user'@'192.168.1.%';

Назначение привилегий​

Принцип наименьших привилегий означает:

  • Приложению не нужны права DROP TABLE, ALTER PROCEDURE, CREATE USER;
  • Приложению не нужен доступ к системным таблицам (pg_catalog, information_schema, sys, dba_*);
  • Приложению не нужен доступ на запись в таблицы, используемые только для чтения (справочники, конфигурации).

В PostgreSQL типичный набор для веб-приложения:

GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
-- Если используется отдельная схема:
-- GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- Для будущих таблиц:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

В Oracle:

GRANT CREATE SESSION TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.orders TO app_user;
GRANT SELECT ON app_schema.products TO app_user;  -- только чтение

В MS SQL:

USE AppDB;
CREATE USER app_user FOR LOGIN app_user;
-- Использование предопределённых ролей:
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
-- Или точечные права:
GRANT EXECUTE ON SCHEMA::dbo TO app_user;
DENY DELETE ON SCHEMA::config TO app_user;  -- явный запрет

В MySQL:

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.orders TO 'app_user'@'app-host';
GRANT SELECT ON app_db.products TO 'app_user'@'app-host';
FLUSH PRIVILEGES;  -- требуется в старых версиях, сейчас часто не нужно

Запрет использования суперпользователей для приложений​

Использование учётных записей postgres, sa, root, SYS в коде приложения — грубейшая ошибка. Последствия:

  • Любая уязвимость в приложении даёт атакующему полный контроль над СУБД;
  • Отсутствие аудита: невозможно определить, какое приложение выполнило опасную операцию;
  • Невозможность отзыва прав без остановки всех сервисов.

Решение — строгая политика: суперпользовательские учётные записи используются только для первоначальной настройки и аварийного восстановления. Доступ к ним ограничен по IP, защищён многофакторной аутентификацией и фиксируется в журнале.

Администрирование и управление доступом​

Администрирование — это непрерывный цикл: мониторинг → диагностика → коррекция → проверка. Управление доступом — его неотъемлемая часть, обеспечивающая конфиденциальность, целостность и доступность данных.

Аутентификация и авторизация — разграничение понятий​

Аутентификация (AuthN) — процесс подтверждения личности субъекта: "Кто вы?"
Авторизация (AuthZ) — процесс проверки прав субъекта на выполнение операции: "Можете ли вы это сделать?"

Непонимание этой разницы приводит к ошибкам — например, настройка сложного LDAP-аутентификационного провайдера, но выдача всем вошедшим роли sysadmin.

Методы аутентификации​

Каждая СУБД поддерживает несколько методов, выбор зависит от требований безопасности и инфраструктуры.

Парольная аутентификация — базовый метод. Важно использовать криптостойкие алгоритмы:

  • PostgreSQL — scram-sha-256 (рекомендуется с версии 10), md5 (устаревший, уязвим к атакам offline);
  • Oracle: хеши хранятся в USER$, используется модифицированный SHA-1; с версии 12c поддерживается SHA2 для паролей;
  • MS SQL: хеши хранятся в sys.sql_logins, алгоритм зависит от версии (SHA-2 с 2012 SP1);
  • MySQL: caching_sha2_password (по умолчанию с 8.0), mysql_native_password (совместимость).

Сертификаты SSL/TLS используются для:

  • Шифрования трафика (защита от прослушивания);
  • Аутентификации клиента по сертификату (вместо пароля).
    В PostgreSQL: clientcert=verify-full в pg_hba.conf, sslmode=verify-full в строке подключения.
    В Oracle: настройка Wallet и SSL_SERVER_CERT_DN.
    В MS SQL: включение Force Encryption и настройка сертификата в Configuration Manager.
    В MySQL: REQUIRE X509 при создании пользователя.

Аутентификация ОС — делегирование проверки подлинности операционной системе:

  • PostgreSQL — peer (Unix-сокеты), ident (TCP, устаревший), ldap, pam;
  • MS SQL: Integrated Security=SSPI — использует Kerberos/NTLM;
  • MySQL: auth_socket (локально, по имени пользователя ОС).

Внешние сервисы:

  • LDAP/Kerberos — централизованное управление учётными записями (поддержка в PostgreSQL через pg_ldap, в Oracle через Enterprise User Security, в MS SQL через Windows Auth + AD);
  • PAM (Pluggable Authentication Modules) — гибкий фреймворк для Linux, позволяющий подключать произвольные модули (например, двухфакторную аутентификацию).

Управление привилегиями (авторизация)​

Привилегии делятся на:

  • Системные — действия на уровне сервера: , CREATE ROLE, SHUTDOWN;
CREATE DATABASE
  • Объектные — действия над конкретными объектами — SELECT, INSERT, EXECUTE, REFERENCES.

Роли — механизм группировки привилегий. Вместо назначения прав десяткам пользователей, права назначаются роли, а пользователи добавляются в роль. Это упрощает управление и снижает риск ошибок. Например, роль analyst может включать SELECT на все таблицы, но не INSERT.

Row-Level Security (RLS) — политики, ограничивающие доступ на уровне строк. Полезно для мультиарендных приложений или разделения данных по отделам.

  • PostgreSQL: , ENABLE ROW LEVEL БЕЗОПАСНОСТЬ ON table;
CREATE POLICY
  • Oracle: Virtual Private Database (VPD), DBMS_RLS;
  • MS SQL: ;
CREATE БЕЗОПАСНОСТЬ POLICY ... ADD FILTER PREDICATE
  • MySQL: нет встроенной поддержки (требуется реализация на уровне приложения или через представления).

Мониторинг​

Мониторинг — основа проактивного администрирования.

Активные сеансы показывают, что происходит сейчас:

  • PostgreSQL — pg_stat_activity: pid, пользователь, база, запрос, состояние (active, idle), время начала.
SELECT * FROM pg_stat_activity;
  • Oracle — v$session: sid, serial#, program, status, wait_event.
SELECT * FROM v$session WHERE type != 'BACKGROUND';
  • MS SQL — или sp_who2 — session_id, login_name, status, cpu_time, reads.
SELECT * FROM sys.dm_exec_sessions;
  • MySQL: или SELECT * FROM information_schema.processlist;.
SHOW PROCESSLIST;

Анализ позволяет выявить "висящие" транзакции, долгие запросы, аномальное число соединений.

Блокировки — частая причина деградации производительности:

  • PostgreSQL: pg_locks, pg_blocking_pids();
  • Oracle: v$lock, v$session_wait;
  • MS SQL: sys.dm_tran_locks, sys.dm_os_waiting_tasks;
  • MySQL: information_schema.innodb_trx, sys.innodb_lock_waits.

Журналы ошибок — первое место для диагностики сбоев. Расположение:

  • PostgreSQL: log_directory в postgresql.conf;
  • Oracle: background_dump_dest, user_dump_dest;
  • MS SQL: "Журналы SQL Server" в Configuration Manager или ERRORLOG в каталоге LOG;
  • MySQL: log_error в my.cnf.

Обслуживание​

Регулярное обслуживание предотвращает накопление технического долга.

PostgreSQL — обслуживание таблиц и индексов:

VACUUM (VERBOSE, ANALYZE) orders;
VACUUM FULL orders;              -- блокирует таблицу, редко
ANALYZE orders;
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Автоматизировано через autovacuum (не отключать в production).

Oracle:

  • DBMS_STATS.GATHER_SCHEMA_STATS: сбор статистики;
  • перестроение индекса;
ALTER INDEX ... REBUILD
  • дефрагментация таблицы.
ALTER TABLE ... MOVE

MS SQL:

UPDATE STATISTICS dbo.Orders;
ALTER INDEX ALL ON dbo.Orders REBUILD;
DBCC CHECKDB (AppDB) WITH NO_INFOMSGS, ALL_ERRORMSGS;

MySQL:

  • ANALYZE TABLE: обновление статистики;
  • OPTIMIZE TABLE: эквивалент — дефрагментация (блокирует таблицу).
ALTER TABLE ... ENGINE=InnoDB

Управление пользователями и аудит​

Смена паролей должна быть регламентирована:

-- PostgreSQL
ALTER ROLE app_user PASSWORD 'NewStrongPass!';

-- Oracle
ALTER USER app_user IDENTIFIED BY "NewStrongPass!";

-- MS SQL
ALTER LOGIN app_user WITH PASSWORD = 'NewStrongPass!';

-- MySQL
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewStrongPass!';

Отзыв привилегий и удаление пользователей — операции с последствиями — проверяется, не используются ли права в других местах (например, в DEFINER у представлений или процедур).

Аудит — фиксация критических действий:

  • PostgreSQL: pgAudit (расширение), log_statement = 'ddl';
  • Oracle: AUDIT CREATE TABLE, DROP USER;;
  • MS SQL: SQL Server Audit (на уровне сервера или базы);
  • MySQL: Enterprise Audit Plugin или general_log (не для production).

Аудит должен записываться внешним хранилищем (отдельный сервер, SIEM), чтобы его нельзя было удалить локально.

Резервное копирование​

Резервное копирование — обязательный процесс, обеспечивающий выполнение требований по доступности и целостности. Отказ от бэкапа эквивалентен отказу от ответственности за данные.

Что такое резервное копирование и зачем оно нужно?​

Основные угрозы, от которых защищает бэкап:

  • Аппаратный сбой (отказ диска, контроллера, сервера);
  • Человеческая ошибка (удаление таблицы, DROP DATABASE, некорректный UPDATE);
  • Программный сбой (ошибка в коде приложения, баг в СУБД);
  • Кибератака (ransomware, SQL-инъекция с повреждением данных).

Резервная копия — единственный способ восстановить данные в целостном, согласованном состоянии после таких событий.

Основные типы резервного копирования​

Логическое копирование​

Экспорт данных в виде SQL-скриптов или структурированных текстовых файлов.

  • PostgreSQL — pg_dump (одна БД), pg_dumpall (все БД + глобальные объекты):
pg_dump -U postgres -Fc -f app_db.dump app_db
pg_dumpall -U postgres --globals-only -f globals.sql

Плюсы — переносимость между версиями и ОС, фильтрация (--table, --schema), сжатие.
Минусы — медленно на больших объёмах, блокировка таблиц при --lock-wait-timeout, отсутствие информации о физической структуре.

  • Oracle — expdp (Data Pump Export) в двоичный .dmp:
expdp app_user/password@ORCL \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=app_schema.dmp \
  SCHEMAS=APP_USER \
  CONTENT=DATA_ONLY

Плюсы: гибкость (CONTENT=DATA_ONLY), поддержка сетевого экспорта.
Минусы: требует места на диске, не поддерживает PITR напрямую.

  • MS SQL: bcp (Bulk Copy Program) или SSIS для экспорта таблиц в файлы.
    Примечание: BACKUP DATABASE ... TO DISK создаёт физический бэкап, несмотря на расширение .bak.

  • MySQL — mysqldump генерирует SQL-скрипты CREATE и INSERT:

mysqldump -u root -p --single-transaction --routines app_db > app_db.sql

Плюсы: простота, совместимость.
Минусы: однопоточность (до 8.0), блокировка при --single-transaction не полная.

Физическое копирование​

Копирование файлов данных на уровне файловой системы или бинарных образов.

  • PostgreSQL — pg_basebackup (весь кластер + WAL), также pg_probackup, снимки LVM/ZFS:
pg_basebackup -h localhost -U replicator -D /backup/base -Fp -Xs -P

Плюсы: скорость, точное восстановление состояния.
Минусы: привязка к версии и ОС; для ручного снимка файлов — pg_start_backup() / pg_stop_backup().

  • Oracle: RMAN (Recovery Manager) — стандарт де-факто. Работает на уровне блоков, поддерживает инкрементальные бэкапы, сжатие, шифрование, интеграцию с ARCHIVELOG.
    Плюсы — надёжность, производительность, встроенная проверка целостности.
    Минусы: сложность настройки, требует обучения.

  • MS SQL — физический бэкап:

BACKUP DATABASE AppDB
  TO DISK = 'D:\Backup\AppDB_full.bak'
  WITH COMPRESSION, CHECKSUM, STATS = 10;

Плюсы: интеграция с Recovery Model, сжатие и шифрование.
Минусы: большой размер, зависимость от версии.

  • MySQL: Percona XtraBackup — горячее копирование InnoDB без блокировки. Файловая копия возможна только при остановке (FLUSH TABLES WITH READ LOCK).
    Плюсы: онлайн-бэкап, поддержка инкрементальных.
    Минусы: требует дополнительного ПО.

Стратегии резервного копирования​

Выбор стратегии определяется целевыми показателями (их закладывают в SLA и в требования к надёжности):

  • RPO (Recovery Point Objective, целевая точка восстановления) — максимальный интервал данных, допустимый к потере между последним успешным сохранением и аварией. Напрямую задаёт частоту полных/инкрементных бэкапов и необходимость непрерывной архивации журналов (WAL, redo, transaction log). Для банков и платёжных систем RPO часто измеряют секундами.
  • RTO (Recovery Time Objective, целевое время восстановления) — время от аварии до возврата информационной системы в эксплуатацию (после обнаружения инцидента, восстановления и проверки). Уменьшение RTO удорожает инфраструктуру (горячий standby, автоматический failover, отработанные runbook'и). Задержка обнаружения сбоя входит в фактический простой — нужен мониторинг и дежурство.

Теория RPO/RTO и отличие crash recovery от restore — Восстановление после сбоя; формулировки в контракте — экономика сопровождения.

Полное (Full) — копирование всего. База для восстановления. Частота: ежедневно, еженедельно.

Инкрементное (Incremental) — копирование блоков, изменённых с момента последнего бэкапа любого типа. Требует цепочки: Full → Inc1 → Inc2 → ...
Плюсы: минимальный объём.
Минусы: восстановление требует применения всех бэкапов в цепочке — медленно, уязвимо к потере одного звена.

Дифференциальное (Differential) — копирование блоков, изменённых с момента последнего полного бэкапа.
Плюсы: восстановление быстрее (Full + Diff), менее уязвимо.
Минусы: объём Diff растёт со временем.

Горячее (Online) — бэкап без остановки СУБД. Требует включённого архивирования журналов (WAL, Archive Log, Transaction Log, Binary Log).
Холодное (Offline) — бэкап при остановленной СУБД. Проще, но требует простоя.

Настройка автоматического резервного копирования​

Ручное выполнение бэкапов недопустимо в production.

  • Системные планировщики — cron (Linux), Task Scheduler (Windows). Пример ежедневного логического бэкапа PostgreSQL:
# /etc/cron.d/pg_backup
0 2 * * * postgres pg_dump -Fc app_db > /backup/app_db_$(date +\%F).dump

Аналогично: mysqldump, sqlcmd -Q "BACKUP DATABASE ...".

  • Встроенные инструменты:
    • PostgreSQL: pgAgent (расширение);
    • Oracle: DBMS_SCHEDULER;
    • MS SQL: SQL Server Agent;
    • MySQL: Event Scheduler (редко используется для бэкапов).
  • Сторонние решения — pg_probackup, Bacula, Veeam, Barman — предоставляют мониторинг, проверку целостности, управление сроками хранения.

Ключевые требования к автоматизации:

  • Логирование результатов (успех/ошибка, объём, время);
  • Проверка целостности (например, pg_restore --list, RESTORE VERIFYONLY);
  • Ротация и удаление старых бэкапов по политике (например, 7 daily, 4 weekly, 12 monthly);
  • Хранение копий вне основного сервера (сетевое хранилище, облако, лента).

Восстановление​

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

Восстановление из логического бэкапа​

Логическое восстановление — импорт данных из текстовых или структурированных файлов. Подходит для переноса между средами, исправления ошибок на уровне данных (например, отката некорректного UPDATE), но не для аварийного восстановления всей системы.

  • PostgreSQL:
    Скрипт из pg_dump импортируют через psql (plain) или pg_restore (custom/tar). Перед восстановлением создают целевую базу и пользователя. Для полного восстановления кластера сначала поднимают глобальные объекты из pg_dumpall --globals-only, затем отдельные БД.
# plain SQL
psql -U postgres -d app_db -f dump.sql

# custom/tar
pg_restore -U postgres -d app_db -Fc archive.dump

# глобальные объекты + отдельные БД
pg_restore -U postgres -d postgres --clean --if-exists globals.dump
pg_restore -U postgres -d app_db app_db.dump

Особенность — порядок создания объектов в дампе гарантирует, что зависимости (типы, функции, таблицы) создаются корректно.

  • Oracle — импорт через impdp (Data Pump Import):
impdp app_user/password@ORCL \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=app_schema.dmp \
  SCHEMAS=APP_USER \
  TABLE_EXISTS_ACTION=REPLACE \
  CONTENT=ALL

Требует каталога DIRECTORY и согласованного имени дампа. TABLE_EXISTS_ACTION=REPLACE пересоздаёт таблицы, но теряются внешние ключи, триггеры, гранты — их восстанавливают отдельно.

  • Microsoft SQL Server — физическое восстановление (логическое — через bcp/SSIS):
RESTORE DATABASE AppDB FROM DISK = 'D:\Backup\AppDB_full.bak' WITH REPLACE;
  • MySQL:
    Импорт через mysql из дампа mysqldump. Целевая БД должна существовать (если в дампе нет CREATE DATABASE). С --single-transaction данные согласованы на момент начала экспорта.
mysql app_db < dump.sql

mysql app_db -e "
  SET autocommit=0;
  SET unique_checks=0;
  SET foreign_key_checks=0;
  SOURCE dump.sql;
  SET foreign_key_checks=1;
  COMMIT;
"

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

Восстановление из физического бэкапа​

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

  • PostgreSQL — замена каталога данных копией из pg_basebackup и применение архивных WAL:
pg_ctl stop -D /var/lib/postgresql/16/main -m fast
# заменить содержимое PGDATA файлами из pg_basebackup
# в postgresql.auto.conf (PG 12+) — restore_command, recovery_target_time
pg_ctl start -D /var/lib/postgresql/16/main

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

  • Oracle:
    Процесс управляется через RMAN:
  RMAN> STARTUP MOUNT;
  RMAN> RESTORE DATABASE;
  RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('2025-11-18 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
  RMAN> ALTER DATABASE OPEN RESETLOGS;

RESETLOGS создаёт новые журналы Redo и обнуляет номера SCN — обязательный шаг при PITR. Без архивных логов восстановление возможно только до момента создания бэкапа.

  • Microsoft SQL Server:
  RESTORE DATABASE AppDB 
  FROM DISK = 'full.bak' 
  WITH NORECOVERY;  -- не открывать БД, ожидать log backup
  
  RESTORE LOG AppDB 
  FROM DISK = 'log1.trn' 
  WITH NORECOVERY;
  
  RESTORE LOG AppDB 
  FROM DISK = 'log2.trn' 
  WITH RECOVERY, STOPAT = '2025-11-18 14:30:00';

Ключевые опции — NORECOVERY (для цепочки), RECOVERY (финальный шаг), STOPAT (для PITR). Требует, чтобы бэкапы логов были сделаны в режиме FULL или BULK_LOGGED Recovery Model.

  • MySQL:
    1. Остановка mysqld;
    2. Замена файлов в каталоге данных (InnoDB — ibdata1, ib_logfile*, *.ibd);
    3. Запуск сервера — InnoDB автоматически выполняет crash recovery на основе ib_logfile;
    4. Для PITR: после запуска применение бинарных логов:
     mysqlbinlog --start-datetime="2025-11-18 14:00:00" \
                 --stop-datetime="2025-11-18 14:30:00" \
                 binlog.000001 binlog.000002 | mysql -u root -p

Требуется, чтобы binlog_format = ROW и log_bin = ON были включены до сбоя.

Восстановление на момент времени (Point-in-Time Recovery, PITR)​

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

Общий принцип для всех СУБД:

  1. Восстановить последний полный физический бэкап (состояние на момент T0);
  2. Последовательно применить все архивные журналы, созданные после T0, вплоть до целевого момента T1 (T0 < T1).

Без включённого архивирования журналов (WAL archive, ARCHIVELOG, Transaction Log backup, Binary Log) PITR невозможен. Журналы должны храниться отдельно от основных файлов данных — иначе их утрата при сбое диска делает PITR бессмысленным.

Типичная ошибка — включить архивирование, но не настроить archive_command (PostgreSQL), не настроить назначение архивов в RMAN (Oracle), не делать регулярные LOG BACKUP (MS SQL), не копировать бинарные логи (MySQL). В этом случае система думает, что делает PITR, но на деле может восстановиться только до момента последнего полного бэкапа.

Репликация, масштабирование и кластеризация​

Эти механизмы решают три взаимосвязанные задачи: отказоустойчивость, масштабирование и географическое распределение. Выбор архитектуры зависит от приоритетов: если критична доступность — кластеризация; если нагрузка на чтение — репликация; если нагрузка на запись — шардинг. Расширенный обзор ускорения и нагрузки (включая партиции и EXPLAIN) — девять рычагов производительности; ниже — семь стратегий, сфокусированных на росте системы.

Семь стратегий масштабирования БД​

Когда данных и запросов становится больше, масштабирование идёт по нескольким осям — быстрее отвечать на один запрос (индексы, денормализация, материализованные представления, кэш) и выдерживать больше трафика и объёма (вертикальное железо, реплики, шарды). Семь типовых стратегий удобно держать как шпаргалку; на практике их комбинируют.

СтратегияСутьЧто масштабирует в первую очередь
Индексы (Indexing)Индексы под реальные шаблоны запросов приложения (WHERE, JOIN, ORDER BY)Скорость поиска и отбора на одном узле; меньше полных сканирований
Материализованные представленияТяжёлый SELECT считается заранее; результат хранится как таблица и обновляется по расписаниюПовторяющиеся отчёты и аналитика без пересчёта при каждом обращении
ДенормализацияДублирование полей или "плоские" витрины вместо длинных цепочек JOINЧтение и простые запросы; ценой усложнения записи и синхронизации
Вертикальное масштабированиеБольше CPU, RAM, быстрых дисков на сервере СУБДПропускная способность одного узла, буферный кэш, параллелизм
Кэширование БДГорячие данные в Redis/Memcached (или в буферах СУБД) до обращения к дискуПовторяющиеся чтения; снижение нагрузки на primary
РепликацияКопии primary на других серверах; чтение с репликНагрузку SELECT, отказоустойчивость, отчёты в стороне от OLTP
ШардингЛогическая БД делится на шарды по ключу; каждый шард — свой узел (часто со своими репликами)Объём данных и запись; потолок одной машины
Индексы и шардинг

Индексы ускоряют поиск внутри таблицы на узле. Шардинг делит объём между узлами — у каждого шарда свой набор индексов и меньший размер данных. Это разные уровни: сначала индексы и партиции на одном сервере, шардинг — когда одного primary мало по записи или по диску.

Куда углубиться

Партиционирование крупных таблиц в одной СУБД и тонкая настройка SQL (EXPLAIN, статистика) в эту семёрку не входят, но часто предшествуют шардингу — см. девять рычагов.

Репликация - копии БД для отказоустойчивости​

Репликация — это процесс копирования данных с одного узла (источника, primary) на другие (реплики, standby/replica). Цели:

  • Отказоустойчивость: при отказе primary автоматически или вручную переключаются на реплику;
  • Масштабирование чтения: направление запросов SELECT на реплики снижает нагрузку на primary;
  • Географическое распределение: размещение реплик в разных дата-центрах уменьшает задержки для пользователей;
  • Выполнение ресурсоёмких операций — бэкапы, аналитические запросы, отчёты — на реплике, без влияния на OLTP-нагрузку.

Репликация лидер–подчинённый и реплики только для чтения​

Схема лидер–подчинённый (primary–replica, в MongoDB — primary/secondary) задаёт одного лидера для всех записей и одну или несколько копий, которые применяют тот же поток изменений (WAL, redo, binlog, oplog). Клиентские INSERT/UPDATE/DELETE идут на primary; реплики в режиме hot standby или read replica могут обслуживать SELECT.

Реплика только для чтения — осознанная политика маршрутизации — отчёты, BI, тяжёлые агрегации и фоновые задачи направляют на replica, чтобы не конкурировать с OLTP на primary. В PostgreSQL — hot standby; в MS SQL Always On — READ_ONLY routing; в MySQL — read_only=1 на replica и read_only=OFF на primary.

Учитывают replication lag: после записи на primary данные на replica появляются с задержкой (миллисекунды–секунды). Для "прочитал сразу после записи" используют чтение с primary, session stickiness или уровни согласованности в драйвере (readConcern в MongoDB, проверка pg_last_xact_replay_timestamp()). Сводная карта тем — масштабирование БД — опорные темы.

Типы репликации​

Физическая (бинарная) репликация​

Копирование изменений на уровне журналов транзакций (WAL, Redo Log, Transaction Log, Binary Log). Реплика получает поток бинарных записей и применяет их локально.

  • PostgreSQL: Streaming Replication — встроенная, асинхронная или синхронная (с synchronous_commit = remote_write/remote_apply). Реплика работает в режиме hot standby — разрешены запросы SELECT, но не DDL/DML.
    Преимущества — низкая задержка, минимальная нагрузка на primary, высокая надёжность.
    Недостатки: реплика полностью идентична primary — нельзя реплицировать только часть БД.

  • Oracle: Data Guard — enterprise-решение для физической репликации. Поддерживает Maximum Performance (асинхронно), Maximum Availability (полусинхронно), Maximum Protection (синхронно).
    Реплика может быть в режиме Physical Standby (только для failover) или Active Data Guard (разрешены запросы чтения).

  • MS SQL: Always On Availability Groups — замена устаревшего Database Mirroring. Группа баз данных реплицируется на один или несколько вторичных узлов. Поддерживает синхронный и асинхронный коммит, автоматический failover при наличии кворума (WSFC).
    Вторичные реплики могут использоваться для чтения (READ_ONLY routing).

  • MySQL: Standard Replication и Semisynchronous Replication — цепочка binlog → relay log → применение на replica:

# my.cnf на primary
log_bin = mysql-bin
server_id = 1

# на replica
relay_log = relay-bin
read_only = 1

Проблема: задержка репликации (replication lag) при высокой нагрузке на запись.

Логическая репликация​

Копирование изменений на уровне логических операций (INSERT/UPDATE/DELETE) или строк. Позволяет гибко управлять объёмом данных.

  • PostgreSQL: Logical Replication (с версии 10). Использует publication (источник) и subscription (потребитель). Можно реплицировать отдельные таблицы, фильтровать по условиям, даже объединять данные с других СУБД через расширения.
    Применение — микросервисы, data warehousing, миграции.

  • Oracle: GoldenGate — standalone-продукт для логической репликации и интеграции. Поддерживает гетерогенные системы (например, Oracle → Kafka → PostgreSQL).

  • MS SQL — Transactional Replication — публикация статей (таблиц, представлений), подписка на них. Подходит для сценариев "один ко многим", с фильтрацией по строкам/столбцам.

  • MySQL: Group Replication и InnoDB Cluster — реализуют кворум-базированную репликацию с автоматическим разрешением конфликтов (на основе векторных часов). Обеспечивают высокую доступность и согласованность (по модели primary-secondary или multi-primary).

Масштабирование​

  • Вертикальное (Scale-Up) — увеличение ресурсов одного сервера (CPU, RAM, быстрые диски). Просто в реализации, но имеет физические и экономические пределы. Подходит для монолитных приложений. Это первый ответ на рост нагрузки, пока один узел ещё принимает нагрузку; дальше — реплики для чтения и шардинг для записи (см. таблицу рычагов).

  • Горизонтальное (Scale-Out): добавление узлов.

    • Масштабирование чтения — достигается через репликацию — запросы SELECT распределяются между репликами (с помощью pgpool-II, HAProxy, ProxySQL).
    • Масштабирование записи: требует шардинга — разбиения данных по ключу и распределения шардов по разным серверам. Управление шардингом может быть встроенным (Citus для PostgreSQL, Vitess для MySQL) или прикладным.

Шардинг​

Шардинг — горизонтальное разделение данных: каждый шард — отдельный экземпляр (или replica set), хранящий подмножество строк. Одна логическая таблица ("unsharded") превращается в несколько физических на разных серверах; приложение или прокси знает, по какому ключу (tenant_id, user_id, хеш) направить запрос. В отличие от репликации, шардинг увеличивает суммарную ёмкость и пропускную способность записи; отказоустойчивость достигают репликацией каждого шарда.

Типы разбиения

  • По диапазону — например, user_id 0…999 на shard 1; удобно для отчётов по ключу, риск перегруза "хвостового" диапазона при монотонном ID.
  • По справочнику (directory) — таблица или сервис "значение → shard"; гибкий перенос тенанта, отдельная точка администрирования.
  • По хешу ключа — hash(shard_key) mod N или консистентное хеширование; равномернее при высокой кардинальности user_id, tenant_id.

Ключ шарда задаёт, куда попадёт строка. Перед внедрением проверяют кардинальность (много уникальных значений), равномерность частот (без hot partition) и отсутствие монотонного "хвоста" (timestamp, serial — лучше составной ключ или хеш). Типичные ошибки — шардировать по status с тремя значениями или по глобальному created_at, когда все вставки идут в один шард.

Маршрутизация — кто вычисляет шард — прокси СУБД (mongos, Vitess), драйвер/ORM с shard-aware пулом или логика приложения. Запросы без ключа шарда вынуждают scatter-gather по всем узлам — дорого на больших кластерах.

Операционные задачи DBA — мониторинг перекоса размера шардов, план перебалансировки при добавлении узла, бэкап по шардам, согласованность миграций схемы на всех шардах. Подробнее — 12 концепций § шардинг, MongoDB sharding.

Распределённые соединения (JOIN)​

На одном сервере JOIN выполняет оптимизатор локально — индексы, hash join, merge join. При шардинге строки двух таблиц с разными ключами шарда оказываются на разных узлах — СУБД не может "бесплатно" соединить их, как в монолите.

Типичные последствия:

  • запрос без shard key в WHERE → scatter-gather (опрос всех шардов + слияние на координаторе);
  • JOIN между таблицами с разными ключами шарда → пересылка больших промежуточных наборов по сети;
  • глобальные вторичные индексы (в некоторых распределённых СУБД) координируют запись между узлами и удорожают INSERT.

Архитектурные ответы — проектировать схему так, чтобы связанные строки попадали в один шард (общий tenant_id, user_id); денормализовать витрины; выносить кросс-шардовую аналитику в DWH через CDC; для редких кросс-шардовых транзакций — 2PC или Saga. Обзор в Shared Nothing.

Распределённые транзакции — 2PC и 3PC​

Когда одна бизнес-операция затрагивает несколько баз или шардов, локального BEGIN … COMMIT недостаточно. Двухфазный коммит (2PC) — координатор запрашивает у участников prepare, затем commit или abort. Протокол даёт атомарность, но снижает доступность: при сбое координатора участники могут долго держать блокировки.

Трёхфазный коммит (3PC) добавляет фазу pre-commit, чтобы при потере координатора участники могли завершить транзакцию без бесконечного ожидания. В промышленных микросервисах чаще применяют Saga (цепочка локальных транзакций с компенсациями) или outbox + события, а не глобальный 2PC.

Внутри одной распределённой СУБД (например, Citus, CockroachDB) координатор 2PC может быть встроен в движок — это отдельный уровень от "2PC между десятью микросервисами". Подробнее — распределённые транзакции, MSA.

Кластеризация​

Кластеризация объединяет несколько узлов в единый отказоустойчивый сервис.

  • Высокая доступность (HA):

    • PostgreSQL — стек Patroni + etcd/ZooKeeper/Consul — управляет лидерством (координатор обычно на Raft), автоматически переключает primary при отказе, интегрируется с pg_rewind для быстрого восстановления бывшего primary. Схемы выборов лидера — алгоритмы выбора лидера.
    • Oracle: RAC (Real Application Clusters) — несколько узлов одновременно работают с одними и теми же файлами данных (через shared storage или ASM), обеспечивая отказоустойчивость и масштабирование нагрузки.
    • MS SQL: Always On Failover Cluster Instances (FCI) — кластер на уровне ОС (WSFC), общий диск; Availability Groups — кластер на уровне БД, без общего диска.
    • MySQL: InnoDB Cluster — интеграция Group Replication, MySQL Shell и MySQL Router для автоматического маршрутизирования запросов.

  • Балансировка нагрузки:
    Простое круговое распределение (round-robin) недостаточно — необходимо учитывать:

    • Состояние узла (здоров/нездоров);
    • Тип запроса (чтение/запись);
    • Задержку репликации (направлять чтение только на реплики с replication_lag < 5s).
      Инструменты — HAProxy (с кастомными health-checks), pgpool-II (для PostgreSQL), ProxySQL (для MySQL).

Производительность​

Оптимизация производительности — итеративный процесс: измерить → проанализировать → изменить → измерить снова. Нет универсальных "лучших настроек" — каждая система уникальна.

Девять рычагов производительности​

Ниже — обзор типовых приёмов ускорения БД и снятия нагрузки. Каждый закрывает свой класс узких мест; на практике их комбинируют, начиная с измерений и "дешёвых" правок (индексы, текст запроса), затем настройку сервера и архитектурные решения. Семь из них совпадают с стратегиями масштабирования (индексы, MV, денормализация, vertical scale, кэш, репликация, шардинг); здесь дополнительно — партиционирование и оптимизация запросов.

ПриёмЧто даётПодробнее
ИндексыБыстрый поиск и отбор по условию WHERE / JOIN вместо полного сканирования таблицыТипы по роли, пять структур, сложные индексы
Материализованные представленияГотовый результат тяжёлого запроса на диске; отчёты без повторных JOIN и агрегацийПредставления — материализованные
Вертикальное масштабированиеБольше CPU, RAM и быстрых дисков на одном узлеМасштабирование ниже, буферные пулы
ДенормализацияМеньше JOIN за счёт осознанного дублирования данныхНормализация — денормализация
КэшированиеЧасто читаемые данные в Redis/Memcached или в буферах СУБДКэш вне СУБД, буферные пулы
РепликацияКопии primary для чтения, DR и отказоустойчивостиРепликация
ШардингРазделение данных по узлам — рост записи и объёмаШардинг
ПартиционированиеКрупная таблица — логические части в одной СУБД; pruning, обслуживание по срезамПартиционирование
Оптимизация запросовПереписывание SQL, план, статистика, EXPLAINПошаговая формулировка запроса, JOIN, Оптимизация SQL-запросов, планировщик
Порядок на практике

Сначала метрики и план медленного запроса → индексы и формулировка SQL → настройка памяти и дисков на сервере → реплики чтения и кэш → партиции → шардинг и денормализация только при доказанной необходимости.

Кэширование вне СУБД​

Кэш приложения (Redis, Memcached, in-process) хранит ответы на горячие ключи — профиль пользователя, справочники, агрегаты дашборда — и снижает число обращений к primary. Типичный паттерн cache-aside: сначала кэш, при промахе — запрос в БД и запись в кэш с TTL.

Инвалидация при обновлении данных — отдельная дисциплина (удаление ключа, pub/sub, версионирование записи). Устаревший кэш хуже промаха: клиент получает неверные данные без ошибки.

Кэш СУБД (shared_buffers, innodb_buffer_pool_size) ускоряет повторное чтение страниц с диска на том же сервере; он не заменяет распределённый кэш перед кластером приложений. См. также масштабирование микросервисов — кэш.

Общие принципы​

  • Измерение — основа решения. Без метрик (время выполнения, количество I/O, CPU usage) любые изменения — слепые.
  • Изменять по одному параметру. Комбинированные изменения делают невозможным определение причины эффекта.
  • Тестировать в окружении, приближенном к production. Нагрузочное тестирование должно имитировать реальный профиль запросов:
pgbench -i -s 50 app_db
pgbench -c 10 -j 2 -T 60 app_db
# также — sysbench, HammerDB

Ключевые области для настройки​

Память​

Неправильное распределение — главная причина проблем.

  • Буферные пулы (кэширование данных в ОЗУ):
    • PostgreSQL: shared_buffers (ядро СУБД) + ОС-кэш (остальная память); оптимально shared_buffers = 25% RAM, остальное — ОС.
    • Oracle: SGA_TARGET (разделяемая память: DB_CACHE_SIZE, SHARED_POOL_SIZE) + PGA_AGGREGATE_TARGET (приватная память процессов).
    • MS SQL — ограничение памяти экземпляра (оставьте 4+ ГБ для ОС):
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 28672;  -- пример: 32 ГБ RAM − 4 ГБ
RECONFIGURE;

  • MySQL: innodb_buffer_pool_size — до 70–80% RAM при dedicated-сервере.

  • Память для временных операций:

    • PostgreSQL: work_mem — память на операцию (сортировка, хеширование); при 100 одновременных запросах с work_mem = 64MB может потребоваться 6.4 GB.
    • Oracle: SORT_AREA_SIZE (устаревший), PGA_AGGREGATE_TARGET управляет динамически.
    • MS SQL: memory grants выделяются динамически, контролируются через max server memory и Resource Governor.
    • MySQL — sort_buffer_size, join_buffer_size — на соединение, поэтому осторожность при высокой конкуренции.

Дисковая подсистема​

I/O — самый медленный компонент.

  • Тип дисков: SSD (NVMe предпочтительно) обязательны для журналов транзакций и файлов данных. HDD допустимы только для архивных данных и cold backup’ов.
  • RAID:
    • RAID 10 — оптимален для журналов (высокая скорость записи, отказоустойчивость);
    • RAID 5/6 — для файлов данных при ограниченном бюджете (но с осторожностью из-за write penalty).
  • Размещение — журналы транзакций (WAL, Redo, Transaction Log, Binary Log) обязательно на отдельном физическом диске/массиве от файлов данных — иначе запись в журнал блокируется записью данных.

Параллелизм​

Современные СУБД активно используют многопоточность.

  • PostgreSQL — max_worker_processes, max_parallel_workers, max_parallel_workers_per_gather — управляют параллельным выполнением запросов (начиная с 9.6).
  • Oracle: PARALLEL_MAX_SERVERS, PARALLEL_THREADS_PER_CPU.
  • MS SQL: max degree of parallelism (MAXDOP) — ограничивает число ядер на запрос (часто 4–8 для OLTP).
  • MySQL: innodb_parallel_read_threads (с 8.0) для параллельного чтения.

Планировщик запросов​

Оптимизатор выбирает план выполнения на основе стоимости, рассчитанной по статистике. На стороне разработчика тот же рычаг — переписывание запроса, индексы под предикаты и разбор EXPLAIN (оптимизация SQL).

  • Сбор статистики:

    • ANALYZE (PostgreSQL, MySQL), DBMS_STATS.GATHER_TABLE_STATS (Oracle), UPDATE STATISTICS (MS SQL) — должен выполняться регулярно (автоматически или по расписанию).
    • Устаревшая статистика приводит к выбору неоптимальных планов (например, full scan вместо index scan).

  • Настройка стоимости операций:

    • random_page_cost, seq_page_cost (PostgreSQL) — корректируют предпочтение index scan vs seq scan в зависимости от типа диска (для SSD random_page_cost снижается до 1.1).
    • OPTIMIZER_INDEX_COST_ADJ (Oracle), cost threshold for parallelism (MS SQL).

Сетевые настройки​

При распределённых системах сеть может стать узким местом.

  • Размеры буферов сокетов (net.core.rmem_max, net.core.wmem_max в Linux) увеличивают пропускную способность при bulk-операциях.
  • Включение tcp_keepalive помогает обнаруживать "мёртвые" соединения.

Инструменты для анализа производительности​

  • PostgreSQL:
    • показывает реальное время, число блоков чтения/записи;
EXPLAIN (ANALYZE, BUFFERS)

  • pg_stat_statements — собирает статистику по всем запросам (требует установки расширения);

  • auto_explain — логирует планы медленных запросов.

  • Oracle:

    • план выполнения;
EXPLAIN PLAN FOR ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

  • AWR (Automatic Workload Repository) — отчёты по нагрузке за период;

  • ADDM (Automatic Database Diagnostic Monitor) — автоматический анализ AWR и рекомендации.

  • MS SQL:

    • SET STATISTICS IO, TIME ON — показывает logical/physical reads и время CPU;
    • Actual Execution Plan в SSMS — визуальный план с реальными метриками;
    • DMVs (sys.dm_exec_query_stats, sys.dm_os_wait_stats) — детальная статистика.

  • MySQL:

    • расширенный план;
EXPLAIN FORMAT=JSON
  • Performance Schema — низкоуровневая телеметрия;
  • Slow Query Log — фиксация запросов дольше long_query_time.
Содержание