Резервное копирование 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
Скрипт для автоматического резервного копирования
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 — имя учетной записи для подключения к БУБД.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.
Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:
3 0 * * * /scripts/postgresql_dump.sh
* наш скрипт будет запускаться каждый день в 03:00.
На удаленном сервере
Если сервер баз данных находится на другом сервере, просто добавляем опцию -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 — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:
В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:
При желании, можно изучить дополнительные параметры для резервного копирования:
После нажимаем Резервная копия — ждем окончания процесса и кликаем по Завершено.
Не текстовые форматы дампа
Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные.
Как передать пароль в дамп pg?
Я пытаюсь создать cronjob для резервного копирования моей базы данных каждую ночь, прежде чем произойдет что-то катастрофическое. Похоже, эта команда должна удовлетворить мои потребности:
кроме как после запуска этого, он ожидает, что я введу пароль. Я не смогу этого сделать, если буду работать с Кроном. Как я могу передать его автоматически?
10 ответов
создать .pgpass файл в домашнем каталоге учетной записи, который pg_dump будет работать как. См. документацию Postgresql libpq-pgpass для деталей формата (включая последний абзац, где он объясняет, что он будет проигнорирован, если вы не установите режим 0600 ).
или вы можете настроить crontab для запуска скрипта. Внутри этого скрипта вы можете установить переменную окружения такой: export PGPASSWORD=»$put_here_the_password»
таким образом, если у вас есть несколько команд, которые потребуют пароль, вы можете поместить их все в сценарии. Если пароль меняется, вам нужно изменить его только в одном месте (скрипт).
и я согласен с Джошуа, используя pg_dump -Fc создает наиболее гибкий формат экспорта и уже сжат. Для получения дополнительной информации см.:помощью pg_dump документация
Если вы хотите сделать это в одну команду:
для однострочного, как миграция базы данных вы можете использовать —dbname затем следует строка подключения (включая пароль), как указано в руководство помощью pg_dump
Примечание: убедитесь, что вы используете —dbname вместо более короткого -d и используйте допустимый префикс URI, postgresql:// или postgres:// .
общая форма URI есть:
Лучшая практика в вашем случае (повторяющаяся задача в cron) это не должно быть сделано из-за проблем безопасности. Если бы не . —7—> файл я бы сохранил строку подключения в качестве переменной среды.
тогда имейте в своем crontab
0 3 * * * pg_dump —dbname=$MYDB | gzip >
@Josue Alexander Ibarra ответ работает на centos 7 и версии 9.5 если —dbname не передается.
исправьте меня, если я ошибаюсь, но если системный пользователь такой же, как пользователь базы данных, PostgreSQL не будет запрашивать пароль — он полагается на систему для аутентификации. Это может быть вопросом конфигурации.
таким образом, когда я хотел владельца базы данных postgres для резервного копирования его баз данных каждую ночь, я мог бы создать crontab для него: crontab -e -u postgres . Конечно, postgres должно быть разрешено выполнять задания cron; таким образом, он должен быть указан в /etc/cron.allow или /etc/cron.deny должен быть пустой.
резервное копирование через ssh с паролем с использованием временного .учетные данные pgpass и нажмите на S3:
просто замените первые пару строк конфигурации тем, что вам нужно — очевидно. Для тех, кто не заинтересован в резервной части S3, выньте ее — очевидно.
этот скрипт удаляет учетные данные в .pgpass затем, потому что в некоторых средах пользователь SSH по умолчанию может sudo без пароля, например экземпляр EC2 с ubuntu пользователь, поэтому используя .pgpass С другой учетной записью хоста для защиты этих учетных данных может быть бессмысленным.
другой (вероятно, не безопасный) способ передать пароль — использовать перенаправление ввода, т. е. вызов
самый простой способ, на мой взгляд, это: вы редактируете главный файл конфигурации postgres: pg_hba.conf там вы должны добавить следующую строку:
host 127.0.0.1/32 trust
и после этого вам нужно начать КРУН таким образом:
Создание бэкапа базы 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-файла раз в неделю.