Как сделать дамп базы данных postgresql

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

В этой инструкции описывается, как сделать дамп базы данных PostgreSQL в консоли при подключении по SSH с помощью утилит pg_dump, pg_dumpall, pg_restore.

На этой странице

• Перед началом работы
• Создание бэкапа одной БД с помощью pg_dump
• Создание бэкапа всех имеющихся БД с помощью pg_dumpall
• Восстановление из бэкапа с помощью pg_restore

Перед началом работы

1. Войдите в консоль управления Advanced.

• Инструкция по входу с помощью личного кабинета Cloud.ru
• Инструкция по входу в консоль для IAM-пользователей

Создание бэкапа одной БД с помощью pg_dump

Утилита pg_dump — встроенный инструмент для создания резервных копий в PostgreSQL. Утилита имеет синтаксис:

pg_dump параметры> название БД> > файл для сохранения копии> 

1. Подключитесь к виртуальной машине и обновите на ней репозитории.

apt-get update 

apt-get install postgresql-client 

pg_dump -U user> -W -h host> -p port> datastore> > /tmp/datastore-backup>.dump 

В этом примере используется БД PostgreSQL с внешним IP (EIP) и названием БД «postgres», для входа необходимо указать пользователя root, а также пароль.

pg_dump -U root -W -h 172.16.0.49 postgres > /tmp/postgres.dump 

• -U — имя пользователя экземпляра базы данных RDS. По умолчанию — root .
• -h — IP-адрес экземпляра первичной БД. Получить этот IP-адрес можно на странице Instance Management , нажав на название экземпляра БД.

Примечание Если подключение осуществляется:

• через ECS, то IP-адрес можно найти на вкладке Basic Information в разделе Connection Information → Floating IP Address .
• через EIP, то адрес IP будет располагаться на вкладке EIPs .

В результате будет выполнен бэкап БД в файл «postgres» с расширением .dump.

Создание бэкапа всех имеющихся БД с помощью pg_dumpall

Утилита pg_dumpall реализует резервное копирование всего экземпляра (кластера или инстанса) базы данных без указания конкретной базы данных на инстансе. Принцип работы с ней аналогичен pg_dump.

Установите клиент на виртуальную машину.

apt-get install postgresql-client 

pg_dumpall -U user> -W -h host> -p port> datastore> > /tmp/datastore-backup>.bak 

Для сжатия резервной копии рекомендуется сразу передать вывод на архиватор gzip:

pg_dumpall datastore> | gzip > /tmp/datastore-backup>.tar.gz 

В этом примере используется БД PostgreSQL с внешним IP (EIP) и названием БД «postgres», для входа необходимо указать пользователя root, а также пароль.

pg_dumpall -U root -W -h 172.16.0.49 postgres > /tmp/postgres.bak 

Параметры утилиты аналогичны pg_dump.

В результате будет выполнен бэкап всех имеющихся в системе БД в файл «postgres» с расширением .bak.

Восстановление из бэкапа с помощью pg_restore

Утилита позволяет восстанавливать данные из резервных копий. Например, чтобы восстановить только определенную БД (в нашем примере postgres), нужно запустить эту утилиту с параметром -d :

pg_restore -U user> -W -h host> -p port> -d datastore> /tmp/datastore-backup>.bak 

В этом примере используется БД PostgreSQL с внешним IP (EIP) и названием БД «postgres», для входа необходимо указать пользователя root, а также пароль.

pg_restore -U root -W -h 172.16.0.49 -d postgres /tmp/postgres.bak 

• -U — имя пользователя экземпляра базы данных RDS. По умолчанию — root .
• -h — IP-адрес экземпляра первичной БД. Получить этот IP-адрес можно на странице Instance Management , нажав на название экземпляра БД.

Примечание Если подключение осуществляется:

• через ECS, то IP-адрес можно найти на вкладке Basic Information в разделе Connection Information → Floating IP Address .
• через EIP, то адрес IP будет располагаться на вкладке EIPs .

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

Перенос базы данных PostgreSQL с помощью дампа и ее восстановление

Базу данных PostgreSQL можно извлечь в файл дампа с помощью pg_dump. Метод восстановления базы данных зависит от формата выбранного дампа. Если дамп принимается с обычным форматом (который по умолчанию -Fp является стандартным, поэтому не требуется указывать конкретный параметр), то единственным вариантом восстановления является psql , так как он выводит обычный текстовый файл. Для других трех методов дампа: настраиваемых, каталогов и tar pg_restore следует использовать.

Инструкции и команды, приведенные в этой статье, предназначены для выполнения в терминалах Bash. К ним относятся такие среды, как подсистема Windows для Linux (WSL), Azure Cloud Shell и другие интерфейсы, совместимые с bash. Убедитесь, что вы используете терминал bash для выполнения действий и выполнения команд, описанных в этом руководстве. Использование другого типа среды терминала или оболочки может привести к различиям в поведении команд и не может привести к ожидаемым результатам.

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

Портал Azure упрощает этот процесс с помощью колонки Подключение, предлагая предварительно настроенные команды, адаптированные к серверу, с значениями, замененными пользовательскими данными. Важно отметить, что колонка Подключение доступна только для База данных Azure для PostgreSQL — гибкого сервера, а не для одного сервера. Вот как можно использовать эту функцию:

1. Доступ портал Azure. Сначала перейдите к портал Azure и выберите колонку Подключение.
2. Выберите базу данных: в колонке Подключение вы найдете раскрывающийся список баз данных. Выберите базу данных, из которой вы хотите выполнить дамп.
3. Выберите подходящий метод: в зависимости от размера базы данных можно выбрать один из двух методов:
   • pg_dump & psql — использование единственного текстового файла: идеально подходит для небольших баз данных, этот параметр использует один текстовый файл для процесса дампа и восстановления.
   • pg_dump & pg_restore — использование нескольких ядер: для больших баз данных этот метод эффективнее, так как он использует несколько ядер для обработки процесса дампа и восстановления.

Необходимые компоненты

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

Прежде чем приступить к выполнению этого руководства, необходимы следующие компоненты:

• Сервер базы данных Azure для PostgreSQL с правилами брандмауэра, разрешающими доступ к этом серверу.
• pg_dump, psql, pg_restore и pg_dumpall в случае, если вы хотите перенести роли и разрешения, установленные служебные программы командной строки.
• Определите расположение дампа: выберите место, из которого вы хотите выполнить дамп. Это можно сделать из различных расположений, таких как отдельная виртуальная машина, облачная оболочка (где служебные программы командной строки уже установлены, но могут не находиться в соответствующей версии, поэтому всегда проверка версию с помощью, например, psql —version или собственного ноутбука. Всегда помните расстояние и задержку между сервером PostgreSQL и расположением, из которого выполняется дамп или восстановление.

Важно использовать pg_dump psql pg_restore pg_dumpall служебные программы, которые имеют одну или ту же основную версию или более высокую основную версию, чем сервер базы данных, из которых экспортируются или импортируются данные. Это может привести к неудачной миграции данных. Если целевой сервер имеет более высокую основную версию, чем исходный сервер, используйте служебные программы, которые совпадают с той же основной версией или выше, чем целевой сервер.

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

Дампа пользователей и ролей с помощью pg_dumpall -r

pg_dump используется для извлечения базы данных PostgreSQL в файл дампа. Однако важно понимать, что pg_dump не дамп ролей или определений пользователей, так как они считаются глобальными объектами в среде PostgreSQL. Для комплексной миграции, включая пользователей и ролей, необходимо использовать pg_dumpall -r . Эта команда позволяет записывать все сведения о роли и пользователя из среды PostgreSQL. Если вы переносите базы данных на том же сервере, вы можете пропустить этот шаг и перейти к разделу «Создать новую базу данных «.

pg_dumpall -r -h -U > roles.sql 

Например, если у вас есть сервер с именем и пользователь с именем mydemoserver myuser выполните следующую команду:

pg_dumpall -r -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql 

Если вы используете отдельный сервер, ваше имя пользователя включает компонент имени сервера. Поэтому вместо myuser этого используйте myuser@mydemoserver .

Дампа ролей с гибкого сервера

В среде гибкого сервера расширенные меры безопасности означают, что у пользователей нет доступа к таблице pg_authid, где хранятся пароли ролей. Это ограничение влияет на выполнение дампа ролей, так как стандартная pg_dumpall -r команда пытается получить доступ к этой таблице для паролей и завершиться ошибкой из-за отсутствия разрешений.

При дампах ролей с гибкого сервера важно включить —no-role-passwords этот параметр в pg_dumpall команду. Этот параметр предотвращает pg_dumpall попытку доступа pg_authid к таблице, которую она не может прочитать из-за ограничений безопасности.

Чтобы успешно дампать роли с гибкого сервера, используйте следующую команду:

pg_dumpall -r --no-role-passwords -h -U > roles.sql 

Например, если у вас есть сервер с именем mydemoserver myuser пользователя, выполните следующую команду:

pg_dumpall -r --no-role-passwords -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql 

Очистка дампа ролей

При переносе выходного файла roles.sql могут включать определенные роли и атрибуты, которые не применимы или допустимы в новой среде. Вот что вам нужно рассмотреть:

• Удаление атрибутов, которые могут быть заданы только суперпользователями: при миграции в среду, в которой у вас нет привилегий суперпользователя, удалите атрибуты, такие как NOSUPERUSER и NOBYPASSRLS из дампа ролей.
• Исключение пользователей, относящихся к службе: исключение пользователей службы с одним сервером, например azure_superuser или azure_pg_admin . Они относятся к службе и будут созданы автоматически в новой среде.

Используйте следующую sed команду для очистки дампа ролей:

sed -i '/azure_superuser/d; /azure_pg_admin/d; /azuresu/d; /^CREATE ROLE replication/d; /^ALTER ROLE replication/d; /^ALTER ROLE/ ' roles.sql 

Эта команда удаляет строки со строками azure_superuser , azure_pg_admin azuresu начинающимися с CREATE ROLE replication и ALTER ROLE replication , и удаляет NOSUPERUSER NOBYPASSRLS атрибуты из ALTER ROLE инструкций.

Создание файла дампа, содержащего необходимые для загрузки данные

Чтобы экспортировать существующую базу данных PostgreSQL в локальную или виртуальную машину в файл скрипта SQL, выполните следующую команду в существующей среде:

• pg_dump и psql — использование единственного текстового файла
• pg_dump & pg_restore — использование нескольких ядер

pg_dump -h -U > _dump.sql 

Например, если у вас есть сервер с именем , пользователь с именем mydemoserver myuser и вызываемая testdb база данных, выполните следующую команду:

pg_dump testdb -h mydemoserver.postgres.database.azure.com -U myuser > testdb_dump.sql 

pg_dump -Fd -j  -h -U -f .dump 

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

Например, если у вас есть сервер с именем , пользователь с именем mydemoserver myuser и вызываемая testdb база данных, и вы хотите использовать два ядра для дампа, выполните следующую команду:

pg_dump -Fd -j 2 testdb -h mydemoserver.postgres.database.azure.com -U myuser -f testdb.dump 

Если вы используете отдельный сервер, ваше имя пользователя включает компонент имени сервера. Поэтому вместо myuser этого используйте myuser@mydemoserver .

Восстановление данных в целевую базу данных

Восстановление ролей и пользователей

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

Чтобы восстановить роли и пользователей в целевой базе данных, используйте следующую команду:

psql -f roles.sql -h -U

Замените именем целевого сервера и именем пользователя. Эта команда использует psql программу для выполнения команд SQL, содержащихся в roles.sql файле, эффективно восстанавливая роли и пользователей в целевой базе данных.

Например, если у вас есть сервер с именем mydemoserver myuser пользователя, выполните следующую команду:

psql -f roles.sql -h mydemoserver.postgres.database.azure.com -U myuser 

Если вы используете отдельный сервер, ваше имя пользователя включает компонент имени сервера. Поэтому вместо myuser этого используйте myuser@mydemoserver .

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

Создание базы данных

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

С помощью createdb служебной программы программа createdb позволяет создавать базы данных непосредственно из командной строки Bash без необходимости войти в PostgreSQL или оставить среду операционной системы. Например:

createdb -h -U

Например, если у вас есть сервер с именем mydemoserver myuser , имя пользователя и новая база данных, которую вы хотите создать testdb_copy , выполните следующую команду:

createdb testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser 

CREATE DATABASE ; 

Замените именем, которое вы хотите предоставить новой базе данных. Например, чтобы создать базу данных с именем testdb_copy , команда будет:

CREATE DATABASE testdb_copy; 

Восстановление дампа

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

• pg_dump и psql — использование единственного текстового файла
• pg_dump & pg_restore — использование нескольких ядер

psql -f _dump.sql -h -U 2> errors.log 

Например, если у вас есть сервер с именем , пользователь с именем mydemoserver myuser и новая база данных testdb_copy , выполните следующую команду:

psql -f testdb_dump.sql testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser 2> errors.log 

pg_restore -Fd -j -d .dump -h -U 2> errors.log 

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

Например, если у вас есть сервер с именем , пользователь с именем mydemoserver myuser и новая база данных testdb_copy , и вы хотите использовать два ядра для дампа, выполните следующую команду:

pg_restore -Fd -j 2 -d testdb_copy testdb.dump -h mydemoserver.postgres.database.azure.com -U myuser 2> errors.log 

Проверка после восстановления

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

Оптимизация процесса миграции

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

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

Следующие шаги

• Рекомендации по pg_dump и pg_restore.
• Дополнительные сведения о переносе баз данных в службу «База данных Azure для PostgreSQL» см. в этой статье.

Как сделать дамп 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-скрипт с помощью команды:

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

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

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

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

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

Синтаксис pg_dump

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

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

Параметр

Значение

`-d [имя]` или `—dbname=[имя]`

Позволяют указать имя базы данных.

`-h [имя]` или `—host=[имя]`

Указывает имя сервера (по умолчанию используется значение переменной окружения PGHOST).

`-p [порт]` или `—port=[порт]`

Используется для обозначения порта (по умолчанию используется значение переменной окружения PGPORT).

`-U [имя]` или `—username=[имя]`

Позволяет указать имя пользователя.

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

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

Параметр

Значение

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

По умолчанию добавляет в дамп большие объекты (BLOB).

Не сохраняет большие объекты в дампе.

Добавляет команды DROP в SQL-скрипт, что может пригодиться, если в базе данных есть объекты с одинаковыми именами. Применим только к SQL-скриптам.

Добавляет команды для создания базы данных и подключения к ней в SQL-скрипт. Применимо только к SQL-скриптам.

`-E кодировка` или `—encoding=кодировка`

Устанавливает определенную кодировку для дампа.

`-f [каталог]` или `—file=[каталог]`

Задает каталог, в который будет сохранен дамп.

`-F [формат]` или `—format=[формат]`

Определяет формат дампа. В PostgreSQL можно выбрать формат из следующих значений: plain (SQL скрипт, значение по умолчанию), custom (архив), directory (каталог), tar (формат .tar).

`-j [количество]` или `—jobs=[количество]`

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

`-n [схема]` или `—schema=[схема]`

Сохраняет схемы, которые соответствуют указанному шаблону.

`-N [схема]` или `—exclude-schema=[схема]`

Исключает схемы, соответствующие указанному шаблону.

Сохраняет OID (Object ID) в дампе.

Не добавляет в дамп команды, которые связаны с установкой владельцев объектов.

`-s` или `—schema-only`

Сохраняет только схемы, без данных.

`-t [схема]` или `—table=[схема]`

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

`-T [схема]` или `—exclude-table=[схема]`

Исключает таблицы, которые соответствуют указанному шаблону.

`-x` или `—no-privileges` или `—no-acl`

Не сохраняет права доступа.

Выбирает уровень сжатия (где 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=[имя]`

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

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

Параметр

Значение

Сохраняет только данные, исключая создание структуры таблиц и других объектов. Полезно, если вам нужно только содержимое таблиц, а не схема и структура.

Добавляет команды «DROP» перед командами «CREATE» в SQL-скрипте. Это может быть полезно, если в вашем кластере есть объекты с одинаковыми именами.

`-f [catalog]` или `—file=[catalog]`

Задает каталог, в который будет сохранен SQL-скрипт.

`-g` или `—globals-only`

Сохраняет только глобальные объекты, такие как роли, табличные пространства и т. д.

Сохраняет OID (Object ID) в дампе, если они используются в вашей базе данных.

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

`-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=[скорость]`

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

Выбирает уровень сжатия (где 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`

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

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

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

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

Параметр

Значение

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

Удаляет одноименные объекты перед восстановлением.

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

`-d [имя]` или `—dbname=[имя]`

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

`-e` или `—exit-on-error`

Завершает восстановление в случае ошибки.

`-j [количество-потоков]` или `—jobs=[количество-потоков]`

Осуществляет восстановление в многопоточном режиме.

`-n [схема]` или `—schema=[схема]`

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

`-N [схема]` или `—exclude-schema=[схема]`

Исключает объекты из указанной схемы.

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

`-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 и распакуйте ее:

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

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

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

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

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

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

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

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

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

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

1. Используйте 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. На странице загрузки выберите подходящую версию для вашей операционной системы.

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

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

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

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

General:

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

Connection:

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

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

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

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

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

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

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

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

Как сделать дамп базы данных postgresql

Идея, стоящая за этим методом, заключается в генерации текстового файла с командами SQL, которые при выполнении на сервере пересоздадут базу данных в том же самом состоянии, в котором она была на момент выгрузки. Postgres Pro предоставляет для этой цели вспомогательную программу pg_dump . Простейшее применение этой программы выглядит так:

pg_dump имя_базы > файл_дампа 

Как видите, pg_dump записывает результаты своей работы в устройство стандартного вывода. Далее будет рассмотрено, чем это может быть полезно. В то время как вышеупомянутая команда создаёт текстовый файл, pg_dump может создать файлы и в других форматах, которые допускают параллельную обработку и более гибкое управление восстановлением объектов.

Программа pg_dump является для Postgres Pro обычным клиентским приложением (хотя и весьма умным). Это означает, что вы можете выполнять процедуру резервного копирования с любого удалённого компьютера, если имеете доступ к нужной базе данных. Но помните, что pg_dump не использует для своей работы какие-то специальные привилегии. В частности, ей обычно требуется доступ на чтение всех таблиц, которые вы хотите выгрузить, так что для копирования всей базы данных практически всегда её нужно запускать с правами суперпользователя СУБД. (Если у вас нет достаточных прав для резервного копирования всей базы данных, вы тем не менее можете сделать резервную копию той части базы, доступ к которой у вас есть, используя такие параметры, как -n схема или -t таблица .)

Указать, к какому серверу должна подключаться программа pg_dump , можно с помощью аргументов командной строки -h сервер и -p порт . По умолчанию в качестве сервера выбирается localhost или значение, указанное в переменной окружения PGHOST . Подобным образом, по умолчанию используется порт, заданный в переменной окружения PGPORT , а если она не задана, то порт, указанный по умолчанию при компиляции. (Для удобства при компиляции сервера обычно устанавливается то же значение по умолчанию.)

Как и любое другое клиентское приложение Postgres Pro , pg_dump по умолчанию будет подключаться к базе данных с именем пользователя, совпадающим с именем текущего пользователя операционной системы. Чтобы переопределить имя, либо добавьте параметр -U , либо установите переменную окружения PGUSER . Помните, что pg_dump подключается к серверу через обычные механизмы проверки подлинности клиента (которые описываются в Главе 19).

Важное преимущество pg_dump в сравнении с другими методами резервного копирования, описанными далее, состоит в том, что вывод pg_dump обычно можно загрузить в более новые версии Postgres Pro , в то время как резервная копия на уровне файловой системы и непрерывное архивирование жёстко зависят от версии сервера. Также, только метод с применением pg_dump будет работать при переносе базы данных на другую машинную архитектуру, например, при переносе с 32-битной на 64-битную версию сервера.

Дампы, создаваемые pg_dump , являются внутренне согласованными, то есть, дамп представляет собой снимок базы данных на момент начала запуска pg_dump . pg_dump не блокирует другие операции с базой данных во время своей работы. (Исключение составляют операции, которым нужна исключительная блокировка, как например, большинство форм команды ALTER TABLE .)

24.1.1. Восстановление дампа

Текстовые файлы, созданные pg_dump , предназначаются для последующего чтения программой psql . Общий вид команды для восстановления дампа:

psql имя_базы < файл_дампа 

где файл_дампа — это файл, содержащий вывод команды pg_dump . База данных, заданная параметром имя_базы , не будет создана данной командой, так что вы должны создать её сами из базы template0 перед запуском psql (например, с помощью команды createdb -T template0 имя_базы ). Программа psql принимает параметры, указывающие сервер, к которому осуществляется подключение, и имя пользователя, подобно pg_dump . За дополнительными сведениями обратитесь к справке по psql . Дампы, выгруженные не в текстовом формате, восстанавливаются утилитой pg_restore .

Перед восстановлением SQL-дампа все пользователи, которые владели объектами или имели права на объекты в выгруженной базе данных, должны уже существовать. Если их нет, при восстановлении будут ошибки пересоздания объектов с изначальными владельцами и/или правами. (Иногда это желаемый результат, но обычно нет).

По умолчанию, если происходит ошибка SQL, программа psql продолжает выполнение. Если же запустить psql с установленной переменной ON_ERROR_STOP , это поведение поменяется и psql завершится с кодом 3 в случае возникновения ошибки SQL:

psql --set ON_ERROR_STOP=on имя_базы < файл_дампа

В любом случае вы получите только частично восстановленную базу данных. В качестве альтернативы можно указать, что весь дамп должен быть восстановлен в одной транзакции, так что восстановление либо полностью выполнится, либо полностью отменится. Включить данный режим можно, передав psql аргумент -1 или —single-transaction . Выбирая этот режим, учтите, что даже незначительная ошибка может привести к откату восстановления, которое могло продолжаться несколько часов. Однако это всё же может быть предпочтительней, чем вручную вычищать сложную базу данных после частично восстановленного дампа.

Благодаря способности pg_dump и psql писать и читать каналы ввода/вывода, можно скопировать базу данных непосредственно с одного сервера на другой, например:

pg_dump -h host1 имя_базы | psql -h host2 имя_базы

Важно

Дампы, которые выдаёт pg_dump , содержат определения относительно template0 . Это означает, что любые языки, процедуры и т. п., добавленные в базу через template1 , pg_dump также выгрузит в дамп. Как следствие, если при восстановлении вы используете модифицированный template1 , вы должны создать пустую базу данных из template0 , как показано в примере выше.

После восстановления резервной копии имеет смысл запустить ANALYZE для каждой базы данных, чтобы оптимизатор запросов получил полезную статистику; за подробностями обратитесь к Подразделу 23.1.3 и Подразделу 23.1.6. Другие советы по эффективной загрузке больших объёмов данных в Postgres Pro вы можете найти в Разделе 14.4.

24.1.2. Использование pg_dumpall

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

pg_dumpall > файл_дампа 

Полученную копию можно восстановить с помощью psql :

psql -f файл_дампа postgres

(В принципе, здесь в качестве начальной базы данных можно указать имя любой существующей базы, но если вы загружаете дамп в пустой кластер, обычно нужно использовать postgres ). Восстанавливать дамп, который выдала pg_dumpall , всегда необходимо с правами суперпользователя, так как они требуются для восстановления информации о ролях и табличных пространствах. Если вы используете табличные пространства, убедитесь, что пути к табличным пространствам в дампе соответствуют новой среде.

pg_dumpall выдаёт команды, которые заново создают роли, табличные пространства и пустые базы данных, а затем вызывает для каждой базы pg_dump . Таким образом, хотя каждая база данных будет внутренне согласованной, состояние разных баз не будет синхронным.

Только глобальные данные кластера можно выгрузить, передав pg_dumpall ключ —globals-only . Это необходимо, чтобы полностью скопировать кластер, когда pg_dump выполняется для отдельных баз данных.

24.1.3. Управление большими базами данных

Некоторые операционные системы накладывают ограничение на максимальный размер файла, что приводит к проблемам при создании больших файлов с помощью pg_dump . К счастью, pg_dump может писать в стандартный вывод, так что вы можете использовать стандартные инструменты Unix для того, чтобы избежать потенциальных проблем. Вот несколько возможных методов:

Используйте сжатые дампы. Вы можете использовать предпочитаемую программу сжатия, например gzip :

pg_dump имя_базы | gzip > имя_файла.gz

Затем загрузить сжатый дамп можно командой:

gunzip -c имя_файла.gz | psql имя_базы

cat имя_файла.gz | gunzip | psql имя_базы

Используйте split . Команда split может разбивать выводимые данные на небольшие файлы, размер которых удовлетворяет ограничению нижележащей файловой системы. Например, чтобы получить части по 2 гигабайта:

pg_dump имя_базы | split -b 2G - имя_файла

Восстановить их можно так:

cat имя_файла* | psql имя_базы

Использовать GNU split можно вместе с gzip :

pg_dump имя_базы | split -b 2G --filter='gzip > $FILE.gz'

Восстановить данные после такого разбиения можно с помощью команды zcat .

Используйте специальный формат дампа pg_dump . Если при сборке Postgres Pro была подключена библиотека zlib , дамп в специальном формате будет записываться в файл в сжатом виде. В таком формате размер файла дампа будет близок к размеру, полученному с применением gzip , но он лучше тем, что позволяет восстанавливать таблицы выборочно. Следующая команда выгружает базу данных в специальном формате:

pg_dump -Fc имя_базы > имя_файла

Дамп в специальном формате не является скриптом для psql и должен восстанавливаться с помощью команды pg_restore , например:

pg_restore -d имя_базы имя_файла

За подробностями обратитесь к справке по командам pg_dump и pg_restore .

Для очень больших баз данных может понадобиться сочетать split с одним из двух других методов.

Используйте возможность параллельной выгрузки в pg_dump . Чтобы ускорить выгрузку большой БД, вы можете использовать режим параллельной выгрузки в pg_dump . При этом одновременно будут выгружаться несколько таблиц. Управлять числом параллельных заданий позволяет параметр -j . Параллельная выгрузка поддерживается только для формата архива в каталоге.

pg_dump -j число -F d -f выходной_каталог имя_базы

Вы также можете восстановить копию в параллельном режиме с помощью pg_restore -j . Это поддерживается для любого архива в формате каталога или специальном формате, даже если архив создавался не командой pg_dump -j .

Пред.	Наверх	След.
Глава 24. Резервное копирование и восстановление	Начало	24.2. Резервное копирование на уровне файлов