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

Postgres предлагает три принципиально разных подхода к резервному копированию своих данных:

• Дамп SQL (или логический)
• Резервное копирование на уровне файловой системы (или физическое)
• Непрерывное архивирование (или восстановление на момент времени)

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

Как создать файл дампа PostgreSQL

В Postgres для извлечения базы данных в файл сценария или другой архивный файл используется утилита под названием « pg_dump ». Важным преимуществом этой утилиты является то, что вы можете восстанавливать ее дампы на более новых версиях PostgreSQL или на машинах с другой архитектурой. Другие методы резервного копирования, такие как резервное копирование на уровне файлов и непрерывное архивирование, привязаны к конкретной версии и архитектуре сервера.

Самое простое использование этой команды:

pg_dump имя_базы_данных> database.sql

или же:

pg_dump имя_базы_данных -f database.sql

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

Если вы получили сообщение об ошибке «pg_dump не распознается как внутренняя или внешняя команда», добавьте путь к каталогу bin PostgreSQL в переменную среды PATH (в моем случае это «C:\Program Files\PostgreSQL\11\bin»). Вариант изменения переменной окружения — запуск команды с полным путем, например:

«C:\Program Files\PostgreSQL\ 11 \bin\pg_dump» имя_базы_данных > database.sql

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

pg_dump -U postgres имя_базы_данных> database.sql

Пакетный запуск pg_dump (без присмотра)

Если вы планируете автоматически создавать резервные копии базы данных, вам может потребоваться избавиться от запроса пароля. Это будет полезно, если вы запускаете резервное копирование в пакетном режиме или сценариях, где нет пользователя для ввода пароля. Вы можете сделать это, назначив пароль переменной PGPASSWORD:

УСТАНОВИТЬ PGPASSWORD=мой_пароль

pg_dump -U postgres имя_базы_данных> database.sql

В качестве альтернативы, если вы не хотите хранить пароль в пакетном файле, вы можете поместить учетные данные в %APPDATA%\postgresql\pgpass.conf в следующем формате:

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

Звездочки могут заменить имя хоста и базу данных.

Следующие команды создадут каталог и добавят запись в файл одним пакетом:

компакт-диск %appdata%

мкдир postgresql

компакт-диск postgresql

echo localhost: 5432 : my_database: postgres: my_password >> pgpass.conf

Резервное копирование удаленного сервера

Если вам нужно создать резервную копию удаленного сервера, добавьте параметры -h и -p:

pg_dump -h имя_хоста -p номер_порта имя_базы_данных > database.sql

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

Резервное копирование одной таблицы

Чтобы сделать дамп одной таблицы, используйте параметр -t:

pg_dump -t имя_таблицы имя_базы_данных > таблица.sql

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

В старых (до 8.2) версиях PostgreSQL параметр -t имя_таблицы выводил все таблицы с указанным именем. Современные движки Postgres сбрасывают все видимое в ваш путь поиска по умолчанию. Если вы хотите вернуться к старому поведению, вы можете написать -t «*.table_name» .

Кроме того, вы должны написать что-то вроде -t имя_схемы.имя_таблицы , чтобы выбрать таблицу определенной схемы вместо старых опций, таких как -n имя_схемы -t имя_таблицы .

Сжатие сценария резервного копирования

Если вам нужно сжать выходной файл, вы должны использовать опцию -Z:

pg_dump -Z6 имя_базы_данных > database.gz

Эта команда вызывает сжатие всего выходного файла, как если бы он был передан через gzip с уровнем сжатия, равным 6 (он может варьироваться от 0 до 6).

Другой способ получить файл резервной копии меньшего размера — использовать пользовательский формат файла для резервной копии.

Как восстановить файл дампа PostgreSQL

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

createdb -T template0 имя_базы_данных
psql имя_базы_данных < database.sql

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

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

Если вам нужно восстановить базу данных на удаленном сервере, вы можете подключить к ней psql с помощью опций -h и -p:

psql -h имя_хоста -p номер_порта имя_базы_данных < database.sql

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

pg_dump -h исходный_хост имя_базы_данных | psql -h host_host имя_базы_данных

Эта команда будет дублировать базу данных:

createdb -T template0 новая_база данных
pg_dump существующая_база данных | psql новая_база_данных

Обработка ошибок

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

psql --set ON_ERROR_STOP=on имя_базы_данных < database.sql

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

psql --set ON_ERROR_STOP=on -1 имя_базы_данных < database.sql

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

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

Основное использование этой команды выглядит следующим образом:

pg_dumpall > all_databases.sql

Psql и опцию -f можно использовать для восстановления полученного дампа:

psql -f all_databases.sql postgres

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

Как сделать резервную копию PostgreSQL в файл архива пользовательского формата

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

Чтобы создать файл резервной копии в пользовательском формате дампа, вам нужно добавить параметр -Fc:

pg_dump -Fc имя_базы_данных> database.dump

Этот формат файла необходимо использовать с pg_restore для перестроения базы данных. Это позволяет pg_restore выбирать, что восстанавливать, или даже переупорядочивать элементы перед восстановлением. Форматы архивных файлов предназначены для передачи между архитектурами.

Чтобы восстановить пользовательский формат файла, используйте следующую команду:

pg_restore -d имя_базы_данных database.dump

Используя параметр -j, вы можете значительно сократить время восстановления большой базы данных на сервер, работающий на многопроцессорной машине. Это достигается за счет выполнения самых трудоемких частей pg_restore, а именно тех, которые загружают данные, создают ограничения или создают индексы с помощью нескольких одновременных задач. Каждое задание представляет один поток или один процесс; он использует отдельное соединение с сервером и зависит от операционной системы. Например, эта команда восстановит базу данных в четырех одновременных заданиях:

pg_restore -j 4 -d имя_базы_данных database.dump

Используйте следующее, чтобы удалить базу данных и воссоздать ее из дампа:

dropdb имя_базы_данных

pg_restore -C -d имя_базы_данных database.dump

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

Выполните следующее, чтобы перезагрузить дамп в новую базу данных:

createdb -T template0 имя_базы_данных

pg_restore -d имя_базы_данных database.dump

Другие форматы резервных копий PostgreSQL

pg_dump предоставляет два других формата выходных файлов: каталог и tar. Оба они восстанавливаются с помощью утилиты pg_restore.

Чтобы создать архив в формате каталога, вам нужно использовать параметр -Fd:

pg_dump -Fd имя_базы_данных -f database.dump

Будет создан каталог с одним файлом для каждой таблицы и дампа большого двоичного объекта, а также так называемый файл Table of Contents, описывающий объекты дампа в машиночитаемом формате, который может прочитать pg_restore. Стандартные инструменты Unix могут использоваться для управления архивом в формате каталога; например, инструмент gzip можно использовать для сжатия файлов в несжатом архиве. Этот формат поддерживает параллельные дампы, сжатые по умолчанию.

Формат tar совместим с форматом каталога: действительный архив в формате каталога создается при извлечении архива в формате tar. Однако формат tar не поддерживает сжатие. Кроме того, при использовании формата tar нельзя изменить относительный порядок элементов данных таблицы в процессе восстановления.

Чтобы создать tar-файл, используйте параметр -Ft:

pg_dump -Ft имя_базы_данных -f database.tar

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

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

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

pg_dumpall --только схема > определения.sql

Используйте следующую команду для резервного копирования только определения роли:

pg_dumpall --только роли > roles.sql

Используйте следующую команду для резервного копирования определения табличных пространств :

pg_dumpall --только для табличных пространств > tablespaces.sql

Резервное копирование на уровне файловой системы

Альтернативная стратегия резервного копирования — прямое копирование файлов, которые PostgreSQL использует для хранения данных в базе данных. Можно использовать любой метод резервного копирования файловой системы, например:

xcopy «C:\Program Files\PostgreSQL\ 11 \data» «D:\backup» /E

Затем вы сможете запустить новый экземпляр сервера с тем же именем выпуска в этой папке (обратите внимание, что вы должны запустить эту команду на уровне администратора):

pg_ctl start -D «D:\backup»

Этот метод дает вам следующие преимущества:

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

и в то же время предполагает некоторые ограничения:

• Требует закрытия базы данных
• Можно восстановить только на той же основной версии PostgreSQL.
• Отдельные базы данных или отдельные таблицы не могут быть восстановлены: нужно восстановить все или ничего
• Создает очень большие резервные копии, поскольку включает все индексы и раздувание и поэтому может быть намного больше, чем дампы SQL.

Непрерывное архивирование

Метод непрерывного архивирования сочетает резервное копирование на уровне файловой системы с резервным копированием файлов WAL (в котором сохраняются все изменения, внесенные в файлы данных базы данных). Этот способ сложнее в администрировании, чем любой из предыдущих подходов, но он имеет ряд существенных преимуществ:

• Нет необходимости иметь идеально согласованную резервную копию файловой системы в качестве отправной точки. Воспроизведение журнала исправит любое внутреннее несоответствие в резервной копии (это не имеет существенного отличия от того, что происходит во время восстановления после сбоя). Таким образом, вам не нужно создавать снимок файловой системы, достаточно tar или аналогичного средства архивации.
• Непрерывного резервного копирования можно добиться, просто продолжая архивировать файлы WAL. Это особенно ценно для больших баз данных, где не всегда удобно выполнять полное резервное копирование.
• Нет необходимости воспроизводить записи WAL до конца. Вы можете остановить воспроизведение в любой момент и получить непротиворечивый снимок существующей базы данных. Таким образом, этот метод поддерживает так называемое «восстановление на момент времени»: базу данных можно восстановить в ее состояние в любое время с момента создания резервной копии базы данных.
• Если вы постоянно передаете серию файлов WAL на другую машину, на которую был загружен тот же файл базовой резервной копии, у вас будет система горячего резерва: вторая машина может быть запущена в любое время с почти текущей копией базы данных.

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

Мы не будем освещать все процессы настройки непрерывного архивирования, так как оно исчерпывающе описано в документации .
 

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

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

• резервное копирование по расписанию
• уведомлять вас, если что-то пошло не так
• отправка вашей резервной копии в один из известных облачных сервисов хранения (например, Dropbox, Google Drive, Amazon S3 и т. д.)
• шифрование архива
• резервное копирование файлов
• поддержка баз данных SQL Server, My SQL и Azure.

Начать работу с SQLBackupAndFTP довольно просто. После загрузки и установки подключите его к своей базе данных PostgreSQL, нажав кнопку «Gear» в разделе «Connect to Database Server». В появившемся всплывающем окне выберите PostgreSQL (TCP/IP) в качестве типа сервера, затем укажите логин/пароль. После этого нажмите «Проверить соединение», чтобы проверить, все ли в порядке, и нажмите «Сохранить и закрыть», чтобы применить все настройки:

Затем вам нужно выбрать базы данных, для которых вы хотите создать резервную копию, щелкнув шестеренку в разделе «Выбрать базы данных»:

После этого выберите место назначения, куда будут отправляться резервные копии базы данных PostgreSQL, щелкнув значок «плюс» в разделе «Хранить резервные копии в выбранных местах назначения».

Вы можете отправить резервную копию в следующие облачные хранилища: Локальная или сетевая папка, FTP-сервер, Amazon S3, Dropbox, Google Диск, OneDrive, Box, Azure Storage, OneDrive, для бизнеса, Backblaze B2, Яндекс Диск:

Если вы хотите запускать резервное копирование по расписанию, вам необходимо настроить план резервного копирования. Вы можете сделать это следующим образом: включите переключатель рядом с заголовком «Расписание резервного копирования» и нажмите кнопку «Шестеренка», чтобы получить доступ к расширенным настройкам расписания резервного копирования:

Это все. Если вам нужно создать резервную копию прямо сейчас, нажмите «Выполнить сейчас»:

При желании, если вам нужно применить некоторые параметры pg_dump, вы можете прокрутить вниз, нажать «Дополнительные настройки…», а затем снова «Дополнительные настройки…» в разделе «Параметры резервного копирования». Это даст вам следующее окно с несколькими быстрыми опциями и возможностью добавить свои собственные:

Как восстановить базу данных PostgreSQL с помощью SQLBackupAndFTP

Есть два способа восстановить ранее созданную резервную копию с помощью SQLBackupAndFTP:

• На панели «История и восстановление», если резервная копия была создана с помощью SQLBackupAndFTP.
• Использование «Restore Job» , если резервная копия была создана другими способами, скорее всего, непосредственно с помощью утилиты pg_dump

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

На последнем шаге у вас будет возможность изменить имя базы данных (если вы не хотите перезаписывать существующую базу данных) и указать архив пароль (если вы установили его при создании архива):

 

Как восстановить произвольный дамп SQL с помощью SQLBackupAndFTP

Если у вас есть произвольный дамп резервной копии базы данных PostgreSQL, который вы хотите восстановить на этой машине, вы можете воспользоваться «Заданием восстановления», доступным в SQLBackupAndFTP. Это задание берет дамп SQL из облачного хранилища и восстанавливает его в выбранной базе данных. Процедура настройки такого задания аналогична созданию резервных копий.

Создайте «Задание восстановления», нажав «Задание» > «Добавить новое задание восстановления»:
 

Выберите место, где находится ваша резервная копия:
 

В разделе «Хранить базы данных для восстановления» выберите резервную копию, которую необходимо восстановить:
 

Подключитесь к вашему серверу PostgreSQL в разделе «Восстановить на сервер базы данных»:
 

Теперь, когда вся подготовка завершена, восстановите резервную копию, нажав кнопку «Выполнить сейчас»: