Postgresql сделать бэкап базы linux
Обновлено: 04.07.2024
pg_dump - это утилита позволяющая делать бэкап базы данных из postgresql. Она сохраняет в файл набор SQL команд которые полностью воссоздают структуру исходной базы данных. Для последующего восстановления из подобного файла достаточно psql которая почти всегда идет в комплекте с самим postgresql.
С помощью pg_dump, вы можете сделать бэкап базы данных в текущий момент времени, и в дальнейшнейм развернуть его без каких либо проблем. Для тех, кто раньше работал с mysql, pg_dump может показаться весьма знакомым. С mysql в поставке идет аналогичная утилита под названием mysqldump .
В этой небольшой статье мы рассмотрим несколько полезных примеров использования pg_dump.
Резервное копирование базы данных
Бэкап одной базы данных
В примере ниже, мы сделаем резервную копию базы данных под названием thebosharu , принадлежащей пользователю bosha и сохраним её в файл thebosharu.sql :
Если вы работаете с базой данных не под тем же пользователем, под которым работаете в системе, то pg_dump спросит пароль к базе данных и после его успешного ввода создаст указанный файл содержащий SQL команды для создания необходимой структуры и копирования данных.
Вот часть дампа моей базы данных, чтобы вы понимали что в нем находится:
Так же можно передать ключ --format=t , чтобы pg_dump запаковал базу данных в tar:
Бэкап всех баз данных
Для того, чтобы сделать резервную копию всех баз данных, нужно использовать другую утилиту - pg_dumpall.
Права доступа могут быть настроены у всех по разному, поэтому бэкап всех баз данных лучше делать из под пользователя postgres .
Бэкап всех баз данных будет содержаться в файле all_databases.sql .
Крайне желательно убедится, что все нужные базы данных были копированы. Для этого все из под того же пользователя postgres посмотрим список всех баз данных:
В моем случае было три базы данных:
Теперь удостоверимся, что все перечисленные базы попали в дамп:
Бэкап определенной таблицы
Для бэкапа определенной таблицы используется параметр --table и следом за ним названием таблицы. Если в базе данных указанная таблица есть в разных схемах, то её можно указать используя параметр --schema .
Восстановление из бэкапа
Восстановление одной базы данных
В процессе восстановления из резервной копии, могут возникнуть ошибки и предупреждения. Их можно игнорировать:
Задача резервного копирования — одна из основных при сопровождении и поддержке PostgreSQL. Для резервного копирования логической схемы и данных можно использовать как встроенные инструменты СУБД, так и внешние. В этой статье мы разберем оба варианта.
В статье будем использовать виртуальный сервер с конфигурацией 2 vCPU, 4 ГБ RAM и 10 ГБ HDD с операционной системой CentOS 8 64-bit.
Теперь прокрутим представление ниже, где находятся настройки сети. Важно, чтобы у сервера был внешний плавающий IP-адрес для доступа извне.
После выбора операционной системы, конфигурации сервера и выполнения сетевых настроек переходим к завершению заказа и нажимаем на кнопку Создать. Через несколько минут сервер будет готов.
Перед началом демонстрации возможностей резервного копирования, мы подготовили PostgreSQL. Для целей наполнения базы данных и создания непрерывного потока записи, развернули там Zabbix (некоторое время назад публиковали о нем статью).
Создание резервных копий и восстановление из командной строки
В этом разделе мы расскажем как сделать дамп базы данных PostgreSQL в консоли при подключении по SSH, разберем синтаксис и покажем примеры использования утилит pg_dump, pg_dumpall, pg_restore, pg_basebackup и wal-g.
Утилита pg_dump
В PostgreSQL есть встроенный инструмент для создания резервных копий — утилита pg_dump. Утилита имеет простой синтаксис:
В простейшем случае достаточно указать имя базы данных, которую в дальнейшем нужно будет восстановить. Резервная копия создается следующей командой:
Если требуется авторизация под определенным пользователем, можно воспользоваться ключом -U:
Ключ -U определяет пользователя, а -W обязывает ввести пароль.
Чтобы сэкономить место на диске, можно сразу же сжимать дамп:
Резервное копирование обычно выполняется по расписанию, например, ежедневно в 3 часа ночи. Нижеприведенный пример скрипта не только выполняет бэкап, но и удаляет все файлы старше 61 дня (за исключением 15-го числа месяца).
Чтобы настроить регулярное выполнение, выполним следующую команду в планировщике crontab:
Чтобы выполнить аналогичную команду на удаленном сервере, достаточно добавить ключ -h:
Ключ -t задает таблицу, для которой нужно создать резервную копию:
При помощи специальных ключей можно создавать резервные копии структуры данных или непосредственно данных:
У утилиты pg_dump также есть ключи для сохранения дампа в другие форматы. Чтобы сохранить копию в виде бинарного файла используются ключи -Fc:
Чтобы создать архив — -Ft:
Чтобы сохранить в directory-формате — -Fd:
Резервное копирование в виде каталогов позволяет выполнять процесс в многопоточном режиме.
Ниже мы перечислим возможные параметры утилиты pg_dump.
- p, plain) — формирует текстовый SQL-скрипт;
- c, custom) — формирует резервную копию в архивном формате;
- d, directory) — формирует копию в directory-формате;
- t, tar) — формирует копию в формате tar.
Утилита pg_dumpall
Утилита pg_dumpall реализует резервное копирование всего экземпляра (кластера или инстанса) базы данных без указания конкретной базы данных на инстансе. По принципу схожа с pg_dump. Добавим, что только утилиты pg_dump и pg_dumpall предоставляют возможность создания логической копии данных, остальные утилиты, рассматриваемые в этой статье, позволяют создавать только бинарные копии.
Чтобы сразу сжать резервную копию экземпляра базы данных, нужно передать вывод на архиватор gzip:
Ниже приведены параметры, с которыми может вызываться утилита pg_dumpall.
Утилита pg_restore
Утилита позволяет восстанавливать данные из резервных копий. Например, чтобы восстановить только определенную БД (в нашем примере zabbix), нужно запустить эту утилиту с параметром -d:
Чтобы этой же утилитой восстановить определенную таблицу, нужно использовать ее с параметром -t:
Также утилитой pg_restore можно восстановить данные из бинарного или архивного файла. Соответственно:
При восстановлении можно одновременно создать новую базу:
Восстановить данные из дампа также возможно при помощи psql:
Если для подключения нужно авторизоваться, вводим следующую команду:
Ниже приведен синтаксис утилиты pg_restore.
<имя_файла> — расположение восстанавливаемых данных.
- p, plain — формирует текстовый SQL-скрипт;
- c, custom — формирует резервную копию в архивном формате;
- d, directory — формирует копию в directory-формате;
- t, tar — формирует копию в формате tar.
Утилита pg_basebackup
Утилитой pg_basebackup можно выполнять резервное копирования работающего кластера баз данных PostgreSQL. Результирующий бинарный файл можно использовать для репликации или восстановления на определенный момент в прошлом. Утилита создает резервную копию всего экземпляра базы данных и не дает возможности создавать слепки данных отдельных сущностей. Подключение pg_basebackup к PostgreSQL выполняется при помощи протокола репликации с полномочиями суперпользователя или с правом REPLICATION.
Для выполнения резервного копирования локальной базы данных достаточно передать утилите pg_basebackup параметр -D, обозначающий директорию, в которой будет сохранена резервная копия:
Чтобы создать сжатые файлы из табличных пространств, добавим параметры -Ft и -z:
То же самое, но со сжатием bzip2 и для экземпляра базы с общим табличным пространством:
Ниже приведен синтаксис утилиты pg_basebackup.
- p, plain — значение для записи выводимых данных в текстовые файлы;
- t, tar — значение, указывающее на необходимость записи в целевую директорию в формате tar.
- f, fetch — включение режима сбора файлов журналов транзакций при окончании процесса копирования;
- s, stream — включение передачи журнала транзакций в процессе создания резервной копии.
Утилита wal-g
Wal-g — утилита для резервного копирования и восстановления базы данных PostgreSQL. При помощи wal-g можно выполнять сохранение резервных копий на хранилищах S3 или просто на файловой системе. Ниже мы разберем установку, настройку и работу с утилитой. Покажем как выполнить резервное копирование в Облачное хранилище S3 от Selectel.
Создадим пользователя для облачного хранилища, учетные данные которого будем потом использовать для сохранения резервной копии. Перейдем в меню Пользователи и нажмем кнопку Создать пользователя:
Дополнительную информацию можно получить в нашей Базе знаний. Первую часть логина изменить нельзя — это идентификатор пользователя в панели управления. Вторая часть логина задается произвольно. Например, 123456_wal-g:
Заполним конфигурационный файл wal-g и изменим его владельца на учетную запись postgres:
Далее настроим автоматизированное создание резервных копий в PostgreSQL и перезагрузим процессы базы данных:
Теперь проверим корректность проведения настроек и загрузим резервную копию в хранилище:
После выполнения процесса резервного копирования, в созданном контейнере появится директория с резервными копиями баз данных:
Такой процесс в продакшене может выполняться при помощи планировщика заданий на регулярной основе.
Утилита pgAdmin
Управлять созданием резервных копий возможно также и в графическом интерфейсе. Для этого мы будем использовать утилиту pgAdmin. Актуальную версию для Windows или другой поддерживаемой ОС можно свободно скачать с официального сайта.
После скачивания утилиту нужно установить и запустить. Она работает в виде веб-приложения через браузер.
После добавления сервера с базой данных, в интерфейсе появляется возможность создания резервной копии. Аналогичным образом здесь же можно выполнить восстановление из резервной копии.
После выполнения команды Backup резервная копия сохраняется в заранее определенную директорию.
Работа с облачной базой данных в панели управления Selectel
В Облачной платформе Selectel есть возможность создавать управляемые базы данных (Managed Databases). Такие БД разворачиваются в несколько кликов мыши, однако, их основные преимущества — автоматическое резервное копирование, отказоустойчивость, быстрое масштабирование и управление различными характеристиками из графического интерфейса. Ниже мы создадим экземпляр управляемой базы данных, создадим резервную копию базы данных на виртуальном сервере и восстановим ее в управляемую базу данных.
Чтобы создать управляемую базу данных, перейдем в меню Базы данных и нажмем кнопку Создать кластер:
Появится форма создания кластера. Здесь можно выбрать версию PostgreSQL, конфигурацию кластера, настройки сети, режим пулинга и размер пула.
Обращаем внимание на блок Резервные копии, в котором указаны частота резервного копирования, время и срок хранения выгрузок. «Под капотом» используется механизм wal-g, о котором мы писали выше.
Автоматическое создание резервных копий отключить нельзя.
Следующий шаг — создание пользователя, от имени которого мы позже будем обращаться к базе данных. Для этого перейдем на вкладку Пользователи и нажмем на кнопку Создать пользователя.
После этого появится приглашение ввести имя пользователя и пароль. После ввода этих данных нажимаем Сохранить.
Пользователь создан и отображается в списке пользователей.
Теперь создадим базу данных. Для этого перейдем на вкладку Базы данных и нажмем на кнопку Создать базу данных.
Заполняем необходимые поля и нажимаем кнопку Сохранить.
База данных создана и отображается в списке баз данных.
Теперь проверим возможность подключения. Для этого откроем консоль и вводим реквизиты:
В консоли должно появиться приглашение к вводу SQL-запроса или других управляющих команд.
Выполним резервное копирование при помощи команды pg_dump:
И следом резервное восстановление в созданную управляемую базу данных:
В результате выполнения команды выше мы восстановили резервную копию в управляемую базу данных.
Чтобы воспользоваться восстановлением из резервной копии, которая автоматически создается на платформе Selectel, необходимо нажать на символ с тремя точками. В открывшемся меню нужно нажать на опцию Восстановить. После этого появится модальное окно, в котором можно выбрать резервную копию, а также дату и время, на которое нужно восстановить базу данных. Это так называемый Point-in-Time Recovery из WAL-файлов.
Услуга «Управляемые базы данных в облаке» позволяет перенести существующий кластер PostgreSQL на сервис управляемых баз данных бесшовно и без простоя, обратившись в техническую поддержку. Инженеры Selectel готовы помочь с переносом, а также проконсультировать по всем связанным с этим процессом вопросам.
Заключение
Мы рассмотрели возможности выполнения резервного копирования и показали отличия утилит pg_dump, pg_dumpall, pg_restore, pg_basebackup и wal-g. Вы увидели как можно создать управляемую базу данных, чтобы переложить часть административных задач на облачного провайдера.
Узнать подробнее об управляемых базах данных можно в документации Selectel.
Базовая команда
pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>
Пример (для дампа одной бд зайдите под su postgres):
pg_dump bd > /tmp/bd.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> или --table=<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_dump -Fc users > users.bak
pg_dump -Ft users > users.tar
pg_dump -Fd users > users.dir
Использование pg_basebackup
pg_basebackup позволяет создать резервную копию для кластера PostgreSQL.
pg_basebackup -h node1 -D /backup
* в данном примере создается резервная копия для сервера node1 с сохранением в каталог /backup.
pg_dumpall
Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта.
Утилиту удобно использовать с ключом -g (--globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).
Восстановление
Может понадобиться создать базу данных. Это можно сделать SQL-запросом:
* где users — имя базы; UTF-8 — используемая кодировка.
Базовая команда
psql <имя базы> < <файл с дампом>
psql users < /tmp/users.dump
С авторизацией
При необходимости авторизоваться при подключении к базе вводим:
psql -U dmosk -W users < /tmp/users.dump
* где dmosk — имя учетной записи; опция W потребует ввода пароля.
Из файла gz
Сначала распаковываем файл, затем запускаем восстановление:
psql users < users.dump
Или одной командой:
zcat users.dump.gz | psql users
Определенную таблицу
Если резервная копия делалась для определенной таблицы, можно просто запустить восстановление:
psql users < /tmp/students.dump
Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t:
pg_restore -a -t students users.dump
С помощью pgAdmin
Запускаем pgAdmin - подключаемся к серверу - кликаем правой кнопкой мыши по базе, для которой хотим восстановить данные - выбираем Восстановить:
Выбираем наш файл с дампом:
И кликаем по Восстановить:
Использование pg_restore
Данная утилита предназначена для восстановления данных не текстового формата (в одном из примеров создания копий мы тоже делали резервную копию не текстового формата).
pg_restore -Fc users.bak
pg_restore -Ft users.tar
С создание новой базы:
pg_restore -Ft -C users.tar
Возможные ошибки
Input file appears to be a text format dump. please use psql.
Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore.
Решение: восстановить данные можно командой psql <имя базы> < <файл с дампом> или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор.
No matching tables were found
Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.
Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.
Too many command-line arguments
Причина: Утилита pg_dump чувствительна к лишним пробелам.
Решение: проверьте, что нет лишних пробелов.
Aborting because of server version mismatch
Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.
Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql/<version>/bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.
PostgreSQL является современной системой управления базами данных, часто используемая для хранения и обработки информации, связанной с веб-сайтами или сторонними приложениями. У любой базы данных, важно вовремя реализовать резервное копирование, чтобы избежать возможную потерю данных.
В этом посте я постараюсь рассказать о некоторых способах, которыми вы можете сделать резервную копию PostgreSQL. Для тестов будем использовать Ubuntu 12,04 VPS с PostgreSQL 9.1. Для большинства современных дистрибутивов и последних версии PostgreSQL мои советы будут актуальны.
- Создание резервной копии PostgreSQL при помощи pg_dump
Команда должна быть запущена под пользователем с привилегиями на чтение базы данных.
Расширенный синтаксис выглядит следующим образом:
- Как восстановить дампы pg_dump в PostgreSQL
Эта операция не создает новую базу данных. Об этом необходимо позаботиться заранее.
По умолчанию, PostgreSQL будет пытаться продолжить восстановление базы данных, даже если он сталкнется с ошибками. По многим причинам так делать не стоит. Мы можем явно указать PostgreSQL на остановку, в случае любой ошибки:
С данной опцией мы получим частично восстановленную базу данных.
При этом любая ошибка приведет к откату процесса восстановления, что может потребовать достаточно продолжительного времени.
- Резервное копирование и восстановление всех баз данных в PostgreSQL
Похожим способом можно восстановить базы данных:
Резервные копии являются важным аспектом при любой работе с данными. К счастью, PostgreSQL включает утилиты, необходимые для эффективного резервного копирования важной информации. Как с любым видом резервного копирования, важно регулярно проверять свои резервные копии.
В качестве дополнения скрипт, который создает резервную копию с меткой времени и сохраняет последние 14 резервных копий:
Читайте также: