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

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

Дампы В Postgre SQL  Резервное Копирование И Восстановление

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

pg_dump

В postgresql резервное копирование одной конкретной базы данных можно осуществить с помощью pg_dump. Во работы этой утилиты пользователь может обращаться к БД: записывать или читать данные.

Формат дампа пользователь определяет сам. Это может быть архив или скрипт. Скрипт - это текстовый файл с перечнем SQL команд. Восстановление БД с помощью скрипта реализуется несколькими путями:

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

Восстановления БД с помощью архива реализуется утилитой pg_restore.

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

Важно не забывать: pg_dump создает дамп только одного экземпляра БД. При наличии глобальных объектов PostgreSQL необходимо использовать утилиту pg_dumpall, речь о которой пойдет дальше.

Синтаксис

pg_dump [параметры для подключения] [параметры дампа] [имя БД*] > [каталог, куда необходимо сохранить backup postgres database]

* Если не задать имя БД, то вместо него будет использоваться значение переменной окружения PGDATABASE. А если PGDATABASE не присвоено какое-либо значение, то pg_dump воспользуется именем пользователя, инициирующего утилиту.

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

  • -d [name] или --dbname=[name]: имя БД. Равнозначно [имя базы данных].
  • -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
  • -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
  • -U [name] или --username=[name]: имя пользователя.

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

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

pg_dumpall

Pg_dumpall создает бэкап целого кластера или инстанса. Результат работы утилиты - SQL скрипт. Во многих аспектах эта утилита похожа на pg_dump.

Синтаксис

pg_dumpall [параметры для подключения] [параметры дампа] > [каталог, куда необходимо сохранить дамп]

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

  • -d [connection string] или --dbname=[connection string]: задаем строку подключения
  • -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
  • -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
  • -U [name] или --username=[name]: имя пользователя.
  • -l [name] или --database=[name]: выбираем БД, через которую загрузим глобальные объекты.

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

  • -a или --data-only: сохраняем только данные. Например, при использовании этого параметра связи между таблицами не сохраняются.
  • -c или --clean: добавляем в дамп команды DROP перед CREATE. Может понадобится при наличии объектов с одинаковыми именами.
  • -f [catalog] или --file=[catalog]: каталог, куда сохраняем дамп. Параметр равнозначен указанному в синтаксисе [каталог, куда необходимо сохранить дамп]
  • -g или --globals-only: сохраняем только глобальные объекты.
  • -o или --oids: сохранять OID.
  • -O или --no-owner: не добавляем в скрипт команды, связанные с установкой владельцев.
  • -r или --roles-only: сохраняем только роли.
  • -s или --schema-only: сохраняем только схемы.
  • -t или --tablespaces-only: сохраняем только табличные пространства.
  • -x или --no-privileges или --no-acl: не сохраняем права доступа.

pg_basebackup

Pg_basebackup - это утилита для создания бэкапа всего инстанса или кластера. Результат работы - дамп в бинарном формате. Сам процесс нельзя настроить: вы сохраняете кластер (инстанс) целиком. В postgresql список пользователей, обладающих правом создания дампа с помощью pg_basebackup, ограничен. Для этого необходимо быть суперпользователем или обладать правом REPLICATION.

Синтаксис

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

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

  • -d [connection string] или --dbname=[connection string]: задаем строку подключения
  • -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
  • -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
  • -U [name] или --username=[name]: имя пользователя.

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

  • -D [catalog] или --pgdata=[catalog]: каталог, каталог, куда сохраняем дамп.
  • -F [format] или --format=[format]: формат дампа. Может иметь следующие значения:
    • p или plain: обычные файлы;
    • t или tar: формат .tar;
  • -r [speed]или --max-rate=[speed]: задаем максимальную скорость передачи данных в Кб/с
  • -Z [0..9] или --compress=[0..9]: выбираем уровня сжатия (0 - не сжимать, 9 - максимальный).

pg_restore

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

Синтаксис

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

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

  • -h [name] или --host=[name]: имя сервера. По умолчанию host = PGHOST.
  • -p [port] или --port=[port]: порт. По умолчанию port = PGPORT.
  • -U [name] или --username=[name]: имя пользователя.
  • -w или --no-password: отключаем запрос пароля.
  • -W или --password: принудительно включаем запрос пароля.
  • --role=[name]: задаем имя роли.

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

  • -a или --data-only: восстанавливаем только данные.
  • -c или --clean: удаляем одноименные объекты перед восстановлением.
  • -C или --create: создаем БД перед восстановлением.
  • -d [name] или --dbname=[name]: подключаемся к [name] БД и восстанавливаем данные в неё.
  • -e или --exit-on-error: завершаем восстановление в случае ошибки.
  • -j [count] или --jobs=[count]: осуществляем восстановление в многопоточном режиме ([count]=количество потоков)
  • -n [schema] или --schema=[schema]: восстанавливаем объекты только этой схемы.
  • -N [schema] или --exclude-schema=[schema]: не восстанавливаем объекты этой схемы.
  • -O или --no-owner: не восстанавливаем права на владение объектами.
  • -s или --schema-only: восстанавливаем только схему
  • -t таблица или --table=таблица: восстанавливаем только указанную таблицу.
  • -x или --no-privileges или --no-acl: не восстанавливаем права доступа.

wal-g

Wal-g - это сторонняя утилита для выгрузки дампов в хранилища и восстановления БД. Её действие распространяется не только на PostgreSQL, но и на другие СУБД. Wal-g поддерживает работу с несколькими типами хранилищ. Мы сосредоточимся на работе с S3.

Загрузка и установка

Wal-g работает в linux-системах, для работы на Win 10 необходимо использовать сервисы наподобие WSL. Работа с wal-g начинается с github, где расположены файлы утилиты. На вкладке releases размещены версии для различных систем и СУБД. Для установки последней версии wal-g на Ubuntu 20.04 выполняем эти команды:

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

Настройка

Для настройки wal-g можно воспользоваться как переменными окружения, так и конфигурационным файлом. Мы создадим конфигурационный файл для хранилища S3 по такому шаблону bash:

cat > /var/lib/postgresql/.walg.json << EOF
{
    "WALG_S3_PREFIX": "Путь к вашему бакету",
    "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

*Дельта-бэкап - это бэкап разницы между текущим состоянием системы и последним полным бэкапом. Такой подход позволяет экономить пространство в хранилище. Количество дельт - это количество таких бэкапов. 

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

За создание бэкапов в wal-g отвечает функция backup_push. Её можно использовать прямо в консоли следующим образом:

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

pgAdmin

pgAdmin — это утилита с графическим интерфейсом для создания дампов. Для начала работы переходим на сайт pgAdmin и загружаем подходящую версию программы:

Image1

Image2

После загрузки и установки необходимо подключиться к серверу:

Image4

Image3

Telegram
VK
Скопировать ссылку
Как использовать Axios в React
Как использовать Axios в React
Как пользоваться SSH
Как пользоваться SSH

Зарегистрируйтесь и начните пользоваться
сервисами Timeweb Cloud прямо сейчас

15 лет опыта
Сосредоточьтесь на своей работе: об остальном позаботимся мы
165 000 клиентов
Нам доверяют частные лица и компании, от небольших фирм до корпораций
Поддержка 24/7
100+ специалистов поддержки, готовых помочь в чате, тикете и по телефону