Как создать архив базы PostgreSQL

Данный урок посвящен архивированию базы данных СУБД PostgreSQL или, проще говоря «Созданию Backup базы». Сегодня мы научимся создавать архивы двумя способами это через графический интерфейс pgAdmin и через командную строку.

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

Если говорить о MSSql то на этом сайте уже затрагивалась тема восстановления базы в статье Как восстановить базу данных из архива — MS SQL Server 2000.

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

Примечание! Все примеры будут выполняться на локальном сервере под управлением операционной системе Windows 7 профессиональная. Версия PostgreSQL 8.4. Версия pgAdmin 1.10.1.

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

Создаем архив базы через графический интерфейс pgAdmin

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

Открываем pgAdmin, подключаемся к серверу, плюсиком раскрываем базы, и выбираем правой кнопкой мыши ту базу, которую хотим заархивировать (в нашем случае test), и жмем «Резервная копия»

Затем у Вас откроется окно, где Вы можете задать настройки backup, я выставил следующие настройки, в принципе эти настройки по умолчанию, единственное необходимо выбрать папку, т.е. задать путь к ней, в которую будет создаваться архив нашей базы данных, для примера я ввел диск C папка temp и задать имя будущего архива, я назвал test_ backup. Далее жмем «ОК»

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

Создаем архив базы через командную строку

Данный способ понадобится Вам в случае быстрого запуска процесса архивирования и для автоматизации этого процесса, путем написания батника и создания задания в «Планировщике заданий». Поэтому еще раз запускать эту команду не будем через командную строку, а сразу напишем батник. Что это такое можете почитать в статье – Написание bat файлов — Примеры батников.

Для этого мы просто скопируем ту строку, которая показана на предыдущем скриншоте, но с небольшими изменениями, а именно, мы путь к pg_dump.exe напишем с кавычками, так как в данном пути присутствуют папки с названием из двух слов с пробелами (Program Files) и для того чтобы потом отличать этот архив от других, предполагается, что допустим он должен выполняться ежедневно, добавим к названию нашего архива текущую дату, используя системную переменную %date%. В итоге у нас должно получиться вот так:

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

Остальное я думаю все понятно, сначала мы запускаем специальную утилиту (первая строка), затем подключаемся к серверу (host, port, username – вторая строка) и в третей строке мы уже создаем backup с параметрами, указываем путь, название архива и только в самом конце название базы данных которую необходимо заархивировать (test).

Теперь все это копируем в блокнот, советую пользоваться Notepad++, и сохраняем с любым названием, но с расширением .bat, т.е. командный файл. И все, теперь, для ежедневного автоматического создания архива настраиваете, планировщик Windows и у Вас каждый день будет создаваться backup.

На сегодня это все, ничего сложного в создании архива базы PostgreSQL нет, поэтому проблем возникнуть не должно. Удачи!

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

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

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

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

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

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

for dbname in `echo «SELECT datname FROM pg_database;» | psql | tail -n +3 | head -n -2 | egrep -v ‘template0|template1|postgres’`; do
pg_dump $dbname | gzip > $pathB/$dbname-$(date «+%Y-%m-%d»).sql.gz
done;

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

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

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

chown postgres:postgres /scripts/postgresql_dump.sh

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /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= :

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

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

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

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

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

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

Читайте также:  Не могу установить windows efi

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

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

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

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

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

Создание бэкапа базы PostgreSQL для Windows

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

Справочную информацию о командах, испульзуемых в этом файле можно получить из командной строки набрав следующую команду: «[Имя команды] /?»
Многие использованные здесь команды достаточно распространены и известны, поэтому хочется акцентировать внимание на нескольких менее известных.

Строки 15, 16 выполняют переход в папку в которой находится файл «backup.bat». «%0» возвращает имя bat-файла; «%

dp0″ возвращают соответственно диск и путь к bat-файлу. Подробные сведения о работе с параметрами файла можно посмотреть по этой ссылке.

В строке 19 формируется строковое представление даты и времени в нужном формате. При формировании происходит обращение к переменным окружения DATE и TIME, которые хранят текстовое представление даты и времени соответственно. После имени переменной указывается строка вида «:

m,n», где m — позиция в строке, n — количество символов.

В строке 27 вызывается утилита резервного копирования pg_dump.exe. Вызов выполняется с применением команды CALL, это позволяет дождаться завершения утилиты и проанализировать результат выполнения. Вызов утилиты завершается строкой «2>%LOGPATH%». Эта строка означает что поток ошибок STDERR, номер которого 2, приложения pg_dump.exe перенаправляется в файл, имя которого сохранено в переменной окружения LOGPATH. Так как приложение pg_dump.exe выводит все сообщения в стандартный поток ошибок, то в файле LOGPATH будет сохранен подробный отчет о выполнении резервного копирования.

В строках 37 и 42 выполняется перенаправление вывода в файл backup.log. Перенаправление осуществляется оператором «>>». Различие между операторами «>» и «>>» в том, что первый каждый раз создает новый файл, затирая ранее записанные данные, а второй — дописывает данные в существующий файл. Таким образом можно вести журнал с подробными сведениями о результатах резервного копирования.

Проверяем как работает bat-файл. Если дампы базы создаются, то можно приступать к созданию задачи для планировщика заданий Windows.
Создаем задание, которое будет запускать bat-файл каждый день в ночное время.

Ежедневные бэкапы со временям породят проблему свободного пространства на жестком диске. Можно чистить ручками, но лучше уж автоматизацию сделать полной. Решается этот вопрос также созданием bat-файла и задачи в планировщике заданий Windows.

Содержимое bat-файла такое:

Здесь указана команда при выполнении которой будут удаляться файлы старше 5 дней.
В планировщике заданий можно создать задачу на исполнения этого bat-файла раз в неделю.