Вступление
В этой статье будет разбираться резервное копирование и восстановление PostgreSQL. Покажу все на небольшом практическом docker-compose стенде. Я специально сделаю ошибочное изменение данных, после чего восстановлю базу до состояния непосредственно перед этой ошибкой.
Восстановление данных буду делать с помощью второго PostgreSQL контейнера. Если кратко - данные WAL и base_backup будут прокидываться с помощью volume и контейнер будет стартовать с новыми данными.
Пока что рабочий стенд будет ограничиваться PostgreSQL 17 в Docker (без S3, Kubernetes, автоматизации и т.п) и множеством ручных действий, потом возможно сделаю дополнительные статьи, охватывающие более сложные случаи ближе к production.
Сразу скажу, что статья не претендует на присутствие какой-то уникальной информации, для продвинутых пользователей она не подойдет, однако статья будет полезна тем, кто только начинает разбираться в базах данных, хочет посмотреть стандартные команды, узнать как они работают и как ими пользоваться.
Основная часть
Подготовка базы
Напишем docker-compose:
services: postgresql: container_name: psql image: postgres:17 restart: always shm_size: 128mb environment: POSTGRES_PASSWORD: pwd volumes: - postgres_data:/var/lib/postgresql/data # docker volume чтобы все настройки не пропали при выключении контейнера - ./base_backup:/var/lib/postgresql/base_backup # передаем бэкап на хост - ./wal_archive:/var/lib/postgresql/wal_archive # передаем wal архив на хост postgresql-recovery: # новый контейнер, который будет использоваться для восстановления container_name: psql-recovery image: postgres:17 restart: always shm_size: 128mb environment: POSTGRES_PASSWORD: pwd volumes: - ./base_backup:/var/lib/postgresql/data # все данные из бэкапа идут в папку data. При старте контейнер увидит инициализированный кластер и не будет создавать новый через initdb. Также важный момент, файлы должны иметь иметь правильного владельца, а не root, иначе будет ошибка инициализации. - ./wal_archive:/var/lib/postgresql/wal_archive # пробрасываем архив чтобы восстановить данные до конца volumes: postgres_data:
Запускаем контейнер и заходим:
docker compose up -d postgresql docker exec -it psql bash su - postgres # заходим как postgres пользователь psql # консольный клиент postgres
Подготавливаем базу:
CREATE DATABASE appdb; \c appdb -- заходим в appdb базу CREATE TABLE users ( -- создаем таблицу id BIGSERIAL PRIMARY KEY, -- у каждого пользователя уникальный id name TEXT NOT NULL, -- имя, обязательное поле email TEXT NOT NULL UNIQUE, -- почта, обязательное поле, уникальное для каждого пользователя balance NUMERIC(10,2) NOT NULL -- баланс, обязательное поле, число 8 знаков на целую часть, 2 знака на дробную ); INSERT INTO users (name, email, balance) -- добавляем значения VALUES ('Roman', 'mail@gmail.com', 5000), ('Lera', 'mail2@gmail.com', 100), ('John', 'mail3@gmail.com', 1000000); SELECT * FROM users; -- выводим что у нас сейчас в таблице id | name | email | balance ----+-------+-----------------+------------ 1 | Roman | mail@gmail.com | 5000.00 2 | Lera | mail2@gmail.com | 100.00 3 | John | mail3@gmail.com | 1000000.00
Настраиваем WAL
Немного терминологии перед продолжением.
WAL (Write-Ahead Log) — это журнал изменений PostgreSQL. Информация об изменениях сначала попадает в WAL, а изменения файлов данных могут быть записаны на диск позже. Благодаря этому после сбоя PostgreSQL может воспроизвести WAL и восстановить состояние базы.
PITR (Point-in-Time Recovery) позволяет восстановить PostgreSQL до указанного момента времени, например до состояния непосредственно перед “катастрофой”.
Пример реализации:
Раз в сутки создается полная копия данных, при этом система непрерывно сохраняет все изменения (логи, WAL-файлы), которые происходят после бэкапа. При сбое администратор выбирает конкретное время, система берет бэкап и с помощью WAL накатывает изменения ровно до указанного времени.
Теперь настраиваем WAL Архивирование. Учитывая сказанное выше, для реализации PITR одного dump недостаточно, нужна исходная физическая копия базы и журнал изменений. Поэтому делаем следующее:
chown postgres:postgres /var/lib/postgresql/wal_archive # меняем владельца на postgres.
Проверяем текущие параметры PostgreSQL:
SHOW wal_level; -- узнаем режим wal (значение replica, подходит) SHOW archive_mode; -- смотрим включено ли архивирование (выключено) SHOW archive_command; -- смотрим установлена ли команда (пусто)
Включаем archive_mode:
ALTER SYSTEM SET archive_mode = 'on'; -- сохраняем настройку в конфигурации PostgreSQL (значение записывается в postgresql.auto.conf)
Устанавливаем команду copy:
%p - внутренняя переменная postgresql. обозначает путь к WAL файлу
%f - обозначает оригинальное имя WAL файла
ALTER SYSTEM SET archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f';
После применения настроек ничего не произойдет, так как нужно перезагрузиться, чтобы изменения вступили в силу.
su - postgres # pg_ctl запускаем от пользователя postgres. /usr/lib/postgresql/17/bin/pg_ctl \ # выполняем перезагрузку. Для pg_ctl используем полный путь, так как $PATH в контейнере настроен только для root. -D /var/lib/postgresql/data \ restart
После перезагрузки все изменения должны быть применены.
Теперь проверяем состояние архивирования. Нужно посмотреть значение failed_count (ну и остальные значения заодно), если оно не равно нулю, значит с архивацией есть проблемы, которые нужно решить, прежде чем идти дальше.
SELECT archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;
У меня все хорошо, поэтому иду дальше. Теперь для нашего стенда принудительно переключаем WAL-сегмент, чтобы текущий сегмент завершился и PostgreSQL мог передать его в archive_command.
SELECT pg_switch_wal();
Что такое логический бэкап
Логический backup создаётся с помощью pg_dump. Он сохраняет логическое представление базы: структуру объектов и их данные. По умолчанию pg_dump создаёт SQL-скрипт, но также поддерживает архивные форматы.
Из плюсов можно выделить универсальность, возможность выборочного восстановления объектов и независимость от конкретной файловой структуры кластера.
К минусам можно отнести медленный процесс экспорта и импорта на больших объемах данных. Для восстановления нужна целевая база данных и утилита для восстановления (к примеру pg_restore).
Что такое физический бэкап и почему используем именно его
Создается с помощью pg_basebackup. Это копия файлов PostgreSQL-кластера. При необходимости в него также можно включить WAL, необходимый для восстановления.
К плюсам можно отнести быстрое создание и восстановление.
Из минусов: зависимость от конкретной файловой системы, ОС и версии СУБД. Занимает много места.
Для использования WAL нужно использовать именно физический бэкап, так как WAL работает на уровне байтов (физических блоков диска), а не SQL-команд. Также в физическом бэкапе есть точное значение LSN, на котором он был сделан.
LSN (Log Sequence Number) — это позиция в потоке WAL. Он используется PostgreSQL для адресации WAL-записей, позволяет определять положение бэкапа и необходимые границы WAL для восстановления.
Создание бэкапа и WAL архива
Так как нам нужен физический бэкап, создаем с помощью pg_basebackup:
pg_basebackup \ -h localhost \ # подключаемся локально -U postgres \ # пользователь postgres -D /var/lib/postgresql/base_backup \ # целевая директория -Fp \ # формат вывода plain, сохраняем "как есть" -Xs \ # одновременно передаём необходимые WAL во время создания бэкапа -P \ # отображения прогресса -v # подробный режим вывода
Делаем изменения в базе, и параллельно фиксируем время:
SELECT now(); INSERT INTO users (name, email, balance) VALUES ('Alice', 'alice@gmail.com', 2500); UPDATE users SET balance = 9999 WHERE name = 'Roman'; SELECT now();
Теперь создадим “исскуственную катастрофу”. За нее я буду считать удаление одного из пользователей.
DELETE FROM users WHERE name = 'Roman'; SELECT now();
В итоге ситуация получилась примерно такая:
Base backup -> 09:40:18 INSERT Alice -> 09:40:30 UPDATE Roman 9999 -> 09:40:43 DELETE Roman
Теперь опять придется исскуственно переключить WAL, чтобы все сегменты точно попали в наш внешний архив (по умолчанию WAL закрывает текущий сегмент журнала при достижении 16 МБ).
SELECT pg_switch_wal();
Восстановление
Чтобы PostgreSQL не восстановил WAL вместе с нашей “катастрофой”, в base_backup/postgresql.conf нужно указать конкретный промежуток, до которого восстанавливаем. Для этого ранее нужны были SELECT now();
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p' recovery_target_time = '2026-08-28 09:40:42'
Чтобы PostgreSQL понял, что он запускается в recovery режиме нужно создать файл recovery.signal:
touch ./base_backup/recovery.signal
И теперь мы наконец-то можем запустить контейнер:
docker compose up -d postgresql-recovery
В логах можно увидеть примерно следующее:
starting point-in-time recovery to 2026-08-28 09:40:43+00
PostgreSQL берёт base backup, получает необходимые WAL через restore_command и начинает последовательно воспроизводить изменения. В результате он доходит до момента перед ошибочной транзакцией и останавливается.
В логах это выглядит примерно так:
recovery stopping before commit of transaction ... time 2026-08-28 09:40:43.951449+00
Если мы зайдем в контейнер, то увидим что все данные на месте:
docker exec -it psql-recovery bash su - postgres psql
SELECT * FROM users; id | name | email | balance ----+-------+-----------------+------------ 1 | Roman | mail@gmail.com | 9999.00 2 | Lera | mail2@gmail.com | 100.00 3 | John | mail3@gmail.com | 1000000.00 4 | Alice | alice@gmail.com | 2500.00
После восстановления PostgreSQL всё ещё находится в recovery:
SELECT pg_is_in_recovery(); t -- true
Когда мы убедились, что данные восстановлены, можем выполнить promotion и проверить состояние еще раз:
SELECT pg_promote(); -- после выполнения recovery.signal удаляется и кластер переходит в режим записи (Read/Write) с новой временной шкалой SELECT pg_is_in_recovery(); f -- false
В итоге данные были восстановлены.
Как это сделать лучше
Где хранить WAL, и что такое RPO, RTO
Если говорить про что-то больше похожее на production (но еще не прям), то WAL на одном сервере хранить точно не стоит. Обычно WAL архивируют в отдельное хранилище на другом сервере.
Отсюда есть 2 полезных термина:
RPO (например 5 минут) - означает, что при аварии система должна быть рассчитана так, чтобы допустимая потеря данных не превышала пять минут.
RTO (например 30 минут) - означает, что после аварии восстановление должно завершиться не позднее чем через 30 минут. RTO имеет смысл только тогда, когда recovery действительно регулярно проверяется.
Немного про репликацию
Если кратко, репликация - это когда есть 2 PostgreSQL.
При физической репликации есть Primary и Replica:
Primary -> WAL streaming -> Replica
На Primary работает walsender, а Replica получает WAL через walreceiver. Передача происходит по сети напрямую, отдельное общее хранилище для этого не требуется.
Такая схема отлично защищает от аппаратных и инфраструктурных проблем: падения сервера, виртуальной машины, диска и других подобных отказов.
Однако если на Primary выполнить нежелательное действие, реплика от этого не спасет. То есть репликация решает прежде всего задачу доступности и быстрого переключения на резервный сервер, но сама по себе не защищает от ошибок.
На production-системах эти механизмы обычно дополняют друг друга. Реплика помогает быстро пережить отказ основного сервера, а backup и WAL позволяют восстановиться после повреждения или логической ошибки.
Заключение
Надеюсь материал был полезен. Спасибо за прочтение!
Если остались какие-то вопросы, или просто хотите, чтобы я вам точно ответил, можете связаться со мной, используя любые удобные вам контакты в моем профиле.