Раздел помощи SpaceWeb

Дампы в PostgreSQL: резервное копирование и восстановление

25 июл, 2024

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

Резервное копирование и восстановление данных – это ключевые аспекты управления базами данных, которые обеспечивают защиту информации.

PostgreSQL, одна из самых популярных реляционных СУБД с открытым исходным кодом, предоставляет множество инструментов и методов для резервного копирования и восстановления данных. В этой статье рассмотрим основные подходы и инструменты, которые доступны в PostgreSQL.

Создание резервных копий и восстановление 

Резервное копирование данных (бэкап) подразумевает создание копии данных, которую можно использовать для их восстановления в случае потери, повреждения БД или других сбоев. 

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

Логический бэкап – это копия данных, которая создана на уровне SQL-запросов. В этом методе данные экспортируются из базы данных в виде SQL-скриптов или других форматов.

Он позволяет гибко восстанавливать данные, отдельные таблицы или схемы и поддерживает переносимость между различными версиями PostgreSQL. Для создания логических резервных копий используют pg_dump, pg_dumpall и pgAdmin о которых мы поговорим позже. 

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

Основной инструмент создания физического бэкапа – это pg_basebackup, но его так же можно создать с помощью wal-g.

Как создать резервные копии в PostgreSQL

Способ 1. pg_dump 

pg_dump – это утилита командной строки PostgreSQL, которая предназначена для создания логических резервных копий баз данных. Она экспортирует данные и структуру БД в формате SQL-скриптов или в других форматах.

Пользователь может выбрать формат дампа по своему усмотрению: это может быть либо архив, либо скрипт – текстовый файл, который содержит список SQL-команд. 

Восстановление базы данных с помощью скрипта можно выполнить несколькими способами:

  • в консольном клиенте PostgreSQL;
  • с помощью команды psql:
psql [имя БД] < [SQL-скрипт PostgreSQL]

Что лучше: скрипт или архив? Всё зависит от вашей базы данных и цели резервного копирования. Если необходимо перенести базу данных на другой сервер PostgreSQL, подойдёт скрипт. Архивы удобны тем, что их можно переносить на другие платформы, а восстановление с помощью pg_restore позволяет настраивать процесс благодаря различным параметрам утилиты.

Синтаксис:

pg_dump [параметры для подключения] [параметры дампа] [имя БД] > [каталог]

Важно! Если имя базы данных не указано, то система автоматически задаст значение переменной окружения PGDATABASE. Если PGDATABASE не присвоено значение, она укажет имя пользователя, который запускает утилиту.

Параметры для подключения:

Параметр

Описание

-d <имя_бд>, --dbname=имя_бд

Подключается к указанной базе данных.

-h <сервер>, --host=сервер

Устанавливает имя сервера.

-p <порт>, --port=порт

Указывает порт для подключения.

-U <пользователь>, --username=пользователь

Использует указанную учётную запись для подключения.

-w, --no-password

Отключает запрос пароля.

-W, --password

Включает запрос пароля.

--role=имя роли

Задаёт роль, от имени которой генерируется резервная копия.

Параметры создания резервной копии:

Параметр

Описание

-a, --data-only

Выводит только данные, исключая схемы объектов (DDL).

-b, --blobs

Включает большие объекты в выгрузку.

-c, --clean

Добавляет команды DROP перед командами CREATE в файл резервной копии.

-C, --create

Генерирует команды для создания базы данных в файле резервной копии.

-E <кодировка>, --encoding=кодировка

Определяет кодировку резервной копии.

-f <файл>, --file=файл

Задаёт имя файла для сохранения вывода утилиты.

-F <формат>, --format=формат

Определяет формат резервной копии:

  • p (plain) – формирует текстовый SQL-скрипт;
  • c (custom) – формирует резервную копию в архивном формате;
  • d (directory) – формирует копию в directory-формате;
  • t (tar) – формирует копию в формате tar.


 

-j <число_заданий>, --jobs=число_заданий

Активирует параллельную выгрузку для одновременной обработки нескольких таблиц (равной числу заданий). Работает только при выгрузке копии в формате directory.

-n <схема>, --schema=схема

Выгружает в файл копии только указанную схему.

-N <схема>, --exclude-schema=схема

Исключает из выгрузки указанные схемы.

-o, --oids

Включает идентификаторы объектов (OIDs) в выгрузку вместе с данными таблиц.

-O, --no-owner

Отключает создание команд, которые определяют владельцев объектов в базе данных.

-s, --schema-only

Добавляет в выгрузку только схемы данных, без самих данных.

-S <пользователь>, --superuser=пользователь

Использует указанную учетную запись привилегированного пользователя для отключения триггеров.

-t <таблица>, --table=таблица

Выгружает только указанную таблицу.

-T <таблица>, --exclude-table=таблица

Исключает из выгрузки указанную таблицу.

-v, --verbose

Включает режим подробного логирования.

-V, --version

Выводит версию pg_dump.

-Z 0..9, --compress=0..9

Устанавливает уровень сжатия данных (0 – сжатие выключено).

Пример:

pg_dump -U sweb -h localhost -p 5432 -d swebdb -F c -b -f /path/to/backup/mydb.backup

В этом примере:

  • «-U sweb» – имя пользователя.
  • «-h localhost» – сервер (хост).
  • «-p 5432» – порт.
  • «-d swebdb» – имя базы данных.
  • «-F c» – формат дампа (custom).
  • «-b» указывается на включение больших объектов.
  • «-f /path/to/backup/mydb.backup» – файл, в который будет сохранена резервная копия

Важно! pg_dump создаёт дамп только одной базы данных. Если у вас есть глобальные объекты PostgreSQL, необходимо использовать утилиту pg_dumpall, которую мы рассмотрим далее.
 

Способ 2. pg_dumpall 

pg_dumpall – это утилита командной строки PostgreSQL, которая предназначена для создания резервных копий всех баз данных на сервере PostgreSQL, включая глобальные объекты: роли и настройки конфигурации. 

В отличие от pg_dump, который работает с отдельными базами данных, pg_dumpall охватывает весь сервер базы данных, обеспечивая полный снимок состояния всех БД.

Результат работы pg_dumpall – текстовый файл с SQL-скриптами, который можно использовать для восстановления всех баз данных и глобальных объектов на сервере.

Синтаксис:

pg_dumpall [параметры для подключения и создания резервной копии] > [каталог]

Параметры для подключения

Параметр

Описание

-d <имя_бд>, --dbname=имя_бд

Указывает имя базы данных.

-h <сервер>, --host=сервер

Устанавливает имя сервера.

-p <порт>, --port=порт

Указывает TCP-порт для подключения.

-U <пользователь>, --username=пользователь

Использует указанную учетную запись для подключения.

-w, --no-password

Отключает запрос пароля.

-W, --password

Включает запрос пароля.

--role=<имя роли>

Задаёт роль, от имени которой генерируется резервная копия.

Параметры создания резервной копии

Параметр

Описание

-a, --data-only

Создаёт резервную копию без схемы данных.

-c, --clean

Добавляет операторы DROP перед операторами CREATE.

-f <имя_файла>, --file=имя_файла

Направляет вывод в указанный файл.

-g, --globals-only

Выгружает глобальные объекты без баз данных.

-o, --oids

Выгружает идентификаторы объектов (OIDs) вместе с данными таблиц.

-O, --no-owner

Отключает генерацию команд, которые устанавливают владельцев объектов.

-r, --roles-only

Выгружает только роли без баз данных и табличных пространств.

-s, --schema-only

Выгружает только схемы данных.

-S <имя_пользователя>, --superuser=имя_пользователя

Использует имя суперпользователя для отключения триггеров.

-t, --tablespaces-only

Выгружает только табличные пространства без баз данных и ролей.

-v, --verbose

Включает режим подробного логирования.

-V, --version

Выводит версию утилиты pg_dumpall.

Пример:

Допустим, вам нужно создать резервную копию всех баз данных на сервере PostgreSQL и сохранить результат в сжатом формате. Вот как это можно сделать:

pg_dumpall -U sweb -h localhost -p 5432 -a -W | gzip > /path/to/backup/all_databases_data_only.sql.gz

Здесь:

  • «-U sweb» указывает имя пользователя для подключения.
  • «-h localhost» задает имя сервера (хоста).
  • «-p 5432» указывает порт для подключения.
  • «-a» создаёт резервную копию только данных, исключая схемы объектов (DDL).
  • «-W» активирует запрос пароля для подключения к серверу.
  • «| gzip» направляет вывод утилиты pg_dumpall на вход утилиты gzip для сжатия.
  • «> /path/to/backup/all_databases_data_only.sql.gz» указывает путь и имя файла для сохранения сжатой резервной копии.

