Как получить дамп базы данных

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

Сегодня я решил опубликовать свои шпаргалки, на самые часто встречающиеся ошибки при работе с MySQL.

Работа с бекапами

• -Q
  оборачивает имена обратными кавычками
• -c
  делает полную вставку, включая имена колонок
• -e
  делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

Для таблиц InnoDB надо добавлять —single-transaction
, это гарантирует целостность данных бекапа.

Для таблиц MyISAN это не актуально, ибо они не поддерживают транзакционность.

Общие факты

• Полезно под каждую базу на боевом сервере создавать своего пользователя
• Кодировка базы может быть любой, если она UTF8
• В большинстве случаев лучше использовать движок InnoDB
• В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli
• Новую копию MySQL всегда можно настроить и оптимизировать
• Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов
  

  ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

Работа с данными

Числа

• На 32-битных системах практически нет смысла ставить для типа INTEGER свойство UNSIGNED, так как такие большие числа в php не поддерживаются.
  

  На 64-битных системах, php поддерживает большие числа, вплоть до MySQL BIGINT со знаком.

• Связанные таблицы («Foreign keys») должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано «INTEGER UNSIGNED DEFAULT 0 NOT NULL» то и на другой должно быть указано аналогично
• Для хранения булевых значений, нужно использовать TINYINT
• А деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что DECIMAL(10,2) может сохранить 9999999,99

Строки

• В старых версиях (до 5.0.3) VARCHAR была ограничена 255 символами, но сейчас можно указывать до 65535 символов
• Помните, что тип TEXT ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь «LONGTEXT»
• Самая правильная кодировка для вашей БД UTF8

Даты

Не забывайте, что

• DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование
• TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку
• Сравнивая типы данных DATETIME и TIMESTAMP, не забывайте делать преобразование типов, например:
  

  SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

Перечисления

• Для перечислений правильно использовать тип ENUM
• Правильно пишется так: ENUM(‘мама’, ‘мыла’, ‘раму’)
• Можно ставить значение по-умолчанию, как и для любой строки
• В базе поле с перечислением хранится как число, поэтому скорость работы — потрясающе высокая
• Количество перечислений ~ 65 тысяч

Отладка

• Если запросы тормозят, то можно включить лог для медленных запросов в /etc/mysql/my.cnf
• А потом оптимизировать запросы через EXPLAIN
• И наблюдать за запросами удобно через программу mytop

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

В статье мы расскажем, как экспортировать и импортировать базу данных.

• Что такое дамп базы данных
• Когда нужно создать файл дампа
• Как создать дамп базы данных
• Как импортировать базу данных

Что такое дамп базы данных

Дамп (dump) — это файл с содержимым базы данных. Чаще всего он имеет расширение .sql и включает в себя:

• информацию о пользователях и их правах,
• каталог товаров или услуг,
• публикации на сайте,
• комментарии и др.

Дамп БД позволяет обезопасить информацию от повреждения или потери.

Когда нужно создать файл дампа

Копия базы данных может понадобиться в следующих случаях:

• Создание резервной копии
  . Если вы хотите сделать копию сайта, необходимо сохранить файлы из корневой директории и дамп БД. Оба этих компонента необходимы для корректной работы ресурса: если сайт перестанет работать, вы можете восстановить всю информацию из бэкапа.
• Перенос данных на другую услугу
  . Если вы планируете переносить сайт с одного сервера на другой, потребуется архив с файлами сайта и локальный дамп базы данных.

Как создать дамп базы данных

Получить дамп БД из памяти хостинга можно тремя способами:

• через панель управления SpaceWeb,
• через phpMyAdmin,
• по SSH.

Все эти способы мы описали ниже.

Как создать дамп базы данных в панели управления

1. Перейдите в панель управления.
2. Разверните раздел Хостинг
   
   и кликните Базы данных
   :

3. Справа от нужной базы данных нажмите на три точки
   и выберите Создать копию (дамп) базы
   :

Если в БД есть данные, на экране появится сообщение:

Архив базы данных MySQL создается.

Процесс может занять от минуты до нескольких часов.

По завершении вы получите уведомление на административный email.

Готово, ссылка на скачивание дампа придет на контактный email.

Как создать дамп базы данных в phpMyAdmin

1. Перейдите в панель управления.
2. Разверните раздел Хостинг
   и кликните Базы данных
   :

3. Справа от нужной базы данных нажмите на три точки
   и выберите Открыть phpMyAdmin для редактирования
   :

4. Кликните по строке с именем базы данных:

5. Нажмите Экспорт
   :

6. Отметьте пункт Быстрый
   и кликните Вперед
   :

Готово, файл с копией БД загрузится на ваш компьютер.

Как создать дамп базы данных через SSH-подключение

1. Подключитесь к хостингу по SSH.
2. Чтобы задампить базу данных, выполните команду:

• username
  — имя пользователя БД,
• db_name
  — название БД.

3. Введите пароль пользователя базы данных.

Готово, damp БД сохранится в корневую директорию хостинга.

Как импортировать базу данных

Импортировать базу данных можно двумя способами:

• через phpMyAdmin,
• по SSH.

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

1. Перейдите в панель управления.
2. Разверните блок Хостинг
   и выберите Базы данных
   :

3. Нажмите Создать базу данных
   :

4. Заполните поля:

• Тип базы данных
  — отметьте пункт MySQL,
• Имя
  — укажите название базы данных,
• Пароль
  — сгенерируйте пароль БД или введите его вручную.

Затем кликните Создать
:

1. Перейдите в панель управления.
2. Разверните блок Хостинг
   и выберите Базы данных
   :

3. Справа от новой базы данных нажмите на три точки
   и выберите Открыть phpMyAdmin для редактирования
   :

4. Кликните по строке с именем базы данных:

5. Выберите Импорт
   и нажмите Выбрать файл
   . После этого выберите файл копии БД на компьютере:

6. Пролистайте страницу до конца и нажмите Вперед
   :

Готово, после успешного импорта вы получите уведомление Импорт успешно завершён
.

Как импортировать базу данных по SSH

1. Загрузите дамп на сервер. Для этого выполните команду:

• /home/test.sql
  — путь к файлу на компьютере,
• username
  — имя пользователя SSH на сервере,
• 123.123.123.123
  — IP-адрес сервера,
• /directory
  — путь до папки на сервере, в которую нужно загрузить дамп.

2. Подключитесь к серверу по SSH.
3. Выполните команду:

• user
  — имя пользователя БД,
• db_name
  — название БД.

Готово, вы импортировали базу данных по SSH.

Создание дампа БД PostgreSQL

Для создания дампа БД PostgreSQL следует использовать в консоли SSH команду следующего вида:

• hostname
  — имя сервера БД;
• format
  — формат дампа (может быть одной из трех букв: ‘с’ (custom — архив .tar.gz), ‘t’ (tar — tar-файл), ‘p’ (plain — текстовый файл). В команде букву надо указывать без кавычек.);
• dumpfile
  — имя создаваемого файла дампа;
• dbname
  — имя базы данных.

Для баз созданных до 16.09.2019
имя хоста будет выглядеть так: pg.sweb.ru
; для баз данных, которые были созданы после 16.09.2019
имя хоста будет таким: pg2.sweb.ru
. Для баз данных созданных после 24.04.2023
имя хоста будет таким: pg3.sweb.ru

После завершения задачи файл с именем dumpfile будет размещен в директории, из которой запускалась команда.

Пример создания дампа базы vh36sup в файл архива формата postgress. где custom — архив, в формате самого postgress:

pg_dump -h pg2.sweb.ru -U vh36sup -F c -f dump.tar.gz vhsup

Импорт дампа БД PostgreSQL

Для импорта необходимо использовать команду вида:

Параметры аналогичные, за исключением того, что format
может быть либо ‘c’, либо ‘t’.

Пример загрузки архива дампа dump.tar.gz в базу vhsup:

pg_restore -h pg2.sweb.ru -U vhsup -F c -d vhsup dump.tar.gz

Дампы представленные в виде текстового файла можно импортировать с помощью следующей команды:

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

 pg_dump  имя_базы  >  файл_дампа  

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

