Бэкап postgresql по расписанию linux

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 в Linux

О том как делать автоматические резервные копии в PostgreSQL рассказано в статье Automated Backup on Linux. Скрипты из этой статьи всем хороши, кроме того, что если их прописать в cron, то ничего бэкапиться не будет. А происходит так из-за того, что нигде не указывается пароль пользователя PostgreSQL от имени которого делается бэкап.

Для того чтобы эти скрипты заработали в автоматическом режиме нужно в самый конец pg_backup.config добавить:
# Set PGUSER and PGPASSWORD
PGUSER=»postgres»
PGPASSWORD=»password»
export PGUSER PGPASSWORD
# End

Естественно, вместо пользователя postgres можно использовать любого нужного пользователя, а вместо password нужно указать пароль этого пользователя.

А в целях безопасности в самом конце pg_backup.sh нужно вставить:
# Unset PGUSER and PGPASSWORD
PGUSER=»»
PGPASSWORD=»»
export PGUSER PGPASSWORD
# End

Теперь можно сделать файлы исполняемыми:
chmod u+x pg_backup.sh pg_backup_rotated.sh

(для проверки работоспособности нужно выполнить bash pg_backup.sh)

Прописать их в cron пользователя postgres (на примере Ubuntu):
sudo crontab -u postgres -e

В файл нужно добавить строки:
SHELL=/bin/bash
15 3 * * * /path/to/script/pg_backup.sh > /dev/null 2>&1

Надо напоминать, что /path/to/script/ — это путь к директории с файлом?

Теперь ждём 03:15 утра и проверяем работоспособность.

Автоматический бэкап PostgreSQL в Linux: 6 комментариев

А почему бы не сделать по-элегантнее, без «засветки» пароля, например так:
1. Определим user map для root. Для этого добавим в файл $PGDATA/pg_ident.conf строчку :
root root postgres
2. Добавим этот user map в соответствующую запись в файле $PGDATA/pg_hba.conf (приписать опцию map=root в соответсвующую строку идентификации), например:
local all postgres peer map=root
3. Рестарт Postgres:
service postgresql-9.3 restart
После этого, все клиенты (тот же psql ), запускаемые под акком root , будут коннектиться к PostgresQL как postgres . Все должно запахать, и нигде никаких паролей указывать не надо.
Не нравится вам root – тоже самое можно проделать для любого системного акка, под которым вы хотите запускать бэкап скрипт.

Роман, спасибо за красивое решение! Постараюсь опробовать его и отписаться о том что получилось.

А не сделал я так из-за того, что я по роду деятельности не системный администратор и многого ещё не знаю — я только учусь (с) 🙂

Ребят, подскажите пожалуйста, как настроить все то же самое, только с выгрузкой backup-а на сторонний FTP сервер, а не локально на машину.

Или проще выгрузить dump локально и через отдельный скрипт загружать его на FTP?

Rolex, я вижу несколько вариантов:

1. Взять скрипты из пакета backup-manager ( apt-get install backup-manager ) и либо позаимствовать оттуда код отправки файлов на FTP, либо использовать скритпы из пакета для этой цели, только учти, что пакет уже давно не поддерживается, однако, стабильно работает.
2. Использовать вместо FTP SSH, что проще ( нужно добавить в существующий скрипт только одну строчку scp) и надёжнее.

Не думаю, что для кого-то открыл америку, но вот еще один вариант для создания бэкапов. Через утилиту barman

Источник

10 способов сделать резервную копию в PostgreSQL

Если рассматривать резервное копирование как вполне конкретный процесс, то возникает два простых вопроса:
1. откуда запускать резервное копирование?
2. какие инструменты следует использовать для резервного копирования?

На первый вопрос есть два варианта ответа: можно запускать задачу резервного копирования с выделенного backup сервера, на мой взгляд это наиболее подходящий вариант. Либо запускать задачу непосредственно с сервера БД, это в случае если нет выделенного сервера бэкапов.

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

В комплекте PostgreSQL есть 2 утилиты которые позволяют делать резервные копии, это pg_dump/pg_dumpall и pg_basebackup. Кроме того есть возможность использовать утилиты файлового копирования, такие как rsync, tar, cp и т.п.
Итак, каким инструментом запускать бэкап?
pg_dump — подходит для случаев когда нужно сделать резервную копию таблицы, базы, схемы или данных.
pg_basebackup — подходит для случаев когда нужно сделать резервную копию целиком всего кластера БД или настроить hot standby реплику.
rsync/tar/cp — также используются для случаев копирования всего кластера.

Когда только случился релиз PostgreSQL 9.0 резервное копирование выполнялось с помощью rsync, однако уже в 9.1 появился pg_basebackup, который имеет некоторыми преимуществами перед rsync:

• pg_basebackup не требует ssh доступа, но требует доступа к базе указанного в pg_hba.conf;
• pg_basebackup богаче по функциональности (копирование WAL, создание recovery.conf, встроенное сжатие gzip и пр.);
• pg_basebackup не требует отдельного вызова функций pg_start_backup/pg_stop_backup как это требуется при использовании rsync/tar/cp;
• pg_basebackup выполняет копирование быстрее чем rsync за счет использования протокола потоковой репликации.

но есть и некоторые недостатки:

• pg_basebackup идет out-of-the-box, и соответственно требует установленного postgres;
• pg_basebackup не имеет встроенных функций для ограничения скорости копирования (обещают только в 9.4);
• pg_basebackup требует включенных опций wal_level = hot_standby, max_wal_senders в postgresql.conf.

Здесь я буду рассматривать pg_basebackup, хотя и pg_dump тоже может использоваться в нижеперечисленных способах.

1. Простое и без изысков резервное копирование с backup сервера в каталог /backup (каталог должен быть предварительно создан):

2. Копирование с пониженным приоритетом IO операций с помощью ionice, для случаев когда нужно уменьшить нагрузку на дисковый ввод-вывод от резервного копирования:

3. Копирование с сжатием в bzip2, для случаев когда нужно использовать нестандартный для pg_basebackup алгоритм сжатия (gzip). Здесь мы передаем данные через стандартный вывод (stdout) на стандартный ввод (stdin) программе bzip2.

4. Копирование с сжатием в несколько потоков (используем lbzip2 и задействуем 6 ядер). При таком раскладе можно задействовать простаивающие ядра и ускорить процесс сжатия.

5. Здесь копирование запускается на сервере БД. Формируемая резервная копия отправляется на удаленный сервер по ssh.

6. Здесь копирование также запускается на сервере БД и выполняется отправка на удаленный сервер, но уже с архивированием в 6 потоков с помощью lbzip2.

7. Копирование на удаленный сервер с ограничением пропускной полосы до 10Мб с помощью pv и последующее архивирование на удаленной стороне. Этот вариант для случаев когда нужно передать не нагружая сеть.

Тут стоит отметить что c 9.4 в pg_basebackup уже есть возможность ограничения скорости передачи (-r, —max-rate).
8. Копирование запускается на backup сервере, а далее происходит раздваивание потока на две части. Один поток сжимается с bzip2 (сам бэкап) и второй поток через tar копируется во временный каталог для последующей валидации. Способ редкоиспользуемый, но тут интересна сама реализация.

9. Копирование с задействование lbzip2 на обоих узлах, для случаев когда у сети маленькая пропускная способность, сначала поток сжимается, затем передается по сети и затем расжимается на удаленной стороне. Здесь используется tar и требуется выполнение pg_start_backup(‘label_name’) на стороне postgres.

10. бэкапирование с шифрованием через GPG, для случаев когда нужно зашифровать резервную копию. Предварительно следует создать ключи через gpg —gen-key (в моем случае ключи созданы с именем backup)

Для расшифровки резервной копии следует выполнить такую команду

На этом все, подведем итоги по инструментам:

• pg_basebackup — утилита для создания резервных копий postgres;
• lbzip2 — bzip2 сжатие с использованием несокльких ядер — если нужно запаковать быстрее (аналоги: pbzip2, pigz);
• ionice — регулировка класса и приоритета для планировщика ввода-вывода (также можно использовать nice для регулировки приоритета процессов для CPU планировщика);
• pv — контролируем объем передаваемых данных через pipe и т.о. используем для ограничения объема передаваемых данных в единицу времени (аналог — throttle);
• tar — утилита архивирования, нужна для вспомогательных целей когда неиспользуется сжатие bzip2/gzip;
• tee — чтение с stdin c записью в stdout и другие файлы (является частью coreutils);
• gpg — решает задачи по шифрованию.

Всем спасибо за внимание!

Источник