2.06. Данные и СУБД

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

Разработчику Архитектору Инженеру

Данные и СУБД

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

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

Ниже рассматриваются ключевые аспекты администрирования СУБД как сервиса операционной системы.

---

1. Установка СУБД

Установка — это первая и фундаментальная операция. От правильности выполнения этого этапа зависит дальнейшая стабильность и безопасность системы.

СУБД устанавливаются тремя основными способами:

• Пакетный менеджер ОС (например, apt в Ubuntu/Debian, yum/dnf в CentOS/RHEL, pacman в Arch). Это наиболее предпочтительный способ для production-сред, так как обеспечивает:

  • версионную согласованность с ядром и библиотеками ОС;
  • автоматическое размещение конфигурационных файлов в стандартных путях (/etc/);
  • регистрацию службы в системе инициализации (systemd);
  • возможность автоматического обновления (при контроле совместимости);
  • минимальные риски нарушения целостности пакета.

  Пример:

  sudo apt update && sudo apt install postgresql-14

  После установки СУБД обычно запускается в фоне, создаётся кластер (PostgreSQL), инициализируется служебная база (postgres, mysql), и формируется учётная запись суперпользователя (например, postgres с логином через peer-аутентификацию).

• Инсталлятор от поставщика (.exe, .msi, .dmg, .rpm, .deb, tarball). Используется при необходимости развернуть специфическую версию, не входящую в репозиторий, или при развёртывании на Windows, где пакетные менеджеры менее универсальны. Требует аккуратности: инсталляторы могут модифицировать переменные окружения, создавать пользователей ОС, регистрировать службы и изменять политики безопасности.

• Контейнеризация (Docker, Podman и др.). В современных средах часто применяется для изоляции, тестирования и стандартизации развёртывания. Например:

  docker run --name pg-main -e POSTGRES_PASSWORD=securepass -d -p 5432:5432 postgres:14

  При этом необходимо помнить:
  — данные внутри контейнера эфемерны, если не смонтирован volume;
  — сетевые настройки (порты, firewall) требуют явного указания;
  — обновление версии обычно требует миграции данных (в т.ч. через pg_dump/pg_restore);
  — использование в production требует тщательной настройки ресурсных лимитов и мониторинга.

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

• используется официальный источник дистрибутива;
• подписаны GPG-ключи репозиториев (для deb/rpm);
• выбрана LTS- или стабильная версия, совместимая с ОС и зависимостями;
• отключены ненужные компоненты (например, веб-интерфейсы администрирования по умолчанию).

---

2. Первичная настройка и безопасность «из коробки»

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

2.1. Суперпользователь и аутентификация

По умолчанию СУБД создаёт учётную запись администратора (например: root в MySQL, sa в MS SQL, postgres в PostgreSQL). Часто пароль не задан, либо используется временный, либо аутентификация происходит через механизм ОС (peer, ident, Windows Authentication). Это приемлемо для локального администрирования, но недопустимо при любом виде сетевого доступа.

— MySQL/MariaDB: после установки выполняется mysql_secure_installation, который:

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

— PostgreSQL: по умолчанию суперпользователь postgres аутентифицируется через peer — только локально, от имени одноимённого пользователя ОС. Для сетевого доступа необходимо отредактировать pg_hba.conf и postgresql.conf, но делать это следует после настройки пароля:

ALTER USER postgres PASSWORD 'строгий_пароль_с_длиной_не_менее_16_символов';

— MS SQL Server (Linux): первоначальная настройка пароля производится при запуске (MSSQL_SA_PASSWORD в переменных окружения). В Windows — через SQL Server Configuration Manager или sqlcmd.

2.2. Сетевая экспозиция

По умолчанию многие СУБД слушают только localhost (127.0.0.1, ::1). Это безопасно, но ограничивает взаимодействие. Если приложению требуется обращаться к СУБД с другого хоста — необходимо изменить параметр привязки.

Например:

• PostgreSQL: в postgresql.conf:

  listen_addresses = 'localhost,192.168.1.10'  # или '*' — но только при наличии firewall
  port = 5432

• MySQL: в /etc/mysql/mysql.conf.d/mysqld.cnf:

  bind-address = 0.0.0.0
  port = 3306

• MongoDB: в /etc/mongod.conf:

  net:
    bindIp: 127.0.0.1,192.168.1.20
    port: 27017

Критически важно: открытие интерфейса на 0.0.0.0 без защиты недопустимо. Даже при наличии firewall необходимо:

• использовать минимально необходимый список разрешённых IP;
• включить шифрование (TLS/SSL) для транзитных соединений;
• убедиться, что учётные записи имеют сложные пароли и ограничения по хостам.

---

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

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

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

Происходит внутри одного хоста. Используется, как правило:

• Unix-сокеты (PostgreSQL, MySQL на Linux/macOS) — быстрее TCP, не проходит через сетевой стек;
• loopback-интерфейс (127.0.0.1) — универсально, работает везде;
• именованные каналы (MS SQL на Windows).

Примеры:

# PostgreSQL через сокет (peer-аутентификация)
sudo -u postgres psql

# MySQL через сокет
mysql -u root -S /var/run/mysqld/mysqld.sock

# MS SQL через named pipes (в Windows)
sqlcmd -S np:\\.\pipe\sql\query

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

3.2. Удалённое подключение

Требует:

• прослушивания СУБД на сетевом интерфейсе (см. выше);
• разрешения подключения в списке контроля доступа (ACL) СУБД (например, pg_hba.conf, mysqld.cnf + GRANT, mongod.conf + security.authorization);
• открытия порта в firewall (см. таблицу ниже);
• аутентификации (логин/пароль, сертификаты, Kerberos и др.).

Клиентское подключение может осуществляться через:

• консольные утилиты (psql, mysql, mongosh, sqlcmd);
• GUI-инструменты (pgAdmin, DBeaver, Azure Data Studio, MongoDB Compass);
• SDK/драйверы приложений (через connection string).

Connection string содержит:

• хост и порт;
• имя базы;
• учётные данные (иногда в отдельном vault);
• параметры подключения (таймауты, SSLmode, read-only и т.п.).

Пример (PostgreSQL):

postgresql://app_user:password@db.example.com:5432/app_db?sslmode=require

---

4. Сетевые порты, firewall и безопасность транспорта

Ниже — стандартные порты для наиболее распространённых СУБД (могут быть изменены, но это требует согласования со всеми клиентами):

СУБД	Стандартный порт	Протокол	Примечания
PostgreSQL	5432	TCP	Можно менять в postgresql.conf
MySQL / MariaDB	3306	TCP	33060 — X Protocol (MySQL 8+)
MS SQL Server	1433	TCP	UDP 1434 — SQL Browser для именованных экземпляров
Oracle Database	1521	TCP	Listener-порт; RAC использует дополнительные
MongoDB	27017	TCP	27018 — sharding config, 27019 — replica set heartbeat
Redis	6379	TCP	16379 — sentinel
Cassandra	9042 (CQL)	TCP	7000 — intra-node gossip, 7199 — JMX

Требования к firewall:

• разрешать соединения только с доверенных IP (приложений, админ-хостов);
• использовать stateful-правила;
• запрещать порты по умолчанию в публичных зонах (internet);
• для DMZ и production — разделять трафик по VLAN/VPC и применять network segmentation.

Шифрование:

• TLS/SSL обязателен для любого подключения вне локального хоста;
• сертификаты должны быть подписаны доверенным CA (даже внутренним);
• в PostgreSQL: ssl = on, ssl_cert_file, ssl_key_file в postgresql.conf;
• в MySQL: require_secure_transport = ON;
• в MS SQL: «Force Encryption» в конфигураторе;
• в MongoDB: net.ssl.mode: requireSSL.

Отказ от шифрования — грубое нарушение политик безопасности при обработке любых регулируемых данных (ПДн, PCI DSS, ГОСТ Р 57580 и т.п.).

---

5. Развёртывание баз данных

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

Это включает:

1. Создание базы через утилиту администрирования:

   • CREATE DATABASE app_db; (SQL);
   • db.createDatabase("app_db"); (MongoDB shell); — в pgAdmin или DBeaver — через контекстное меню.

2. Настройку кодировки и локали (особенно для PostgreSQL/MySQL):

   • LC_COLLATE, LC_CTYPE, encoding должны соответствовать ожиданиям приложения;
   • например, UTF8 + ru_RU.UTF-8 для русскоязычных систем;
   • изменение после создания невозможно — требует пересоздания.

3. Привязку к таблице пространств (tablespaces), если используется внешнее хранилище (SSD для hot data, HDD для cold archive).

4. Инициализацию структуры (при отсутствии дампа): — выполнение SQL-скриптов миграций; — применение схемы через ORM-миграции (EF Core, Flyway, Liquibase) от лица учётной записи приложения.

5. Настройку параметров производительности на уровне БД (если требуется):

   • work_mem, maintenance_work_mem (PostgreSQL);
   • innodb_buffer_pool_size (MySQL); — но не рекомендуется задавать на уровне отдельной БД — предпочтительнее настройка на уровне экземпляра СУБД.

---

6. Управление учётными записями и правами доступа

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

Принципы:

• Минимальных привилегий: пользователь приложения получает только SELECT, INSERT, UPDATE, DELETE на конкретные таблицы — никогда DROP, CREATE, ALTER, GRANT.
• Разделение ролей:
  — app_user — для приложения (только DML);
  — report_user — для отчётов (только SELECT, возможно, с ограничением по времени/объёму);
  — backup_user — SELECT + LOCK TABLES (MySQL) или pg_read_all_data (PostgreSQL);
  — admin_user — полные права, но не суперпользователь (чтобы избежать обхода логгирования);
  — superuser — только для экстренных случаев и локального доступа.
• Ограничение по хосту: GRANT ... TO 'user'@'192.168.1.50', а не '%'.
• Срок действия паролей и ротация: настройка через политики (например, default_password_lifetime в MySQL 8+, или внешние vault-системы).
• Аудит подключений: включение журналов (log_connections, log_statement), интеграция с SIEM.

Пример безопасного подхода в PostgreSQL:

CREATE ROLE app_role NOINHERIT;
GRANT CONNECT ON DATABASE app_db TO app_role;
GRANT USAGE ON SCHEMA public TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_role;

CREATE USER app_user WITH PASSWORD '...';
GRANT app_role TO app_user;

В MongoDB (с включённой авторизацией):

use admin
db.createUser({
  user: "app_user",
  pwd: "...",
  roles: [{ role: "readWrite", db: "app_db" }]
})

Важно: запрещено использовать root/sa/postgres в production-приложениях. Любое подключение должно происходить через специально созданную учётную запись с минимально достаточными правами.

---

7. Резервное копирование и восстановление

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

7.1. Типы резервного копирования

• Логическое (dump): выгрузка структуры и данных в текстовый/бинарный формат (SQL, BSON, JSON).
  Преимущества: переносимость между версиями, фильтрация, сжатие, шифрование.
  Недостатки: медленнее, требует остановки записи (или --single-transaction), не подходит для очень больших БД.
  Инструменты: pg_dump, mysqldump, mongodump, sqlcmd -Q "BACKUP DATABASE...".

• Физическое/«горячее» (filesystem-level или native backup): копирование файлов данных (datafiles, WAL/redo logs, .MDF/.LDF).
  Преимущества: высокая скорость, поддержка инкрементальных копий, point-in-time recovery (PITR).
  Недостатки: привязка к версии СУБД, требует согласованности (checkpoint, archive mode).
  Инструменты: pg_basebackup, Percona XtraBackup, RMAN (Oracle), SQL Server Backup.

• Снапшоты LVM/ZFS/BTRFS: мгновенное создание точки восстановления на уровне файловой системы.
  Эффективны в связке с WAL-архивированием для PITR.

7.2. Стратегия

Рекомендуется гибридный подход:

• ежедневный логический pg_dump (полный);
• ежечасная архивация WAL-файлов (PostgreSQL) / binlog (MySQL);
• еженедельный физический бэкап для крупных БД;
• хранение копий в трёх местах: локально (быстрое восстановление), в другом дата-центре (аварийное), в облаке (катастрофа).

7.3. Восстановление

Восстановление — это процесс, а не разовая операция. Важно регулярно проверять:

• читаемость файлов бэкапа;
• успешность восстановления в изолированной среде (staging);
• согласованность данных после restore.

Пример восстановления PostgreSQL из логического дампа:

# Создать чистую БД
createdb -U postgres restored_app

# Восстановить (с отключением триггеров, если необходимо)
pg_restore -U postgres -d restored_app --clean --if-exists dump.custom

При физическом восстановлении (PITR):

pg_basebackup -D /var/lib/postgresql/14/restore -X stream
# Заменить recovery.conf (12+) или postgresql.auto.conf
# Запустить в recovery mode → остановится на указанной точке времени

---

8. Мониторинг и диагностика

Мониторинг СУБД — неотъемлемая часть системного администрирования. Цель — обнаружить сбой и предотвратить его за счёт выявления деградирующих параметров.

8.1. Ключевые метрики

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

• Ресурсы ОС (через top, htop, vmstat, iostat, sar, Prometheus Node Exporter):

  • загрузка CPU (особенно steal time в виртуальной среде);
  • потребление памяти (RSS, shared, buffers/cache);
  • дисковый I/O: latency, iops, util% (критично для HDD и перегруженных SSD);
  • использование swap — индикатор нехватки RAM.

• Внутренние метрики СУБД:

  • количество активных подключений (pg_stat_activity, SHOW PROCESSLIST, sys.dm_exec_sessions);
  • длительные запросы (long-running queries) — потенциальные блокировки;
  • состояние пула соединений (если используется pgbouncer, ProxySQL);
  • длина очереди WAL (PostgreSQL), размер redo log buffer (Oracle), checkpoint age;
  • частота cache misses (буферный кэш, shared pool);
  • доля запросов, требующих full scan (отсутствие индексов);
  • частота deadlock’ов и lock wait time.

• Сетевые параметры:

  • latency и packet loss между приложением и СУБД;
  • количество разрывов соединений (возможно, таймауты фаервола или keepalive не настроены);
  • использование TLS — шифрование не должно вызывать значительного CPU overhead.

8.2. Инструменты

• Встроенные представления:

  • PostgreSQL: pg_stat_*, pg_locks, pg_stat_replication;
  • MySQL: performance_schema, information_schema, sys schema;
  • MS SQL: DMV (sys.dm_exec_*, sys.dm_os_*);
  • MongoDB: db.serverStatus(), db.currentOp(), mongostat.

• Внешние системы:

  • Prometheus + Grafana — стандарт де-факто. Экспортеры:
    • postgres_exporter,
    • mysqld_exporter,
    • mssql_exporter,
    • mongodb_exporter.
  • Zabbix / Nagios — классические системы для enterprise-сред; поддерживают шаблоны мониторинга СУБД.
  • pgBadger / pt-query-digest — анализ логов медленных запросов (slow query log).

8.3. Алертинг

Настройка оповещений должна быть дифференцированной:

Уровень	Примеры условий	Реакция
Critical	Служба остановлена, диск заполнен >95%, реплика отстала >1 час	SMS/email/Telegram, автоматический инцидент в ITSM
Warning	>80% max_connections, cache hit ratio <90%, I/O latency >10 мс	Email, отображение в дашборде
Info	Рост объёма БД на >10% в сутки, рост числа мёртвых кортежей (PostgreSQL)	Еженедельный отчёт

Важно: избегать «алерт-фата» — настраивать гистерезис, подавление дублей, подтверждение срабатывания.

---

9. Обновление и патчинг СУБД

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

9.1. Типы обновлений

• Патч-релизы (minor/bugfix):
  Например, PostgreSQL 14.5 → 14.6, MySQL 8.0.32 → 8.0.33.
  Обычно backward-compatible, не требуют изменения структуры данных.
  Рекомендуется применять регулярно — содержат исправления уязвимостей.

• Мажорные версии (major):
  Например, PostgreSQL 13 → 14, MySQL 5.7 → 8.0.
  Могут включать: — изменения формата on-disk (требуют pg_upgrade или дамп/восстановление);
  — устаревание параметров конфигурации;
  — изменение поведения оптимизатора;
  — новые требования к ОС (glibc, OpenSSL).

9.2. Процедура безопасного обновления

1. Анализ changelog’а — выявить breaking changes, deprecated features.
2. Резервное копирование — полный логический и/или физический бэкап до начала.
3. Тестирование в staging: — развернуть копию production-БД; — выполнить обновление; — прогнать нагрузочные и функциональные тесты; — проверить логи на наличие предупреждений.
4. Планирование окна обслуживания — с учётом времени на rollback.
5. Выполнение: — остановка приложений; — остановка СУБД; — обновление пакета (apt upgrade, yum update, установка нового бинарника); — запуск (для major — pg_upgrade --check, затем --link или --copy).
6. Валидация: — проверка версии (SELECT version();); — тестовые запросы; — сверка контрольных сумм (если реализовано); — мониторинг первых 15 минут после возврата трафика.

9.3. Откат

Должен быть предусмотрен и протестирован:

• для minor — обычно достаточно остановить новую версию и запустить старую (если данные не модифицированы);
• для major — только через восстановление из бэкапа (т.к. формат данных изменён).

Важно: никогда не обновлять «на лету» production без staging-валидации.

---

10. Отказоустойчивость и репликация

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

10.1. Модели репликации

Тип	Описание	СУБД-примеры	Особенности для администратора
Master-Slave (Primary-Replica)	Запись — только на primary; чтение — на replica(ы)	PostgreSQL (streaming), MySQL (binlog), MS SQL (Log Shipping / Always On AG)	Replica не принимает запись; задержка (lag) критична для consistency; приложений нужно обучать читать с replica только идемпотентные запросы
Master-Master (Multi-Primary)	Запись возможна на нескольких узлах	MySQL Cluster (NDB), Postgres-BDR (deprecated), Oracle RAC	Высокая сложность разрешения конфликтов; требует строгой синхронизации времени (NTP); редко применяется из-за рисков
Consensus-based (Raft/Paxos)	Кворум узлов принимает решение о записи	MongoDB (replica set), etcd, CockroachDB	Автоматический выбор лидера; tolerates F failures при 2F+1 nodes; требует нечётного числа голосующих узлов

10.2. Настройка репликации (на примере PostgreSQL streaming)

1. На primary:

   • в postgresql.conf:

     wal_level = replica
     max_wal_senders = 5
     wal_keep_size = 1GB

   • в pg_hba.conf:

     host replication replicator 192.168.1.21/32 md5

   • создать роль:

     CREATE USER replicator REPLICATION LOGIN PASSWORD '...';

2. На replica:

   • остановить PostgreSQL;
   • очистить PGDATA;
   • выполнить pg_basebackup -h primary -U replicator -D /var/lib/postgresql/14/main -P -v -R;
   • запустить — replica автоматически подключится.

3. Мониторинг:

   SELECT * FROM pg_stat_replication;
   -- lag в байтах и секундах:
   SELECT 
     application_name,
     client_addr,
     state,
     sent_lsn,
     write_lsn,
     flush_lsn,
     replay_lsn,
     (pg_wal_lsn_diff(sent_lsn, replay_lsn) / 1024 / 1024) AS lag_mb
   FROM pg_stat_replication;

10.3. Переключение при отказе (failover)

• Ручное: администратор останавливает primary, повышает replica до primary (pg_ctl promote), перенаправляет трафик (изменение DNS или балансировщика).
• Автоматическое: с использованием оркестраторов:
  • repmgr (PostgreSQL),
  • MHA (MySQL),
  • Patroni + etcd/Consul (highly available PostgreSQL clusters),
  • MongoDB Replica Set (встроенный механизм).

Важно: автоматический failover требует: — надёжного механизма кворума (чтобы избежать split-brain); — проверки health-состояния («порт открыт» и SELECT 1); — интеграции с DNS или service discovery (Consul, Kubernetes Services).

---

11. Работа с журналами (logging)

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

Основные типы журналов

• Журнал запуска/остановки — systemctl status postgresql, /var/log/syslog, journalctl -u postgresql.
• Журнал ошибок СУБД — log_directory/log_filename (PostgreSQL), error_log (MySQL), ERRORLOG (MS SQL).
• Журнал подключений — включение log_connections, log_disconnections.
• Журнал медленных запросов — log_min_duration_statement = 1000 (PostgreSQL), slow_query_log (MySQL).
• Аудит-журналы — pgAudit (PostgreSQL), SQL Server Audit, MySQL Enterprise Audit — фиксируют DDL/DML по политикам.

Рекомендации

• хранить логи отдельно от данных (иначе при заполнении диска упадёт и СУБД);
• ротация через logrotate (например, ежедневно, 30 дней хранения);
• централизованная отправка в SIEM (Graylog, ELK, Splunk);
• маскировка чувствительных данных (пароли, ПДн) в логах — отключить log_statement = 'all' в production.

---

12. Безопасность на уровне ОС и среды

СУБД не существует в вакууме. Её безопасность зависит от окружения.

12.1. Изоляция

• Запуск под непривилегированным пользователем ОС (postgres, mysql, mongod);
• Отключение shell-доступа для этого пользователя (/usr/sbin/nologin);
• Использование chroot, systemd-ограничений (ProtectSystem=strict, PrivateTmp=yes);
• В контейнерах — --read-only, --cap-drop=ALL, --security-opt no-new-privileges.

12.2. Файловые права

• PGDATA, datadir, dbpath должны быть доступны только владельцу (например, drwx------ 7 postgres postgres);
• конфигурационные файлы (pg_hba.conf, mysql.cnf) — не должны быть world-readable;
• бэкапы — шифрование в покое (GPG, pg_dump | openssl enc, mysqldump --ssl + шифрование архива).

12.3. SELinux / AppArmor

• Включить и настроить профили (например, postgres в /etc/apparmor.d/usr.sbin.postgres);
• Не отключать «ради удобства» — это компрометирует всю систему.

---

13. Документирование и стандартизация

Системный администратор обеспечивает воспроизводимость и передаваемость знаний. Требуется вести:

• Инвентаризацию: — версия СУБД, ОС, ядра; — пути к данным, логам, конфигам; — список пользователей и их назначение.

• Runbook’и: — как сделать бэкап/восстановление; — как выполнить failover; — как обновить; — как диагностировать high load.

• Change management: — все изменения — только через заявки и peer review; — запрет на ad-hoc-изменения в production.

---

Немного полезного

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

---

1. Универсальный скрипт логического резервного копирования PostgreSQL (pg_backup.sh)

Поддерживает:

• полный дамп (custom-формат для pg_restore);
• сжатие (gzip);
• шифрование (опционально, через openssl);
• удаление старых бэкапов (по количеству или возрасту);
• отправка метрик в stdout для мониторинга.

#!/bin/bash
# pg_backup.sh — резервное копирование одной или нескольких БД PostgreSQL
# Требования: pg_dump, gzip, openssl (если шифрование включено)

set -euo pipefail

# === Настройки (вынести в .env при production-использовании) ===
PGUSER="${PGUSER:-postgres}"
PGHOST="${PGHOST:-localhost}"
PGPORT="${PGPORT:-5432}"
BACKUP_DIR="/var/backups/postgresql"
RETENTION_COUNT=7          # хранить N последних копий
ENCRYPT=false              # true — шифровать через openssl
ENCRYPTION_PASS_FILE="/etc/postgresql/backup.key"  # 32+ байт, права 600

# Список баз: "db1 db2" или "all" для pg_dumpall --globals-only + per-db
DATABASES="${DATABASES:-all}"

# === Вспомогательные функции ===
log() { echo "[$(date -Iseconds)] $*" >&2; }
fail() { log "ERROR: $*"; exit 1; }

# Проверка зависимостей
command -v pg_dump >/dev/null || fail "pg_dump not found"
command -v gzip >/dev/null || fail "gzip not found"

# Создание каталога с правами
install -d -m 700 "$BACKUP_DIR" || fail "Cannot create $BACKUP_DIR"

# Формат имени файла: pg_backup_<dbname>_<YYYY-MM-DDTHHMMSS>.dump[.gz][.enc]
timestamp=$(date -u +%FT%TZ)

# === Основная логика ===
if [[ "$DATABASES" == "all" ]]; then
    # Глобальные объекты (роли, табличные пространства)
    global_file="$BACKUP_DIR/pg_globals_${timestamp}.sql.gz"
    log "Dumping globals → $global_file"
    pg_dumpall --host="$PGHOST" --port="$PGPORT" --username="$PGUSER" \
               --globals-only --no-role-passwords | gzip > "$global_file" \
        || fail "Global dump failed"

    # Построчное получение списка баз (исключая служебные)
    psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -t -c "
        SELECT datname FROM pg_database 
        WHERE datistemplate = false AND datname NOT IN ('postgres', 'template0', 'template1');
    " | while read -r dbname; do
        [[ -z "$dbname" ]] && continue
        backup_file="$BACKUP_DIR/pg_backup_${dbname}_${timestamp}.dump"
        log "Dumping database '$dbname' → $backup_file"

        # Выполнение pg_dump в custom-формате (поддержка параллельного restore)
        pg_dump --host="$PGHOST" --port="$PGPORT" --username="$PGUSER" \
                --format=custom --compress=6 --file="$backup_file" "$dbname" \
            || fail "Dump of '$dbname' failed"

        # Сжатие (если не custom+compress, или для sql-формата)
        # В данном случае custom уже сжат, но можно дополнительно:
        # gzip "$backup_file"

        # Шифрование (опционально)
        if [[ "$ENCRYPT" == "true" ]]; then
            [[ -r "$ENCRYPTION_PASS_FILE" ]] || fail "Pass file missing or unreadable"
            openssl enc -aes-256-cbc -salt -in "$backup_file" \
                       -out "$backup_file.enc" -pass file:"$ENCRYPTION_PASS_FILE"
            shred -u "$backup_file"  # безопасное удаление
            backup_file+=".enc"
        fi

        # Отправка метрики (для сбора в Prometheus через node_exporter textfile)
        size_kb=$(stat -c%s "$backup_file" 2>/dev/null | awk '{print int($1/1024)}')
        echo "pg_backup_size_bytes{db=\"$dbname\"} $(stat -c%s "$backup_file")" \
             > "$BACKUP_DIR/.pg_backup_${dbname}.prom"
        echo "pg_backup_last_success_timestamp{db=\"$dbname\"} $(date +%s)" \
             >> "$BACKUP_DIR/.pg_backup_${dbname}.prom"
    done
else
    for dbname in $DATABASES; do
        backup_file="$BACKUP_DIR/pg_backup_${dbname}_${timestamp}.dump"
        log "Dumping single DB '$dbname' → $backup_file"
        pg_dump --host="$PGHOST" --port="$PGPORT" --username="$PGUSER" \
                --format=custom --file="$backup_file" "$dbname" \
            || fail "Dump of '$dbname' failed"
        # (аналогично: сжатие, шифрование, метрики — как выше)
    done
fi

# === Очистка старых копий ===
log "Cleaning backups older than $RETENTION_COUNT versions"
# Для каждой уникальной БД — отдельная ротация
for prefix in pg_backup_* pg_globals; do
    # Удаление по количеству: оставить N самых свежих
    ls -1t "$BACKUP_DIR"/${prefix}_*.dump* 2>/dev/null | \
        tail -n +$((RETENTION_COUNT + 1)) | \
        xargs -r rm -- 
done

log "Backup completed successfully"
exit 0

Как использовать

1. Создать /etc/postgresql/backup.key:

   openssl rand -base64 32 | sudo tee /etc/postgresql/backup.key
   sudo chmod 600 /etc/postgresql/backup.key
   sudo chown postgres: /etc/postgresql/backup.key

2. Настроить .pgpass для пользователя postgres:

   localhost:5432:*:postgres:пароль_суперпользователя

   и chmod 600 ~/.pgpass.
3. Добавить в crontab -u postgres:

   30 2 * * * /opt/scripts/pg_backup.sh >> /var/log/pg_backup.log 2>&1

---

2. Health-check для PostgreSQL (pg_healthcheck.sh)

Выполняет набор диагностических проверок. Возвращает 0 при успехе, 1 — при критической проблеме, 2 — при предупреждении.

#!/bin/bash
# pg_healthcheck.sh — проверка здоровья PostgreSQL
# Используется для readiness/liveness probe в Kubernetes, Zabbix, etc.

set -euo pipefail

PGUSER="${PGUSER:-postgres}"
PGHOST="${PGHOST:-localhost}"
PGPORT="${PGPORT:-5432}"
TIMEOUT=10  # секунд на весь чек

# Временный файл для psql
SQL_TMP=$(mktemp --suffix=.sql)
trap 'rm -f "$SQL_TMP"' EXIT

# Параметры подключения
PSQL_OPTS=(-h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -v ON_ERROR_STOP=1 -A -t -q)

# === 1. Доступность: можно ли подключиться? ===
if ! timeout "$TIMEOUT" psql "${PSQL_OPTS[@]}" -c "SELECT 1;" >/dev/null 2>&1; then
    echo "CRITICAL: Cannot connect to PostgreSQL at $PGHOST:$PGPORT"
    exit 1
fi

# === 2. Состояние репликации (если replica) ===
cat > "$SQL_TMP" <<'EOF'
SELECT
  CASE 
    WHEN pg_is_in_recovery() THEN
      'replica' || 
      CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN '' ELSE '_lagging' END
    ELSE 'primary'
  END AS role,
  COALESCE(
    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int,
    -1
  ) AS replay_lag_sec,
  pg_is_in_recovery() AS in_recovery
EOF

result=$(psql "${PSQL_OPTS[@]}" -f "$SQL_TMP" 2>/dev/null || echo "primary	-1	f")
role=$(echo "$result" | cut -f1)
lag_sec=$(echo "$result" | cut -f2)

if [[ "$role" == *"lagging"* ]] && (( lag_sec > 300 )); then
    echo "WARNING: Replica lag is $lag_sec seconds"
    exit_code=2
fi

# === 3. Свободное место в табличных пространствах ===
cat > "$SQL_TMP" <<'EOF'
SELECT 
  spcname AS tablespace,
  pg_size_pretty(pg_tablespace_size(oid)) AS size,
  pg_size_pretty(pg_tablespace_size(oid) - (pg_tablespace_size(oid) * (1 - (pg_stat_file('base/' || oid || '/PG_VERSION')::json->>'size')::float / pg_tablespace_size(oid)))) AS used,
  100 - (pg_tablespace_size(oid) - (pg_stat_file('base/' || oid || '/PG_VERSION')::json->>'size')::bigint) * 100.0 / pg_tablespace_size(oid) AS pct_used
FROM pg_tablespace
WHERE spcname NOT IN ('pg_global')
EOF

