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

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

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

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

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

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

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 — решает задачи по шифрованию.

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

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

Резервное копирование и восстановление в PostgreSQL. Существуют три принципиально различных подхода к резервному копированию данных PostgreSQL:

Восстановление после сбоя

Если работа сервера аварийно завершается, в логе сервера появляется сообщение с уровнем важности PANIC.

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

В логе WAL есть контрольный точки (checkpoints).

Логическое резервирование (SQL)

Ключи pg_dump:

Логическое резервирование заключается в создании текстового файла с командами SQL. Такой файл можно передать обратно на сервер и воссоздать базу данных в том же состоянии, в котором она была во время бэкапа. У PostgreSQL для этого есть специальная утилита — pg_dump. При выполнении pg_dump, таблицы блокируются минимально, только запрет на изменение структуры таблицы.

базу данных «dbname» потребуется создать перед восстановлением и пользователя (которому принадлежит восстанавливаемая база данных)

Пример

Пример: Полное логическое (SQL) резервирование и восстановление БД mbillcz5054. Алгоритм:

Если вы хотите исключить какие-либо таблицы из дампа, не забудьте сделать схему этой таблицы, чтобы в будущем Вы ее могли восстановить на новом сервере, например исключим таблицу cdr из дампа и создадим ее схему:

Скрипт для резервирования БД

Запускаем еженедельно при помощи Anacron (если установлено) или Использование планировщика cron в Linux, для этого создаем символическую ссылку в директорию /etc/cron.weekly

Дополнительная информация по изучению PostgreSQL

Обучение PostgreSQL. Полный курс по работе с базой данных PostgreSQL!

Курс включает в себя все инструменты: управление доступом, резервное копирование, репликация, журналирование, работа со статистикой, способы масштабирование, а также работа PostgreSQL в облаках AWS, GCP, Azure и в Kubernetes. Проверь свои знания — пройди тестирование

Крупнейшая в Европе школа английского языка

Промокоды, акции и подарки, чтобы Ваше обучение было не только интересным, но и выгодным. Закажите пробный урок уже сейчас!

Онлайн школа английского языка

Английский по скайпу от 680р за урок, без заучивания правил. Эффективно! Удобно! Выгодно! Начните обучение прямо сейчас.

Школа английского языка по Skype

Персональные занятия по разумным ценам. Бесплатные ресурсы для студентов: разговорные клубы, блог, вебинары, книги, тест на определение уровня английского. Пробный урок бесплатно!