Резервные копии PostgreSQL: pg_dump, pg_basebackup и PITR

← назад к разделу

В пятницу вечером кто-то удалил таблицу с заказами. Или выкатили миграцию, которая затёрла данные. Всё, что стоит между вами и катастрофой, это резервная копия, и первый вопрос в этот момент звучит не «где копия», а «сколько мы потеряли и когда поднимемся». Разберём, как копии устроены в PostgreSQL, начиная с двух чисел, которыми это измеряют.

Два числа, с которых начинается разговор

Любое решение про копии принимают на языке двух величин. RPO, recovery point objective, это сколько данных до аварии позволено потерять: если копия снимается ночью, а авария случилась в 14:30, потеряно всё с 02:00, двенадцать с половиной часов заказов. RTO, recovery time objective, это сколько времени позволено не работать: развернуть четыре терабайта из объектного хранилища на скорости двести мегабайт в секунду это почти шесть часов, и весь этот срок магазин не принимает заказы.

Цену часа между копией и аварией показывает обычный запрос по своим данным, и его стоит выполнить до того, как выбирать схему копий:

живой пример

SELECT date_trunc('hour', created_at) AS lost_hour,
       count(*) AS orders_lost,
       sum(total_amount) AS amount_lost
FROM orders
WHERE created_at > timestamp '2026-05-07 02:00'
GROUP BY date_trunc('hour', created_at)
ORDER BY lost_hour;

Дальше всё в статье это способы уменьшить одно из двух чисел и цена каждого способа.

Логический или физический: что вы возвращаете

Выбор инструмента зависит от того, что придётся возвращать. Если это одна таблица, схема или база для переноса на другую версию PostgreSQL, нужна выгрузка в виде команд, которую можно прочитать и накатить куда угодно: это логическая копия, pg_dump. Если это весь кластер на конкретный момент времени, нужна копия файлов на диске, поверх которой можно доиграть журнал: это физическая копия, pg_basebackup, и только у неё есть восстановление на момент времени.

На проде живут оба: физическая копия с архивом ради RPO и RTO, логическая ради переносов и возврата отдельных таблиц.

pg_dump: выгрузка, которую можно прочитать

Простейший вариант выгружает базу в SQL-файл, который открывается в редакторе:

pg_dump -U user -h host mydb > mydb.sql
psql -U user -h host mydb_new < mydb.sql

Для баз больше игрушечных берут формат custom (-Fc): файл компактнее, а pg_restore -j 4 при восстановлении делит таблицы между четырьмя потоками и заметно ускоряет большие базы. Плоский SQL так не умеет, параллелятся только custom и directory. Из такого файла достают и часть: pg_restore --table=orders восстановит одну таблицу, --schema-only даст структуру без данных, --data-only данные без структуры.

pg_dump -Fc -U user -h host mydb > mydb.dump
pg_restore -j 4 -d mydb_new mydb.dump

pg_dump копирует только одну базу. Роли, tablespaces и глобальные настройки хранятся на уровне кластера, и без них восстановленная база не заработает: у таблиц не будет владельцев, у приложения права. Их добирает pg_dumpall --globals-only, и этот файл кладут рядом с каждым дампом.

pg_dump выглядит безобидно, он ведь только читает, но читает особым образом. Вся выгрузка идёт в одной транзакции с одним снимком данных, иначе дамп получился бы несогласованным, с заказами из разных моментов. Пока эта транзакция открыта, уборщик не имеет права убирать старые версии строк, ровно то же, чем вредит любая долгая транзакция. Кроме того, дамп берёт ACCESS SHARE на каждую таблицу и держит до конца: обычным запросам это не мешает, а ALTER TABLE встанет за ним в очередь, и за ALTER TABLE встанет всё остальное. Миграция, запущенная во время ночного дампа, вешает базу до утра. Поэтому дамп снимают с реплики, а не с мастера, и не одновременно с накатом миграций.

Дамп продакшена, который отдают разработчику для отладки, сначала анонимизируют: необработанные персональные данные не уезжают никуда.

pg_basebackup: копия кластера и её проверка

Физическая копия это точная копия файлов кластера:

pg_basebackup -U replicator -h master -D /backup/2026-05-07 -Ft -z -P

Флаги значат: упаковать в tar, сжать, показывать прогресс. Архивов при -Ft получается не один, и на этом спотыкаются при первом же восстановлении. Рядом с base.tar лежит pg_wal.tar, журнальные сегменты, снятые вместе с копией, и распаковывать их надо в разные места:

tar -xf base.tar    -C /var/lib/postgresql/data
tar -xf pg_wal.tar  -C /var/lib/postgresql/data/pg_wal

Без второй строчки кластер не стартует: в копии есть страницы, изменения которых остались только в журнале, и без него база не знает, как довести себя до согласованного состояния. Версия PostgreSQL при восстановлении должна быть той же мажорной; перенос между версиями это работа pg_dump.

Третий файл, backup_manifest, это список всего скопированного с контрольными суммами, и по нему pg_verifybackup (с PostgreSQL 13) сверяет каждый файл копии:

pg_verifybackup /backup/2026-05-07

До PostgreSQL 18 команда проверяет только распакованную копию, снятую в обычном формате -Fp; на -Ft она скажет, что видит base.tar вместо ожидаемых файлов. Проверка ловит битые и недокачанные файлы, но не отвечает на главный вопрос: заведётся ли кластер. На него отвечает только восстановление, поэтому копию регулярно поднимают всерьёз, и об этом ниже.

PITR: восстановление на момент времени

Самая важная возможность физической копии это доиграть поверх неё журнальные файлы WAL до нужного момента. Если в 14:30 удалили все заказы, а копия снята в 02:00, PITR вернёт базу на 14:29: потеря в минуту вместо полдня.

Для этого сервер должен непрерывно откладывать журнал в архив:

archive_mode = on
archive_command = 'test ! -f /wal-archive/%f && cp %p /wal-archive/%f'

Каждый заполненный сегмент WAL копируется в архивный каталог, смонтированный с другой машины. Проверка test ! -f не даёт затереть уже сохранённый сегмент, а при неудаче команда обязана вернуть ненулевой код: тогда PostgreSQL оставит сегмент у себя и попробует снова. Эта строчка пример из документации, и документация помечает её как пример, а не рекомендацию: cp отчитывается об успехе, как только отдал данные операционной системе, не дожидаясь записи на диск, и при выключении узла в этот момент сегмент в архиве окажется пустым. В проде архивирование отдают инструменту, который дожидается записи и умеет в объектное хранилище: pgBackRest или WAL-G.

Восстановление на конкретный момент это пустой файл recovery.signal в каталоге данных и две строки в postgresql.conf:

restore_command = 'cp /wal-archive/%f %p'
recovery_target_time = '2026-05-07 14:29:00 MSK'

Кластер стартует с базовой копии, restore_command подтягивает сегменты из архива один за другим, и накат останавливается точно в 14:29. Дальше сервер по умолчанию встаёт на паузу: можно проверить запросами, та ли это точка, и закончить восстановление вызовом pg_wal_replay_resume(); если проверять не нужно, ставят recovery_target_action = 'promote'.

Следить за архивом, а не верить ему

Сломанный archive_command ничем не выдаёт себя до дня аварии: база работает, сегменты копятся в pg_wal, диск медленно заполняется, а в архиве дыра, через которую PITR не пройдёт. Состояние архивирования база показывает сама:

живой пример

SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

Растущий failed_count и last_failed_time свежее last_archived_time это сигнал, на который вешают оповещение; второй сигнал это объём каталога pg_wal, который в норме не растёт. Но и зелёный счётчик не доказывает, что архив непрерывен: единственная проверка это накат. Раз в неделю копию разворачивают на отдельной машине, доигрывают журнал до вчерашнего вечера, делают несколько запросов к самым важным таблицам и записывают, сколько это заняло: это одновременно проверка архива, проверка копии и измерение RTO. Копия, из которой ни разу не восстанавливались, не копия, и автоматический ночной прогон стоит дешевле одного дня простоя.

Глубже: Контрольные суммы страниц и инкрементальные копиирасширенное

К проверке файлов и учебному восстановлению добавляются ещё две вещи: контрольные суммы страниц и инкрементальные копии.

Третий уровень защиты это контрольные суммы самих страниц. Если кластер создан с initdb --data-checksums (в PostgreSQL 18 это значение по умолчанию), база сама обнаруживает порчу страницы при чтении, а не отдаёт молча испорченные данные: SHOW data_checksums покажет режим, pg_stat_database.checksum_failures число находок.

С PostgreSQL 17 pg_basebackup умеет снимать не всю базу, а только изменения с прошлого раза, если отдать ему манифест предыдущей копии:

pg_basebackup -D /backup/full -Ft
pg_basebackup -D /backup/mon -Ft --incremental=/backup/full/backup_manifest
pg_combinebackup /backup/full /backup/mon -o /var/lib/postgresql/restored

Для базы в несколько терабайт это меняет картину: ежедневная копия снимается за минуты вместо часов, а места в хранилище нужно кратно меньше. Цена в цепочке: потеряли или испортили полную копию, и все инкрементальные поверх неё бесполезны, поэтому полную снимают регулярно и проверяют отдельно.

Глубже: Снимки тома: когда четыре терабайта некогда копироватьрасширенное

Полная копия четырёх терабайт снимается часами и восстанавливается часами, и это ставит потолок RTO, который не пробить ни -j 4, ни инкрементами. Пробивают его снимки тома: EBS в облаке, LVM или ZFS на своём железе делают снимок диска за секунды, потому что не копируют данные, а фиксируют указатели на блоки. Восстановление из снимка это подключить том и запустить кластер: двадцать минут вместо шести часов.

Ловушка в согласованности. Снимок одного тома для PostgreSQL безопасен: после старта кластер доигрывает свой журнал, как после выключения питания. Но если данные и журнал лежат на разных томах, или табличные пространства раскиданы, снимки этих томов сделаны в разные мгновения, и кластер получит несогласованную картину. Тогда либо снимают все тома одной операцией, у облаков она есть, либо на время снимка переводят базу в режим копирования: pg_backup_start('label') перед снимком и pg_backup_stop() после, и кластер знает, что копия снята в окне, которое надо доиграть журналом.

Снимок тома плюс архив WAL это ответ на задачу «четыре терабайта, терять не больше пяти минут, подниматься не дольше часа»: снимок закрывает время, журнал закрывает потери. Платят местом под журнал и еженедельной проверкой восстановления.

Глубже: Линии времени и механика накатарасширенное

Останавливаться в целевой точке и проверять, а не сразу делать promote, стоит из-за линий времени.

Пауза не формальность, и вторая попытка обходится дороже первой. Как только кластер вышел из восстановления и стал обычным сервером, он начинает новую линию времени и пишет журнал уже в неё. Старая ветка в архиве никуда не делась, но по умолчанию восстановление идёт по последней линии, и повторный накат до 15:10 упрётся в обрыв: в новой линии этой точки нет.

recovery_target_time = '2026-05-07 15:10:00 MSK'
recovery_target_timeline = '1'

Механику наката видно и без базы, на игрушечном журнале из пяти записей:

живой пример

import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class PitrDemo {

    record Wal(String time, String orderId, String status) {}

    static final List<Wal> JOURNAL = List.of(
            new Wal("08:15", "ord-3", "NEW"),
            new Wal("12:40", "ord-3", "PAID"),
            new Wal("14:30", "ord-1", null),
            new Wal("14:30", "ord-2", null),
            new Wal("14:30", "ord-3", null));

    public static void main(String[] args) {
        System.out.println("копия 02:00:    " + replay("02:00"));
        System.out.println("накат до 14:29: " + replay("14:29"));
        System.out.println("накат до конца: " + replay("23:59"));
    }

    static Map<String, String> replay(String target) {
        Map<String, String> db = new TreeMap<>(Map.of("ord-1", "PAID", "ord-2", "PAID"));
        for (Wal entry : JOURNAL) {
            if (entry.time().compareTo(target) > 0) {
                break;
            }
            if (entry.status() == null) {
                db.remove(entry.orderId());
            } else {
                db.put(entry.orderId(), entry.status());
            }
        }
        return db;
    }
}

Вторая строка это накат до 14:29: заказ, созданный и оплаченный днём, на месте. Третья это накат без ограничения: последние записи журнала повторяют удаление, и база снова сломана. Поэтому в PITR указывают время цели, а не «примени всё, что есть».

Глубже: Вернуть одну таблицурасширенное

Самый частый реальный запрос звучит не «верните базу», а «верните таблицу за вчера»: удалили справочник, а заказы за сегодня трогать нельзя. Из физической копии одну таблицу не достать, она восстанавливается только целиком. Поэтому первым делом проверяют самый дешёвый вариант: если удаление сделано в открытой транзакции, данные ещё на месте, транзакцию ищут в pg_stat_activity, и откат в той же сессии или pg_terminate_backend() для её процесса возвращает таблицу без всяких копий.

Если транзакция уже зафиксирована, путей два. Рядом с физическими копиями держат ночной логический дамп в формате custom: тогда pg_restore --table=orders -d prod_side dump восстанавливает таблицу в соседнюю базу, откуда её сверяют и переносят. Если дампа нет, копию с журналом разворачивают на отдельной машине через PITR на момент перед удалением, снимают с неё pg_dump --table=orders и накатывают в прод. Второй путь занимает часы, первый минуты, и это аргумент держать оба вида копий, а не один.

Глубже: Хранение, доступ и ценарасширенное

Типичная схема хранения: ежедневные копии семь дней, еженедельные четыре недели, ежемесячные год, архив журнала последние семь дней. У неё есть встроенное свойство: восстановление на произвольный момент есть только на последнюю неделю, пока жив архив. Копия трёхмесячной давности восстанавливается ровно в одну точку, момент её снятия, и одиннадцать месячных копий это одиннадцать точек, а не одиннадцать месяцев.

Все копии лежат отдельно от сервера с данными, в объектном хранилище или на другой площадке: умер диск, умерло и всё, что на нём было. Копия это те же данные, что и база, поэтому у неё те же требования к доступу: хранилище шифруют, ключ живёт не рядом с копиями, а скачивать их может узкий круг, и каждая выгрузка оставляет след в журнале доступа. Анонимизация здесь ни при чём: анонимизируют дампы для разработчиков, боевые копии хранят как есть и охраняют.

Цена складывается из трёх частей, и её считают до выбора схемы. Место: полная копия на каждую неделю хранения плюс инкременты и журнал, для базы в четыре терабайта это десятки терабайт в объектном хранилище. Трафик: выгрузка из облачного хранилища наружу платная, и учебное восстановление раз в неделю его тоже тратит. Время: разворачивание четырёх терабайт это часы машинного времени, и они же RTO. Снимки тома в облаке дороже за терабайт, но убирают трафик и время, и на большой базе выходят дешевле по сумме.

Глубже: Что делать, если всё сломалосьрасширенное

Порядок при потере данных, с оценкой времени на каждый шаг.

1. Остановить изменения: закрыть запись со стороны приложения или перевести его в режим только чтения. Минуты. Каждая запись после аварии усложняет всё дальнейшее.
2. Проверить открытую транзакцию: если таблица удалена в незафиксированной транзакции, откат возвращает её сразу. Минуты, и это единственный бесплатный исход.
3. Снять копию текущего состояния до любого восстановления: снимок тома или pg_basebackup. Десятки минут; без неё вторая ошибка станет последней.
4. Решить, кто объявляет простой. Восстановление в прод это остановка сервиса на RTO, и решение принимает владелец продукта, а не тот, кто держит терминал; DBA готовит два варианта с временем и объёмом потерь.
5. Восстанавливаться: PITR на момент до аварии, если есть архив; последняя хорошая копия, если нет. Часы, столько, сколько показало последнее учебное восстановление.
6. Разобраться с данными после аварии. PITR затирает всё, что записано после целевой точки: заказы, оформленные между 14:30 и остановкой, из прода исчезнут. Их сохраняют из копии текущего состояния (шаг 3), сверяют и переносят отдельно; если таких данных много, восстанавливают не в прод, а рядом, и переносят в прод только потерянное.

Коротко

• Стратегию задают два числа: RPO, сколько данных позволено потерять, и RTO, сколько позволено не работать. Ночной дамп при аварии в 14:30 теряет 12,5 часов заказов, а подъём четырёх терабайт из объектного хранилища занимает почти шесть часов.
• Логическая копия pg_dump -Fc возвращает одну таблицу (pg_restore --table=orders), переносит между мажорными версиями и восстанавливается параллельно (pg_restore -j 4); физическая pg_basebackup возвращает весь кластер, и только у неё есть восстановление на момент времени. На проде держат обе.
• pg_dump копирует одну базу без ролей и tablespaces: их добирает pg_dumpall --globals-only. Дамп держит один снимок и ACCESS SHARE на каждой таблице до конца, поэтому его снимают с реплики и не одновременно с миграциями.
• pg_basebackup -Ft даёт base.tar и pg_wal.tar, их распаковывают в разные каталоги, без второго кластер не стартует; pg_verifybackup сверяет файлы по backup_manifest, но заведётся ли кластер, показывает только восстановление.
• PITR: на сервере archive_mode = on и archive_command, при восстановлении пустой recovery.signal, restore_command и recovery_target_time. cp в archive_command это пример из документации, в проде архивирование отдают pgBackRest или WAL-G.
• После promote кластер начинает новую линию времени, и повторный накат по умолчанию упрётся в обрыв: вторую попытку делают с recovery_target_timeline, поэтому в целевой точке сначала останавливаются и проверяют.
• За архивом следят по pg_stat_archiver (растущий failed_count) и по объёму pg_wal, но доказывает архив только еженедельный накат на отдельной машине: копия, из которой не восстанавливались, не копия.
• Снимок тома (EBS, LVM, ZFS) плюс архив WAL закрывает и потери, и время: подъём за двадцать минут вместо шести часов; несколько томов снимают одной операцией или между pg_backup_start() и pg_backup_stop().
• Одну таблицу возвращают из ночного логического дампа или с боковой PITR-копии, но первым делом проверяют открытую транзакцию в pg_stat_activity; копии лежат отдельно от сервера и шифруются, а цена это место, трафик и время подъёма.

Что почитать дальше

• WAL в PostgreSQL — как устроен журнал, на котором держится PITR.
• Репликация в PostgreSQL — реплика не копия, но pg_basebackup создаёт и её.
• Анонимизация данных — как готовить дамп продакшена к передаче разработчикам.
• Расширения PostgreSQL — где живут инструменты вроде pgBackRest и как их ставят.