Восстановление данных PostgreSQL после потери pg_control

    Для обеспечения отказоустойчивости СУБД PostgreSQL, как и многие базы данных, использует специальный журнал, в котором ведет историю изменения данных. Перед тем как записать данные в файлы БД, сервер PostgreSQL аккумулирует изменения в оперативной памяти и записывает в последовательный файл журнала, чтобы не потерять их из-за непредвиденного отключения питания.


    Данные в журнал пишутся до того как пользователь базы данных получит сообщение об успешном применении изменений. Этот журнал называется журналом упреждающей записи (Write-Ahead Log или просто WAL), а файлы журнала хранятся в каталоге pg_xlog. Также периодически PostgreSQL сбрасывает измененные аккумулированные данные из оперативной памяти на диск. Этот процесс согласования данных называется контрольной точкой (checkpoint). Контрольная точка выполняется также при каждом штатном выключении PostgreSQL.


    Информация о том, с какими внутренними значениями завершилась контрольная точка, хранится в файле global/pg_control и потому этот файл должен быть доступен СУБД еще до момента восстановления данных. Если PostgreSQL отключается нештатно, то изменения из файлов журнала (pg_xlog) применяются к файлам БД, начиная с позиции последней контрольной точки. Этот процесс называется восстановлением данных.


    В файле pg_control находится информация:


    • версия формата control-файла,
    • контрольная сумма записанных в этот файл данных,
    • версия формата файлов БД,
    • уникальный идентификатор экземпляра БД,
    • текущее состояние: работает/остановлен,
    • позиция в журнале, соответствующая запущенной и предыдущей контрольным точкам,
    • текущая ветвь времени (timeline),
    • максимальный видимый номер транзакции (xid),
    • максимальный номер внутреннего счетчика объектов (oid),
    • время создания,
    • и многое другое.

    Посмотреть содержимое pg_control можно при помощи утилиты pg_controldata:


    $ pg_controldata /var/lib/pgsql/9.5/data
    
    pg_control version number:            942
    Catalog version number:               201510051
    Database system identifier:           6242923005164171508
    Database cluster state:               in production
    pg_control last modified:             Fri Apr 29 01:00:00 2016
    Latest checkpoint location:           EEAF/BAA5520
    Prior checkpoint location:            EEAF/BAA5440
    ...
    Latest checkpoint's NextXID:          7/876524573
    Latest checkpoint's NextOID:          264355612
    Latest checkpoint's NextMultiXactId:  134512401
    Latest checkpoint's NextMultiOffset:  547842659
    ...

    Если содержимое pg_control было потеряно, то PostgreSQL не сможет запустить процедуру восстановления. Если же случилось так, что файлы БД неожиданно пропали, что может произойти при аварийном отключении с параметром fsync=off, то правильным способом восстановления является переключение на резервную копию. Эта статья может быть полезна в тех случаях, когда нужно в минимальные сроки восстановить работоспособность базы, но невозможно переключиться на резервную копию и можно пожертвовать частью данных.


    Файл pg_control не защищен от сбоев, и восстановить его можно только при помощи утилиты pg_resetxlog или hex-редактором. Используя pg_resetxlog, вы можете потерять часть данных. Вы отказываетесь от всех текущих журналов транзакций и считаете, что PostgreSQL завершил свою работу штатно: все данные записаны в файлы, как будто только что завершилась контрольная точка. Вам также придется выбрать максимальный видимый номер счетчика транзакций. Если вы выберете номер транзакции слишком большим, то в файлах данных не окажется информации, которую СУБД еще не сбросила на диск из оперативной памяти, как это сделал бы процесс создания контрольной точки. Если же вы выберете номер транзакции слишком маленьким, то данные, записанные позднее, окажутся невидимыми.


    Логично выбрать момент контрольной точки, но откуда можно достать это значение? На помощь приходит штатная утилита pg_xlogdump, которой можно посмотреть содержимое WAL-файлов. Вам необходимо выбрать самый свежий файл, в котором находится запись о контрольной точке, с типом записи XLOG:


    $ pg_xlogdump -r XLOG pg_xlog/$FILE
    ...
    rmgr: XLOG        len (rec/tot):     80/   106, tx:          0, lsn: EEAF/0BAA5B40, prev EEAF/0BAA5B08, desc: CHECKPOINT_ONLINE redo EEAF/BAA5B08; tli 2; prev tli 2; fpw true; xid 7/876524573; oid 264355612; multi 134512401; offset 547842659; oldest xid 686019718 in DB 16400; oldest multi 128391103 in DB 16400; oldest/newest commit timestamp xid: 0/0; oldest running xid 876524573; online
    

    В данном случае можно выбрать такие параметры для pg_resetxlog:


    $ pg_resetxlog -x 876524573 -o 264355612 -m 134512401,128391103 -n /var/lib/pgsql/9.5/data

    Чтобы указанная команда применила значения, вам необходимо запустить ее без ключа -n и с дополнительным ключом -f. Команда очистит содержимое каталога pg_xlog и запишет новые значения в файл pg_control. После этого вы сможете запустить PostgreSQL без восстановления данных.


    Если для восстановления вы выбрали контрольную точку, то, чтобы не попасть в ситуацию, когда вытесненные данные из буферного кэша оказались записанными в файлы БД, рекомендуется до старта экземпляра выставить значение параметра autovacuum=off и снять логическую резервную копию при помощи утилиты pg_dump. Если при снятии резервной копии возникают ошибки, то воспользуйтесь параметром zero_damaged_pages=on. После снятия логического резервной копии её необходимо восстановить на новом экземпляре PostgreSQL.


    Всем успешной эксплуатации PostgreSQL и резервных копий под рукой!

    • +25
    • 12,3k
    • 8
    Postgres Professional 604,06
    Российский вендор PostgreSQL
    Поделиться публикацией
    Комментарии 8
    • 0
      Прям в официальной документации намек, что pg_control особо не защищен и если что при его смерти с последнего лога ищем последний живой checkpoint, с остальным — не повезло, возможно. Как «скопировать» схему а-ля MS SQL. У меня два сервера в сети и каждую минуту логи основного копируются на второй и там так же накладываются? WAL ведь собственно трансзакционный лог и есть?
      • 0
        Да, WAL собственно и есть транзакционный лог. Копировать логи раз в минуту совершенно не обязательно: в PostgreSQL замечательно работает streaming-репликация как асинхронная так и синхронная. Последняя не вносит дополнительных издержек к commit, кроме времени сетевого latency.
      • 0
        Тема очень интересная, но статья явно не полная. Добавили бы побольше примеров. Возможно, ссылки на документацию или какие-то похожие статьи.
        • +1

          Документация как мне кажется не права, так как рекомендует задирать значение xid в максимальное значение при использовании pg_resetxlog. Я руководствуюсь тем, что лучше иметь консистентную картину сразу после checkpoint, чем последние полу-записанные (только вытесненные из shared buffers) данные и удаленный xlog.


          У статьи будет продолжение — мы напишем маленькую утилиту, с помощью которой можно будет создать/переписать pg_control, чтобы можно было воспользоваться стандартным механизмом восстановления после сбоя.

          • +1
            Спасибо за ответ
            будем ждать с нетерпением продолжения и утилиту )))
            • 0
              С этого места подробнее. Самое разумное — найти точно живой чекпоинт и катить с него. Я так понял pg_resetxlog дает несколько вариантов, в том числе и «авосей». То есть может больше данных поднять, но с неизвестным качеством?
              • 0

                Можно поднять больше: под shared_buffers выделен конечный размер RAM и измененные данные эпизодически вытесняются на диск до процесса создания контрольной точки, поэтому select-запрос может вызвать запись (обратите внимание на written):


                postgres=# explain (analyze,buffers) select max(bid) from pgbench_accounts;
                                                                           QUERY PLAN                                                            
                ---------------------------------------------------------------------------------------------------------------------------------
                 Aggregate  (cost=28894.00..28894.01 rows=1 width=4) (actual time=183.727..183.727 rows=1 loops=1)
                   Buffers: shared hit=2176 read=14218
                   ->  Seq Scan on pgbench_accounts  (cost=0.00..26394.00 rows=1000000 width=4) (actual time=0.045..97.754 rows=1000000 loops=1)
                         Buffers: shared hit=2176 read=14218
                 Planning time: 0.068 ms
                 Execution time: 183.752 ms
                (6 строк)
                
                postgres=# update pgbench_accounts set bid = bid +1;
                UPDATE 1000000
                postgres=# explain (analyze,buffers) select max(bid) from pgbench_accounts;
                                                                              QUERY PLAN                                                              
                --------------------------------------------------------------------------------------------------------------------------------------
                 Aggregate  (cost=57786.24..57786.25 rows=1 width=4) (actual time=8688.176..8688.176 rows=1 loops=1)
                   Buffers: shared hit=191 read=32596 dirtied=32787 written=1883
                   ->  Seq Scan on pgbench_accounts  (cost=0.00..52786.39 rows=1999939 width=4) (actual time=3807.514..8598.292 rows=1000000 loops=1)
                         Buffers: shared hit=191 read=32596 dirtied=32787 written=1883
                 Planning time: 0.052 ms
                 Execution time: 8688.195 ms
                (6 строк)
                
                postgres=# 
                • 0

                  Что будет записано в таком случае после checkpoint — большой вопрос. Как мне кажется это мусорные данные, которыми нельзя пользоваться.

          Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

          Самое читаемое