Хочу поділитись з вами моїм першим успішним досвідом відновлення повної працездатності бази даних Postgres. З СУБД Postgres я познайомився півроку тому, до цього досвіду адміністрування баз даних я не мав зовсім.
Я работаю полу-DevOps инженером в крупной IT-компании. Наша компания занимается разработкой программного обеспечения для высоконагруженных сервисов, я же отвечаю за работоспособность, сопровождение и деплой. Передо мной поставили стандартную задачу: обновить приложение на одном сервере. Приложение написано на Django, во время обновления выполняются миграции (изменение структуры базы данных), и перед этим процессом мы снимаем полный дамп базы данных через стандартную программу pg_dump на всякий случай.
Під час зняття дампи виникла непередбачена помилка (версія Postgres – 9.5):
pg_dump: Oumping the contents of table “ws_log_smevlog” failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
pg_dump: The command was: COPY public.ws_log_smevlog [...]
pg_dunp: [parallel archtver] a worker process dled unexpectedly
Помилка «invalid page in block» говорить про проблеми на рівні файлової системи, що дуже погано. На різних форумах пропонували зробити FULL VACUUM з опцією zero_damaged_pages для вирішення цієї проблеми. Що ж, попрробеум...
Підготовка до відновлення
УВАГА! Обов'язково зробіть резервну копію Postgres перед спробою відновити базу даних. Якщо у вас віртуальна машина, зупиніть базу даних та зробіть снепшот. Якщо немає можливості зробити снепшот, зупиніть базу та скопіюйте вміст каталогу Postgres (включаючи wal-файли) у надійне місце. Головне у нашій справі – не зробити гірше. Прочитайте .
Поскольку в целом база у меня работала, я ограничился обычным дампом базы данных, но исключил таблицу с повреждёнными данными (опция -T, -exclude-table = TABLE pg_dump).
Сервер був фізичним, зняти снепшот було неможливо. Бекап знято, рухаємося далі.
Перевірка файлової системи
Перед спробою відновлення бази даних необхідно переконатися, що у нас все гаразд із файловою системою. І у разі помилок виправити їх, оскільки в іншому випадку можна зробити лише гірше.
В моём случае файловая система с базой данных была примонтирована в "/srv" та тип був ext4.
Останавливаем базу данных: зупинка systemctl [захищено електронною поштою] та перевіряємо, що файлова система ніким не використовується і її можна відмонтувати за допомогою команди також:
lsof +D /srv
Мені довелося ще зупинити базу даних redis, оскільки вона також використовувала "/srv". Далі я відмонтував / SRV (umount).
Перевірка файлової системи була виконана за допомогою утиліти e2fsck с ключиком -f (Force checking even if filesystem is marked clean):
Далі за допомогою утиліти dumpe2fs (sudo dumpe2fs /dev/mapper/gu2-sys-srv | grep checked) можно убедиться, что проверка действительно была произведена:
e2fsck каже, що проблем на рівні файлової системи ext4 не знайдено, а це означає, що можна продовжувати спроби відновити базу даних, а точніше повернутися до vacuum full (Зрозуміло, необхідно примонтувати файлову систему назад і запустити базу даних).
Якщо у вас фізичний сервер, то обов'язково перевірте стан дисків (через smartctl -a /dev/XXX) або RAID-контролера, щоб переконатися, що проблема не на апаратному рівні. У моєму випадку RAID виявився «залізним», тому я попросив місцевого адміна перевірити стан RAID (сервер був за кілька сотень кілометрів від мене). Він сказав, що помилок немає, а це означає, що ми можемо точно почати відновлення.
Спроба 1: zero_damaged_pages
Підключаємося до бази через psql акаунтом, що має права суперкористувача. Нам потрібний саме суперкористувач, т.к. опцію zero_damaged_pages може міняти лише він. У моєму випадку це postgres:
psql -h 127.0.0.1 -U postgres -s [database_name]
Опція zero_damaged_pages потрібна для того, щоб проігнорувати помилки читання (з сайту postgrespro):
При выявлении повреждённого заголовка страницы Postgres Pro обычно сообщает об ошибке и прерывает текущую транзакцию. Если параметр zero_damaged_pages включён, вместо этого система выдаёт предупреждение, обнуляет повреждённую страницу в памяти и продолжает обработку. Это поведение разрушает данные, а именно все строки в повреждённой странице.
Включаємо опцію та пробуємо робити full vacuum таблиці:
VACUUM FULL VERBOSE
На жаль, невдача.
Мы столкнулись с аналогичной ошибкой:
INFO: vacuuming "“public.ws_log_smevlog”
WARNING: invalid page in block 4123007 of relation base/16400/21396989; zeroing out page
ERROR: unexpected chunk number 573 (expected 565) for toast value 21648541 in pg_toast_106070– механизм хранения «длинных данных» в Poetgres, если они не помещаются в одну страницу (по умолчанию 8кб).
Попытка 2: reindex
Перша порада з гугла не допомогла. Після кількох хвилин пошуку я знайшов другу пораду – зробити переіндексувати повреждённой таблицы. Этот совет я встречал во многих местах, но он не внушал доверия. Сделаем reindex:
reindex table ws_log_smevlog
переіндексувати завершився без проблем.
Однако это не помогло, VACUUM FULL аварийно завершался с аналогичной ошибкой. Поскольку я привык к неудачам, я стал искать советов в интернете дальше и наткнулся на довольно интересную .
Спроба 3: SELECT, LIMIT, OFFSET
У статті вище пропонували подивитися таблицю рядково та видалити проблемні дані. Для початку необхідно було переглянути всі рядки:
for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; doneУ моєму випадку таблиця містила 1 628 991 рядків! По-хорошому потрібно було подбати про але це тема для окремого обговорення. Була субота, я запустив цю команду в tmux і пішов спати:
for ((i=0; i<1628991; i++ )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog LIMIT 1 offset $i" >/dev/null || echo $i; doneК утру я решил проверить, как обстоят дела. К моему удивлению, я обнаружил, что за 20 часов было просканировано только 2% данных! Ждать 50 дней я не хотел. Очередной полный провал.
Но я не стал сдаваться. Мне стало интересно, почему же сканирование шло так долго. Из документации (опять на postgrespro) я узнал:
OFFSET вказує пропустити вказану кількість рядків, перш ніж почати видавати рядки.
Якщо вказано і OFFSET, і LIMIT, система спочатку пропускає OFFSET рядків, а потім починає підраховувати рядки для обмеження LIMIT.Застосовуючи LIMIT, важливо також використовувати пропозицію ORDER BY, щоб рядки результату видавалися в певному порядку. Інакше повертатимуться непередбачувані підмножини рядків.
Очевидно, что вышенаписанная команда была ошибочной: во-первых, не было order by, результат мог получиться ошибочным. Во-вторых, Postgres сначала должен был просканировать и пропустить OFFSET-строк, и с возрастанием OFFSET продуктивність знижувалася б ще сильніше.
Попытка 4: снять дамп в текстовом виде
Далі мені на думку спала, здавалося б, геніальна ідея: зняти дамп у текстовому вигляді і проаналізувати останній записаний рядок.
Но для начала, ознакомимся со структурой таблицы ws_log_smevlog:
В нашем случае у нас есть столбец "Ідентифікатор"що містить унікальний ідентифікатор (лічильник) рядка. План був такий:
- Починаємо знімати дамп у текстовому вигляді (у вигляді sql-команд)
- У певний момент часу зняття дампа перервалося б через помилку, але тектовий файл все одно зберігся б на диску
- Смотрим конец текстового файла, тем самым мы находим идентификатор (id) последней строки, которая снялась успешно
Я почав знімати дамп у текстовому вигляді:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpЗняття дампа, як і очікувалося, перервалося з тією ж помилкою:
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989
Далі через хвіст я переглянув кінець дампа (tail -5 ./my_dump.dump) обнаружил, что дамп прервался на строке с id 186 525. «Значит, проблема в строке с id 186 526, она битая, её и надо удалить!» – подумал я. Но, сделав запрос в базу данных:
«select * from ws_log_smevlog where id=186529» виявилося, що з цим рядком все нормально ... Рядки з індексами 186 - 530 теж працювали без проблем. Чергова "геніальна ідея" провалилася. Пізніше я зрозумів, чому так сталося: при видаленні зміни даних з таблиці вони не видаляються фізично, а позначаються як «мертві кортежі», далі приходить автовакуум і позначає ці рядки віддаленими та дозволяє використовувати ці рядки повторно. Щоб зрозуміти, якщо дані в таблиці змінюються і включені autovacuum, то вони не зберігаються послідовно.
Попытка 5: SELECT, FROM, WHERE id=
Невдачі роблять нас сильнішими. Не варто ніколи здаватися, треба йти до кінця і вірити в себе та свої можливості. Тому я вирішив спробувати ще один варіант: просто переглянути всі записи в базі даних по одному. Знаючи структуру моєї таблиці (див. вище), ми маємо поле id, яке є унікальним (первинним ключем). У таблиці у нас 1 628 991 рядків та id йдуть по порядку, а це означає, що ми можемо просто перебрати їх по одному:
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneЯкщо хтось не розуміє, команда працює наступним чином: переглядає рядково таблицю і відправляє stdout в / dev / nullале якщо команда SELECT провалюється, то виводиться текст помилки (stderr відправляється в консоль) і виводиться рядок, що містить помилку (завдяки ||, яка означає, що у select виникли проблеми (код повернення команди не 0)).
Мені пощастило, у мене були створені індекси по полю id:
А это значит, что нахождение строки с нужным id не должен занимать много времени. В теории должно сработать. Что же, запускаем команду в tmux і йдемо спати.
На ранок я виявив, що переглянуто близько 90 000 записів, що становить трохи більше 5%. Відмінний результат, якщо порівнювати із попереднім способом (2%)! Але чекати 20 днів не хотілося.
Спроба 6: SELECT, FROM, WHERE id >= and id
У замовника під БД було виділено відмінний сервер: двопроцесорний Intel Xeon E5-2697 v2, в нашем расположении было целых 48 потоков! Нагрузка на сервере была средняя, мы без особых проблем могли забрать около 20-ти потоков. Оперативной памяти тоже было достаточно: аж 384 гигабайт!
Поэтому команду нужно было распараллелить:
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneТут можна було написати красивий та елегантний скрипт, але я вибрав найшвидший спосіб розпаралелювання: розбити діапазон 0-1628991 вручну на інтервали по 100 000 записів та запустити окремо 16 команд виду:
for ((i=N; i<M; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; doneАле це не все. За ідеєю, підключення до бази даних також забирає якийсь час і системні ресурси. Підключати 1 було не дуже розумно, погодьтеся. Тому давайте при одному підключенні витягувати 628 рядків замість одного. У результаті команда перетворилася на це:
for ((i=N; i<M; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; doneВідкриваємо 16 вікон у сесії tmux та запускаємо команди:
1) for ((i=0; i<100000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 2) for ((i=100000; i<200000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done … 15) for ((i=1400000; i<1500000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 16) for ((i=1500000; i<1628991; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done
За день я отримав перші результати! А саме (значення XXX та ZZZ вже не збереглися):
ERROR: missing chunk number 0 for toast value 37837571 in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value XXX in pg_toast_106070
829000
ERROR: missing chunk number 0 for toast value ZZZ in pg_toast_106070
146000Це означає, що у нас три рядки містять помилку. id першого і другого проблемного запису перебували між 829 000 і 830 000, id третього – між 146 000 і 147 000. Далі ми мали просто знайти точне значення id проблемних записів. Для цього переглядаємо наш діапазон із проблемними записами з кроком 1 та ідентифікуємо id:
for ((i=829000; i<830000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number 2 (expected 0) for toast value 37837843 in pg_toast_106070 829449 for ((i=146000; i<147000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number ZZZ (expected 0) for toast value XXX in pg_toast_106070 146911
щасливий фінал
Мы нашли проблемные строки. Заходим в базу через psql и пробуем их удалить:
my_database=# delete from ws_log_smevlog where id=829417;
DELETE 1
my_database=# delete from ws_log_smevlog where id=829449;
DELETE 1
my_database=# delete from ws_log_smevlog where id=146911;
DELETE 1К моему удивлению, записи удалились без каких-либо проблем даже без опции zero_damaged_pages.
Потім я підключився до бази, зробив VACUUM FULL (думаю робити було необов'язково), і нарешті успішно зняв бекап за допомогою pg_dump. Дамп знявся без жодних помилок! Проблему вдалося вирішити у такий тупий спосіб. Радості не було меж, після стількох невдач вдалося знайти рішення!
Благодарности и заключение
Вот такой получился мой первый опыт восстановления реальной базы данных Postgres. Этот опыт я запомню надолго.
Ну і насамкінець, хотів би сказати спасибі компанії PostgresPro за перекладену документацію російською мовою та за , которые очень сильно помогли во время анализа проблемы.
Джерело: habr.com