# Упрощённая проверка: проверим только pg_default (обычно /var/lib/postgresql/.../main)
df_line=$(df --output=pcent "$PGDATA" 2>/dev/null | tail -1)
pct_used=${df_line//[% ]/}
if (( pct_used > 90 )); then
    echo "CRITICAL: Disk usage at $PGDATA is ${pct_used}%"
    exit 1
elif (( pct_used > 80 )); then
    echo "WARNING: Disk usage at $PGDATA is ${pct_used}%"
    exit_code=${exit_code:-0}
    ((exit_code == 0)) && exit_code=2
fi

# === 4. Количество подключений ===
max_conn=$(psql "${PSQL_OPTS[@]}" -c "SHOW max_connections;" -t | tr -d ' ')
cur_conn=$(psql "${PSQL_OPTS[@]}" -c "SELECT count(*) FROM pg_stat_activity;" -t | tr -d ' ')
pct_conn=$(( cur_conn * 100 / max_conn ))

if (( pct_conn > 90 )); then
    echo "WARNING: Connection usage is ${pct_conn}% (${cur_conn}/${max_conn})"
    exit_code=${exit_code:-0}
    ((exit_code == 0)) && exit_code=2
fi

# === 5. Блокировки более 30 секунд ===
blocking=$(psql "${PSQL_OPTS[@]}" -c "
    SELECT count(*) 
    FROM pg_locks bl
    JOIN pg_stat_activity a ON bl.pid = a.pid
    WHERE NOT bl.granted AND a.query_start < now() - interval '30 seconds';
" -t | tr -d ' ')

if (( blocking > 0 )); then
    echo "CRITICAL: Found $blocking blocking queries"
    exit 1
fi

# === Завершение ===
if [[ ${exit_code:-0} -eq 2 ]]; then
    echo "Health check passed with warnings"
    exit 2
else
    echo "OK: PostgreSQL is healthy"
    exit 0
fi

Интеграция

• Kubernetes liveness probe:

  livenessProbe:
    exec:
      command: ["/bin/bash", "/opt/scripts/pg_healthcheck.sh"]
    initialDelaySeconds: 60
    periodSeconds: 30
    failureThreshold: 3

• Zabbix UserParameter:

  UserParameter=pg.health,/opt/scripts/pg_healthcheck.sh >/dev/null 2>&1; echo $?

---

3. Шаблон для MySQL-бэкапа с XtraBackup (горячий физический бэкап)

Требует percona-xtrabackup (для InnoDB). Подходит для больших БД (>100 ГБ), где mysqldump неприемлем по времени.

#!/bin/bash
# mysql_xtrabackup.sh — горячий бэкап MySQL с помощью xtrabackup
# Требует: xtrabackup, qpress (для сжатия), права SUPER/BACKUP_ADMIN

set -euo pipefail

USER="backup_user"
PASSWORD_FILE="/etc/mysql/backup.pass"  # одна строка: пароль
BACKUP_ROOT="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="$BACKUP_ROOT/incr_$DATE"   # или full_$DATE

# Тип: full (по расписанию) или incr (ежечасно)
TYPE="${1:-full}"  # full | incr

# Базовый каталог для инкрементальных (последний full)
LATEST_FULL=$(ls -1td "$BACKUP_ROOT"/full_* 2>/dev/null | head -1)

[[ -r "$PASSWORD_FILE" ]] || { echo "Password file missing"; exit 1; }
PASSWORD=$(cat "$PASSWORD_FILE")

# === Полный бэкап ===
if [[ "$TYPE" == "full" ]]; then
    install -d -m 700 "$BACKUP_DIR"
    xtrabackup \
        --user="$USER" --password="$PASSWORD" \
        --backup --compress --compress-threads=4 \
        --target-dir="$BACKUP_DIR" \
        || { echo "Full backup failed"; exit 1; }

    # Подготовка (apply-log) — только для full, чтобы ускорить восстановление позже
    xtrabackup --decompress --remove-original --target-dir="$BACKUP_DIR"
    xtrabackup --prepare --target-dir="$BACKUP_DIR"

# === Инкрементальный бэкап ===
elif [[ "$TYPE" == "incr" ]] && [[ -n "$LATEST_FULL" ]]; then
    BACKUP_DIR="$BACKUP_ROOT/incr_$DATE"
    install -d -m 700 "$BACKUP_DIR"
    xtrabackup \
        --user="$USER" --password="$PASSWORD" \
        --backup --compress --compress-threads=4 \
        --target-dir="$BACKUP_DIR" \
        --incremental-basedir="$LATEST_FULL" \
        || { echo "Incremental backup failed"; exit 1; }
else
    echo "No full backup found for incremental"
    exit 1
fi

# Очистка: хранить 7 full + все incr до следующего full
find "$BACKUP_ROOT" -name "full_*" -type d | sort | head -n -7 | xargs -r rm -rf

---

4. Универсальный health-check для любой СУБД по порту (tcp_healthcheck.sh)

Проверяет, слушает ли процесс на заданном порту и принимает ли соединения.

#!/bin/bash
# tcp_healthcheck.sh <host> <port> [timeout_sec]
# Пример: ./tcp_healthcheck.sh localhost 5432 5

HOST="${1:-localhost}"
PORT="${2:-5432}"
TIMEOUT="${3:-3}"

if timeout "$TIMEOUT" bash -c "</dev/tcp/$HOST/$PORT" 2>/dev/null; then
    echo "OK: $HOST:$PORT is reachable"
    exit 0
else
    echo "CRITICAL: $HOST:$PORT is unreachable or not accepting connections"
    exit 1
fi

Полезен для:

• проверки после установки СУБД;
• readiness probe перед запуском приложения;
• диагностики фаервола.

---

Рекомендации по эксплуатации скриптов

1. Хранение:

   • /opt/db-scripts/ — с chmod 750, владельцем root:dba;
   • конфиги — в /etc/db-scripts/, chmod 640, root:dba.

2. Безопасность:

   • пароли — только в файлах с chmod 600, защищённых от чтения другими;
   • использовать systemd-сервисы с PermissionsStartOnly, чтобы сбросить права перед запуском.

3. Мониторинг:

   • перенаправлять stderr в журнал;
   • отправлять exit code в систему алертинга;
   • логировать время выполнения (time или date +%s в начале/конце).

4. Тестирование:

   • запускать вручную с set -x для отладки;
   • эмулировать отказы (остановить СУБД, заполнить диск).

---