Postgresql не восстанавливается бд из файла backup exit code 1

Обновлено: 05.07.2024

pg_restore — восстановить базу данных PostgreSQL из файла архива, созданного командой pg_dump

Синтаксис

pg_restore [ параметр-подключения . ] [ параметр . ] [ имя_файла ]

Описание

Утилита pg_restore предназначена для восстановления базы данных PostgreSQL из архива, созданного командой pg_dump в любом из не текстовых форматов. Она выполняет команды, необходимые для восстановления того состояния базы данных, в котором база была сохранена. При наличии файлов архивов, pg_restore может восстанавливать данные избирательно или даже переупорядочить объекты перед восстановлением. Заметьте, что разработанный для файлов архива формат не привязан к архитектуре.

Утилита pg_restore может работать в двух режимах. Если указывается имя базы данных, pg_restore подключается к этой базе данных и восстанавливает содержимое архива непосредственно в неё. В противном случае создаётся SQL-скрипт с командами, необходимыми для пересоздания базы данных, который затем выводится в файл или в стандартное устройство вывода. Сформированный скрипт будет в точности соответствовать выводу pg_dump в простом текстовом формате. Поэтому некоторые из параметров, управляющих выводом, аналогичны параметрам pg_dump .

Разумеется, pg_restore может восстановить информацию, только если она присутствует в файле архива, и только в существующем виде. Например, если архив был создан с указанием « выгрузить данные в виде команд INSERT » , pg_restore не сможет загрузить эти данные, используя операторы COPY .

Параметры

Утилита pg_restore принимает следующие аргументы командной строки.

Указывает расположение восстанавливаемого файла архива (или каталога, для архива в формате каталога). По умолчанию используется устройство стандартного ввода. -a
--data-only

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

Флаг похож на --section=data , но по историческим причинам не равнозначен ему. -c
--clean

Создать базу данных, прежде чем восстанавливать данные. Если также указан параметр --clean , удалить и пересоздать целевую базу данных перед подключением к ней.

С этим параметром база, заданная параметром -d , применяется только для подключения и выполнения начальных команд DROP DATABASE и CREATE DATABASE . Все данные восстанавливаются в базу данных, имя которой записано в архиве. -d имя_бд
--dbname= имя_бд

Подключиться к базе данных имя_базы и восстановить данные непосредственно в неё. В данном аргументе может задаваться строка подключения. В этом случае параметры в строке подключения переопределяют одноимённые параметры, заданные в командной строке. -e
--exit-on-error

Завершать работу в случае возникновения ошибки при выполнении команд SQL в базе данных. По умолчанию процесс восстановления продолжается, а по его окончании выдаётся число ошибок. -f имя_файла
--file= имя_файла

Задаёт файл для вывода сгенерированного скрипта или списка объектов, получаемого с параметром -l . Чтобы выбрать стандартное устройство вывода, используйте - (этот вариант также подразумевается по умолчанию). -F формат
--format= формат

Задаёт формат архива. Указывать формат необязательно, так как pg_restore определяет формат автоматически. Но если формат задаётся, допускается один из этих вариантов:

Архив сохранён в специальном формате pg_dump . d
directory

Архив сохранён в каталоге. t
tar

Архив сохранён в формате tar .

Восстановить определение только заданного индекса. Добавив дополнительные ключи -I , можно указать несколько индексов. -j число-заданий
--jobs= число-заданий

Запустить наиболее длительные операции pg_restore (в частности, загрузку данных, создание индексов или ограничений) в нескольких параллельных заданиях. Это позволяет кардинально сократить время восстановления большой базы данных, когда сервер работает на многопроцессорном компьютере.

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

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

Вывести содержимое архива. Вывод этой операции можно подать на вход этой же команде с параметром -L . Учтите, что когда вместе с -l применяются параметры фильтрации (например, -n или -t ), они сокращают список выводимых объектов. -L файл-список
--use-list= файл-список

Восстановить из архива только элементы, перечисленные в файле-списке , и в том порядке, в каком они идут в этом файле. Заметьте, что когда вместе с -L применяются параметры фильтрации (например, -n или -t ), они дополнительно ограничивают восстанавливаемые объекты.

Данный файл-список обычно представляет собой отредактированный результат предыдущей операции -l . Строки в нём могут быть переставлены или удалены, а также могут быть закомментированы точкой с запятой ( ; ), добавленной в начале строки. См. примеры ниже. -n пространство_имён
--schema= схема

Восстановить только объекты в указанной схеме. Добавив дополнительные ключи -n , можно указать несколько схем. Этот параметр можно сочетать с -t , чтобы восстановить только определённую таблицу. -O
--no-owner

Не генерировать команды, устанавливающие владение объектами, как в исходной базе данных. По умолчанию, pg_restore генерирует команды ALTER OWNER или SET SESSION AUTHORIZATION , восстанавливающие исходных владельцев создаваемых элементов схемы. Однако эти команды можно будет выполнить, только если к базе данных первоначально подключается суперпользователь (или пользователь, владеющими всеми объектами в скрипте). Чтобы получить скрипт, который сможет восстановить любой подключающийся пользователь (но при этом он станет владельцем всех созданных объектов), используется -O . -P имя-функции(тип-аргумента[, . ])
--function= имя-функции(тип-аргумента[, . ])

Восстановить только указанную функцию. При этом важно записать имя функции и аргументы в точности так, как они фигурируют в оглавлении файла архива. Добавив дополнительные ключи -P , можно указать несколько функций. -R
--no-reconnect

Параметр является устаревшим, но в целях совместимости ещё работает. -s
--schema-only

Восстановить только схему (определения данных), без данных, в объёме, в котором элементы схемы представлены в архиве.

Действие параметра противоположно действию --data-only . Это похоже на указание --section=pre-data --section=post-data , но по историческим причинам не равнозначно ему.

(Не путайте этот параметр с --schema , где слово « схема » используется в другом значении.) -S имя_пользователя
--superuser= имя_пользователя

Задаёт имя суперпользователя, полномочия которого будут использоваться для отключения триггеров. Этот параметр применяется только с параметром --disable-triggers . -t таблица
--table= таблица

Восстановить определение и/или данные только указанной таблицы. В этом контексте под « таблицей » подразумеваются также представления, материализованные представления, последовательности и сторонние таблицы. Чтобы выбрать несколько таблиц, ключ -t можно указать несколько раз. Этот параметр можно скомбинировать с -n , чтобы выбрать таблицу(ы) в определённой схеме.

Примечание

Когда указывается -t , pg_restore не пытается восстанавливать объекты базы данных, от которых могут зависеть выбранные таблицы. Таким образом, в этом случае не гарантируется, что выгруженные таблицы будут успешно восстановлены в чистой базе данных.

Примечание

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

Примечание

В версиях PostgreSQL до 9.6 этот флаг выбирал только таблицы, но не другие типы отношений.

Восстановить только указанный триггер. Добавив дополнительные ключи -T , можно указать несколько триггеров. -v
--verbose

Сообщить версию pg_restore и завершиться. -x
--no-privileges
--no-acl

Не восстанавливать права доступа (не выполнять команды GRANT/REVOKE). -1
--single-transaction

Произвести восстановление в одной транзакции (то есть, завернуть выполняемые команды в BEGIN / COMMIT ). При этом гарантируется, что либо все команды будут выполнены успешно, либо не будет никаких изменений. Этот режим подразумевает --exit-on-error . --disable-triggers

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

В настоящее время команды, генерируемые с --disable-triggers , должны выполнятся суперпользователем. Поэтому необходимо также задать имя суперпользователя в параметре -S или, что предпочтительнее, запускать pg_restore от имени суперпользователя PostgreSQL . --enable-row-security

Этот параметр имеет смысл только при восстановлении содержимого таблицы, для которой включена защита строк. По умолчанию pg_restore устанавливает для row_security значение off для уверенности, что в таблице восстановлены все данные. Если у пользователя недостаточно прав для обхода защиты строк, выдаётся ошибка. Этот параметр указывает pg_restore установить в row_security значение on, чтобы пользователь мог попытаться восстановить содержимое таблицы с включённой защитой строк. Однако и при этом возможна ошибка, если пользователь не будет иметь права добавлять в эту таблицу выгруженные строки данных.

Заметьте, что в настоящее время для этого требуется, чтобы выгрузка выполнялась в режиме INSERT , так как COPY FROM не поддерживает защиту строк. --if-exists

При очистке целевой базы использовать условные команды (добавлять предложение IF EXISTS ). Применяется только с параметром --clean . --no-data-for-failed-tables

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

Этот параметр действует только при восстановлении непосредственно в базу данных (не при генерации SQL-скрипта). --no-security-labels

Не выводить команды, восстанавливающие метки безопасности, даже если они содержатся в архиве. --no-tablespaces

Не формировать команды для указания табличных пространств. Все объекты будут создаваться в табличном пространстве по умолчанию. --section= имя секции

Восстановить только указанный раздел. В качестве имени раздела можно задать pre-data , data или post-data . Указав этот параметр неоднократно, можно выбрать несколько разделов. По умолчанию восстанавливаются все разделы.

Раздел «data» содержит собственно данные таблиц и определения больших объектов. В разделе «post-data» содержатся определения индексов, триггеров, правил и ограничений (кроме отдельно проверяемых). Раздел «pre-data» содержит все остальные определения. --strict-names

Требует, чтобы каждому указанию схемы ( -n / --schema ) и таблицы ( -t / --table ) соответствовала минимум одна схема/таблица в файле резервной копии. --use-set-session-authorization

Выводить команды SET SESSION AUTHORIZATION , соответствующие стандарту, вместо ALTER OWNER , для назначения владельцев объектов. В результате выгруженный скрипт будет более стандартизированным, но может не восстановиться корректно, в зависимости от истории объектов. -?
--help

Показать справку по аргументам командной строки pg_restore и завершиться.

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

Указывает имя компьютера, на котором работает сервер. Если значение начинается с косой черты, оно определяет каталог Unix-сокета. Значение по умолчанию берётся из переменной окружения PGHOST , если она установлена. В противном случае выполняется подключение к Unix-сокету. -p порт
--port= порт

Указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения. Значение по умолчанию определяется переменной окружения PGPORT , если она установлена, либо числом, заданным при компиляции. -U имя_пользователя
--username= имя_пользователя

Имя пользователя, под которым производится подключение. -w
--no-password

Не выдавать запрос на ввод пароля. Если сервер требует аутентификацию по паролю и пароль не доступен с помощью других средств, таких как файл .pgpass , попытка соединения не удастся. Этот параметр может быть полезен в пакетных заданиях и скриптах, где нет пользователя, который вводит пароль. -W
--password

Принудительно запрашивать пароль перед подключением к базе данных.

Это несущественный параметр, так как pg_restore запрашивает пароль автоматически, если сервер проверяет подлинность по паролю. Однако чтобы понять это, pg_restore лишний раз подключается к серверу. Поэтому иногда имеет смысл ввести -W , чтобы исключить эту ненужную попытку подключения. --role= имя роли

Задаёт имя роли, которая будет осуществлять восстановление. Получив это имя, pg_restore выполнит SET ROLE имя_роли после подключения к базе данных. Это полезно, когда проходящий проверку пользователь (указанный в -U ) не имеет прав, необходимых для pg_restore , но может переключиться на роль, наделённую этими правами. В некоторых окружениях правила запрещают подключаться к серверу непосредственно суперпользователю, и этот параметр позволяет выполнить восстановление, не нарушая их.

Переменные окружения

Параметры подключения по умолчанию

Как и большинство других утилит PostgreSQL , эта утилита также использует переменные среды, поддерживаемые libpq (см. Раздел 32.14). Однако она не учитывает PGDATABASE , когда имя базы не указано.

Диагностика

Замечания

Если в вашей инсталляции база данных template1 содержит какие-либо дополнения, важно убедиться в том, что вывод pg_restore загружается в действительно пустую базу; иначе вы, скорее всего, получите ошибки из-за дублирования определений создаваемых объектов. Чтобы получить пустую базу данных без дополнительных объектов, выберите в качестве шаблона template0 , а не template1 , например так:

Ограничения pg_restore описаны ниже.

При восстановлении данных в уже существующие таблицы с параметром --disable-triggers , pg_restore выполняет команды, отключающие триггеры в пользовательских таблицах до добавления данных, а затем, после добавления данных, выполняет команды, снова включающие эти триггеры. Если восстановление прервётся в середине, системные каталоги могут оказаться в некорректном состоянии.

Также обратитесь к документации pg_dump , чтобы узнать о связанных ограничениях pg_dump .

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

Примеры

Предположим, что мы выгрузили базу данных mydb в файл специального формата:

Мы можем удалить базу данных и восстановить её из копии:

В аргументе -d можно указать любую базу данных, существующую в кластере; pg_restore использует её, только чтобы выполнить команду CREATE DATABASE для базы mydb . С параметром -C данные всегда восстанавливаются в базу, имя которой записано в файле архива.

Восстановить данные в новую базу newdb можно так:

Обратите внимание, мы не используем параметр -C , а вместо этого подключаемся непосредственно к базе, в которую хотим восстановить данные. Также заметьте, что мы создаём базу данных из шаблона template0 , а не template1 , чтобы изначально она была гарантированно пустой.

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

Файл оглавления содержит заголовок и по одной строке для каждого элемента, например:

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

Строки в этом файле можно закомментировать, удалить или переместить. Например, список:

можно передать утилите pg_restore , чтобы восстановить только элементы 10 и 6 в указанном порядке:

Уже давно известно, что делать бэкапы в SQL-дампы (используя pg_dump или pg_dumpall) – не самая хорошая идея. Для резервного копирования СУБД PostgreSQL лучше использовать команду pg_basebackup, которая делает бинарную копию WAL-журналов. Но когда вы начнёте изучать весь процесс создания копии и восстановления, то поймёте что нужно написать как минимум пару трёхколёсных велосипедов, чтобы всё это работало и не вызывало у вас боль как сверху, так и снизу. Дабы облегчить страдания был разработан WAL-G.

WAL-G – это инструмент, написанный на Go для резервного копирования и восстановления PostgreSQL баз данных (а с недавнего времени и MySQL/MariaDB, MongoDB и FoundationDB). Он поддерживает работу с хранилищами Amazon S3 (и аналогами, например, Yandex Object Storage), а также Google Cloud Storage, Azure Storage, Swift Object Storage и просто с файловой системой. Вся настройка сводится к простым шагам, но из-за того что статьи о нём разрозненны по интернету – нет полного how-to мануала, который бы включал все шаги от и до (на Хабре есть несколько постов, но многие моменты там упущены).

Данная статья написана в первую очередь чтобы систематизировать свои знания. Я не являюсь DBA и где-то могу выражаться обывательско-разработческим языком, поэтому приветствуются любые исправления!

Отдельно отмечу что всё нижеприведенное актуально и проверено для PostgreSQL 12.3 на Ubuntu 18.04, все команды должны выполняться от привилегированного пользователя.

Установка

В момент написания данной статьи стабильная версия WAL-G – v0.2.15 (март 2020). Её мы и будем использовать (но если вы захотите самостоятельно собрать его из master-ветки, то в репозитории на github есть все инструкции для этого). Для скачивания и установки нужно выполнить:

После этого нужно сконфигурировать вначале WAL-G, а потом сам PostgreSQL.

Настройка WAL-G

Для примера хранения бэкапов будет использоваться Amazon S3 (потому что он ближе к моим серверам и его использование обходится очень дёшево). Для работы с ним нужен «s3-бакет» и ключи доступа.

Во всех предыдущих статьях о WAL-G использовалось конфигурирование с помощью переменных окружения, но с этого релиза настройки можно расположить в .walg.json файле в домашней директории пользователя postgres. Для его создания выполним следующий bash-скрипт:

Немного поясню по всем параметрам:

WALG_S3_PREFIX – путь к вашему S3-бакету куда будут заливаться бэкапы (можно как в корень, так и в папку);
AWS_ACCESS_KEY_ID – ключ доступа в S3 (в случае восстановления на тестовом сервере – данные ключи должны иметь ReadOnly Policy! Об этом подробнее написано в разделе про восстановление);
AWS_SECRET_ACCESS_KEY – секретный ключ в хранилище S3;
WALG_COMPRESSION_METHOD – метод компрессии, лучше использовать Brotli (так как это золотая середина между итоговым размером и скоростью сжатия/разжатия);
WALG_DELTA_MAX_STEPS – количество «дельт» до создания полного бэкапа (позволяют экономить время и размер загружаемых данных, но могут чуть замедлить процесс восстановления, поэтому не желательно использовать большие значения);
PGDATA – путь к директории с данными вашей базы (можно узнать, выполнив команду pg_lsclusters);
PGHOST – подключение к базе, при локальном бэкапе лучше делать через unix-socket как в этом примере.

