Дампы в PostgreSQL: резервное копирование и восстановление
Важным моментом в администрировании любой базы данных является вопрос сохранности данных от воздействия внешних и внутренних факторов: физическая неисправность сервера, взлом БД или ошибка пользователя. Резервное копирование занимает особое место в вопросе сохранности информации. В postgresql это реализуется встроенными и сторонними инструментами, о которых сегодня и пойдет речь.
Создание резервных копий
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 и загружаем подходящую версию программы:
После загрузки и установки необходимо подключиться к серверу: