26
июня
0

SQL дамп PostgreSQL



24.1. SQL дамп

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

pg_dump dbname > outfile 

Как видно, pg_dump пишет результат выполнения в стандартный вывод (прим. на экран). Ниже будет описано, как это может быть использовано.
pg_dump — это входящее в комплект поставки PostgreSQL клиентское приложение (тем не менее чрезвычайно функциональное). Это средство может выполнять процедуру резервного копирования с любого удаленного хоста, имея соответствующий доступ к базе данных. Но следует помнить, что pg_dump не работает без соответствующего доступа. На практике это должен быть доступ на чтение всех таблиц, дамп которых нужно сделать, поэтому почти всегда утилита выполняется от суперпользователя.

Для определения с каким сервером управления базами данных должен работать pg_dump используются опции командной строки -h host и -p port. По умолчанию в качестве host выступает сервер на котором запускается pg_dump или то, что задано переменной окружения PGHOST. Подобным образом по умолчанию значение port определяется переменной окружения PGPORT либо, в случае отсутствия, значением, заданным при компиляции. (Удобно, что сервер обычно скомпилирован с тем же значением по умолчанию.)

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

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

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

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

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

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

psql dbname < infile 

где infile — это выходной файл, полученный после использования pg_dump. База данных dbname не будет создана этой командой, поэтому нужно самостоятельно ее создать с template0 перед выполнением psql (например, командой createdb -T template0 dbname). psql поддерживает опции подобные pg_dump для выбора сервера баз данных для подключения и имени пользователя. Больше информации можно посмотреть на справочной странице psql.

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

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

psql --set ON_ERROR_STOP=on dbname < infile 

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

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

pg_dump -h host1 dbname | psql -h host2 dbname 

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

24.1.2. Использование pg_dumpall

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

pg_dumpall > outfile 

Полученный дамп может быть восстановлен с помощью psql:

psql -f infile postgres 

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

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

24.1.3. Обработка больших баз данных

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

Тут есть несколько возможных методов:

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

pg_dump dbname | gzip > filename.gz 

Восстанавливается следующим образом:

gunzip -c filename.gz | psql dbname 

или:

cat filename.gz | gunzip | psql dbname 

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

pg_dump dbname | split -b 1m - filename 

Восстанавливается следующим образом:

cat filename* | psql dbname 

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

pg_dump -Fc dbname > filename 

Пользовательский формат дампа не удастся восстановить, как скрипт для psql, вместо этого должна быть использована утилита pg_restore:

pg_restore -d dbname filename 

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

Эта статья является переводом соответствующего параграфа официальной документации PostgreSQL 9.1 раздела «Администрирование сервера» главы №24 «Резервное копирование и восстановление».

Понравилась статья?
Подписаться на RSS feed
Оставить комментарий