Резервное копирование и восстановление в PostgreSQL

Посвящается Владимиру Габриелю

Ушел из жизни молодым Владимир Габриель. Этот замечательный человек сделал много для развития экосистемы свободного программного обеспечения в России. Работая в Microsoft, Владимир проводил тематические конференции по свободному программному обеспечению, на которые приглашались местные российские хакеры, в число которых попадал и я. С одной стороны, конечно, на этих конференциях решались корпоративные задачи компании Microsoft, шла реклама технологий, технологических платформ и мировоззрений. С другой стороны, организаторы не боялись привезти в Россию лидеров сообществ и компаний, занимавшихся свободным ПО, — Брайана Беклендорфа, Эрика Рэймонда, Эндрю Мортона, Мигеля де Иказу, Мэтта Эсея. Образования и общения с живыми иконами не хватало, поэтому подобные конференции были для нас, как глоток свежего воздуха.

Владимир работал на этих конференциях не с самой удобной, с протестной аудиторией. И добивался результата — пусть с ним не соглашались, но запоминали. Можно рассматривать и компанию Датавед, как попытку экспериментальной проверки острых, иногда жестких и неудобных, вопросов и тем, поднимавшихся Владимиром на этих конференциях. Причем же здесь PostgreSQL?

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

PostgreSQL предлагает несколько режимов резервного копирования и восстановления баз данных (БД). Поскольку БД располагаются в файловой системе, вполне нормальным методом является резервное копирование на уровне файлов, то есть копирование самого каталога, где размещаются файлы БД. Единственное условие для использования такого режима — полный останов сервера PostgreSQL.

Для систем высокой готовности такой режим резервного копирования не подойдет. Поэтому PostgreSQL позволяет выполнять резервное копирование при запущенном сервере, не прерывая его обычной работы. Наиболее простой режим — это получение дампа БД в текстовом виде (в форме операторов SQL). Для экономии дискового пространства можно сразу же сжимать такой дамп утилитой сжатия (например, gzip). Также существует возможность создания дампа БД в двоичной форме, а также возможность задавать специальные параметры для большего удобства в получении резервной копии и её последующего восстановления.

PostgreSQL также предоставляет возможность резервного копирования в реальном времени посредством Write-Ahead Log (WAL). За счет этого возможно восстановление содержания БД на конкретный момент времени, а также инкрементальное резервное копирование.

Дамп SQL

Идея метода дампа заключается в генерации текстового файла с командами SQL, которые при выполнении на сервере, пересоздадут базу данных в том же самом состоянии, в котором она была на момент создания дампа. PostgreSQL предоставляет для этой цели програмнную утилиту pg_dump. Базовый сценарий вызова данной команды для записи дампа базы bd_name в файл dump.sql выглядит так:

pg_dump bd_name > dump.sql

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

Чтобы указать, к какому серверу должен подключаться pg_dump, необходимо использовать опцию командной строки -h server_name -p port_num. По умолчанию в качестве сервера выбирается localhost или же тот сервер, который указан в переменной окружения PGHOST. Подобным образом, в качестве порта по умолчанию используется порт, указанный в переменной окружения PGPORT или, если переменная не задана, то порт, указанный по умолчанию при компиляции, как правило, 5432.

Как и любое другое клиентское приложение PostgreSQL, pg_dump по умолчанию будет подключаться к базе данных под пользователем, имя которого совпадает с именем текущего пользователя операционной системы. Чтобы изменить это, необходимо либо указать опцию -U, либо установить переменную окружения PGUSER. Подключения, которые выполняет pg_dump, работают с учётом обычных механизмов авторизации — для доступа может потребоваться пароль, или тикет Kerberos, или иная аутентификационная информация.

Важное преимущество pg_dump над другими методами резервного копирования состоит в том, что вывод pg_dump может быть обычно перезагружен в более новые версии PostgreSQL, в то время как резервная копия на уровне файловой системы и непрерывное архивирование жёстко зависят от версии сервера. Также, только pg_dump является методом, который будет работать при переносе базы данных на другую машинную архитектуру, например, при переносе с 32-битной на 64-битную версию сервера.

Дампы, создаваемые pg_dump, являются внутренне целостными, что означает, что дамп представляет собой снимок базы данных на момент начала запуска pg_dump. pg_dump не блокирует другие операции с базой данных во время своей работы. Исключениями являются случаи, когда есть необходимость работы с эксклюзивной блокировкой, как у большинства форм команды ALTER TABLE.

Важное замечание. Если Ваша схема базы данных полагается на идентификаторы OID (например, для связывания, как внешние ключи), необходимо указать pg_dump, чтобы в дамп были также включены OID посредством опцию командной строки -o.

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