Программа является для обычным клиентским приложением (хотя и весьма умным). Это означает, что вы можете выполнять процедуру резервного копирования с любого удалённого компьютера, если имеете доступ к нужной базе данных. Но помните, что не использует для своей работы какие-то специальные привилегии. В частности, ей обычно требуется доступ на чтение всех таблиц, которые вы хотите выгрузить, так что для копирования всей базы данных практически всегда её нужно запускать с правами суперпользователя СУБД. ( Если у вас нет достаточных прав для резервного копирования всей базы данных, вы тем не менее можете сделать резервную копию той части базы, доступ к которой у вас есть, используя такие параметры, как -n схема
или -t таблица
.)

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

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

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

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

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

 psql  имя_базы  <  файл_дампа  

где файл_дампа

— это файл, содержащий вывод команды . База данных, заданная параметром имя_базы

, не будет создана данной командой, так что вы должны создать её сами из базы template0
перед запуском (например, с помощью команды createdb -T template0 имя_базы
). Программа принимает параметры, указывающие сервер, к которому осуществляется подключение, и имя пользователя, подобно . За дополнительными сведениями обратитесь к справке по
. Дампы, выгруженные не в текстовом формате, восстанавливаются утилитой
.

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

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

psql --set ON_ERROR_STOP=on  имя_базы  <  файл_дампа  

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

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

pg_dump -h  host1   имя_базы  | psql -h  host2   имя_базы  

Важно

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

После восстановления резервной копии имеет смысл запустить ANALYZE

для каждой базы данных, чтобы оптимизатор запросов получил полезную статистику; за подробностями обратитесь к Подразделу 25.1.3
и Подразделу 25.1.6
. Другие советы по эффективной загрузке больших объёмов данных в вы можете найти в Разделе 14.4
.

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

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

 pg_dumpall >  файл_дампа  

Полученную копию можно восстановить с помощью :

 psql -f  файл_дампа  postgres 

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

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

Только глобальные данные кластера можно выгрузить, передав ключ --globals-only
. Это необходимо, чтобы полностью скопировать кластер, когда выполняется для отдельных баз данных.

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

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

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

pg_dump  имя_базы  | gzip >  имя_файла 
.gz

Затем загрузить сжатый дамп можно командой:

gunzip -c  имя_файла 
.gz | psql  имя_базы  

cat  имя_файла 
.gz | gunzip | psql  имя_базы  

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

pg_dump  имя_базы  | split -b 2G -  имя_файла  

Восстановить их можно так:

cat  имя_файла 
* | psql  имя_базы  

Использовать GNU можно вместе с :

pg_dump  имя_базы  | split -b 2G --filter='gzip > $FILE.gz'

Восстановить данные после такого разбиения можно с помощью команды zcat
.

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

pg_dump -Fc  имя_базы  >  имя_файла  

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

pg_restore -d  имя_базы   имя_файла  

За подробностями обратитесь к справке по командам
и
.

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

Используйте возможность параллельной выгрузки в . 
Чтобы ускорить выгрузку большой БД, вы можете использовать режим параллельной выгрузки в . При этом одновременно будут выгружаться несколько таблиц. Управлять числом параллельных заданий позволяет параметр -j
. Параллельная выгрузка поддерживается только для формата архива в каталоге.

pg_dump -j  число  -F d -f  выходной_каталог   имя_базы  

Вы также можете восстановить копию в параллельном режиме с помощью pg_restore -j
. Это поддерживается для любого архива в формате каталога или специальном формате, даже если архив создавался не командой pg_dump -j
.

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

pg_dump  имя_базы  >  файл_дампа  

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

Программа является для обычным клиентским приложением (хотя и весьма умным). Это означает, что вы можете выполнять процедуру резервного копирования с любого удалённого компьютера, если имеете доступ к нужной базе данных. Но помните, что не использует для своей работы какие-то специальные привилегии. В частности, ей обычно требуется доступ на чтение всех таблиц, которые вы хотите выгрузить, так что для копирования всей базы данных практически всегда её нужно запускать с правами суперпользователя СУБД. ( Если у вас нет достаточных прав для резервного копирования всей базы данных, вы тем не менее можете сделать резервную копию той части базы, доступ к которой у вас есть, используя такие параметры, как -n схема

или -t таблица

.)

Указать, к какому серверу должна подключаться программа , можно с помощью аргументов командной строки -h сервер

и -p порт

. По умолчанию в качестве сервера выбирается localhost или значение, указанное в переменной окружения PGHOST
. Подобным образом, по умолчанию используется порт, заданный в переменной окружения PGPORT
, а если она не задана, то порт, указанный по умолчанию при компиляции. ( Для удобства при компиляции сервера обычно устанавливается то же значение по умолчанию.)

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

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

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

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

psql  имя_базы  <  файл_дампа  

где файл_дампа

— это файл, содержащий вывод команды . База данных, заданная параметром имя_базы

, не будет создана данной командой, так что вы должны создать её сами из базы template0
перед запуском (например, с помощью команды createdb -T template0 имя_базы

). Программа принимает параметры, указывающие сервер, к которому осуществляется подключение, и имя пользователя, подобно . За дополнительными сведениями обратитесь к справке по
. Дампы, выгруженные не в текстовом формате, восстанавливаются утилитой
.

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

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

 psql --set ON_ERROR_STOP=on  имя_базы  <  файл_дампа  

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

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

 pg_dump -h  host1   имя_базы  | psql -h  host2   имя_базы  

Важно

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

После восстановления резервной копии имеет смысл запустить ANALYZE

для каждой базы данных, чтобы оптимизатор запросов получил полезную статистику; за подробностями обратитесь к Подразделу 25.1.3
и Подразделу 25.1.6
. Другие советы по эффективной загрузке больших объёмов данных в вы можете найти в Разделе 14.4
.

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

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

 pg_dumpall >  файл_дампа  

Полученную копию можно восстановить с помощью :

 psql -f  файл_дампа  postgres 

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

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

Только глобальные данные кластера можно выгрузить, передав ключ --globals-only
. Это необходимо, чтобы полностью скопировать кластер, когда выполняется для отдельных баз данных.

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

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

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

имя_файла pg_dump имя_базы

| gzip > имя_файла

.gz

Затем загрузить сжатый дамп можно командой:

имя_файла gunzip -c имя_файла

.gz | psql имя_базы

cat имя_файла

.gz | gunzip | psql имя_базы

имя_базы

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

pg_dump имя_базы

| split -b 2G — имя_файла

Восстановить их можно так:

split cat имя_файла

* | psql имя_базы

split

Использовать GNU можно вместе с :

pg_dump имя_базы

| split -b 2G —filter=’gzip > $FILE.gz’

Восстановить данные после такого разбиения можно с помощью команды zcat
.

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

pg_dump -Fc имя_базы

> имя_файла

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

 pg_restore -d  имя_базы   имя_файла  gzip 
 За подробностями обратитесь к справке по командам   и  
. 
 
 Для очень больших баз данных может понадобиться сочетать split с одним из двух других методов. 
 
  Используйте возможность параллельной выгрузки в .  
Чтобы ускорить выгрузку большой БД, вы можете использовать режим параллельной выгрузки в . При этом одновременно будут выгружаться несколько таблиц. Управлять числом параллельных заданий позволяет параметр -j
. Параллельная выгрузка поддерживается только для формата архива в каталоге. 
 
 pg_dump -j  число  -F d -f  выходной_каталог   имя_базы  
 Вы также можете восстановить копию в параллельном режиме с помощью pg_restore -j
 . Это поддерживается для любого архива в формате каталога или специальном формате, даже если архив создавался не командой pg_dump -j
 .
 Путеводитель по резервному копированию баз данных имя_файла
  
  Время на прочтение
 
 
  – О, никакое убежище не выдержит попадания метеорита. Но ведь у вас, как и у каждого, есть резерв, так что можете не беспокоиться.
  Станислав Лем, «Звёздные дневники Ийона Тихого» 
 Резервным копированием называется сохранение копии данных где-то вне основного места их хранения.
 
 
Главное назначение резервного копирования – восстановление данных после их потери. В связи с этим нередко приходится слышать, что при наличии реплики базы данных с неё всегда можно восстановить данные, и резервное копирование не нужно. На самом деле резервное копирование позволяет решить как минимум три задачи, которые не могут быть решены при помощи реплики, да и реплику без резервной копии не инициализировать.
Во-первых, резервная копия позволяет восстановить данные после логической ошибки. Например, бухгалтер удалил группу проводок или администратор БД уничтожил табличное пространство. Обе операции абсолютно легитимны с точки зрения базы данных, и процесс репликации воспроизведёт их в базе-реплике.
Во-вторых, современные СУБД – весьма надёжные программные комплексы, однако изредка всё же происходит повреждение внутренних структур базы данных, после которого доступ к данным пропадает. Что особенно обидно, такое нарушение происходит обычно при высокой нагрузке или при установке какого-нибудь обновления. Но как высокая нагрузка, так и регулярные обновления говорят о том, что база данных – отнюдь не тестовая, и данные, хранящиеся в ней, ценны.
Наконец, третья задача, решение которой требует наличия резервной копии, – это клонирование базы, например, для целей тестирования.
Резервное копирование баз данных так или иначе базируется на одном из двух принципов:
 Выборка данных с последующим сохранением в произвольном формате;
 Снимок состояния файлов БД и сохранение журналов.
 Давайте рассмотрим эти принципы и реализующие их инструменты подробнее.
  
 Выгрузка данных
 В наборе утилит, прилагающихся к любой СУБД, обязательно есть инструменты для выгрузки и загрузки данных. Данные сохраняются либо в текстовом формате, либо в двоичном формате, специфичном для конкретной СУБД. В таблице ниже приведён список таких инструментов:
 Текстовый формат хорош тем, что его можно редактировать или даже создавать внешними программами, а двоичный в свою очередь хорош тем, что позволяет быстрее выгружать и загружать данные за счёт распараллеливания загрузки и экономии ресурсов на преобразовании форматов.
Несмотря на простоту и очевидность идеи выгрузки данных, для резервирования нагруженных промышленных баз такой метод применяют редко. Вот причины, по которым выгрузка не подходит для полноценного резервного копирования:
 процесс выгрузки создаёт значительную нагрузку на систему-источник;
 выгрузка занимает много времени – к моменту окончания выгрузки она станет уже неактуальной;
 сделать согласованную выгрузку всей базы данных при высокой нагрузке практически невозможно, поскольку СУБД вынуждена хранить снимок своего состояния на момент начала выгрузки. Чем больше транзакций совершено с момента начала выгрузки, тем больше объём снимка (неактуальных копий данных в PostgreSQL, пространства undo в Oracle, tempdb в Microsoft SQL Server и т. п.);
 выгрузка сохраняет логическую структуру данных, но не сохраняет их физическую структуру – параметры физического хранения таблиц, индексы и др.; восстановление индексов при загрузке может занимать значительное время.
 Тем не менее, у выгрузки есть и достоинства:
 высокая избирательность: можно выгрузить отдельные таблицы, отдельные поля и даже отдельные строки;
 выгруженные данные можно загрузить в базу данных другой версии, а если выгрузка сделана в текстовом формате, то и в другую базу данных.
 Таким образом, выгрузка используется в основном для таких задач как резервирование небольших таблиц (например, справочников) или распространение наборов данных с очередным релизом приложения.
Самым же распространённым методом резервного копирования баз данных является копирование файлов базы.
 «Холодное» сохранение файлов БД
 Очевидная идея – остановить базу данных и скопировать все её файлы. Такая резервная копия называется «холодной». Способ крайне надёжный и простой, но у него есть два очевидных недостатка:
 из «холодной» резервной копии можно восстановить только то состояние базы данных, которое было в момент останова; транзакции, сделанные после рестарта базы, в «холодную» резервную копию не попадут;
 далеко не у каждой базы данных есть технологическое окно, когда базу можно остановить.
 Если же «холодное» резервное копирование вас устраивает, нужно помнить что
 «холодная» копия иногда должна включать в себя и журналы. Методы определения журналов, которые должны попасть в «холодную» копию, индивидуальны для каждой СУБД. Например, в Oracle необходимо скопировать так называемые online redo, то есть фиксированное количество журнальных файлов в специальном каталоге, причём даже тогда, когда база остановлена корректно. В PostgreSQL нужно сохранить все журналы начиная с журнала, содержащего последнюю контрольную точку, информация о которой содержится в управляющем файле.
 каталог базы данных может содержать достаточно большие файлы временных табличных пространств, которые не обязательно включать в резервную копию. Кстати, это замечание верно и для «горячего» резервного копирования.
 «Горячее» сохранение файлов
 Большинство резервных копий современных баз данных выполняется путём копирования файлов базы данных без остановки базы. Здесь видны несколько проблем:
 В момент начала копирования содержимое базы данных может не совпадать с содержимым файлов, т. к. часть информации находится в кеше и ещё не записана на диск.
 Во время копирования содержимое базы может меняться. Если используются изменяемые структуры данных, то меняется содержимое файлов, а при использовании неизменяемых структур меняется набор файлов: новые файлы появляются, а старые удаляются.
 Поскольку запись данных в базу и чтение файлов БД никак не синхронизированы, программа резервного копирования может прочитать некорректную страницу, в которой половина будет от старой версии страницы, а другая половина – от новой.
 Для того, чтобы резервная копия получилась согласованной, у каждой СУБД существует команда, которая сообщает, что начат процесс резервного копирования. Синтаксически эта команда может выглядеть по-разному:
 в Oracle это отдельная команда ALTER DATABASE/TABLESPACE BEGIN BACKUP;
 в PostgreSQL – функция pg_start_backup();
 в Microsoft SQL Server и DB2 подготовка к резервному копированию выполняется неявно в процессе выполнения команды BACKUP DATABASE;
 в MySQL Enterprise, Percoba Server, Cassandra и MongoDB подготовка неявно выполняется внешней утилитой – mysqlbackup, Percona XtraBackup, OpsCenter и Ops Manager соответственно.
 Несмотря на синтаксические различия, процесс подготовки к резервному копированию выглядит одинаково.
Вот как выглядит подготовка к резервному копированию в СУБД с изменяемыми дисковыми структурами, т. е. во всех традиционных дисковых реляционных системах:
 Запоминается момент начала резервного копирования; резервная копия должна будет содержать журналы базы данных начиная с этого момента.
 Выполняется контрольная точка, то есть все изменения, которые произошли в страницах данных до запомненного момента, сбрасываются на диск. Это гарантирует, что журналы до момента начала резервного копирования при восстановлении не потребуются.
 Включается особый режим журналирования: если страница данных изменилась в первый раз после загрузки с диска, то вместо того, чтобы записывать в журнал изменения страницы, база запишет туда страницу целиком. При выполнении подготовительной процедуры все страницы вытесняются на диск, и поэтому при первом изменении блок всегда будет записан в журнал целиком. Но если в процессе резервного копирования страница снова будет вытеснена на диск, то следующее её изменение также приведёт к появлению в журнале полной копии страницы. Это гарантирует, что если вдруг при копировании файла с данными страница получится некорректной, применение журнала сделает его корректной вновь.
 Блокируется изменение заголовков файлов данных, то есть той его части, изменения которой не отражаются в журналах. Это гарантирует, что заголовок будет скопирован корректно, а потом к файлу данных корректно будут применены журналы.
 После того, как все перечисленные выше процедуры выполнены, можно копировать файлы данных средствами операционной системы – cp, rsync и другими. Включение режима резервного копирования снижает производительность базы данных: во-первых, увеличивается объём журналов, а во-вторых, если вдруг в режиме резервного копирования произойдёт сбой, восстановление будет более продолжительным, т. к. заголовки файлов данных не обновляются. Чем быстрее резервное копирование закончится, тем лучше для базы данных, поэтому здесь уместно применение таких средств как снимок (snapshot) файловой системы или разрыв зеркала (BCV) в дисковом массиве. Одни СУБД (Oracle, PostgreSQL) оставляют администратору возможность самостоятельно выбрать способ копирования, другие (Microsoft SQL Server) предоставляют интерфейс для интеграции собственных утилит резервного копирования с механизмами файловых систем или СХД.
По окончании резервного копирования нужно перевести базу данных обратно в обычное состояние. В Oracle это делается командой ALTER DATABASE/TABLESPACE END BACKUP, в PostgreSQL – вызовом функции pg_stop_backup(), а в других базах – внутренними подпрограммами соответствующих команд или внешних сервисов.
Вот как выглядит временнáя диаграмма процесса резервного копирования:
 
 
 Подготовка к резервному копированию (begin backup) занимает время, иногда значительное. Даже если используются зеркальные тома или файловые системы с возможностью изготовления снимков, процесс резервного копирования не будет мгновенным.
 Вместе с файлами данных необходимо сохранить журналы начиная с момента начала подготовки к резервному копированию и заканчивая моментом возврата базы в нормальное состояние.
 Восстановиться из этой резервной копии можно  на момент возврата базы в нормальное состояние 
 . Восстановление на более ранний момент невозможно.
 С базами данных, использующими неизменяемые структуры данных (снимки памяти, LSM-деревья) ситуация проще. Подготовка к резервному копированию состоит из следующих шагов:
 Данные из памяти сбрасываются на диск.
 Фиксируется список файлов, попадающих в резервную копию. До тех пор, пока процесс резервного копирования не закончится, базе запрещено удалять эти файлы, даже если они становятся не нужны.
 По сигналу об окончании резервного копирования база с неизменяемыми структурами снова может удалять ненужные файлы.
 Восстановление на точку
 Резервная копия позволяет восстановить состояние базы данных на момент, когда завершилась команда возврата из режима резервного копирования. Однако авария, после которой потребуется восстановление, может произойти в любой момент. Задача восстановления состояния БД на произвольный момент называется «восстановлением на точку» (point-in-time recovery).
Чтобы обеспечить такую возможность, следует сохранять журналы БД начиная с момента окончания резервного копирования, а в процессе восстановления продолжить применять журналы к восстановленной копии. После того, как БД восстановлена из резервной копии на момент окончания копирования, состояние базы (файлов и кэшированных страниц) гарантированно корректно, поэтому особый режим журналирования не нужен. Применяя журналы до нужного момента, можно получить состояние базы данных на любую точку во времени.
Если скорость восстановления резервной копии ограничена лишь пропускной способностью диска, то скорость применения журналов обычно ограничена производительностью процессора. Если в основной базе данных изменения происходят параллельно, то при восстановлении все изменения выполняются последовательно – в порядке чтения из журнала. Таким образом время восстановления линейно зависит от того, насколько далеко точка восстановления отстоит от точки окончания резервного копирования. Из-за этого приходится довольно часто делать полные резервные копии – минимум раз в неделю для баз с небольшой транзакционной нагрузкой и до ежедневного копирования высоконагруженных баз.
 Инкрементальное резервное копирование
 Чтобы ускорить восстановление на точку, хотелось бы иметь возможность выполнять резервное копирование как можно чаще, но при этом не занимать лишнего места на дисках и не нагружать базу задачами резервного копирования.
Решение задачи – инкрементальное резервное копирование, то есть копирование только тех страниц данных, которые изменились с момента предыдущего резервного копирования.
Инкрементальное резервное копирование имеет смысл только для СУБД, использующих изменяемые структуры данных.
Инкремент может отсчитываться как от полной резервной копии (кумулятивная копия), так и от любой предыдущей копии (дифференциальная копия).
 
 
К сожалению, единой терминологии не существует, и разные производители используют разные термины:
 При наличии инкрементальных копий процесс восстановления на точку выглядит следующим образом:
 восстанавливается последняя полная резервная копия, сделанная до момента восстановления;
 поверх полной копии восстанавливаются инкрементальные копии;
 накатываются журналы с точки начала резервного копирования до точки восстановления.
 Наличие кумулятивной копии ускоряет процесс восстановления. Так, например, для восстановления состояния базы на точку между T3 и T4 необходимо восстановить две инкрементальных копии, а для восстановления на точку после T4 – только одну.
