Backup restore postgresql базы данных с pg_dump

pg_dump — это утилита позволяющая делать бэкап базы данных из postgresql. Она сохраняет в файл набор SQL команд которые полностью воссоздают структуру исходной базы данных. Для последующего восстановления из подобного файла достаточно psql которая почти всегда идет в комплекте с самим postgresql.

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

В этой небольшой статье мы рассмотрим несколько полезных примеров использования pg_dump.

Резервное копирование базы данных

Бэкап одной базы данных

В примере ниже, мы сделаем резервную копию базы данных под названием thebosharu , принадлежащей пользователю bosha и сохраним её в файл thebosharu.sql :

Если вы работаете с базой данных не под тем же пользователем, под которым работаете в системе, то pg_dump спросит пароль к базе данных и после его успешного ввода создаст указанный файл содержащий SQL команды для создания необходимой структуры и копирования данных.

Вот часть дампа моей базы данных, чтобы вы понимали что в нем находится:

Так же можно передать ключ —format=t , чтобы pg_dump запаковал базу данных в tar:

Бэкап всех баз данных

Для того, чтобы сделать резервную копию всех баз данных, нужно использовать другую утилиту — pg_dumpall.

Права доступа могут быть настроены у всех по разному, поэтому бэкап всех баз данных лучше делать из под пользователя postgres .

Бэкап всех баз данных будет содержаться в файле all_databases.sql .

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

В моем случае было три базы данных:

Теперь удостоверимся, что все перечисленные базы попали в дамп:

Бэкап определенной таблицы

Для бэкапа определенной таблицы используется параметр —table и следом за ним названием таблицы. Если в базе данных указанная таблица есть в разных схемах, то её можно указать используя параметр —schema .

Восстановление из бэкапа

Восстановление одной базы данных

В процессе восстановления из резервной копии, могут возникнуть ошибки и предупреждения. Их можно игнорировать:

Создание бэкапа базы PostgreSQL для Windows

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

Справочную информацию о командах, испульзуемых в этом файле можно получить из командной строки набрав следующую команду: «[Имя команды] /?»
Многие использованные здесь команды достаточно распространены и известны, поэтому хочется акцентировать внимание на нескольких менее известных.

Строки 15, 16 выполняют переход в папку в которой находится файл «backup.bat». «%0» возвращает имя bat-файла; «%

dp0″ возвращают соответственно диск и путь к bat-файлу. Подробные сведения о работе с параметрами файла можно посмотреть по этой ссылке.

В строке 19 формируется строковое представление даты и времени в нужном формате. При формировании происходит обращение к переменным окружения DATE и TIME, которые хранят текстовое представление даты и времени соответственно. После имени переменной указывается строка вида «:

m,n», где m — позиция в строке, n — количество символов.

В строке 27 вызывается утилита резервного копирования pg_dump.exe. Вызов выполняется с применением команды CALL, это позволяет дождаться завершения утилиты и проанализировать результат выполнения. Вызов утилиты завершается строкой «2>%LOGPATH%». Эта строка означает что поток ошибок STDERR, номер которого 2, приложения pg_dump.exe перенаправляется в файл, имя которого сохранено в переменной окружения LOGPATH. Так как приложение pg_dump.exe выводит все сообщения в стандартный поток ошибок, то в файле LOGPATH будет сохранен подробный отчет о выполнении резервного копирования.

В строках 37 и 42 выполняется перенаправление вывода в файл backup.log. Перенаправление осуществляется оператором «>>». Различие между операторами «>» и «>>» в том, что первый каждый раз создает новый файл, затирая ранее записанные данные, а второй — дописывает данные в существующий файл. Таким образом можно вести журнал с подробными сведениями о результатах резервного копирования.

Проверяем как работает bat-файл. Если дампы базы создаются, то можно приступать к созданию задачи для планировщика заданий Windows.
Создаем задание, которое будет запускать bat-файл каждый день в ночное время.

Ежедневные бэкапы со временям породят проблему свободного пространства на жестком диске. Можно чистить ручками, но лучше уж автоматизацию сделать полной. Решается этот вопрос также созданием bat-файла и задачи в планировщике заданий Windows.

Содержимое bat-файла такое:

Здесь указана команда при выполнении которой будут удаляться файлы старше 5 дней.
В планировщике заданий можно создать задачу на исполнения этого bat-файла раз в неделю.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

10 способов сделать резервную копию в PostgreSQL

Если рассматривать резервное копирование как вполне конкретный процесс, то возникает два простых вопроса:
1. откуда запускать резервное копирование?
2. какие инструменты следует использовать для резервного копирования?

На первый вопрос есть два варианта ответа: можно запускать задачу резервного копирования с выделенного backup сервера, на мой взгляд это наиболее подходящий вариант. Либо запускать задачу непосредственно с сервера БД, это в случае если нет выделенного сервера бэкапов.

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

В комплекте PostgreSQL есть 2 утилиты которые позволяют делать резервные копии, это pg_dump/pg_dumpall и pg_basebackup. Кроме того есть возможность использовать утилиты файлового копирования, такие как rsync, tar, cp и т.п.
Итак, каким инструментом запускать бэкап?
pg_dump — подходит для случаев когда нужно сделать резервную копию таблицы, базы, схемы или данных.
pg_basebackup — подходит для случаев когда нужно сделать резервную копию целиком всего кластера БД или настроить hot standby реплику.
rsync/tar/cp — также используются для случаев копирования всего кластера.

Когда только случился релиз PostgreSQL 9.0 резервное копирование выполнялось с помощью rsync, однако уже в 9.1 появился pg_basebackup, который имеет некоторыми преимуществами перед rsync:

• pg_basebackup не требует ssh доступа, но требует доступа к базе указанного в pg_hba.conf;
• pg_basebackup богаче по функциональности (копирование WAL, создание recovery.conf, встроенное сжатие gzip и пр.);
• pg_basebackup не требует отдельного вызова функций pg_start_backup/pg_stop_backup как это требуется при использовании rsync/tar/cp;
• pg_basebackup выполняет копирование быстрее чем rsync за счет использования протокола потоковой репликации.

но есть и некоторые недостатки:

• pg_basebackup идет out-of-the-box, и соответственно требует установленного postgres;
• pg_basebackup не имеет встроенных функций для ограничения скорости копирования (обещают только в 9.4);
• pg_basebackup требует включенных опций wal_level = hot_standby, max_wal_senders в postgresql.conf.

Здесь я буду рассматривать pg_basebackup, хотя и pg_dump тоже может использоваться в нижеперечисленных способах.

1. Простое и без изысков резервное копирование с backup сервера в каталог /backup (каталог должен быть предварительно создан):

2. Копирование с пониженным приоритетом IO операций с помощью ionice, для случаев когда нужно уменьшить нагрузку на дисковый ввод-вывод от резервного копирования:

3. Копирование с сжатием в bzip2, для случаев когда нужно использовать нестандартный для pg_basebackup алгоритм сжатия (gzip). Здесь мы передаем данные через стандартный вывод (stdout) на стандартный ввод (stdin) программе bzip2.

4. Копирование с сжатием в несколько потоков (используем lbzip2 и задействуем 6 ядер). При таком раскладе можно задействовать простаивающие ядра и ускорить процесс сжатия.

5. Здесь копирование запускается на сервере БД. Формируемая резервная копия отправляется на удаленный сервер по ssh.

6. Здесь копирование также запускается на сервере БД и выполняется отправка на удаленный сервер, но уже с архивированием в 6 потоков с помощью lbzip2.

7. Копирование на удаленный сервер с ограничением пропускной полосы до 10Мб с помощью pv и последующее архивирование на удаленной стороне. Этот вариант для случаев когда нужно передать не нагружая сеть.

Тут стоит отметить что c 9.4 в pg_basebackup уже есть возможность ограничения скорости передачи (-r, —max-rate).
8. Копирование запускается на backup сервере, а далее происходит раздваивание потока на две части. Один поток сжимается с bzip2 (сам бэкап) и второй поток через tar копируется во временный каталог для последующей валидации. Способ редкоиспользуемый, но тут интересна сама реализация.

9. Копирование с задействование lbzip2 на обоих узлах, для случаев когда у сети маленькая пропускная способность, сначала поток сжимается, затем передается по сети и затем расжимается на удаленной стороне. Здесь используется tar и требуется выполнение pg_start_backup(‘label_name’) на стороне postgres.

10. бэкапирование с шифрованием через GPG, для случаев когда нужно зашифровать резервную копию. Предварительно следует создать ключи через gpg —gen-key (в моем случае ключи созданы с именем backup)

Для расшифровки резервной копии следует выполнить такую команду

На этом все, подведем итоги по инструментам:

• pg_basebackup — утилита для создания резервных копий postgres;
• lbzip2 — bzip2 сжатие с использованием несокльких ядер — если нужно запаковать быстрее (аналоги: pbzip2, pigz);
• ionice — регулировка класса и приоритета для планировщика ввода-вывода (также можно использовать nice для регулировки приоритета процессов для CPU планировщика);
• pv — контролируем объем передаваемых данных через pipe и т.о. используем для ограничения объема передаваемых данных в единицу времени (аналог — throttle);
• tar — утилита архивирования, нужна для вспомогательных целей когда неиспользуется сжатие bzip2/gzip;
• tee — чтение с stdin c записью в stdout и другие файлы (является частью coreutils);
• gpg — решает задачи по шифрованию.

Всем спасибо за внимание!