Как сделать дамп PostgreSQL

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

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

Что такое дамп PostgreSQL

О том, что такое PostgreSQL, мы уже рассказывали в другом нашем руководстве. 

Дамп PostgreSQL (также известный как «резервная копия» или «дамп базы данных») – это бинарный или текстовый файл, который содержит полную или частичную информацию из базы данных PostgreSQL. 

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

Дамп PostgreSQL включает в себя структуру базы данных (таблицы, индексы, представления, функции и другие объекты) и данные, хранящиеся в этой базе. Простыми словами, он представляет собой снимок состояния базы данных на момент создания дампа.

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

Аренда сервера

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

RU-CENTER предлагает аренду мощных виртуальных серверов на Linux. Наши серверы оснащены SSD-дисками и используют аппаратную виртуализацию KVM для обеспечения максимальной производительности и надежности.

Кроме того, наши решения масштабируются вместе с вашим бизнесом. Вы можете легко управлять ресурсами сервера в зависимости от роста и потребностей вашей компании. 

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

Как сделать дамп PostgreSQL

Создание резервных копий данных в PostgreSQL – это важная процедура для обеспечения безопасности данных и возможности восстановления в случае сбоев или потери информации. 

Один из наиболее распространенных способов создания резервных копий в PostgreSQL – это использование утилиты «pg_dump».

Способ 1. С помощью pg_dump

Утилита «pg_dump» позволяет пользователям создавать резервные копии данных одной конкретной базы данных PostgreSQL. 

Для этого необходимо выполнить следующие шаги:

1.  Выберите формата дампа. Пользователь всегда самостоятельно определяет формат создаваемого дампа. Это может быть либо текстовый скрипт, содержащий SQL команды, либо бинарный архив.
2. Создайте скрипт дампа. Если вы выбрали текстовый формат дампа, то pg_dump создаст текстовый файл. В нём будут содержаться SQL-команды для воссоздания структуры базы данных и вставки данных. Этот скрипт можно выполнить в консоли PostgreSQL или с помощью команды «psql».
3. Создайте архива дампа. Если вы выбрали бинарный формат дампа, то, соответственно, резервная копия будет представлять собой бинарный файл. Для восстановления данных из такой копии используется утилита «pg_restore».

Что выбрать: скрипт или архив

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

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

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

• Выполнить скрипт в консольном клиенте PostgreSQL. 

Вы можете открыть консольный клиент PostgreSQL, подключиться к нужной базе данных и выполнить SQL-скрипт с помощью команды:

 \i путь-к-скрипту

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

• Воспользоваться командой «psql».

Вы также можете восстановить базу данных, используя команду «psql» из командной строки. Для этого нужно указать имя базы данных и путь к SQL-скрипту: 

psql имяБД < SQL-скрипт-postgresql 

Этот метод подходит для автоматизации процесса восстановления базы данных из скрипта и может быть использован в планировщиках задач.

Архив. Подходит, если вы хотите перенести данные на другую платформу, так как бинарные архивы могут быть перенесены между разными операционными системами. 

Восстановления БД с помощью архива реализуется утилитой «pg_restore». Она предоставляет более гибкие настройки восстановления.

Синтаксис pg_dump

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

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

Параметр	Значение
`-d [имя]` или `--dbname=[имя]`	Позволяют указать имя базы данных.
`-h [имя]` или `--host=[имя]`	Указывает имя сервера (по умолчанию используется значение переменной окружения PGHOST).
`-p [порт]` или `--port=[порт]`	Используется для обозначения порта (по умолчанию используется значение переменной окружения PGPORT).
`-U [имя]` или `--username=[имя]`	Позволяет указать имя пользователя.

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

Используя эти параметры, вы можете настроить процесс создания резервной копии PostgreSQL базы данных в соответствии с вашими требованиями:

Параметр	Значение
`-a` или `--data-only`	Сохраняет только данные, не включая в дамп структуру базы данных. Если использовать этот параметр, связи между таблицами также не сохраняются.
`-b` или `--blobs`	По умолчанию добавляет в дамп большие объекты (BLOB).
`-B` или `--no-blobs`	Не сохраняет большие объекты в дампе.
`-c` или `--clean`	Добавляет команды DROP в SQL-скрипт, что может пригодиться, если в базе данных есть объекты с одинаковыми именами. Применим только к SQL-скриптам.
`-C` или `--create`	Добавляет команды для создания базы данных и подключения к ней в SQL-скрипт. Применимо только к SQL-скриптам.
`-E кодировка` или `--encoding=кодировка`	Устанавливает определенную кодировку для дампа.
`-f [каталог]` или `--file=[каталог]`	Задает каталог, в который будет сохранен дамп.
`-F [формат]` или `--format=[формат]`	Определяет формат дампа. В PostgreSQL можно выбрать формат из следующих значений: plain (SQL скрипт, значение по умолчанию), custom (архив), directory (каталог), tar (формат .tar).
`-j [количество]` или `--jobs=[количество]`	Позволяет выполнить утилиту в многопоточном режиме с указанным количеством потоков.
`-n [схема]` или `--schema=[схема]`	Сохраняет схемы, которые соответствуют указанному шаблону.
`-N [схема]` или `--exclude-schema=[схема]`	Исключает схемы, соответствующие указанному шаблону.
`-o` или `--oids`	Сохраняет OID (Object ID) в дампе.
`-O` или `--no-owner`	Не добавляет в дамп команды, которые связаны с установкой владельцев объектов.
`-s` или `--schema-only`	Сохраняет только схемы, без данных.
`-t [схема]` или `--table=[схема]`	Сохраняет только таблицы, которые соответствуют указанному шаблону.
`-T [схема]` или `--exclude-table=[схема]`	Исключает таблицы, которые соответствуют указанному шаблону.
`-x` или `--no-privileges` или `--no-acl`	Не сохраняет права доступа.
`-Z [0..9]` или `--compress=[0..9]`	Выбирает уровень сжатия (где 0 – не сжимать, а 9 – максимальное сжатие).

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

Если в вашей системе существуют глобальные объекты PostgreSQL (например, роли или таблицы), которые принадлежат всей системе, а не конкретной базе данных, то для их сохранения и восстановления необходимо использовать утилиту «pg_dumpall», о которой мы расскажем далее.

Способ 2. С помощью pg_dumpall

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

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

Важно! Для выполнения pg_basebackup требуются соответствующие права. Вы должны быть быть суперпользователем или иметь право REPLICATION.  

Синтаксис pg_dumpall

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

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

Параметр	Значение
`-d [строка-подключения]` или `--dbname=[строка-подключения]`	Позволяет задать строку подключения к серверу PostgreSQL.
`-h [имя]` или `--host=[имя]`	Устанавливает имя сервера PostgreSQL. По умолчанию используется значение переменной окружения «PGHOST».
`-p [порт]` или `--port=[порт]`	Задает порт, на котором работает PostgreSQL. По умолчанию, используется значение переменной окружения «PGPORT».
`-U [имя]` или `--username=[имя]`	Устанавливает имя пользователя для подключения к PostgreSQL.
`-l [имя]` или `--database=[имя]`	Позволяет выбрать конкретную базу данных, через которую будут загружены глобальные объекты. Этот параметр полезен, если в вашем кластере существует несколько баз данных и вы хотите ограничить дамп определенной базой.

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

Параметр	Значение
`-a` или `--data-only`	Сохраняет только данные, исключая создание структуры таблиц и других объектов. Полезно, если вам нужно только содержимое таблиц, а не схема и структура.
`-c` или `--clean`	Добавляет команды «DROP» перед командами «CREATE» в SQL-скрипте. Это может быть полезно, если в вашем кластере есть объекты с одинаковыми именами.
`-f [catalog]` или `--file=[catalog]`	Задает каталог, в который будет сохранен SQL-скрипт.
`-g` или `--globals-only`	Сохраняет только глобальные объекты, такие как роли, табличные пространства и т. д.
`-o` или `--oids`	Сохраняет OID (Object ID) в дампе, если они используются в вашей базе данных.
`-O` или `--no-owner`	Исключает из дампа команды, которые связаны с установкой владельцев объектов.
`-r` или `--roles-only`	Сохраняет только роли пользователей.
`-s` или `--schema-only`	Сохраняет только схемы, без данных.
`-t` или `--tablespaces-only`	Сохраняет только табличные пространства.
`-x` или `--no-privileges` или `--no-acl`	Не сохраняет права доступа (ACL) к объектам.

Таким образом, утилита «pg_dumpall» предоставляет более широкие возможности для создания резервных копий и восстановления кластера PostgreSQL в сравнении с pg_dump, так как она охватывает все глобальные объекты и базы данных в кластере.

Способ 3. С помощью pg_basebackup

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

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

Важно! Процесс нельзя настроить или нацелить на конкретные компоненты кластера – вы в любом случае сохраните весь инстанс целиком.

Синтаксис pg_basebackup

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

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

Параметр	Значение
`-d [строка-подключения]` или `--dbname=[строка-подключения]`	Позволяет задать строку подключения к серверу PostgreSQL.
`-h [имя]` или `--host=[имя]`	Устанавливает имя сервера PostgreSQL. По умолчанию используется значение переменной окружения «PGHOST».
`-p [порт]` или `--port=[порт]`	Задает порт, на котором работает PostgreSQL. По умолчанию, используется значение переменной окружения «PGPORT».
`-U [имя]` или `--username=[имя]`	Устанавливает имя пользователя для подключения к PostgreSQL.

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

Параметр	Значение
`-D [каталог]` или `--pgdata=[каталог]`	Определяет каталог, в который будет сохранен бинарный дамп.
`-F [формат]` или `--format=[формат]`	Определяет формат дампа, который может иметь значения: plain (обычные файлы), tar (формат .tar).
`-r [скорость]` или `--max-rate=[скорость]`	Устанавливает максимальную скорость передачи данных в Кб/с. Это может быть полезно для управления загрузкой системы во время создания дампа.
`-Z [0..9]` или `--compress=[0..9]`	Выбирает уровень сжатия (где 0 – не сжимать, а 9 – максимальное сжатие).

Утилита «pg_basebackup» особенно полезна для создания резервных копий и репликации PostgreSQL кластеров. 

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

Способ 4. С помощью pg_restore

«Pg_restore» – это утилита PostgreSQL, которая используется для восстановления баз данных из резервных копий, которые были созданы с использованием pg_dump. 

Она работает с дампами в архивном формате и предоставляет множество параметров для настройки процесса восстановления. 

Синтаксис pg_restore

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

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

Параметр	Значение
`-h [имя]` или `--host=[имя]`	Устанавливает имя сервера PostgreSQL. По умолчанию используется значение переменной окружения «PGHOST».
`-p [порт]` или `--port=[порт]`	Задает порт, на котором работает PostgreSQL. По умолчанию, используется значение переменной окружения «PGPORT».
`-U [имя]` или `--username=[имя]`	Устанавливает имя пользователя для подключения к PostgreSQL.
`-w` или `--no-password`	Отключает запрос пароля при подключении.
`-W` или `--password`	Принудительно включает запрос пароля при подключении.
`--role=[имя]`	Устанавливает имя роли, от имени которой будет выполняться восстановление.

Параметры восстановления

Параметр	Значение
`-a` или `--data-only`	Восстанавливает только данные, исключая структуру базы данных.
`-c` или `--clean`	Удаляет одноименные объекты перед восстановлением.
`-C` или `--create`	Создает базу данных перед восстановлением.
`-d [имя]` или `--dbname=[имя]`	Подключается к указанной базе данных и восстанавливает данные в нее.
`-e` или `--exit-on-error`	Завершает восстановление в случае ошибки.
`-j [количество-потоков]` или `--jobs=[количество-потоков]`	Осуществляет восстановление в многопоточном режиме.
`-n [схема]` или `--schema=[схема]`	Восстанавливает объекты только из указанной схемы.
`-N [схема]` или `--exclude-schema=[схема]`	Исключает объекты из указанной схемы.
`-O` или `--no-owner`	Исключает из дампа команды, которые связаны с установкой владельцев объектов.
`-s` или `--schema-only`	Восстанавливает только схему, без данных.
`-t [таблица]` или `--table=[таблица]`	Восстанавливает только указанную таблицу.
`-x` или `--no-privileges` или `--no-acl`	Не сохраняет права доступа (ACL) к объектам.

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

Способ 5. С помощью WAL-G

«WAL-G» – это полезная сторонняя утилита, которая предназначена для управления резервными копиями и журналами транзакций PostgreSQL. 

Она предоставляет возможность создавать бэкапы и восстанавливать базы данных PostgreSQL, а также управлять журналами WAL (Write-Ahead Logging). WAL-G также поддерживает работу с различными типами СУБД. 

В нашем руководстве мы настроим непрерывное резервное копирование PostgreSQL на облачный сервис Amazon S3 с помощью WAL-G.

1. Установите WAL-G.

Для этого скачайте последнюю версию WAL-G с GitHub и распакуйте ее:

wget https://github.com/wal-g/wal-g/releases/download/v1.1/wal-g-pg-ubuntu-22.04-amd64.tar.gz

tar -zxvf wal-g-pg-ubuntu-22.04-amd64.tar.gz -C /usr/local/bin/wal-g

Теперь WAL-G установлен и готов к использованию.

2. Настройте WAL-G для S3.

Чтобы настроить WAL-G для работы с Amazon S3, вы должны создать конфигурационный файл. 

В приведенном ниже примере используется файл «.walg.json», который содержит следующие настройки:

cat > /var/lib/postgresql/.walg.json << EOF

{

  "AWS_ENDPOINT": "https://s3.nic.ru",

  "WALG_S3_PREFIX": "s3://имя-бакета", 

  "AWS_ACCESS_KEY_ID": "ключ-доступа к хранилищу",

  "AWS_SECRET_ACCESS_KEY": "секретный-ключ",

  "WALG_COMPRESSION_METHOD": "алгоритм-сжатия",

  "WALG_DELTA_MAX_STEPS": "количество-дельт-бэкапов", 

  "PGDATA": "путь-к-данным-БД",

  "PGHOST": "имя-хоста"

}

EOF

Где: 

• «https://s3.nic.ru» – URL вашего S3-хранилища. 
• В команде «COMPRESSION_METHOD» укажите алгоритм сжатия: brotli, LZ4 или LZMA.

Важно! Дельта-бэкап – это метод резервного копирования данных, который фокусируется на сохранении только изменений (дельт) между двумя состояниями данных, вместо копирования всей информации каждый раз. Это позволяет снизить объем хранимых резервных копий, сэкономить пространство на диске и время, которое тратится на создание и восстановление резервных копий.

Далее введите:

chown postgres: /var/lib/postgresql/.walg.json

Эта команда изменит владельца и группу файла «.walg.json» на «postgres».  При этом права доступа к файлу останутся без изменений.

3. Используйте WAL-G для создания бэкапов. 

Для создания резервных копий PostgreSQL с использованием WAL-G вы можете выполнить следующую команду от имени пользователя PostgreSQL:

su postgres -c '/usr/local/bin/wal-g/wal-g backup_push [путь-к-данным-БД] или [путь-к-PGDATA]

Эта команда создаст бэкап и передаст его в хранилище Amazon S3 с использованием настроек из вашего «.walg.json» файла.

Важно! WAL-G предоставляет и другие функции: например, восстановление баз данных из бэкапов, архивация и удаление старых бэкапов, а также управление журналами WAL. Подробнее о его функциональности и командах можно узнать из официальной документации WAL-G.

Способ 6. С помощью pgAdmin

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

Чтобы с

1. Перейдите на официальный сайт «pgAdmin».
2. На странице загрузки выберите подходящую версию для вашей операционной системы.

3.   К скачиванию доступно три релиза. Можете выбрать последнюю версию – в нашем случае это v7.7.

4. Загрузите подходящий под вашу архитектуру файл.

5. Откройте его и следуйте указаниям, чтобы установить pgAdmin.
6.  По завершении процесса установки запустите «pgAdmin». 
7. Перед вами откроется стартовое окно утилиты. В разделе «Quick Links» щёлкните Add New Server, чтобы подключиться к серверу.

8. В появившемся диалоговом окне «Register - Server» вам будет необходимо заполнить две вкладки: General и Connection. 

General:

В «Name» укажите имя вашего сервера. 

Connection:

• «Host name/ address» – пропишите адрес вашего PostgreSQL сервера. Это может быть IP-адрес или доменное имя.
• «Port» – укажите порт PostgreSQL сервера (по умолчанию 5432).
• «Maintenance database» – введите имя базы данных, к которой вы хотите подключиться.
• «Password» – введите пароль пользователя PostgreSQL.

Важно! Отметьте «Save Password», если вы хотите сохранить пароль для автоматического входа. В целях безопасности, мы не рекомендуем использовать эту опцию на общих компьютерах.

9. Нажмите Save для сохранения настроек сервера.

10. В левой панели навигации под разделом «Servers», вы увидите добавленный вами сервер. Кликните по нему и перейдите в папку Databases. 
11. Щёлкните правой кнопкой мыши по базе данных Postgres.
12. Выберите опцию Backup, чтобы создать резервную копию. 

Готово! Вы успешно создали дамп PostgreSQL.

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

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

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