Очевидно, что объём одной кумулятивной копии меньше, чем объём нескольких дифференциальных копий, потому что некоторые страницы изменились по несколько раз, и каждая инкрементальная копия содержит свою версию страницы.
Есть три способа создания инкрементальной копии:
 создание полной копии и вычисление разницы с предыдущей полной копией;
 разбор журналов, создание списка изменённых страниц и резервирование страниц, включённых в список;
 запрос изменённых страниц в базе данных.
 Первый способ экономит дисковое пространство, но не решает задачу снижения нагрузки на базу данных. Более того, если у нас есть полная резервная копия, то превращать её в инкрементальную бессмысленно, т. к. восстановление полной копии быстрее, чем восстановление предыдущей полной копии и инкремента. Задачу экономии дискового пространства при таком подходе лучше переложить на специальные компоненты со встроенными механизмами дедупликации. Это могут быть как специальные СХД (EMC DataDomain, HPE StorageWorks VLS, вся линейка NetApp), так и программные продукты (ZFS, Veritas NetBackup PureFile, Windows Server Data Deduplication).
Впервые функциональность инкрементального резервного копирования была создана в ПО Oracle Recovery Manager (RMAN), появившемся в релизе Oracle 8i. Oracle сразу реализовал отслеживание изменённых блоков, поэтому необходимости в разборе журналов нет.
PostgreSQL не отслеживает изменённые блоки, поэтому утилита pg_probackup, разработанная российской компанией Postgres Professional, определяет изменённые страница путём анализа журнала. Однако компания поставляет и СУБД PostgresPro, которая включает расширение ptrack, отслеживающее изменение страниц. При использовании pg_probackup с СУБД PostgresPro утилита запрашивает изменённые страницы у самой базы – точно так же, как и RMAN.
Microsoft SQL Server так же, как и Oracle, отслеживает изменённые страницы, но команда BACKUP позволяет делать только полные и кумулятивные резервные копии.
В DB2 есть возможность отслеживания измененных страниц, но по умолчанию она выключена. После включения DB2 позволит делать полные, дифференциальные и кумулятивные резервные копии.
Важное отличие описанных в этом разделе средств (кроме pg_probackup) от файловых средств резервного копирования в том, что они запрашивают образы страниц у базы данных, а не читают данные с диска самостоятельно. Недостаток такого подхода – небольшая дополнительная нагрузка на базу. Однако этот недостаток с лихвой компенсируется тем, что прочитанная страница всегда корректна, поэтому нет необходимости во включении на время резервного копирования особого режима журналирования.
Ещё раз обратите внимание, что наличие инкрементальных копий не отменяет требований к наличию журналов для восстановления на произвольную точку во времени. Поэтому в промышленных базах данных журналы постоянно переписываются на внешний носитель, а резервные копии, полные и/или инкрементальные, создаются по расписанию.
Наилучшей на сегодня реализацией идеи инкрементального резервного копирования является программно-аппаратный комплекс (в терминологии Oracle – engineered system) Zero Data Loss Recovery Appliance – специализированное решение Oracle для резервного копирования собственной БД. Комплекс представляет собой кластер серверов с большим объёмом дисков, на которые установлена модифицированная версия ПО Recovery Manager и может работать как с другими программно-аппаратными комплексами Oracle (Database Appliance, Exadata, SPARC Supercluster), так и с базами Oracle на традиционной инфраструктуре. В отличие от «обычного» RMAN, в ZDLRA реализована концепция «вечного инкремента» (incremental forever). Система единственный раз создаёт полную копию базы данных, а потом делает только инкрементальные копии. Дополнительные модули RMAN позволяют объединять копии, создавая новые полные копии из инкрементальных.
К чести российских разработчиков нужно заметить, что и pg_probackup умеет объединять инкрементальные копии.
 
 
В отличие от многих похожих вопросов, вопрос «какой метод резервного копирования лучше» имеет однозначный ответ – лучше всего родная для используемой СУБД утилита, обеспечивающая возможность инкрементального копирования.
Для администратора БД гораздо более важными являются вопросы выбора стратегии резервного копирования и интеграция средств резервирования баз данных в корпоративную инфраструктуру. Но эти вопросы выходят за рамки данной статьи.
Читайте также:  Используются команды Zabbix-get и некоторые параметры, встроенные в Zabbix