PostgreSQL PITR: как вернуть базу к моменту перед ошибочным DELETE

Ошибочный DELETE уже выполнился, транзакция зафиксирована, а приложение продолжает работать: кто-то оформляет заказы, кто-то платит, кто-то правит профиль. Первый порыв понятен: остановить всё и откатить базу назад. Но откат рабочей базы целиком вернёт не только удалённое, он заодно сотрёт всё правильное, что случилось после ошибки. Каждая минута промедления делает этот размен всё менее выгодным.

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

Сначала хронология, потом команды

Прежде чем что-то восстанавливать, нужно ответить на вопрос, к какому моменту возвращаться. Здесь чаще всего ошибаются: берут время, когда пользователь пожаловался, а не время, когда удаление зафиксировано. Между ними могут лежать часы.

Соберите хронологию из всего, что есть под рукой:

• журнал приложения: какой запрос или какая административная операция выполнялась, от чьего имени;
• журнал PostgreSQL, если в нём включено логирование операторов или медленных запросов: он покажет текст и время команды, но только если логирование было включено заранее;
• собственные поля в таблицах: updated_at, created_at у соседних записей, журнал аудита, если он ведётся;
• история в терминале или в клиентском инструменте того, кто выполнял команду;
• косвенные признаки: резкий скачок ошибок «не найдено», падение числа заказов в метриках, всплеск обращений в поддержку.

Цель — получить интервал: «удаление зафиксировано не раньше A и не позже B». Затем выбирайте точку чуть раньше A. Лишние секунды до ошибки почти ничего не стоят: в восстановленной копии вы потом всё равно будете искать нужные строки по ключам. Точка после B, наоборот, обесценит всю работу, потому что строки там уже отсутствуют.

Отдельно стоит помнить про часовые пояса. Время в журнале приложения может быть в локальном поясе, на сервере базы в UTC, в клиенте администратора ещё в каком-то третьем. Сверяйте абсолютное время с явным смещением, а не «двенадцать ноль пять». Ошибка в три часа здесь абсолютно реальна и неприятна.

Чем можно задать точку

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

На практике почти всегда используют время. Остальные способы удобны, когда вы готовились заранее. Именованная точка перед массовой операцией стоит одной строки и экономит часы разбирательств, так что для плановых чисток и миграций данных её имеет смысл делать привычкой.

Без базовой копии и цепочки WAL машина времени не заведётся

Теперь о главном ограничении, которое лучше услышать до инцидента, а не во время него. Согласно документации PostgreSQL о непрерывном архивировании и PITR, восстановление на момент времени строится из двух частей: базовой файловой копии кластера и архива WAL, то есть журнала упреждающей записи, который позволяет воспроизвести изменения до выбранной точки. Одна без другой бесполезна для произвольной точки. Та же документация прямо указывает, что без нужного диапазона WAL одной базовой копии для PITR недостаточно.

Представьте это так. Базовая копия — это фотография базы в некоторый момент. WAL — это подробная запись всех изменений после неё. Чтобы получить состояние «в 14:37», нужна фотография, сделанная раньше, и непрерывная запись всего, что было между фотографией и 14:37. Если в записи пробел, пусть даже один недостающий сегмент, воспроизведение остановится на нём и дальше не пойдёт.

Из этого следуют вполне практические условия:

• Базовая копия должна быть старше целевой точки. Копия, сделанная после удаления, уже содержит последствия ошибки, и вернуться из неё к более раннему состоянию нельзя.
• Цепочка WAL должна быть непрерывной от момента начала базовой копии до выбранной точки. Не «примерно», а без дыр.
• WAL должен лежать в архиве. Сегменты в рабочем каталоге базы по мере работы переиспользуются или удаляются, и надеяться на то, что нужное найдётся там, не стоит.
• Версия сервера должна подходить. Базовую копию нужно разворачивать тем же основным выпуском PostgreSQL, из которого она сделана, и с совместимой архитектурой.

Если на момент ошибки архивирование WAL не было настроено, честный ответ такой: PITR на произвольную минуту у вас не получится. Тогда остаются другие пути: последний логический дамп, последняя базовая копия как есть, выгрузки из смежных систем, данные, которые дублируются в аналитике или очередях. Они хуже, и этот текст не про них. Но понимать границу нужно сразу, чтобы не тратить ночь на попытки завести то, что завестись не может.

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

Отдельная база рядом с живой

Восстановленный экземпляр должен жить отдельно от рабочего. Это не вопрос стиля, а вопрос предотвращения второй ошибки поверх первой. Если развернуть копию в тот же каталог данных или на тот же порт, можно перезаписать то, что сейчас обслуживает пользователей, и тогда вместо одной потери у вас будет две.

Есть два разумных варианта. Первый — отдельный каталог и другой порт на том же сервере. Он годится для небольшой базы, когда свободного места и памяти хватает, а нагрузка от воспроизведения WAL не помешает живой работе. Второй — отдельный временный сервер. Для него хорошо подходит VPS: он нужен на часы или дни, на нём вы вправе делать что угодно, и производственную базу его эксперименты не затрагивают. На VPS MAATRIX есть root-доступ и NVMe-диски, что удобно для такой задачи: нужно поставить ту же версию PostgreSQL, положить копию и архив, а воспроизведение журнала упирается в дисковые операции, так что быстрый накопитель здесь заметно помогает. Какой тариф брать — вопрос размера вашей базы, а не абстрактной мощности. Ресурсы в плане тарифа не обещают определённую ёмкость, поэтому ориентируйтесь на объём каталога данных, архива и запас под рабочие файлы, а не на название плана.

Считать место нужно честно. Условный пример: допустим, каталог данных занимает 60 ГБ, нужный диапазон архива ещё 25 ГБ. Тогда на временном сервере понадобится место под развёрнутую базовую копию, под сам архив и под то, что база будет дописывать при восстановлении, плюс ваши выгрузки. Диска с запасом хватит, диска «впритык» — нет, и обнаружить это на середине воспроизведения особенно досадно.

Что настроить в восстановленной копии

Схема обычная: разворачиваете базовую копию в пустой каталог данных, указываете, откуда брать сегменты WAL, задаёте цель восстановления и даёте серверу сигнал, что он стартует в режиме восстановления. Названия параметров зависят от версии: в современных выпусках восстановление включается файлом-сигналом в каталоге данных, а параметры лежат в основной конфигурации, в старых использовался отдельный файл. Сверьтесь с документацией вашей версии, а ниже посмотрите схему целиком, подставляя свои значения.

restore_command = 'cp /путь/к/архиву/wal/%f "%p"'
recovery_target_time = '2026-10-30 14:37:00+03'
recovery_target_action = 'pause'
port = 5433

Команда копирования из архива здесь лишь иллюстрация: на практике команда зависит от того, как вы архивируете, и может использовать специализированный инструмент. Важна идея. Параметр recovery_target_action со значением паузы полезен именно в нашем сценарии: достигнув цели, сервер остановится и позволит заглянуть внутрь, не открываясь для записи. Если выяснится, что точка выбрана неудачно, не придётся начинать с нуля из-за того, что сервер уже перешёл в рабочий режим. Как именно двигаться дальше после паузы — поднять точку позже, повторить с другой целью, повысить сервер до обычного режима — тоже уточняйте в документации вашей версии.

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

Копия не должна ничего делать наружу

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

Находим потерянное и всё, что с ним связано

Когда копия поднята и стоит на паузе, начинается самая кропотливая часть. Первым делом убедитесь, что вы попали в нужный момент: проверьте, что удалённые строки действительно на месте, а последние правильные записи до ошибки тоже видны. Если нужных строк нет, точка выбрана слишком поздно, и её нужно сдвинуть раньше. Если нет свежих записей, которые должны быть, возможно, вы слишком далеко отступили или архив не дошёл до нужного места.

Затем нужно понять масштаб. Условный пример для наглядности: в интернет-магазине выполнили команду очистки старых черновиков заказов, но забыли условие по дате и удалили все заказы со статусом «новый». Таблица заказов — только вершина. У заказа есть позиции, оплаты, история статусов, адреса доставки. Вопрос не в том, сколько строк пропало в главной таблице, а в том, какие таблицы пострадали вместе с ней.

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

Работать удобнее по ключам, а не по количеству. Для каждой пострадавшей таблицы нужно получить набор первичных ключей на момент до ошибки и набор ключей сейчас. Разность этих множеств и есть кандидаты на возврат. Слово «кандидаты» здесь не случайное: не всякая строка из этой разности должна вернуться. Часть из них могла быть удалена правильно уже после ошибки: клиент сам закрыл аккаунт, оператор отменил заказ, сработала плановая очистка. Возвращать такие записи значит воскрешать то, что кто-то осознанно убрал.

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

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

Возвращаем аккуратно: после ошибки жизнь продолжилась

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

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

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

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

Четвёртое — триггеры и побочные эффекты самой вставки. Если на таблице заказов висит триггер, который при вставке пишет в журнал, пересчитывает остатки или ставит задачу на отправку письма, массовый возврат заставит его сработать для каждой строки. Прежде чем что-то вставлять, прочтите определения триггеров. Иногда их разумно временно отключить для сессии переноса, но делать это можно, только если вы понимаете, что именно они делают, и готовы выполнить эту работу вручную.

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

Если ошибка затронула только одну таблицу, а остальная база в порядке, вам пригодится отдельный разбор приёмов точечного возврата: в статье о восстановлении одной таблицы PostgreSQL с сохранением новых заказов та же задача рассматривается с акцентом на сохранении свежих записей. Помните, что PITR сам по себе не умеет вернуть одну таблицу: он восстанавливает кластер целиком, а выбирать нужное из него приходится вам, как мы и делали выше.

Окно, блокировки и порядок действий

Нужна ли остановка приложения на время переноса? Часто нет. Если вы добавляете только отсутствующие строки, обычные пользователи не заметят этого процесса. Но бывают исключения: если приложение при отсутствии записи создаёт её заново, например, оформляет черновик или подставляет значения по умолчанию, то во время переноса возможны гонки. Тогда безопаснее ненадолго закрыть соответствующий раздел, перевести его в режим только для чтения или включить техническую страницу. Решение зависит от вашего приложения, универсального рецепта нет.

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

Проверяем смысл, а не количество строк

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

Полезно проверять по уровням.

Смежные системы стоит выделить отдельно, потому что именно они чаще всего напоминают о себе позже. Поиск, построенный на отдельном индексе, мог получить команду на удаление вместе с исчезновением строки и теперь не знает о возвращённой записи. Кэш мог закрепить ответ «не найдено». Очередь могла разослать письма о закрытии заказов, которые теперь снова существуют. Файлы в хранилище могли быть удалены вслед за записью, и это уже не восстановить из базы данных вообще. Прежде чем объявлять работу законченной, пройдите по всем системам, которые реагируют на изменения в базе, и выясните, что у каждой из них происходит при удалении и при создании.

Отдельно проверьте то, что появилось после ошибки и должно остаться. Выберите несколько заказов, оформленных уже после инцидента, и убедитесь, что они на месте и не изменились. Если у вас есть метрики или отчёты, сверьте итоговые суммы за период после ошибки до переноса и после: они не должны сдвинуться, кроме тех мест, где возврат законно добавил старые данные.

Наконец, подумайте о людях. Клиенты, у которых «пропали» заказы, могли написать в поддержку, повторить заказ или оплатить заново. После возврата у них могут оказаться дубликаты. Это не техническая, а организационная часть истории, и о ней нужно позаботиться: передать поддержке список затронутых записей и согласовать, что делать с повторными заказами и платежами.

Когда исчезнувшее вернулось, а вчерашнее осталось

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

И ещё один вывод, который нельзя оставить на потом. Всё описанное сработало потому, что где-то заранее лежали базовая копия и непрерывный архив WAL. Если их нет, этот путь недоступен, и никакая находчивость внутри инцидента его не создаст. Настройте архивирование, проверьте, что сегменты действительно попадают в архив и хранятся достаточно долго, и хотя бы раз проведите репетицию восстановления на отдельном сервере, пока ничто не горит. Тогда в следующий раз ошибочный DELETE превратится из катастрофы в неприятный, но вполне решаемый вечер.

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