Як створити резервну копію PostgreSQL?

У світі VPS/хостингу та серверів, де дані — це фундамент будь-якого бізнесу, резервне копіювання PostgreSQL — це не просто опція, а критично важливий елемент будь-якої стратегії із забезпечення безперервності роботи. На щастя, PostgreSQL надає потужний і гнучкий набір вбудованих інструментів, а також підтримку сторонніх рішень, що дозволяють створювати резервні копії баз даних різних розмірів і з різними вимогами до відновлення. У цій статті ми детально розглянемо, як ефективно створювати резервні копії PostgreSQL, охоплюючи як логічні, так і фізичні методи, а також просунуті стратегії для забезпечення максимальної надійності.

Навіщо взагалі потрібні резервні копії PostgreSQL?

Перш ніж заглиблюватися в технічні деталі, давайте коротко нагадаємо собі, чому резервні копії PostgreSQL не підлягають обговоренню:

• Захист від збоїв обладнання: Відмова диска, пам'яті або іншого компонента сервера.
• Людський фактор: Випадкове видалення даних, некоректні SQL-запити, помилки в конфігурації.
• Програмні помилки: Баги в додатку, що пошкоджують дані.
• Зовнішні загрози: Кібератаки, віруси-вимагачі.
• Відновлення після катастроф: Пожежі, повені, землетруси, що зачіпають дата-центр.
• Міграція і тестування: Резервні копії використовуються для перенесення даних між серверами або для створення тестових середовищ.

Хороша стратегія резервного копіювання і відновлення (B&R) — це ваша страховка і план "Б" на будь-який з цих випадків.

Метод 1: Логічні резервні копії за допомогою pg_dump

pg_dump — це, мабуть, найвідоміший і часто використовуваний інструмент для створення резервних копій PostgreSQL. Він створює логічну копію бази даних, тобто набір SQL-команд, які можна виконати для відтворення структури бази даних і її даних. Це відмінний вибір для більшості невеликих і середніх баз даних, а також для міграції між різними версіями PostgreSQL.

Як працює pg_dump?

pg_dump підключається до бази даних, зчитує її схему (таблиці, індекси, представлення, функції і т.д.) і дані, а потім записує їх у файл у вигляді SQL-операторів CREATE, INSERT та інших. Важливий момент: pg_dump створює узгоджену копію даних, навіть якщо база даних активно використовується, завдяки використанню механізму snapshotting.

Основні опції pg_dump

Базова команда виглядає так:

pg_dump -U <username> -h <hostname> <dbname> > backup.sql

• -U <username>: Ім'я користувача PostgreSQL для підключення.
• -h <hostname>: Хост, на якому працює PostgreSQL. Якщо не вказано, використовується локальний сокет або localhost.
• <dbname>: Ім'я бази даних, яку потрібно скопіювати.
• > backup.sql: Перенаправлення виводу у файл. За замовчуванням вивід йде в stdout.

Приклади використання:

1. Повна резервна копія в SQL-форматі:

pg_dump -U valeuser -h localhost valedb > /var/backups/postgresql/valedb_$(date +%Y%m%d%H%M%S).sql

Ця команда створить SQL-файл з ім'ям, що містить поточну дату і час.

2. Стиснута резервна копія (рекомендується для економії місця):

pg_dump -U valeuser -h localhost valedb | gzip > /var/backups/postgresql/valedb_$(date +%Y%m%d%H%M%S).sql.gz

Використання gzip значно зменшить розмір файлу. Для відновлення такого файлу потрібно буде спочатку розтиснути його або використовувати gunzip -c backup.sql.gz | psql ....

3. Використання "custom" формату (-Fc) для гнучкості:

Custom формат — це бінарний, стиснутий формат, який дозволяє вибірково відновлювати об'єкти (наприклад, тільки схему або окремі таблиці) і підтримує паралельне відновлення.

pg_dump -U valeuser -h localhost -Fc valedb > /var/backups/postgresql/valedb_$(date +%Y%m%d%H%M%S).dump

Для відновлення з цього формату використовується pg_restore:

pg_restore -U valeuser -h localhost -d valedb_new /var/backups/postgresql/valedb_backup.dump

Тут -d valedb_new вказує цільову базу даних, в яку будуть відновлені дані. Якщо база даних не існує, її потрібно створити заздалегідь.

4. Виключення або включення окремих таблиць/схем:

# Виключити таблицю 'logs'
pg_dump -U valeuser -h localhost -Fc -T logs valedb > valedb_without_logs.dump

# Включити тільки таблицю 'users'
pg_dump -U valeuser -h localhost -Fc -t users valedb > valedb_only_users.dump

Відновлення з pg_dump

Відновлення з SQL-файлу виконується за допомогою утиліти psql:

psql -U <username> -h <hostname> -d <dbname> < backup.sql

Важливо: перед відновленням база даних <dbname> повинна бути створена (і бути порожньою, якщо ви відновлюєте повну копію) і належати користувачеві <username>.

# Створюємо нову базу даних
createdb -U valeuser -h localhost valedb_restored

# Відновлюємо дані
psql -U valeuser -h localhost -d valedb_restored < /var/backups/postgresql/valedb_backup.sql

Плюси і мінуси pg_dump

Плюси:

• Простота використання: Легко почати.
• Портативність: SQL-файли можна відновити на будь-якій версії PostgreSQL (в розумних межах) і навіть на інших СУБД (з деякими модифікаціями).
• Людиночитабельність: SQL-файли легко переглядати і редагувати.
• Гнучкість: Можливість вибіркового бекапу/відновлення.

Мінуси:

• Продуктивність: Повільніше для дуже великих баз даних, оскільки вимагає читання і запису всіх даних в логічному форматі.
• Відсутність PITR: Не підтримує відновлення на довільний момент часу (Point-In-Time Recovery). Ви можете відновитися тільки до стану, в якому було зроблено бекап.
• Розмір: SQL-файли можуть бути дуже великими, якщо не використовувати стиснення.

Метод 2: Фізичні резервні копії за допомогою pg_basebackup

pg_basebackup — це утиліта для створення "фізичних" або "файлових" резервних копій PostgreSQL. Вона копіює всі файли каталогу даних PostgreSQL (PGDATA) на момент створення бекапу. Це схоже на копіювання файлів на рівні файлової системи, але з важливою відмінністю: pg_basebackup гарантує цілісність даних, працюючи з активною базою даних.

Як працює pg_basebackup?

pg_basebackup підключається до сервера PostgreSQL через протокол реплікації і копіює всі файли даних. Він також може опціонально включити файли WAL (Write-Ahead Log) в бекап або почати потокову передачу WAL, що критично важливо для Point-In-Time Recovery (PITR).

Основні опції pg_basebackup

Базова команда:

pg_basebackup -U <username> -h <hostname> -D /path/to/backup_directory -F <format> -X <wal_method>

• -U <username>: Користувач PostgreSQL (повинен мати права реплікації).
• -h <hostname>: Хост, на якому працює PostgreSQL.
• -D /path/to/backup_directory: Цільовий каталог для резервної копії.
• -F <format>: Формат виводу.
  • p (plain): Звичайний каталог з файлами даних (за замовчуванням).
  • t (tar): Один або кілька tar-архівів.
• -X <wal_method>: Як обробляти WAL-файли.
  • none: Не включати WAL-файли (не підходить для PITR).
  • fetch: Зібрати WAL-файли після завершення бекапу (може бути повільно).
  • stream: Потокова передача WAL-файлів під час бекапу (рекомендується для PITR).

Приклади використання:

1. Базова фізична копія (plain format):

pg_basebackup -U replica_user -h localhost -D /var/backups/postgresql/basebackup_$(date +%Y%m%d%H%M%S) -P -v

• replica_user: Користувач з правами REPLICATION (наприклад, визначений в pg_hba.conf).
• -P: Показати прогрес.
• -v: Детальний вивід.

Ця команда створить каталог, що містить повну копію PGDATA.

2. Фізична копія в форматі tar з потоковою передачею WAL:

pg_basebackup -U replica_user -h localhost -D /var/backups/postgresql/basebackup_$(date +%Y%m%d%H%M%S) -Ft -Xs -P -v

Цей варіант створить один або кілька tar-архівів і включить в них необхідні WAL-файли, що є основою для PITR.

Налаштування сервера для pg_basebackup

Для роботи pg_basebackup необхідно, щоб користувач, від імені якого він запускається, мав права REPLICATION. Також потрібно налаштувати pg_hba.conf на сервері PostgreSQL, щоб дозволити підключення з клієнта, звідки запускається pg_basebackup:

# pg_hba.conf
host    replication     replica_user    192.168.1.0/24  md5

І перезавантажити сервер (pg_ctl reload).

Відновлення з pg_basebackup

Відновлення з фізичної копії більш прямолінійне, але вимагає, щоб сервер PostgreSQL був зупинений. Якщо ви використовували tar-формат, спочатку потрібно розпакувати архіви.

# 1. Зупиніть сервер PostgreSQL
sudo systemctl stop postgresql

# 2. Очистіть або перемістіть старий каталог даних (PGDATA)
sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main_old

# 3. Скопіюйте резервну копію в PGDATA
# Якщо бекап в plain форматі:
sudo cp -R /var/backups/postgresql/basebackup_YYYYMMDDHHMMSS /var/lib/postgresql/14/main

# Якщо бекап в tar форматі:
sudo tar -xf /var/backups/postgresql/basebackup_YYYYMMDDHHMMSS/base.tar -C /var/lib/postgresql/14/main
# Можливо, знадобиться розпакувати і WAL-файли, якщо вони були в окремому tar-архіві

# 4. Створіть файл recovery.signal (для PostgreSQL 12+) або recovery.conf (для більш старих версій)
# Якщо ви робили бекап з WAL-файлами для PITR:
# Створіть файл recovery.signal в каталозі PGDATA
sudo touch /var/lib/postgresql/14/main/recovery.signal
# або, для більш точного PITR, використовуйте recovery.conf (для старих версій або більш тонкого налаштування)
# restore_command = 'cp /path/to/wal_archive/%f %p'
# recovery_target_time = '2023-10-27 10:00:00 UTC'
# recovery_target_name = 'my_recovery_point'
# recovery_target_xid = '123456789'

# 5. Встановіть правильні права доступу
sudo chown -R postgres:postgres /var/lib/postgresql/14/main
sudo chmod -R 0700 /var/lib/postgresql/14/main

# 6. Запустіть сервер PostgreSQL sudo systemctl start postgresql

Після запуску сервер виявить recovery.signal (або recovery.conf) і почне процес відновлення, застосовуючи WAL-файли, щоб привести базу даних до узгодженого стану.

Плюси та мінуси pg_basebackup

Плюси:

• Продуктивність: Дуже швидкий для великих баз даних, оскільки копіює файли блоками.
• PITR (Point-In-Time Recovery): При правильному налаштуванні WAL-архівування дозволяє відновити базу даних на будь-який момент часу.
• Легке створення реплік: Базові копії ідеально підходять для швидкого створення нових реплік.

Мінуси:

• Менша портативність: Базові копії зазвичай можуть бути відновлені тільки на тій же мажорній версії PostgreSQL і часто на тій же архітектурі ОС.
• Розмір: Копіює весь каталог даних, що може займати багато місця.
• Відновлення: Вимагає зупинки сервера для відновлення.
• Складність: Налаштування WAL-архівування та PITR вимагає глибшого розуміння.

Метод 3: WAL-архівування та Point-In-Time Recovery (PITR)

PITR — це "святий Грааль" резервного копіювання для критично важливих систем. Він дозволяє відновити базу даних на будь-який момент часу, а не тільки до стану останнього повного бекапу. Це досягається шляхом комбінації базової копії (зробленої pg_basebackup) і безперервного архівування WAL-файлів (Write-Ahead Log).

Як це працює?

1. Створюється базова копія (наприклад, за допомогою pg_basebackup). Це ваш "фундамент".
2. PostgreSQL налаштовано на архівування WAL-файлів. Кожна транзакція, кожна зміна даних спочатку записується в WAL-файл. Ці файли архівуються в безпечне місце, як тільки вони заповнюються.
3. При необхідності відновлення, ви берете базову копію і послідовно "програєте" всі WAL-файли, які були створені після цієї базової копії, до потрібного моменту часу.

Налаштування WAL-архівування

У файлі postgresql.conf:

# Включити режим архівування
wal_level = replica # або higher (logical)
archive_mode = on

# Команда для архівування WAL-файлів.
# %p - шлях до WAL-файлу
# %f - ім'я WAL-файлу
# Приклад: копіювання у віддалений S3-бакет або NFS-шару
archive_command = 'cp %p /mnt/wal_archive/%f'
# або для S3:
# archive_command = 'aws s3 cp %p s3://your-s3-bucket/wal/%f'

# Максимальна кількість сегментів WAL, яку може бути неархівовано. # Встановіть достатньо велике значення, щоб уникнути зупинки сервера # при тимчасових проблемах з archive_command. archive_timeout = 600 # в секундах, за замовчуванням 0 (відключено)

Після зміни postgresql.conf потрібне перезавантаження сервера.

Важливо: Каталог /mnt/wal_archive/ (або ваш S3-бакет) повинен бути надійним, високонадійним і мати достатній обсяг для зберігання всіх WAL-файлів.

Відновлення з PITR

Процес відновлення аналогічний відновленню з pg_basebackup, але з додаванням файлу recovery.signal або recovery.conf, що вказує на цільовий момент часу:

# У файлі PGDATA/postgresql.auto.conf (або recovery.conf для старих версій)
restore_command = 'cp /mnt/wal_archive/%f %p' # Команда для отримання WAL-файлів з архіву
recovery_target_time = '2023-10-27 10:30:00 UTC' # Цільовий час відновлення
# recovery_target_xid = '123456789' # Або за ID транзакції
# recovery_target_name = 'my_recovery_point' # Або за іменем точки відновлення
# recovery_target_inclusive = off # Якщо потрібно відновити ДО вказаного моменту, а не ВКЛЮЧАЮЧИ його

Потім запускаємо сервер, і він автоматично застосує базову копію і WAL-файли до вказаного моменту.

Метод 4: Сторонні інструменти та автоматизовані рішення

Для великих, високонавантажених систем або середовищ, що потребують складного управління резервними копіями, вбудованих утиліт може бути недостатньо. Тут на допомогу приходять спеціалізовані сторонні рішення, які автоматизують, оптимізують і спрощують процес.

Популярні інструменти:

• Barman (Backup and Recovery Manager): Потужний інструмент для управління резервними копіями та PITR для PostgreSQL. Він централізує управління бекапами безлічі серверів PostgreSQL, підтримує інкрементальні бекапи, стиснення, віддалене зберігання та багато іншого. Barman — це повноцінна система, розроблена для підприємств.
• pgBackRest: Ще одне високопродуктивне рішення для резервного копіювання та відновлення PostgreSQL. Воно фокусується на швидкості, надійності та паралелізмі, підтримуючи дедуплікацію, стиснення, шифрування, віддалене зберігання (S3, Azure Blob, GCS) і складну логіку відновлення.
• WAL-G: Інструмент для архівування та відновлення WAL-файлів і базових бекапів, орієнтований на хмарні сховища (S3, GCS, Azure Blob, Swift). Розроблений для високої продуктивності та масштабованості.

Переваги використання сторонніх інструментів:

• Автоматизація: Повністю автоматизоване створення, зберігання та ротація бекапів.
• Управління PITR: Спрощене управління WAL-архівами та відновленням на момент часу.
• Оптимізація: Інкрементальні та диференціальні бекапи, стиснення, дедуплікація для економії місця та часу.
• Моніторинг і сповіщення: Вбудовані механізми для відстеження стану бекапів.
• Централізоване управління: Управління бекапами безлічі PostgreSQL-інстансів з єдиної точки.

Вибір такого інструменту залежить від масштабу вашої інфраструктури, вимог до RPO/RTO і готовності інвестувати в їх налаштування та підтримку.

Найкращі практики резервного копіювання PostgreSQL

Незалежно від обраного методу, дотримання цих практик значно підвищить надійність вашої стратегії:

1. Визначте RPO і RTO

• RPO (Recovery Point Objective): Максимально допустимий обсяг втрати даних (скільки даних можна втратити, вимірюється в часі). Визначає частоту створення бекапів.
• RTO (Recovery Time Objective): Максимально допустимий час відновлення системи після збою. Визначає, наскільки швидко ви повинні відновити дані.

Ці метрики допоможуть вам вибрати відповідний метод (наприклад, для низького RPO/RTO потрібен PITR).

2. Автоматизуйте процес

Ручні бекапи — це рецепт катастрофи. Використовуйте cron, скрипти або спеціалізовані інструменти для автоматизації.

# Приклад запису в crontab для щоденного бекапу о 02:00
0 2 * * * /usr/bin/pg_dump -U valeuser -h localhost -Fc valedb > /var/backups/postgresql/valedb_$(date +\%Y\%m\%d).dump 2>&1

3. Зберігайте бекапи віддалено (Off-site)

Резервні копії повинні зберігатися окремо від основної СУБД, бажано в іншому фізичному місці (інший сервер, хмарне сховище на зразок S3, NFS-шара) для захисту від катастроф.

4. Впровадьте політику ротації

Не зберігайте бекапи вічно. Визначте, скільки копій і за який період вам потрібно зберігати (наприклад, 7 щоденних, 4 щотижневих, 12 щомісячних). Видаляйте старі бекапи, щоб не переповнювати сховище.

5. Моніторинг і сповіщення

Переконайтеся, що процес бекапування успішно завершується. Налаштуйте моніторинг логів і сповіщення про збої. Відсутність помилок у логах не означає, що бекап придатний для відновлення!

6. РЕГУЛЯРНО ТЕСТУЙТЕ ВІДНОВЛЕННЯ!

Це найважливіший пункт. Бекап, який неможливо відновити, марний. Регулярно проводьте "навчання з відновлення" на тестовому сервері, щоб переконатися в працездатності ваших копій і відточити процес відновлення. Це також допоможе виявити вузькі місця в RTO.

«Єдиний хороший бекап — це той, який ви успішно відновили.»

7. Захистіть резервні копії

Резервні копії містять ваші найцінніші дані. Забезпечте їх безпеку: шифрування, контроль доступу, обмеження прав до каталогів з бекапами.

8. Враховуйте продуктивність

Бекап може навантажувати сервер. Вибирайте час для бекапу, коли навантаження на базу даних мінімальне, або використовуйте методи, що мінімізують вплив (наприклад, бекап з репліки).

Висновки

Резервне копіювання PostgreSQL — це багатогранне завдання, що вимагає уважного планування та регулярного обслуговування. Ми розглянули основні інструменти: pg_dump для логічних копій (простота, гнучкість, портативність) і pg_basebackup для фізичних копій (швидкість, PITR, основа для реплік). Для найвимогливіших сценаріїв існують потужні сторонні інструменти на зразок Barman і pgBackRest, які автоматизують і оптимізують весь процес.

Ключовим моментом є не просто "зробити бекап", а мати робочу, перевірену стратегію відновлення. Визначте свої RPO і RTO, автоматизуйте, зберігайте бекапи віддалено і, найголовніше, регулярно тестуйте процес відновлення. Тільки тоді ви зможете бути впевнені в безпеці своїх даних і готовності до будь-яких несподіванок. Не чекайте катастрофи, щоб виявити, що ваша "страховка" не працює!