Резервно копиране и възстановяване на PostgreSQL бази данни: Пълно ръководство за потребители на AlexHost

Защо PostgreSQL стратегията за резервни копия е по-важна, отколкото мислите

Загубата на данни не е хипотетичен риск — това е оперативна сигурност, с която всеки администратор на база данни ще се сблъска в някой момент. Отказите на хардуера, случайните изтривания, повредените транзакции и атаките с ransomware могат да приведат производствена среда в колапс в рамките на секунди. За потребителите на PostgreSQL наличието на стабилна, тестирана и автоматизирана стратегия за резервни копия е разликата между малък инцидент и катастрофален отказ на бизнеса.

AlexHost Dedicated Servers предоставят идеална основа за хостване и защита на PostgreSQL бази данни. С NVMe SSD хранилище на корпоративно ниво, което доставя изключителна I/O пропускливост, пълен root достъп за пълен контрол на конфигурацията и вградена DDoS защита, AlexHost ви дава инфраструктурната производителност и позицията на сигурност, която сериозните работни натоварвания на база данни изискват.

Независимо дали управлявате платформа за електронна търговия с висок трафик, SaaS приложение, WordPress инсталация подкрепена от релационна база данни или персонализирана корпоративна система, това ръководство ви преведе през всеки основен метод за резервно копиране и възстановяване на PostgreSQL — от прости SQL дампове до напреднало Point-in-Time Recovery (PITR) — всичко оптимизирано за производствени среди.

Съдържание

1. Разбиране на опциите за резервно копиране на PostgreSQL
2. Предварителни условия и изисквания за привилегии
3. Метод 1 — SQL дамп с pg_dump
4. Метод 2 — Резервно копиране на всички бази данни с pg_dumpall
5. Метод 3 — Резервни копия в персонализиран формат за големи бази данни
6. Възстановяване от SQL дампове
7. Възстановяване от персонализирани дампове в формат
8. Метод 4 — Непрекъснато архивиране и Point-in-Time Recovery (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 хостване, ще имате пълен 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 — Непрекъснато архивиране и Point-in-Time Recovery (PITR)

PITR е золотният стандарт за критични PostgreSQL разпределения. Позволява ви да възстановите вашата база данни до всеки конкретен момент във времето — не само последното резервно копиране — чрез преиграване на Write-Ahead Log (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 и по-ранни) или конфигурирайте postgresql.conf и създайте файл recovery.signal (PostgreSQL 12+):