Настройка PostgreSQL

Чтобы архиватор внутри базы сам заливал WAL-журналы в облако и восстанавливался из них (в случае необходимости) – нужно задать несколько параметров в конфигурационном файле /etc/postgresql/12/main/postgresql.conf. Только для начала вам нужно убедиться, что никакие из нижеприведенных настроек не заданы в какие-то другие значения, чтобы при перезагрузке конфигурации – СУБД не упала. Добавить эти параметры можно с помощью:

Описание устанавливаемых параметров:

wal_level – сколько информации писать в WAL журналы, «replica» – писать всё;
archive_mode – включение загрузки WAL-журналов используя команду из параметра archive_command;
archive_command – команда, для архивации завершённого WAL-журнала;
archive_timeout – архивирование журналов производится только когда он завершён, но если ваш сервер мало изменяет/добавляет данных в БД, то имеет смысл выставить тут лимит в секундах, по истечению которого команда архивации будет вызвана принудительно (у меня интенсивная запись в базу каждую секунду, поэтому я отказался от установки этого параметра в продакшене);
restore_command – команда восстановления WAL-журнала из бэкапа, будет использоваться в случае если в «полном бэкапе» (base backup) будет недоставать последних изменений в БД.

Настройка расписания резервного копирования

Как ни крути, но самым удобным способом для запуска – является cron. Именно его мы и настроим для создания резервных копий. Начнём с команды создания полного бэкапа: в wal-g это аргумент запуска backup-push. Но для начала лучше выполнить эту команду вручную от пользователя postgres, чтобы убедиться что всё хорошо (и нет каких-то ошибок доступа):

В аргументах запуска указан путь к директории с данными – напоминаю что его можно узнать, выполнив pg_lsclusters.

Если всё прошло без ошибок и данные загрузились в хранилище S3, то далее можно настроить периодический запуск в crontab:

В данном примере процесс бэкапа запускается каждый день в 4:15 утра.

Удаление старых резервных копий

Скорее всего вам не нужно хранить абсолютно все бэкапы с мезозойской эры, поэтому будет полезно периодически «подчищать» ваше хранилище (как «полные бэкапы», так и WAL-журналы). Мы сделаем это всё также через cron задачу (обратите внимание на экранирование символов $ и %):

Cron будет выполнять эту задачу каждый день в 6:30 утра, удаляя всё (полные бэкапы, дельты и WAL’ы) кроме копий за последние 10 дней, но оставит как минимум один бэкап до указанной даты, чтобы любая точка после даты попадала в PITR.

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

Ни для кого не секрет что залог здоровой базы – в периодическом восстановлении и проверке целостности данных внутри. Как восстановиться с помощью WAL-G – расскажу в этом разделе, а о проверках поговорим после.

Отдельно стоит отметить что для восстановления на тестовом окружении (всё то, что не production) – нужно использовать Read Only аккаунт в S3, чтобы случайно не перезаписать бэкапы. В случае с WAL-G нужно задать пользователю S3 следующие права в Group Policy (Effect: Allow): s3:GetObject, s3:ListBucket, s3:GetBucketLocation. И, конечно, предварительно не забыть выставить archive_mode=off в файле настроек postgresql.conf, чтобы ваша тестовая база не захотела сбэкапиться по-тихому.

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

Для тех, кто хочет проверять процесс восстановления — ниже подготовлен небольшой кусок bash-магии, чтобы в случае проблем в восстановлении – скрипт упал с ненулевым exit code. В данном примере делается 120 проверок с таймаутом в 5 секунд (всего 10 минут на восстановление), чтобы узнать удалился ли сигнальный файл (это будет означать что восстановление прошло успешно):

После успешного восстановления не забудьте запустить обратно все процессы (pgbouncer/monit и тд).

Проверка данных после восстановления

Обязательно нужно проверить целостность базы после восстановления, чтобы не возникло ситуации с побитой/кривой резервной копией. И лучше делать это с каждым созданным архивом, но где и как – зависит только от вашей фантазии (можно поднимать отдельные сервера на почасовой оплате или запускать проверку в CI). Но как минимум – необходимо проверять данные и индексы в базе.

