Вступление

В этой статье будет разбираться резервное копирование и восстановление 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 позволяют восстановиться после повреждения или логической ошибки.

Заключение

Надеюсь материал был полезен. Спасибо за прочтение!

Если остались какие-то вопросы, или просто хотите, чтобы я вам точно ответил, можете связаться со мной, используя любые удобные вам контакты в моем профиле.

PostgreSQL Recovery на пальцах: WAL, base_backup и PITR в Docker-compose