Текстовые файлы, созданные pg_dump, предназначаются для последующего чтения программой psql. Общий вид команды для восстановления базы bd_name из файла дампа dump.sql:

psql bd_name < dump.sql

База данных, заданная параметром bd_name не будет создана данной командой. Необходимо создать её из базы template0 перед запуском psql (например, с помощью команды createdb -T template0 bd_name). Аналогично pg_dump, команда psql позволяет указать сервер, к которому осуществляется подключение и имя пользователя, от имени которого осуществляется подключение.

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

По умолчанию, если произойдёт ошибка SQL, программа psql продолжит своё выполнение. Можно запустить psql с установленной переменной ON_ERROR_STOP, чтобы изменить такое поведение и заставить psql выйти с кодом выхода 3, в случае возникновения ошибки SQL:

psql --set ON_ERROR_STOP=on bd_name < dump.sql

В любом случае, будет только частичное восстановление базы данных. В качестве альтернативы, можно указать, что весь дамп должен быть восстановлен в одну транзацию, так что восстановление или будет полностью выполнено или полностью не выполнено. Данный режим может быть задан, с помощью опций командной строки -1 или single-transaction для psql. Когда используется этот режим, необходимо проявлять осторожность, т.к. даже маленькая ошибка может привести к откату процесса восстановления, который может продолжаться несколько часов. Однако это может быть предпочтительней, чем ручная чистка сложной базы данных после частично восстановленного дампа.

Возможность pg_dump и psql писать и читать из конвееров делают возможным создание дампа базы данных напрямую с одного сервера на другой.

Например:

 pg_dump -h сервер1 bd_name | psql -h сервер2 bd_name

Important: Дампы, которые делает pg_dump, являются относительными template0. Это означает, что любые языки, процедуры и т.д. добавленные через template1, также попадут в дамп при выполнении pg_dump. В итоге, при восстановлении, если Вы использовали специально изменённый template1, Вы должны создать пустую базу данных из template0, как показано в примере выше.

После восстановления резервной копии, следует запустить команду ANALYZE на каждую базу данных, чтобы оптимизатор запросов получил нужную статистику.

Использование утилиты pg_dumpall

pg_dump делает дамп только одной базы данных в один момент времени и не включает в дамп информацию о ролях или табличных пространствах (потому что эти данные относятся к уровню кластера, а не к самой базе данных). Дамп всего кластера баз данных создается утилитой pg_dumpall. pg_dumpall делает резервную копию каждой базы данных кластера, а также сохраняет данные уровня кластера, включая роли и определения табличных пространств. Базовая форма использования этой команды:

pg_dumpall >  dump.sql

Результирующий дамп может быть восстановлен с помощью psql:

psql –f dump.sql  postgres

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

pg_dumpall получает и выдаёт команды для пересоздания ролей, табличных пространств и пустых баз данных, затем вызывает для каждой базы данных pg_dump. Это означает, что в то время, как каждая база данных будет внутренне целостной, снимки других баз данных могут не быть точно синхронизированы.

Управление большими базами данных

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

Используйте сжатые дампы. Можно использовать любимую программу сжатия, например gzip:

pg_dump bd_name | gzip > dump.sql.gz

Загружая впоследствии сжатый дамп командой:

gunzip -c dump.sql.gz | psql bd_name

Или

cat  dump.sql.gz | gunzip | psql bd_name

Используйте split. Команда split позволяет Вам разбивать вывод на файлы меньшего размера, которые не попадают под ограничения на максимальный размер файла в файловой системе. Например, чтобы нарезать дамп на кусочки по 1 мегабайту:

pg_dump bd_name | split -b 1m - dump.sql

Загружая впоследствии полученные файлы командой:

cat dump.sql* | psql bd_name

Используйте специальный формат дампа в pg_dump. Если PostgreSQL была скомпилирована в системе с установленной библиотекой zlib, то специальный формат дампа будет сжимать данные, которые выдаются в файл вывода. Это приведёт к созданию файла дампа, который по размеру будет похож на дамп, сжатый gzip, но такой формат будет иметь преимущество, потому что позволяет выборочное восстановление таблиц. Следующая команда делает дамп базы данных, используя специальный формат дампа:

pg_dump -Fc bd_name > dump.sql

Специальный формат дампа не является скриптом для psql и должен восстанавливаться с помощью команды pg_restore, например:

pg_restore –d bd_name dump.sql

Для очень больших баз данных, нужно сочетать split с одним из двух других методов. См. подробности на страницах справочного руководства pg_dump и pg_restore.