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 — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:

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

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

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

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

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

Создание бэкапа базы 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-файла раз в неделю.

Automated Backup on Windows

Contents

Automated Backup on Windows

Updated to reflect changes from 8.3 to 11

• This method uses pg_dump.exe along with a batch file to call it. This batch file will create a file/directory for each day it is run.

• Keep in mind pg_dump and pg_dumpall are version specific meaning do not use pg_dump from 9.3 to backup version 11.0. The option -i & —ignore-version are ignored

Files needed to run pg_dump & pg_dumpall

• Getting the pg_dump, pg_dumpall binaries one has to extract it from a PostgreSQL Server Install, compile, or download binaries from EDB. There is no package available to get just these files.

• Should download and install the Windows C/C++ runtime libraries from Microsoft for the version Postgresql being used, version 11.0 uses VS-2013

• Go to backup server/location create a Directory called Drive:\PostgresqlBack then create a sub directory called «bin» in the Drive:\PostgresqlBack and place the following files in this directory.

Using pgdump, creates a new file for each day

• Create batch file called something, example is postgresqlBackup.bat. The file must be located in PostgresqlBack directory not the bin folder.

• Open the File then Copy/Paste the following

• Change to something. One idea is use the name of the database.(make sure there are no spaces after the word BACKUP_FILE any spaces will cause this setting not to work.)Setting is the first part of the file name then followed by the date the file was created with the extension .backup

• Change the

  setting above to the correct password for the backup users. (make sure there is no spaces after the word PGPASSWORD any spaces will cause this setting not to work. Description of pgPassword

• Change either to ip address or dns name of the server hosting Postgresql.

• Change to backup user make sure this users has access to database for backup purposes

• Change to the database name being backed up.

• Save the File

• Create Task for the MS Task Scheduler

• Once you have chosen the security context the Task in going to run in, it is advised to change the directory security where the backup is run and the files are stored, as a high level user name and password are stored in plain text.

• Another option is to modify the pg_hba.conf file adding the backup server as a trusted connection

New feature in pg_dump as of 9.1

The option -F d. This changes the backup from creating a single large TAR file, it will now will create a directory and contain individual files for each table.

the pg_dump command looks like this

There are a few advantages to the -F d option. One restores can be made significantly faster as pg_restore has the option to run parallel connections instead of one table at a time. Two its faster to extract specific table(s) to restore when using this option vs TAR format. Three when coping/moving the files off-site and it fails in mid-stream it does not have to restart at the beginning unlike one large TAR file, for large databases this is a big plus.

Using pg_dumpall

It is necessary to use pg_dumpall to get the logins/roles, and schema information as pg_dump does not include this information

This creates a sql script used to restore roles and schema information. Keep in mind if not restoring to the same server settings such TableSpaces are most likely not valid and will error.

Как передать пароль в pg_dump?

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

За исключением того, что после запуска, он ожидает, что я наберу пароль. Я не могу этого сделать, если я запускаю его из cron. Как я могу передать один автоматически?

Создайте .pgpass файл в домашнем каталоге учетной записи, которая pg_dump будет работать как. См. Документацию Postgresql libpq-pgpass для получения подробной информации о формате (включая последний абзац, где объясняется, что он будет игнорироваться, если вы не установите режим в 0600 ).

/ .pgpass с localhost: 5432: mydbname: postgres: mypass Затем chmod 600

Или вы можете настроить crontab для запуска скрипта. Внутри этого скрипта вы можете установить переменную окружения следующим образом: export PGPASSWORD=»$put_here_the_password»

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

И я согласен с Джошуа, используя pg_dump -Fc генерирует наиболее гибкий формат экспорта и уже сжат. Для получения дополнительной информации см. Документацию pg_dump.

Если вы хотите сделать это одной командой:

Для однострочников, таких как миграция базы данных, вы можете использовать —dbname строку подключения (включая пароль), как указано в руководстве по pg_dump

Примечание. Убедитесь, что вы используете опцию —dbname вместо более короткой -d и используете действительный префикс URI, postgresql:// или postgres:// .

Общая форма URI:

Лучшая практика в вашем случае (повторяющаяся задача в cron) не должна выполняться из-за проблем безопасности. Если бы не .pgpass файл, я бы сохранил строку подключения как переменную окружения.

тогда есть в вашем crontab

0 3 * * * pg_dump —dbname=$MYDB | gzip >

Этот лайнер помогает мне при создании дампа одной базы данных.

@Josue Александр Ибарра ответ работает на centos 7 и версии 9.5, если —dbname не передано.

Обратите внимание, что в Windows pgpass.conf файл должен находиться в следующей папке:

если postgresql внутри %APPDATA% папки нет папки, создайте ее.

pgpass.conf содержимое файла что — то вроде:

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

Таким образом, когда я хотел владельца базы данных postgres для резервного копирования своих баз данных каждую ночь, я мог бы создать кронтаб для него: crontab -e -u postgres . Конечно, postgres нужно разрешить выполнять задания cron; таким образом, он должен быть указан /etc/cron.allow или /etc/cron.deny должен быть пустым.

Сделайте резервную копию через ssh с паролем, используя временные учетные данные .pgpass и отправьте на S3:

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

Этот сценарий .pgpass впоследствии удаляет учетные данные, поскольку в некоторых средах пользователь SSH по умолчанию может выполнять sudo без пароля, например, экземпляр EC2 с ubuntu пользователем, поэтому использование .pgpass другой учетной записи хоста для защиты этих учетных данных может быть бессмысленным.

Как подробно описано в этом блоге , существует два способа неинтерактивно предоставлять пароль для утилит PostgreSQL, таких как команда «pg_dump»: с помощью файла «.pgpass» или с помощью переменной среды «PGPASSWORD» .