Способ 3. pgAdmin 

pgAdmin – это графический интерфейс для управления PostgreSQL, который предоставляет удобные инструменты для выполнения различных задач, включая администрирование баз данных, создание запросов и управление резервными копиями.

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

  1. Запустите pgAdmin и подключитесь к серверу.
  2. Кликните правой кнопкой мыши на базе данных, которую хотите сохранить.
  3. Выберите «Резервная копия».

  1. В открывшемся окне укажите путь для сохранения резервной копии и выберите нужный формат. 
  2. При необходимости изучите и настройте дополнительные параметры для резервного копирования.
  3. Нажмите «Резервная копия», дождитесь завершения процесса. 

Способ 4. pg_basebackup

pg_basebackup – это утилита командной строки PostgreSQL, которая предназначена для создания физической резервной копии всего кластера базы данных. Она копирует все данные, необходимые для восстановления БД в точное состояние на момент создания бэкапа. 

Синтаксис:

pg_basebackup [параметры для подключения] [параметры создания резервной копии]

Параметры для подключения

Параметр

Описание

-d <строка_подключения>, --dbname=строка_подключения

Определяет базу данных в виде строки для подключения.

-h <сервер>, --host=сервер

Указывает имя сервера с базой данных.

-p <порт>, --port=порт

Указывает TCP-порт, через который база данных принимает подключения.

-s <интервал>, --status-interval=интервал

Устанавливает количество секунд между отправками статусных пакетов.

-U <пользователь>, --username=пользователь

Устанавливает имя пользователя для подключения.

-w, --no-password

Отключает запрос на ввод пароля.

-W, --password

Принудительно запрашивает пароль.

Параметры создания резервной копии

Параметр

Описание

-V, --version

Выводит версию утилиты pg_basebackup.

-?, --help

Выводит справку по утилите pg_basebackup.

-D каталог, --pgdata=каталог

Указывает директорию для записи данных.

-F <формат>, --format=формат

Определяет формат вывода:

  • p (plain) – для записи данных в текстовые файлы;
  • t (tar) – для записи в директорию в формате tar.

-r <скорость_передачи>, --max-rate=скорость_передачи

Устанавливает предельную скорость передачи данных в Кб/с.

-R, --write-recovery-conf

Записывает минимальный файл recovery.conf в директорию вывода.

-S <имя_слота>, --slot=имя_слота

Задаёт слот репликации при использовании WAL в режиме потоковой передачи.

-T <каталог_1=каталог_2>, --tablespace-mapping=каталог_1=каталог_2

Перемещает табличное пространство из одного каталога в другой при копировании.

--xlogdir=каталог_xlog

Указывает директорию хранения журналов транзакций.

-X <метод>, --xlog-method=метод

Определяет метод вывода файлов журналов транзакций WAL в резервную копию:

  • f (fetch) собирает файлы журналов транзакций при окончании процесса копирования;
  • s (stream) передаёт журналы транзакций в процессе создания резервной копии.

-z, --gzip

Активирует gzip-сжатие результирующего tar-файла.

-Z <уровень>, --compress=уровень

Определяет уровень сжатия механизмом gzip.

-c, `--checkpoint=fast

Активирует режим создания контрольных точек.

-l <метка>, --label=метка

Устанавливает метку резервной копии.

-P, --progress

Включает вывод отчета о прогрессе.

-v, --verbose

Включает режим подробного логирования.

Пример:

Допустим, вам нужно создать резервную копию базы данных, указав директорию для записи данных, включив gzip-сжатие и подробное логирование. Возможно, вы также хотите установить режим создания контрольных точек на fast:

pg_basebackup -U sweb -h localhost -p 5432 -D /path/to/backup -F t -z -c fast -v

Где:

  • «-U sweb» задаёт имя пользователя для подключения.
  • «-h localhost» указывает имя сервера, на котором работает база данных.
  • «-p 5432» задаёт TCP-порт для подключения.
  • «-D /path/to/backup» указывает директорию для записи данных.
  • «-F t» устанавливает формат вывода – tar.
  • «-z» включает gzip-сжатие tar-файла.
  • «-c fast» активирует режим создания контрольных точек.
  • «-v» включает режим подробного логирования.

Способ 5. wal-g 

wal-g – это инструмент для создания резервных копий и восстановления данных в PostgreSQL, который расширяет функциональность стандартных утилит СУБД и обеспечивает управление логами WAL (Write-Ahead Logging). Он предназначен для более эффективного и быстрого резервного копирования и восстановления, особенно в облачных средах.

Wal-g может работать с несколькими типами хранилищ, но в нашем руководстве мы сосредоточимся на работе с Amazon S3.

Wal-g работает на системах Linux. Для работы на Windows 10 необходимо использовать сервисы вроде WSL (Windows Subsystem for Linux). 

  1. Откройте репозиторий Wal-g на GitHub. Во вкладке releases размещены версии для различных операционных систем и СУБД. 
  2. Чтобы установить последнюю версию Wal-g на Ubuntu, выполните:
wget https://github.com/wal-g/wal-g/releases/download/v1.1/wal-g-pg-ubuntu-20.04-amd64.tar.gz
tar -zxvf wal-g-pg-ubuntu-20.04-amd64.tar.gz -C /usr/local/bin/wal-g

  1. Wal-g можно настроить с помощью переменных окружения или конфигурационного файла. Давайте создадим конфигурационный файл для работы с хранилищем S3. 

Следующий шаблон bash поможет вам настроить конфигурацию:

cat > /var/lib/postgresql/.walg.json << EOF
{
    "AWS_ENDPOINT": "https://s3.timeweb.com",
    "WALG_S3_PREFIX": "s3://имя_бакета",
    "AWS_ACCESS_KEY_ID": "Ключ доступа к хранилищу (логин)",
    "AWS_SECRET_ACCESS_KEY": "Секретный ключ",
    "WALG_COMPRESSION_METHOD": "Алгоритм сжатия: brotli, LZ4 или LZMA",
    "WALG_DELTA_MAX_STEPS": "количество дельт",
    "PGDATA": "путь к данным БД",
    "PGHOST": "имя хоста"
}
EOF
chown postgres: /var/lib/postgresql/.walg.json # Меняем владельца файла на пользователя postgres

  1. Чтобы создать резервную копию, используют функцию backup_push. Вы можете выполнить её в консоли:
su postgres -c '/usr/local/bin/wal-g/wal-g backup_push /путь к данным БД или PGDATA'

Как восстановить данные в PostgreSQL

Способ 1. pg_restore 

pg_restore – это утилита командной строки PostgreSQL, которая предназначена для восстановления баз данных из резервных копий, созданных с помощью pg_dump в специальных форматах: custom, tar или directory. 

Синтаксис

pg_restore [параметры для подключения] [параметры восстановления] [имя файла]

Параметры для подключения

Параметр

Описание

-h <сервер>, --host=сервер

Указывает имя сервера, на котором работает база данных.

-p <порт>, --port=порт

Указывает TCP-порт, через который база данных принимает подключения.

-U <пользователь>, --username=пользователь

Задаёт имя пользователя для подключения.

-w, --no-password

Отключает запрос пароля.

-W, --password

Включает запрос пароля.

--role=имя роли

Устанавливает роль, от имени которой выполняется восстановление.

<имя_файла>

Указывает расположение восстанавливаемых данных.

Параметры создания резервной копии

Параметр

Описание

-a, --data-only

Восстанавливает данные без схемы.

-c, --clean

Добавляет операторы DROP перед операторами CREATE.

-C, --create

Создает базу данных перед запуском процесса восстановления.

-d <имя_бд>, --dbname=имя_бд

Указывает имя целевой базы данных.

-e, --exit-on-error

Завершает работу в случае возникновения ошибки при выполнении SQL-команд.

-f <имя_файла>, --file=имя_файла

Указывает файл для вывода сгенерированного скрипта.

-F <формат>, --format=формат

Определяет формат резервной копии:

  • p (plain) формирует текстовый SQL-скрипт;
  • c (custom) формирует резервную копию в архивном формате;
  • d (directory) формирует копию в directory-формате;
  • t (tar) формирует копию в формате tar.

-I <индекс>, --index=индекс

Восстанавливает только указанный индекс.

-j <число-заданий>, --jobs=число-заданий

Запускает самые длительные операции в нескольких параллельных потоках.

-l, --list

Выводит содержимое архива.

-L <файл-список>, --use-list=файл-список

Восстанавливает из архива элементы, перечисленные в файле-списке, в соответствующем порядке.

-n <пространство_имен>, --schema=схема

Восстанавливает объекты в указанной схеме.

-O, --no-owner

Отключает генерацию команд, устанавливающих владение объектами по образцу исходной базы данных.

-P <имя-функции(тип-аргумента[, …])>, --function=имя-функции(тип-аргумента[, …])

Восстанавливает только указанную функцию.

-s, --schema-only

Восстанавливает только схему без данных.

-S <пользователь>, --superuser=пользователь

Устанавливает учетную запись привилегированного пользователя для отключения триггеров.

-t <таблица>, --table=таблица

Восстанавливает указанную таблицу.

-T <триггер>, --trigger=триггер

Восстанавливает конкретный триггер.

-v, --verbose

Включает режим подробного логирования.

-V, --version

Выводит версию утилиты pg_restore.

Пример:

Предположим, у вас есть резервная копия базы данных в формате custom, которая сохранена в файле sweb.backup, и вам нужно восстановить её на сервере PostgreSQL:

pg_restore -U sweb -h localhost -p 5432 -d swebdb -a -v /path/to/backup/database.backup

В этом примере:

  • «-U sweb» задаёт имя пользователя для подключения.
  • «-h localhost» указывает имя сервера.
  • «-p 5432» задаёт TCP-порт для подключения.
  • «-d swebdb» указывает имя целевой базы данных.
  • «-a» восстанавливает только данные, без схемы.
  • «-v» включает режим подробного логирования.
  • «/path/to/backup/sweb.backup» указывает путь к файлу резервной копии.

Способ 2. PgAdmin

  1. Откройте pgAdmin и подключитесь к серверу.
  2. Найдите базу данных, которую вы хотите восстановить, и щёлкните по ней правой кнопкой мыши. 
  3. В появившемся меню выберите опцию «Восстановить».

  1. В открывшемся окне укажите путь к файлу с резервной копией (дампом), который вы хотите использовать для восстановления:
  2. Нажмите кнопку «Восстановить» и дождитесь завершения процесса восстановления.

Возможные ошибки при работе с СУБД PostgreSQL

При работе с базой данных PostgreSQL вы можете столкнуться с различными ошибками, которые требуют внимания и соответствующего решения. Давайте рассмотрим наиболее распространённые проблемы и способы их устранения.

Aborting because of server version mismatch

Причина: Несовместимая версия сервера базы данных и утилиты pg_dump. Ошибка может появиться после обновления сервера или при использовании удалённой консоли для резервного копирования.

Решение: Убедитесь, что используете соответствующую версию утилиты. При необходимости установите нужную версию.

No matching tables were found

Причина: Таблица, для которой был создан дамп, не существует в базе данных. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.

Решение: Проверьте правильность написания имени таблицы и отсутствие лишних пробелов.

Input file appears to be a text format dump. Please use psql.

Причина: Дамп базы данных был сделан в текстовом формате, поэтому необходимо использовать утилиту psql для восстановления данных.

Решение: Выполните восстановление с помощью команды psql или скопируйте содержимое дампа и выполните его в SQL-редакторе.

No password supplied.

Причина: Отсутствует системная переменная PGPASSWORD или она не содержит пароля.

Решение: Настройте сервер для разрешения доступа без пароля в файле pg_hba.conf или экспортируйте переменную PGPASSWORD (например, «export PGPASSWORD» или «set PGPASSWORD»).

Too many command-line arguments.

Причина: Утилита pg_dump не поддерживает лишние пробелы или дополнительные аргументы командной строки.

Решение: Убедитесь, что в командной строке отсутствуют лишние пробелы и аргументы.

Заключение

В этом руководстве мы рассказали об основных методах создания резервных копий и восстановления данных в системе управления базами данных PostgreSQL:

  • pg_dump,
  • pg_dumpall,
  • pg_restore,
  • pg_basebackup,
  • wal-g,
  • pgAdmin.

Эффективное управление данными в PostgreSQL включает в себя не только регулярное создание резервных копий, но и грамотное планирование их восстановления. Выбор подходящего метода зависит от специфики задачи и требований к системе. Регулярные проверки процессов резервного копирования и восстановления помогут минимизировать риски потери данных и обеспечить надёжность работы с базой данных PostgreSQL.