Резервное копирование
Резервное копирование в PostgreSQL — это не просто техническая процедура, а стратегический процесс обеспечения жизнеспособности бизнеса.
Философия отказоустойчивости:-
Принцип "не надейся на железо" — средний срок службы HDD 3-5 лет
-
Закон Мерфи в действии — сбой произойдет в самый неподходящий момент
-
Теория "Черных лебедей" — необходимо готовиться к непредсказуемым событиям
-
Требования GDPR: хранение персональных данных — до 25 млн. штраф
-
ФЗ-152 "О персональных данных": обязательность резервных копий
-
Отраслевые стандарты (PCI DSS, HIPAA)
Убытки = (Средний доход в час) × (Время простоя) × (Коэф. репутационных потерь)ROI систем бэкапа: 3-6 месяцев за счет предотвращения потерь
Логические дампы (pg_dump/pg_dumpall)
Базовый синтаксис (Linux):# Для одной БД (custom-формат с сжатием)
pg_dump -U postgres -F c -Z 6 -f /backups/db_name.dump db_name
# Для всех БД (plain SQL)
pg_dumpall -U postgres --clean --file=/backups/full_dump.sql
# Дамп одной БД (custom-формат с сжатием)
pg_dump -U postgres -F c -Z 6 -f "C:\Backups\db_name.dump" db_name
# Дамп всех БД (plain SQL)
pg_dumpall -U postgres --clean --file="C:\Backups\full_dump.sql"
Параметры для больших БД
Linux:# Параллельный дамп больших таблиц
pg_dump -j 8 -F d -f /backups/parallel_dump db_name
# Исключение таблиц >100GB
pg_dump --exclude-table-data='*.large_*' db_name
# Дамп только структуры
pg_dump --schema-only db_name
# Параллельный дамп (для формата directory)
pg_dump -j 4 -F d -f "C:\Backups\parallel" db_name
# Исключение таблиц (например, логов)
pg_dump --exclude-table-data='public.logs*' db_name
Восстановление
Linux:# Из custom-формата
pg_restore -U postgres -d new_db /backups/db_name.dump
# Только конкретная таблица
pg_restore -t users -d db_name /backups/db_name.dump
# Из custom-формата
pg_restore -U postgres -d new_db "C:\Backups\db_name.dump"
# Только схема (без данных)
pg_restore --schema-only -d db_name "C:\Backups\db_name.dump"
Физические бэкапы (pg_basebackup)
Linux:pg_basebackup -D /backups/basebackup -U replicator -P -v
pg_basebackup -D "C:\Backups\basebackup" -U replicator -P -v
Продвинутые опции
Linux:# Сжатие на лету (gzip уровня 9)
pg_basebackup --compress=9 -D /backups/compressed
# Только определенные tablespaces
pg_basebackup --tablespace-mapping=/old/location=/new/location
# Проверка целостности
pg_basebackup --verify-checksums
# Сжатие в реальном времени (gzip)
pg_basebackup --compress=9 -D "C:\Backups\compressed"
# Проверка целостности
pg_basebackup --verify-checksums
Восстановление физического бэкапа
Linux:# Остановка PostgreSQL
pg_ctl stop -D /var/lib/postgresql/data
# Копирование файлов
rsync -av /backups/basebackup/ /var/lib/postgresql/data/
# Запуск с recovery
touch /var/lib/postgresql/data/recovery.signal
pg_ctl start -D /var/lib/postgresql/data
# 1) Остановка PostgreSQL
pg_ctl stop -D "C:\Program Files\PostgreSQL\ВАША_ВЕРСИЯ\data"
# 2) Удалите содержимое папки data (кроме postgresql.conf и pg_hba.conf).
# 3) Скопируйте файлы из бэкапа:
robocopy "C:\Backups\basebackup" "C:\Program Files\PostgreSQL\ВАША_ВЕРСИЯ\data" /MIR
# 4) Создайте файл recovery.signal в папке data.
# 5) Запустите PostgreSQL:
pg_ctl start -D "C:\Program Files\PostgreSQL\ВАША_ВЕРСИЯ\data"
WAL-архивирование (PITR) для Linux
Настройка в postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'
archive_timeout = 300 # Форсированная отправка каждые 5 мин
Ручное управление WAL
# Принудительный switch WAL
psql -c "SELECT pg_switch_wal();"
# Проверка статуса
psql -c "SELECT * FROM pg_stat_archiver;"
Восстановление на момент времени
- Восстановить base backup
- Создать recovery.conf:
restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2023-11-20 15:30:00'
recovery_target_action = 'promote'
- Запустить сервер — восстановление начнется автоматически
WAL-архивирование (PITR) для Windows
Настройка в postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'copy "%p" "C:\\Backups\\wal\\%f"'
Ручное управление WAL
# Принудительная ротация WAL
psql -U postgres -c "SELECT pg_switch_wal();"
# Проверка статуса архивации
psql -U postgres -c "SELECT * FROM pg_stat_archiver;"
Восстановление на момент времени
- Восстановить base backup
- Создать recovery.conf:
restore_command = 'copy "C:\\Backups\\wal\\%f" "%p"'
recovery_target_time = '2024-07-20 15:30:00 MSK'
- Запустить сервер — восстановление начнется автоматически
Автоматизация бэкапов
Скрипт для cron (ежедневные дампы)#!/bin/bash
DATE=$(date +%Y-%m-%d)
pg_dump -Fc -Z9 -f /backups/daily/db_$DATE.dump db_name
find /backups/daily/ -mtime +30 -delete
Создайте .bat-файл для ежедневного бэкапа:
@echo off
set BACKUP_DIR=C:\Backups\daily
pg_dump -U postgres -F c -Z 6 -f "%BACKUP_DIR%\db_%date:~0,10%.dump" db_name
Настройте задание в Task Scheduler (триггер — ежедневно в 2:00).
Интеграция с S3
Linux:# Загрузка WAL в S3
archive_command = 'aws s3 cp %p s3://bucket/wal/%f --sse AES256'
rclone copy "C:\Backups" "your_cloud:bucket/postgres" --progress
Мониторинг
-- Проверка последнего бэкапа
SELECT now() - pg_last_backup_start_time() as last_backup_age;
Матрица выбора стратегии
| Критерий | Логический дамп | Физический бэкап | WAL-архивирование |
|---|---|---|---|
| Время восстановления | Часы | Минуты | Секунды |
| Размер БД | До 100GB | Любой | Любой |
| Точность | На момент дампа | + WAL = PITR | Точность до мс |
Чеклист аудита бэкапов
-
Проверка целостности через
pg_checksums -
Тест восстановления на изолированном стенде
-
Мониторинг свободного места для WAL
-
Шифрование чувствительных данных (AES-256)
-
Ротация ключей шифрования (ежеквартально)
Комментарии
Добавить комментарий
Содежание
1. Введение
2. Установка PostgreSQL
3. Объектно-реляционные СУБД
4. Архитектура PostgreSQL
5. Базы данных и схемы
6. Системный каталог
7. Табличные пространства
8. Низкоуровневая организация данных
9. Задачи администрирования
10. Роли и атрибуты
11. Привилегии
12. Политики защиты строк
13. Подключение и аутентификация
14. Резервное копирование
15. Изоляция транзакций
16. Страницы и версии строк