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

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

• Создание резервных копий и восстановление
• Как создать резервные копии в PostgreSQL
  • Способ 1. pg_dump
  • Способ 2. pg_dumpall
  • Способ 3. pgAdmin
  • Способ 4. pg_basebackup
  • Способ 5. wal-g
• Как восстановить данные в PostgreSQL
  • Способ 1. pg_restore
  • Способ 2. PgAdmin
• Возможные ошибки при работе с СУБД 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:

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

Синтаксис:

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

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

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

Пример:

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

• «-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-скриптами, который можно использовать для восстановления всех баз данных и глобальных объектов на сервере.

Синтаксис:

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

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

Пример:

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

Здесь:

• «-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. Выберите «Резервная копия».

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

Способ 4. pg_basebackup

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

Синтаксис:

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

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

Пример:

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

Где:

• «-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, выполните:

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

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

4. Чтобы создать резервную копию, используют функцию backup_push. Вы можете выполнить её в консоли:

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

Способ 1. pg_restore 

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

Синтаксис

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

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

Пример:

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

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

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

Способ 2. PgAdmin

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

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

Возможные ошибки при работе с СУБД 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.