3.08. Управление РСУБД
Разработчику
Аналитику
Тестировщику
Архитектору
Инженеру
Подготовка к установке
Выбор платформы: Краткое сравнение PostgreSQL, Oracle, MS SQL, MySQL по критериям: лицензирование, поддержка ОС, требования к "железу", сообщество.
Планирование ресурсов:
Выделение отдельного сервера/ВМ/контейнера.
Планирование дискового пространства (ОС, СУБД, логи, резервные копии, WAL/журналы).
Настройка файловой системы (рекомендации: XFS/ext4 для Linux, NTFS для Windows; монтирование с noatime).
Создание выделенного пользователя ОС для запуска службы СУБД (не root/sa!).
Безопасность до установки:
Настройка брандмауэра: открытие только необходимых портов (5432, 1521, 1433, 3306).
Планирование сетевой архитектуры (выделенная сеть для репликации, доступ только с определенных IP).
Подготовка стратегии управления паролями и ключами.
Установка СУБД
PostgreSQL
Установка PostgreSQL
Установка через официальные репозитории (Linux) / установщик (Windows).
Инициализация кластера (initdb).
Запуск службы (systemctl start postgresql / служба Windows).
Oracle Database
Установка Oracle Database
Установка Oracle Database Software (OUI).
Создание базы данных через DBCA (Database Configuration Assistant).
Запуск/остановка через sqlplus (startup, shutdown) или srvctl (RAC).
Microsoft SQL Server
Установка Microsoft SQL Server
Запуск установщика, выбор компонентов (Database Engine Services).
Настройка экземпляра (именованный/по умолчанию).
Запуск службы через SQL Server Configuration Manager.
MySQL
Установка MySQL
Установка через пакетный менеджер (apt, yum) или установщик.
Запуск службы (systemctl start mysqld).
Запуск скрипта безопасности (mysql_secure_installation).
MySQL Workbench
Конфигурация
Ключевые конфигурационные файлы:
PostgreSQL: postgresql.conf (основные настройки), pg_hba.conf (аутентификация и доступ).
Oracle: init.ora / spfile.ora (параметры экземпляра), listener.ora, tnsnames.ora (сетевые настройки).
MS SQL: Настройка через SQL Server Management Studio (SSMS) или системные представления (sp_configure).
MySQL: my.cnf / my.ini.
Критически важные параметры:
Слушающие адреса: listen_addresses = '*' (PostgreSQL), bind-address (MySQL), настройка Listener (Oracle), сетевые протоколы (MS SQL).
Память: shared_buffers, work_mem (Pg); SGA_TARGET, PGA_AGGREGATE_TARGET (Oracle); max server memory (MS SQL); innodb_buffer_pool_size (MySQL).
Журналирование: wal_level, archive_mode (Pg); ARCHIVELOG режим (Oracle); Recovery Model (Full/Bulk-Logged/Simple) (MS SQL); binlog_format (MySQL).
Автоматическое обслуживание: autovacuum (Pg); автоматические задания (Oracle/MS SQL); innodb_file_per_table, optimize (MySQL).
Управление службами: Команды для запуска, остановки, перезагрузки, проверки статуса на Linux/Windows для каждой СУБД.
Работа с терминальными клиентами
PostgreSQL: psql — подключение, выполнение запросов, мета-команды (\l, \d, \du, \timing).
Oracle: sqlplus / sqlcl — подключение, выполнение SQL и PL/SQL, настройка формата вывода.
MS SQL: sqlcmd — выполнение запросов, скриптов, управление сервером.
MySQL: mysql — интерактивный режим, выполнение скриптов.
Почему CLI важен: Автоматизация, скриптование, работа на сервере без GUI, диагностика.
Подключение и настройка доступа
Локальное подключение: Использование сокетов (Unix) или именованных каналов (Windows).
Настройка удаленного подключения:
PostgreSQL: Редактирование pg_hba.conf (методы md5, scram-sha-256, cert), postgresql.conf (listen_addresses).
Oracle: Настройка listener.ora, создание записей в tnsnames.ora на клиенте.
MS SQL: Включение TCP/IP в SQL Server Configuration Manager, настройка порта, настройка брандмауэра.
MySQL: bind-address в my.cnf, создание пользователей с хостом % или конкретным IP.
Инструменты для проверки: telnet, nc (netcat), psql -h, sqlplus user/pass@host:port/service.
Создание БД и пользователей
Создание базы данных:
PostgreSQL: CREATE DATABASE app_db OWNER app_user;
Oracle: CREATE DATABASE ... (обычно через DBCA) или создание схемы (CREATE USER app_user IDENTIFIED BY password;).
MS SQL: CREATE DATABASE AppDB;
MySQL: CREATE DATABASE app_db;
Создание пользователей/ролей:
PostgreSQL: CREATE ROLE app_user LOGIN PASSWORD 'strong_pass';
Oracle: CREATE USER app_user IDENTIFIED BY strong_pass;
MS SQL: CREATE LOGIN app_user WITH PASSWORD = 'strong_pass'; CREATE USER app_user FOR LOGIN app_user;
MySQL: CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_pass';
Назначение привилегий:
PostgreSQL: GRANT CONNECT ON DATABASE app_db TO app_user; GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
Oracle: GRANT CREATE SESSION TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.table TO app_user;
MS SQL: USE AppDB; GRANT SELECT, INSERT, UPDATE, DELETE TO app_user;
MySQL: GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'%';
Запрет использования суперпользователей (postgres, sa, root, SYS) для приложений.
Администрирование и управление доступом
Разница между аутентификацией (AuthN) и авторизацией (AuthZ).
Методы аутентификации:
Парольная (md5, scram-sha-256 в Pg; все СУБД).
Сертификаты SSL/TLS (все СУБД).
Аутентификация ОС (peer, ident в Pg; Integrated Security в MS SQL; auth_socket в MySQL).
Внешние сервисы: LDAP, Kerberos, PAM (поддержка варьируется).
Управление привилегиями (авторизация):
Системные привилегии (создание БД, пользователей).
Привилегии на объекты (таблицы, представления, функции).
Использование ролей для группировки привилегий (PostgreSQL, Oracle, MS SQL).
Row-Level Security (RLS) / политики безопасности строк (PostgreSQL, Oracle, MS SQL).
Мониторинг:
Просмотр активных сеансов (PostgreSQL: pg_stat_activity; Oracle: V$SESSION; MS SQL: sp_who2, sys.dm_exec_sessions; MySQL: SHOW PROCESSLIST).
Мониторинг блокировок.
Просмотр журналов ошибок.
Обслуживание:
PostgreSQL: VACUUM, ANALYZE, REINDEX.
Oracle: Сбор статистики (DBMS_STATS), перестроение индексов.
MS SQL: UPDATE STATISTICS, DBCC REINDEX, DBCC CHECKDB.
MySQL: ANALYZE TABLE, OPTIMIZE TABLE.
Управление пользователями: Смена паролей, отзыв привилегий, удаление пользователей.
Аудит: Включение аудита действий пользователей (все СУБД имеют встроенные механизмы).
Резервное копирование
Что такое резервное копирование и зачем оно нужно? (Потеря данных, сбой диска, человеческая ошибка, кибератака).
Основные типы резервного копирования:
Логическое (экспорт данных):
PostgreSQL: pg_dump, pg_dumpall.
Oracle: expdp (Data Pump Export).
MS SQL: BACKUP DATABASE ... TO DISK (логический бэкап — это выгрузка в .bak, но часто под логическим подразумевают bcp/SSIS выгрузку в файлы).
MySQL: mysqldump.
Плюсы: Портабельность, возможность выборочной выгрузки.
Минусы: Медленно на больших БД, требует остановки или создает большую нагрузку.
Физическое (образ файловой системы / бинарный бэкап):
PostgreSQL: pg_basebackup, файловые снимки (LVM, ZFS), pg_probackup.
Oracle: RMAN (Recovery Manager) — золотой стандарт.
MS SQL: BACKUP DATABASE ... TO DISK (создает бинарный файл .bak).
MySQL: Файловая копия (при остановке или в режиме FLUSH TABLES WITH READ LOCK), Percona XtraBackup.
Плюсы: Быстро, восстановление "как было".
Минусы: Менее гибкое, зависит от версии СУБД/ОС.
Стратегии резервного копирования:
Полное (Full): Копирование всей базы данных. База для всех других типов.
Инкрементное (Incremental): Копирование только данных, измененных с момента последнего бэкапа (любого типа). Требует цепочку для восстановления.
Дифференциальное (Differential): Копирование данных, измененных с момента последнего полного бэкапа. Проще для восстановления, чем инкрементное.
Горячее (Online) vs. Холодное (Offline): Можно ли делать бэкап без остановки СУБД?
Настройка автоматического резервного копирования:
Использование системных планировщиков (cron, Task Scheduler).
Инструменты СУБД (pgAgent, SQL Server Agent, Oracle Scheduler, MySQL Event Scheduler).
Сторонние инструменты (Bacula, Veeam, pg_probackup, RMAN).
Восстановление
Восстановление из логического бэкапа:
PostgreSQL: psql -f dump.sql или pg_restore.
Oracle: impdp.
MS SQL: RESTORE DATABASE ... FROM DISK.
MySQL: mysql < dump.sql.
Восстановление из физического бэкапа:
PostgreSQL: Остановка сервера, замена файлов данных, запуск. Использование pg_wal для восстановления на момент времени (PITR).
Oracle: RMAN RESTORE и RECOVER.
MS SQL: RESTORE DATABASE ... FROM DISK WITH RECOVERY.
MySQL: Остановка сервера, замена файлов, запуск. Использование бинарных логов для PITR.
Восстановление на момент времени (Point-in-Time Recovery, PITR): Ключевая возможность для минимизации потерь данных. Требует включенного архивирования журналов транзакций (WAL, Archive Logs, Transaction Log, Binary Log).
Репликация, масштабирование и кластеризация
Зачем нужна репликация? (Отказоустойчивость, чтение с реплик, географическое распределение, бэкап с реплики).
Типы репликации:
Физическая (бинарная): Копирование изменений на уровне дисковых блоков/WAL. Быстрая, но менее гибкая.
PostgreSQL: Встроенная потоковая репликация (Streaming Replication).
Oracle: Data Guard.
MS SQL: Always On Availability Groups (AG), Database Mirroring (устаревшее).
MySQL: Репликация на основе бинарных логов (стандартная асинхронная/полусинхронная).
Логическая: Копирование изменений на уровне SQL-команд или логических записей. Более гибкая (можно реплицировать часть БД, фильтровать данные).
PostgreSQL: Логическая репликация (начиная с 10 версии).
Oracle: GoldenGate, Streams (устаревшее).
MS SQL: Replication (Transactional, Merge, Snapshot).
MySQL: Group Replication, InnoDB Cluster.
Масштабирование:
Вертикальное (Scale-Up): Увеличение мощности одного сервера.
Горизонтальное (Scale-Out): Добавление новых серверов. Репликация — основа для масштабирования чтения. Шардинг — для масштабирования записи.
Кластеризация:
Высокая доступность (HA): Автоматическое переключение на резервный сервер при отказе основного (PgBouncer + Patroni + etcd для Pg; Oracle RAC; MS SQL Always On AG; MySQL InnoDB Cluster).
Масштабирование нагрузки (Load Balancing): Распределение запросов между несколькими серверами (PgPool-II, HAProxy для Pg/MySQL).
Производительность
Общие принципы:
Нет "серебряной пули". Настройка — итеративный процесс.
Измеряйте до и после изменения (EXPLAIN ANALYZE, мониторинг).
Изменяйте по одному параметру за раз.
Ключевые области для настройки:
Память: Размеры буферных пулов (shared buffers, buffer pool, SGA), память для сортировки и хеширования (work_mem, sort_area_size, memory grants).
Дисковая подсистема: Тип дисков (SSD vs HDD), RAID-массивы, расположение WAL/журналов на отдельном быстром диске.
Параллелизм: Количество рабочих процессов/потоков, настройка параллельного выполнения запросов.
Планировщик запросов: Сбор актуальной статистики (ANALYZE), настройка стоимости операций (random_page_cost, seq_page_cost).
Сетевые настройки: Размеры буферов сокетов.
Инструменты для анализа производительности:
PostgreSQL: EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, auto_explain.
Oracle: EXPLAIN PLAN, AWR (Automatic Workload Repository), ADDM (Automatic Database Diagnostic Monitor).
MS SQL: SET STATISTICS IO/TIME ON, Execution Plan в SSMS, DMVs (Dynamic Management Views).
MySQL: EXPLAIN, SHOW PROFILE, Performance Schema, Slow Query Log.