company_banner

Ускоряем восстановление бэкапов в PostgreSQL

Original author: Adam McKerlie
  • Translation

Мои ощущения от процесса работы


Недавно я решил заняться ускорением восстановления нашей базы данных в dev-окружении. Как и во многих других проектах, база вначале была небольшой, но со временем значительно выросла. Когда мы начинали, ее размер было всего несколько мегабайт. Теперь упакованная база занимает почти 2 ГБ (несжатая — 30 ГБ ). Мы восстанавливаем dev-окружение в среднем раз в неделю. Старый способ проведения операции перестал нас устраивать, а вовремя подвернувшаяся в Slack-канале картинка “DB restore foos?” побудила меня к действию.


Ниже описано, как я ускорял операцию восстановления базы данных.


Простой способ


Ниже описывается наша первая версия процедуры резервного копирования и восстановления. Мы начали с запуска pg_dump и направления его вывода в gzip. Для восстановления базы в dev-окружении мы копировали архив с помощью scp, распаковывали его, а затем загружали командой psql.


$ pg_dump db | gzip > dump.gz
real 7m9.882s
user 5m7.383s
sys 2m56.495s

$ gunzip dump.gz
real 2m27.700s
user 1m28.146s
sys 0m41.451s

$ psql db < dump 
real 30m4.237s
user 0m21.545s
sys 0m44.331s

Общее время при простом способе: 39 минут 41 секунда (32,5 минуты на восстановление в dev-окружении).


Такой подход был прост в понимании, элементарен в настройке и отлично работал, пока размер БД не превышал несколько сотен мегабайт. Однако 32,5 минуты на восстановление базы в dev-окружении — это совершенно неприемлемо.


Восстановление и распаковка одной командой


Первое, что пришло в голову, — просто направить запакованный файл напрямую в psql с помощью zcat, которую можно считать аналогом cat для сжатых файлов. Эта команда распаковывает файл и выводит его в stdout, который, в свою очередь, можно направить в psql.


$ pg_dump db | gzip > dump.gz
real 7m9.882s
user 5m7.383s
sys 2m56.495s

$ zcat dump.gz | psql db
real 26m22.356s
user 1m28.850s
sys 1m47.443s

Общее время: 33 минуты 31 секунда (26,3 минут на восстановление в dev-окружении, что на 20% быстрее).


Отлично, нам удалось ускорить процесс на 16%, выиграв 20% при восстановлении. Поскольку ввод/вывод был основным ограничивающим фактором, отказавшись от распаковки файла на диск, мы сэкономили более 6 минут. Но мне показалось, что этого недостаточно. Терять на восстановлении базы 26 минут — все равно плохо. Я должен был придумать что-то еще.


Настраиваемый формат


Углубившись в документацию по pg_dump, я обнаружил, что pg_dump создает простой текстовый SQL-файл. Затем мы сжимаем его gzip-ом, чтобы сделать меньше. У Postgres есть настраиваемый (custom) формат, который по умолчанию использует zlib для сжатия. Я подумал, что можно будет добиться выигрыша в скорости создания бэкапа, сразу упаковывая данные в Postgres вместо направления простого текстового файл в gzip.


Поскольку psql не понимает настраиваемый формат, мне пришлось перейти на pg_restore.


$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db dumpfc.gz
real 26m26.511s
user 0m56.824s
sys 0m15.037s

Общее время 32 минуты 54 секунды (26,4 минуты на восстановление в dev-окружении).


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


Распараллеливание


Когда я начинаю разбираться с какой-либо проблемой, первым делом читаю документацию и исходный код. У Postgres отличная документация, где в том числе ясно и подробно расписаны опции командной строки. Одна из опций команды pg_restore определяет количество параллельных потоков, которые запускаются во время выполнения наиболее затратных по времени задач, загрузки данных, создания индексов или ограничений.


Документация по pg_restore говорит, что лучше начинать с количества потоков, равного количеству ядер. У моей виртуальной машины 4 ядра, но я хотел поэкспериментировать с разными значениями этой опции.


$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db -j 2 dumpfc
real 25m39.796s
user 1m30.366s
sys 1m7.032s

Общее время 32 минуты 7 секунд (25,6 минут на восстановление в dev-окружении, что на 3% быстрее, чем однопоточный запуск pg_restore).


Хорошо, немного выиграли. Можем ли мы еще ускориться?


$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db -j 4 dumpfc.gz
real 22m6.124s
user 0m58.852s
sys 0m34.682s

Общее время 28 минут 34 секунды (22,1 минуты на восстановление в dev-окружении, что на 14% быстрее, чем с двумя потоками).


Отлично! Четыре потока быстрее двух на 14%. Да данный момент в dev-окружении мы ускорились с 32,5 до 22,1 минуты: время улучшено на 32%!


Я решил выяснить, к чему приведет дальнейшее увеличение количества ядер.


$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db -j 8 dumpfc.gz
real 16m49.539s
user 1m1.344s
sys 0m39.522s

Общее время 23 минуты 17 секунд (16,8 на восстановление в dev-окружении, что на 24% быстрее четырех потоков).


Итак, увеличив количество потоков до удвоенного количества ядер, нам удалось уменьшить время с 22,1 до 16,8 минут. Сейчас мы ускорились на 49%, что просто чудесно.


А еще можно что-нибудь выжать?


$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db -j 12 dumpfc.gz
real 16m7.071s
user 0m55.323s
sys 0m36.502s

Общее время 22 минуты 35 секунд (16,1 минуты на восстановление в dev-окружении, что на 4%, чем 8 потоков).


