Резервне копіювання та відновлення

Копія в SQL-форматі

Файл shop.sql міститиме SQL-команди. Відновити його можна через psql:

SQL-формат зручний для перегляду та редагування, але зазвичай повільніший для великих баз.

Копія у custom-форматі

Для практичних резервних копій часто використовують custom-формат:

Такий файл відновлюють командою pg_restore:

Опція --no-owner корисна, якщо ролі з початкового сервера відсутні на сервері відновлення. Без неї pg_restore намагатиметься призначити об’єктам початкових власників.

Custom-формат підтримує вибіркове відновлення та паралельне відновлення:

Параметр --jobs може скоротити час відновлення великих баз, але збільшує навантаження на сервер.

Що входить до логічної копії

pg_dump копіює об’єкти та дані вибраної бази:

• таблиці;

• дані;

• індекси;

• представлення;

• функції;

• тригери;

• схеми;

• права доступу до об’єктів.

Окремо слід зберігати глобальні об’єкти кластера:

• ролі;

• паролі ролей;

• tablespace;

• атрибути ролей.

Для цього можна створити копію глобальних об’єктів:

Під час відновлення спочатку створюють ролі та інші глобальні об’єкти:

Потім відновлюють конкретні бази за допомогою pg_restore або psql.

Переваги та обмеження логічних копій

Переваги:

• можна відновити окрему таблицю або схему;

• копію можна перенести між різними серверами;

• можна змінити структуру під час відновлення;

• формат зручний для міграції між сумісними версіями PostgreSQL;

• копія не залежить від розташування файлів у каталозі даних.

Обмеження:

• відновлення великої бази може бути довгим;

• логічна копія не дає автоматичного відновлення до довільного моменту часу;

• ролі та інші глобальні об’єкти потрібно копіювати окремо;

• копія охоплює одну базу, а не весь кластер.

pg_dump створює узгоджену копію без необхідності зупиняти базу. Зміни, які відбуваються під час виконання команди, не призводять до частково зміненого знімка.

Фізичні резервні копії

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

Основний інструмент для створення фізичної базової копії — pg_basebackup.

У прикладі:

• --pgdata визначає каталог резервної копії;

• --format=plain зберігає звичайну структуру каталогу;

• --wal-method=stream передає WAL під час копіювання;

• --progress показує прогрес операції.

Користувач для pg_basebackup має мати право реплікації або відповідну роль, а сервер повинен дозволяти replication-з’єднання в pg_hba.conf.

Приклад рядка для pg_hba.conf:

Після зміни pg_hba.conf конфігурацію потрібно перечитати:

Резервний користувач може бути створений так:

У реальному середовищі пароль не слід передавати безпосередньо в командному рядку. Для автентифікації можна використовувати файл .pgpass із правильними правами доступу.

Переваги та обмеження фізичних копій

Переваги:

• швидке відновлення всього кластера;

• зберігається точний фізичний стан даних;

• підходить для великих баз;

• може бути основою для відновлення до моменту часу.

Обмеження:

• копія стосується всього кластера;

• її не можна відновити за допомогою pg_restore;

• для фізичного відновлення потрібна сумісна версія PostgreSQL;

• не можна безпечно копіювати каталог даних звичайною командою cp, поки сервер працює;

• для відновлення окремої таблиці фізична копія незручна.

Фізичну копію зазвичай відновлюють як каталог даних іншого сервера, а не як окрему базу.

WAL і відновлення до моменту часу

PostgreSQL записує зміни у файли WAL (Write-Ahead Log) ще до того, як вони потрапляють до основних файлів даних.

Якщо зберігати WAL у зовнішньому сховищі, можна:

1. відновити останню базову фізичну копію;

2. послідовно застосувати WAL;

3. зупинити відновлення на потрібному моменті часу.

Цей підхід називається PITR (Point-in-Time Recovery).

Без архівування WAL фізична копія відновлюється лише до стану, зафіксованого під час її створення. Якщо після цього стався збій, усі наступні зміни можуть бути втрачені.

Увімкнення архівування WAL

Архівування налаштовують у конфігурації PostgreSQL:

Каталог для WAL має існувати та бути доступним користувачу, від імені якого працює PostgreSQL:

archive_mode потребує перезапуску сервера. Після зміни цього параметра:

archive_command виконується для кожного завершеного WAL-файлу. У команді використовуються спеціальні підстановки:

• %p — повний шлях до WAL-файлу в каталозі PostgreSQL;

• %f — ім’я WAL-файлу без шляху.

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

Якщо failed_count постійно зростає, WAL не архівуються. Це потрібно виправити якомога швидше, оскільки каталог pg_wal може заповнити диск.

Каталог для архіву WAL не повинен бути єдиною копією на тому самому диску, що й каталог даних. Втрата диска в такому разі знищить і базу, і резервні копії.

Створення базової копії для PITR

Базову копію створюють після налаштування WAL-архівування:

Параметр --wal-method=stream передає потрібні WAL через окреме з’єднання під час створення копії. Це допомагає отримати самодостатню базову копію, але не замінює постійне архівування WAL.

Після створення резервної копії варто перевірити:

• чи команда завершилася без помилки;

• чи існує очікуваний каталог;

• чи продовжується архівування WAL;

• чи доступна копія з іншого сервера або сховища;

• чи достатньо місця для наступних WAL-файлів.

Відновлення фізичної копії

Загальний порядок відновлення такий:

1. зупинити PostgreSQL на сервері відновлення;

2. зберегти або перейменувати поточний каталог даних;

3. розгорнути в каталог даних фізичну копію;

4. надати каталог власнику postgres;

5. налаштувати отримання WAL;

6. запустити PostgreSQL;

7. перевірити стан і цілісність даних.

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

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

Відновлення до певного моменту часу

На сервері відновлення потрібно вказати команду отримання WAL. Для PostgreSQL сучасних версій це можна зробити в конфігурації:

Після цього в каталозі даних створюють порожній файл recovery.signal:

Запускаємо сервер:

PostgreSQL відновить базову копію та застосує WAL до моменту 2026-09-01 14:30:00+00. Потім сервер завершить відновлення та перейде до звичайного режиму роботи завдяки recovery_target_action = 'promote'.

Час потрібно задавати однозначно, бажано із часовим поясом UTC. Інакше можна випадково відновити базу до неправильного моменту.

Для відновлення не обов’язково використовувати саме час. Можна вказати, наприклад:

• recovery_target_lsn;

• recovery_target_name;

• recovery_target_xid.

На практиці час часто найзручніший, якщо відомо, коли сталася помилка.

Після відновлення слід перевірити журнали PostgreSQL. У них має бути видно, що сервер застосовував WAL і завершив recovery. Також потрібно перевірити:

• наявність потрібних таблиць;

• кількість критичних записів;

• роботу застосунку;

• права доступу;

• останні операції перед цільовим моментом.

Логічна чи фізична копія

Ці типи копій вирішують різні задачі.

Логічна копія підходить, коли потрібно:

• відновити одну базу або таблицю;

• перенести дані на інший сервер;

• змінити структуру під час міграції;

• мати файл, який можна переглянути або обробити.

Фізична копія підходить, коли потрібно:

• швидко відновити весь кластер;

• мінімізувати час простою;

• відновитися після повної втрати сервера;

• використовувати PITR разом з архівом WAL.

Надійна стратегія часто використовує обидва типи:

• фізичні копії та WAL — для аварійного відновлення;

• логічні копії — для вибіркового відновлення та додаткової незалежності.

Приклад стратегії резервного копіювання

Для виробничої бази даних можна побудувати таку стратегію:

1. Безперервно архівувати WAL у зовнішнє сховище.

2. Щодня створювати фізичну базову копію.

3. Щотижня створювати логічну копію важливих баз.

4. Зберігати кілька поколінь копій.

5. Копіювати резервні файли на інший сервер або в інше сховище.

6. Обмежувати доступ до резервних копій.

7. Регулярно виконувати тестове відновлення.

Наприклад, вимоги можуть бути такими:

• RPO — не більше 15 хвилин;

• RTO — не більше 1 години;

• фізичні копії — щодня;

• WAL — безперервно;

• логічна копія — щотижня;

• тестове відновлення — щомісяця.

Сам факт успішного створення файлу не доводить, що резервна копія придатна. Єдиною надійною перевіркою є реальне відновлення на окремому сервері або в ізольованому середовищі.

Автоматизація та зберігання

Команди резервного копіювання зазвичай запускають планувальником, наприклад cron, але скрипт повинен:

• створювати копію з унікальною датою;

• записувати журнал виконання;

• перевіряти код завершення команди;

• повідомляти про помилки;

• видаляти лише копії, які вже вийшли за межі політики зберігання;

• не перезаписувати успішну копію невдалим результатом.

Приклад простого скрипту для логічної копії:

Такий скрипт перевіряє, що pg_dump завершився успішно, а файл не порожній. Для виробничого використання додатково потрібні ротація, відправлення копії у зовнішнє сховище та сповіщення.

Резервні копії мають захищатися так само, як і сама база:

• обмежуйте права файлової системи;

• шифруйте дані під час передавання та зберігання;

• не зберігайте паролі у відкритих скриптах;

• контролюйте доступ до WAL і логічних копій;

• не використовуйте резервне сховище як звичайний робочий каталог.

Типовий сценарій після помилкового видалення даних

Припустімо, о 14:45 користувач помилково видалив важливі рядки. Є базова копія, створена о 00:00, і WAL, архівовані після цього часу.

Порядок дій:

1. Зупинити або ізолювати пошкоджену базу.

2. Визначити момент безпосередньо перед помилковою операцією, наприклад 14:44:59 UTC.

3. Розгорнути базову фізичну копію на окремому сервері.

4. Налаштувати restore_command.

5. Вказати recovery_target_time.

6. Запустити відновлення.

7. Перевірити потрібні дані.

8. Перенести відновлені дані до робочої бази логічними засобами або переключити застосунок на відновлений сервер.

Відновлення краще виконувати спочатку на окремому сервері. Це дозволяє перевірити результат і не знищити єдину доступну копію поточного стану.

Поширені помилки

Копія зберігається на тому самому диску

Якщо диск з даними зламається, резервна копія також стане недоступною.

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

Перевіряється лише створення файлу

Команда може завершитися помилкою, файл може бути пошкоджений або відновлення може вимагати відсутньої ролі.

Регулярно відновлюйте копії в тестове середовище.

Вважається, що pg_dump копіює весь кластер

pg_dump копіює одну базу. Ролі та інші глобальні об’єкти потрібно зберігати окремо за допомогою pg_dumpall --globals-only.

Для фізичної копії використовують pg_restore

pg_restore працює з логічними копіями у custom-, directory- або tar-форматі. Фізична копія відновлюється розгортанням каталогу даних і, за потреби, застосуванням WAL.

Каталог даних копіюють через cp під час роботи сервера

Звичайне копіювання файлів активного каталогу даних не створює гарантовано узгодженої резервної копії. Для фізичного онлайн-бекапу використовуйте pg_basebackup або інший інструмент, який підтримує PostgreSQL.

Не зберігаються WAL після базової копії

Для PITR потрібні всі WAL, необхідні від моменту базової копії до цільового моменту. Видалення WAL раніше часу знищує можливість відновлення до деяких моментів.

Не враховуються ролі та права доступу

Дані можуть відновитися, але застосунок не запрацює через відсутніх власників, ролі або дозволи. Глобальні об’єкти та права потрібно перевіряти окремо.

Не визначено момент відновлення

Якщо відновлення виконують «до останнього доступного стану», можна випадково застосувати WAL із уже пошкодженою операцією. Перед PITR потрібно чітко визначити цільовий час або інший маркер.

Підсумок

• Логічні копії створюють pg_dump і відновлюють через psql або pg_restore.

• Фізичні копії створюють pg_basebackup і використовують для відновлення всього кластера.

• pg_dump не копіює глобальні об’єкти, тому ролі потрібно зберігати окремо.

• Архівування WAL разом із базовою фізичною копією дає змогу виконувати PITR.

• Виробнича стратегія має враховувати RPO, RTO, кілька місць зберігання та політику ротації.

• Резервна копія вважається надійною лише після успішного тестового відновлення.