Резервне копіювання та відновлення баз даних PostgreSQL: Повний посібник для користувачів AlexHost

Чому стратегія резервного копіювання PostgreSQL важливіша, ніж ви думаєте

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

AlexHost Dedicated Servers забезпечують ідеальну основу для розміщення та захисту баз даних PostgreSQL. Завдяки сховищу NVMe SSD корпоративного рівня з винятковою пропускною здатністю введення-виведення, повному доступу root для повного контролю конфігурації та вбудованому захисту від DDoS, AlexHost надає вам продуктивність інфраструктури та стан безпеки, які вимагають серйозні робочі навантаження баз даних.

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

Зміст

1. Розуміння параметрів резервного копіювання PostgreSQL
2. Передумови та вимоги до привілеїв
3. Метод 1 — SQL дамп з pg_dump
4. Метод 2 — Резервне копіювання всіх баз даних з pg_dumpall
5. Метод 3 — Резервні копії користувацького формату для великих баз даних
6. Відновлення з SQL дампів
7. Відновлення з дампів користувацького формату
8. Метод 4 — Безперервне архівування та відновлення до певного моменту часу (PITR)
9. Автоматизація резервних копій за допомогою Cron
10. Захист та зберігання резервних копій поза межами сайту
11. Резюме найкращих практик

1. Розуміння параметрів резервного копіювання PostgreSQL

PostgreSQL поставляється з кількома зрілими, добре задокументованими механізмами резервного копіювання. Вибір правильного залежить від розміру вашої бази даних, цілей часу відновлення (RTO), цілей точки відновлення (RPO) та толерантності до операційної складності.

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

2. Передумови та вимоги до привілеїв

Перед виконанням будь-якої операції резервного копіювання переконайтеся, що виконані наступні передумови:

Привілеї користувача:

• Ви повинні бути суперкористувачем PostgreSQL або власником цільової бази даних, щоб виконати повне резервне копіювання.
• Для pg_dumpall необхідні привілеї суперкористувача.

Перевірте вашу версію PostgreSQL:

psql --version

Перевірте доступне місце на диску перед резервним копіюванням:

df -h /var/lib/postgresql/

Переконайтеся, що ваше місце призначення резервної копії має достатньо вільного місця — мінімум 1,5× розміру бази даних, яка резервно копіюється, щоб врахувати тимчасові файли та накладні витрати на стиснення.

Підключіться до вашого сервера через SSH:

ssh root@your-server-ip

Якщо ви використовуєте план VPS Hosting, у вас буде повний доступ SSH та можливість встановлювати, налаштовувати та керувати PostgreSQL без обмежень.

3. Метод 1 — SQL дамп з pg_dump

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

Крок 1: Відкрийте термінал і отримайте доступ до вашого сервера

ssh root@your-alexhost-server-ip

Крок 2: Запустіть команду pg_dump

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

Розбір параметрів:

Практичний приклад:

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> Порада професіонала: Додавання мітки часу за допомогою $(date +%Y%m%d_%H%M%S) до назви файлу резервної копії гарантує, що ви ніколи не перезапишете попередню резервну копію випадково і створюєте природний хронологічний архів.

Крок 3: Перевірте файл резервної копії

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

Файл повинен починатися з коментарів заголовка PostgreSQL та SET операторів, підтверджуючи, що був створений дійсний дамп.

4. Метод 2 — Резервне копіювання всіх баз даних з pg_dumpall

Коли вам потрібно зробити резервну копію кожної бази даних у екземплярі PostgreSQL — включаючи глобальні об’єкти, такі як ролі та табличні простори — pg_dumpall є правильним інструментом.

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

Ця команда експортує:

• Усі бази даних
• Усі ролі (користувачі та групи)
• Усі табличні простори
• Усю глобальну конфігурацію

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

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. Метод 3 — Резервні копії користувацького формату для великих баз даних

Для виробничих баз даних, які перевищують кілька гігабайт, користувацький формат (-F c) настійно рекомендується замість простих SQL дампів. Резервні копії користувацького формату:

• За замовчуванням стиснуті — значно зменшуючи вимоги до сховища
• Швидше відновлюються — підтримуючи паралельні операції відновлення з прапором -j
• Вибірково відновлюються — дозволяючи вам відновити окремі таблиці або схеми

Створення резервної копії користувацького формату

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

Створення стиснутої резервної копії формату каталогу (підтримує паралелізм)

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

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

6. Відновлення з SQL дампів

Відновіть одну базу даних з простого SQL дампу

Спочатку переконайтеся, що цільова база даних існує. Якщо вона не існує, створіть її:

psql -U postgres -c "CREATE DATABASE my_restored_db;"

Потім відновіть:

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

Розбір параметрів:

Моніторинг ходу відновлення

Для великих SQL файлів ви можете моніторити прогрес за допомогою pv:

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. Відновлення з дампів користувацького формату

Дампи користувацького формату вимагають утиліти pg_restore замість psql.

Базове відновлення

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

Відновіть і створіть базу даних автоматично

Використовуйте прапор -C для інструктування pg_restore створити базу даних перед її заповненням:

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

Паралельне відновлення для швидшого відновлення

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

Використання -j 4 з резервною копією формату каталогу може зменшити час відновлення на до 75% на чотирьохядерному сервері — значна перевага при мінімізації часу простою під час відновлення після катастрофи.

Відновіть лише конкретну таблицю

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

Ця детальна можливість є однією з ключових переваг користувацького формату над простими SQL дампами.

8. Метод 4 — Безперервне архівування та відновлення до певного моменту часу (PITR)

PITR — це золотий стандарт для критичних для місії розгортань PostgreSQL. Це дозволяє вам відновити вашу базу даних до будь-якого конкретного моменту часу — не лише останньої резервної копії — шляхом повторення сегментів журналу попередніх записів (WAL) на основі базової резервної копії. Це необхідно для сценаріїв, коли вам потрібно відновитися від логічної помилки (наприклад, випадкового DROP TABLE), яка сталася в відомий час.

Крок 1: Увімкніть архівування WAL у postgresql.conf

Знайдіть і відредагуйте файл конфігурації PostgreSQL:

nano /etc/postgresql/15/main/postgresql.conf

Додайте або змініть наступні директиви:

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Пояснення параметрів:

Створіть каталог архіву та встановіть правильні дозволи:

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

Перезапустіть PostgreSQL, щоб застосувати зміни:

systemctl restart postgresql

Крок 2: Зробіть базову резервну копію з pg_basebackup

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

Крок 3: Відновіть до певного моменту часу

Щоб відновити з базової резервної копії та архівів WAL:

1. Зупиніть PostgreSQL:

systemctl stop postgresql

1. Очистіть існуючий каталог даних:

rm -rf /var/lib/postgresql/15/main/*

1. Витягніть базову резервну копію:

tar -xzf /backups/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

1. Створіть файл recovery.conf (PostgreSQL 11 та раніше) або нал