Указав 12 потоков, мы еще немного ускорились, но CPU виртуальной машины во время восстановления был загружен настолько, что никакие другие действия в системе выполнить было невозможно. В этом вопросе я решил остановиться на 8 потоках (количество ядер * 2).


Заключение


В итоге нам удалось сократить время почти вдвое: с 30 до 16 минут. Это экономит нам 72 часа времени восстановления в год (6 разработчиков на 52 запуска процедуры восстановления на 14 минут). Я очень доволен этими результатами. В будущем планирую заняться восстановлением только данных, а не базы целиком. Посмотрим, насколько это будет быстрее.


Ссылки:


  1. Оригинал: Speeding up Postgres Restores.
  2. Вторая часть: Ускоряем восстановление бэкапов в Postgres.
Southbridge
Обеспечиваем стабильную работу highload-проектов

Comments 23

    +4
    72 часа за год — это много :)
    у меня основное время при разворачивании базы занимает создание индексов.
    реально помогает задирание maintenance_work_mem и отключание автовакуума
      0
      А если накинуть еще 4 ядра в виртуалку и заливать дамп в 16 потоков?
        +4
        А почему не используете directory format и многопоточный дамп как его плюшку? Ну и если уж жать-разжимать gzip-ом, так использовать многопоточный pigz
          +4
          А почему решили не использовать pg_basebackup?
          В случае его использования время развертывания будет примерно равно времени копирования файлов.
          Из минусов — так можно делать только целиком для кластера и версии PG должны быть одинаковые.
            0
            админ и «админ баз данных» уже давно две разные профессии)
            но ничего, ребята скоро разберутся
              0
              Это ресторить кластер можно, а не одну БД.
              0
              не знаю как в postgres(наверно можно сделать), но в mysql самое быстрое это останавливать mysql и подменять файлы базы. Если стоит ssd или raid, то за пару минут в вас будет копироваться база.
                +1
                А вообще для dev-среды эффективнее всего просто останавливать базу и полностью заменять файлы. Получится моментально. Если, конечно, применимо. Постгрес в контейнер, дамп выкачали по http, распаковали pigz-ом, подсунули новый volume контейнеру и все. Никаких томительных часов ожидания, пока на медленном диске виртуалки индексы прожуются
                  0
                  pg_dump -Fc db

                  Имеет смысл явно указывать уровень сжатия. Например pg_dump -Fc -Z 1 db
                  По умолчанию для -Fc он стоит в 6, что нагружает проц и увеличивает время бекапа
                    0

                    А ещё есть zstd, который в 4 раза быстрее zlib при той же степени сжатия.

                      0
                      pg_basebackup не пробовали?
                        0
                        Как вариант, можно настроить в привилигерованном lxc контейнере реплику и использовать ее в качестве образа для легковесных overlayfs контейнеров. То есть мастер транслирует wal логи на lxc реплику. Когда мы хотим себе полигон, просто делаем lxc-copy с типом overlayfs (легковесный снимок основного lxc контейнера, который пишет только разность относительно замороженного слоя), переводим postgresql overlayfs копии в боевой режим и экспериментируем на здоровье. Должно происходить мгновенно. Была статья на хабре, где парень что-то похожее на эту схему делал, только на btrfs
                          0
                          просто направить запакованный файл напрямую в psql с помощью zcat

                          Не везде есть. Но практически везде есть gzip -cd.

                            0
                            Краткое содержание статьи — прочитали мануал и нашли опции -Fc и -j. Нелохо.
                            Но вот что реально помогло сэкономить время на бекап\восстановление в нашем проекте:
                            как правило, в размерах таблиц в базе соблюдается принцип 20/80. Оказалось что для dev 80 не нужны (обычно это стата). Так вот гораздо более полезной было открытие опции (опять же, из манулов) --exclude-table с выкидывание ненужных таблиц из бекапа и последующим их созданием с минимально необхоимым для дев контентом.
                              0
                              Лучше использовать exclude-table-data тогда сами таблицы оставит, а данные не сдампит.
                              0
                              Как раз тоже занимаюсь анализированием pg_dump — pg_restore. Столкнулся со странным поведением второй команды Почему-то при восстановлении в логи ругаутся ошибками[archiver (db)] could not execute query: ERROR: relation «table_1» does not exist.
                              При восстановлении одной таблицы ругается на отсутствие другой, на отсутствие директорий, отсутствие индексов.
                              В итоге восстановление просто не завершается. И в текстовом формате дампил, и в бинарном. Ничего не понимаю…
                                0
                                Скорее всего не все схемы в дампе или ресторишь не все.
                                +1

                                спасибо автору за полезную статью (переводу), когда планируется 2 часть.

                                +1
                                Варианты увеличение скорости:
                                1) Взять SSD, на нем же быстрее будет идти разработка.
                                2) Бэкапить снапшотом и вытаскиванием файлов баз. Потом просто подключать файлы в базу.
                                3) Зеркалить репликой с мастер базы на слейв. Затем просто оторвать слейв и сделать его девом. К мастеру подключить новый слейв, к моменту когда будет нужен новый дев — база уже докатится.

                                Хотя конечно без структуры вашей фермы разработки и продакшена сложно сказать как лучше оптимизировать.
                                  0
                                  Мы делаем zfs снапшотами, всё моментально, причём на продакшн, причём база 300 гигов а не 30. А уж для dev очевидно что dump-restore это совершенно не нужно, просто держать копию базы где-то и подпихивать её не знаю, симлинком каким-нибудь.
                                    0
                                    В особенности если это используется для тестирования, рекомендую просто подготовить Docker образ, и откатываться к любой закоммиченной точке в течении единичных секунд.

                                    Only users with full accounts can post comments. Log in, please.