Для проверки данных достаточно прогнать их через дамп, но лучше чтобы при создании базы у вас были включены контрольные суммы (data checksums):

Для проверки индексов – существует модуль amcheck, sql-запрос к нему возьмём из тестов WAL-G и вокруг выстроим небольшую логику:

Резюмируя

Выражаю благодарность Андрею Бородину за помощь в подготовке публикации и отдельное спасибо за его вклад в разработку WAL-G!

На этом данная заметка подошла к концу. Надеюсь что смог донести легкость настройки и огромный потенциал для применения этого инструмента у вас в компании. Я очень много слышал о WAL-G, но никак не хватало времени сесть и разобраться. А после того как внедрил его у себя – из меня вышла эта статья.

Отдельно стоит заметить, что WAL-G также может работать со следующими СУБД:

В данной инструкции рассмотрены варианты создания резервных копий и восстановления баз СУБД PostgreSQL.

Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.

Создание резервных копий

Базовая команда

pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>

pg_dump users > /tmp/users.dump

Пользователь и пароль

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.

Для начала, создадим каталог, в котором разместим скрипт, например:

Вариант 1. Запуск от пользователя root; одна база.

PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

find $pathB $ -name "*-1[^5].*" -o -name "*-[023]?.*" $ -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date "+%Y-%m-%d").sql.gz

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Вариант 2. Запуск от пользователя postgres; все базы.

find $pathB $ -name "*-1[^5].*" -o -name "*-[023]?.*" $ -ctime +61 -delete

* где /backup — каталог, в котором будут храниться резервные копии; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.

Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, /backup/postgres.

Зададим в качестве владельца файла, пользователя postgres:

chown postgres:postgres /scripts/postgresql_dump.sh

crontab -e -u postgres

* мы откроем на редактирование cron для пользователя postgres.

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Права и запуск

Разрешаем запуск скрипта, как исполняемого файла:

chmod +x /scripts/postgresql_dump.sh

Единоразово можно запустить задание на выполнение резервной копии:

. или от пользователя postgres:

su - postgres -c "/scripts/postgresql_dump.sh"

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

Дамп определенной таблицы

Запускается с опцией -t <table> или --table=<table>:

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

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

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Только схемы (структуры)

Для резервного копирования без данных (только таблицы и их структуры):

pg_dump --schema-only users > /tmp/users.schema.dump

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

pg_dump --schema-only users -n production > /tmp/users.schema_production.dump

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

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

pg_dump users -n production > /tmp/users.production.dump

Только данные

pg_dump --data-only users > /tmp/users.data.dump

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

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

Запускаем pgAdmin - подключаемся к серверу - кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп - выбираем Резервная копия:

В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:

При желании, можно изучить дополнительные параметры для резервного копирования:

После нажимаем Резервная копия - ждем окончания процесса и кликаем по Завершено.

Не текстовые форматы дампа

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

Как и всё, что содержит важные данные, базы данных PostgreSQL следует регулярно сохранять в резервной копии. В Effector Saver реализовано резервное копирование баз PostgreSQL сервера, обеспечивающее надёжное хранение и быстрое восстановление данных.

Тип задачи «Резервное копирование файлов и баз данных» позволяет сделать корректную копию СУБД, не прерывая работу пользователей. Подробнее о задаче «Резервное копирование файлов и баз данных» в справке программы: «О задаче Резервное копирование файлов и баз данных».

В данной статье подробно рассмотрим как восстановить данные PostgreSQL из резервной копии.

Effector saver сохраняет резервные копии PostgreSQL в архивах Zip и 7-Zip. Поэтому, перед восстановлением данных PostgreSQL, распакуйте подлежащую восстановлению резервную копию.

Откроется окно создания новой базы данных.

Во вкладке «Свойства» в поле «Имя», введите наименование новой базы, все остальные поля оставьте без изменений. Нажмите «ОК».

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

На вкладке «Файл» нажмите на кнопку обзора (…), после чего откроется диалоговое окно «Выберите имя выходного файла». Выберите файл резервной копии и нажмите кнопку «Открыть».

Оставьте все параметры восстановления как есть и нажмите «Восстановить». Запустится процесс восстановления базы данных.

После того, как процесс по восстановлению будет удачно завершен, нажмите «Завершено».

Читайте также: