Идея этого метода дампа заключается в создании файла с командами SQL, который при отправке обратно на сервер воссоздает базу данных в том же состоянии, в котором она была на момент дампа. предоставляет утилиту
для этой цели. Основное использование этой команды:
pg_dumpdbname
>dumpfile
Как видите, записывает свой результат в стандартный вывод. Ниже мы увидим, чем это может быть полезно. Хотя приведенная выше команда создает текстовый файл, она может создавать файлы в других форматах, обеспечивающих параллелизм и более детальный контроль над восстановлением объекта.
Чтобы указать, к какому серверу базы данных следует обращаться, используйте параметры командной строки -h
host
и -p
port
. Хостом по умолчанию является локальный хост или любой другой PGHOST
.
указывает переменная среды. Аналогичным образом порт по умолчанию обозначается PGPORT
.
переменная среды или, в противном случае, по скомпилированному значению по умолчанию. (Удобно то, что сервер обычно имеет одно и то же скомпилированное значение по умолчанию.)
Важным преимуществом метода резервного копирования по сравнению с другими методами резервного копирования, описанными ниже, является то, что выходные данные обычно можно повторно загрузить в более новые версии , тогда как резервное копирование на уровне файлов и непрерывное архивирование в значительной степени зависят от версии сервера. это также единственный метод, который будет работать при переносе базы данных на другую архитектуру компьютера, например при переходе с 32-битного на 64-битный сервер.
26.1.1. Восстановление свалки #
Текстовые файлы, созданные программой, предназначены для чтения. Общая форма команды для восстановления дампа:
psqldbname
<dumpfile
По умолчанию сценарий продолжит выполнение после возникновения ошибки SQL. Возможно, вы захотите бежать с ON_ERROR_STOP
переменная установлена для изменения этого поведения и имеет выход со статусом завершения 3 в случае возникновения ошибки SQL:
psql --set ON_ERROR_STOP=ondbname
<dumpfile
В любом случае у вас будет лишь частично восстановленная база данных. Альтернативно вы можете указать, что весь дамп должен быть восстановлен как одна транзакция, чтобы восстановление либо было полностью завершено, либо полностью откатилось. Этот режим можно указать, передав -1
или --single-transaction
параметры командной строки для . При использовании этого режима имейте в виду, что даже незначительная ошибка может привести к отмене восстановления, которое уже выполнялось в течение многих часов. Однако это все же может быть предпочтительнее, чем вручную очищать сложную базу данных после частично восстановленного дампа.
Возможность записи или чтения из каналов позволяет выгружать базу данных непосредственно с одного сервера на другой, например:
pg_dump -hhost1
dbname
| psql -hhost2
dbname
Важно
Дампы, произведенные относительно template0
. Это означает, что любые языки, процедуры и т. д., добавленные через template1
,
также будет сброшен. В итоге при восстановлении, если использовать кастомный template1
, вы должны создать пустую базу данных из template0
, как в примере выше.
26.1.2. Использование #
выгружает только одну базу данных за раз и не выгружает информацию о ролях или табличных пространствах (поскольку они являются общими для всего кластера, а не для каждой базы данных). Для поддержки удобного дампа всего содержимого кластера базы данных
программа предоставляется. создает резервную копию каждой базы данных в данном кластере, а также сохраняет данные всего кластера, такие как определения ролей и табличных пространств. Основное использование этой команды:
pg_dumpall > dumpfile
Полученный дамп можно восстановить с помощью:
psql -f dumpfile
постгрес
работает, выдавая команды для воссоздания ролей, табличных пространств и пустых баз данных, а затем вызывая каждую базу данных. Это означает, что, хотя каждая база данных будет внутренне согласованной, снимки разных баз данных не синхронизируются.
Данные всего кластера можно сбросить отдельно, используя --globals-only
вариант. Это необходимо для полного резервного копирования кластера при запуске команды на отдельных базах данных.
26.1.3. Работа с большими базами данных #
Некоторые операционные системы имеют ограничения на максимальный размер файла, что вызывает проблемы при создании больших выходных файлов. К счастью, запись может осуществляться в стандартный вывод, поэтому вы можете использовать стандартные инструменты Unix, чтобы обойти эту потенциальную проблему. Есть несколько возможных способов:
Используйте сжатые дампы.
Вы можете использовать свою любимую программу сжатия, например:
pg_dumpdbname
| gzip >filename
.gz
Gunzip -cfilename
.гз | psqldbname
котfilename
.гз | застегнуть молнию | psqldbname
Использовать split
.
split
Команда позволяет разделить выходные данные на файлы меньшего размера, размер которых приемлем для базовой файловой системы. Например, чтобы создать фрагменты по 2 гигабайта:
pg_dumpdbname
| раскол -б 2G -filename
котfilename
* | psqldbname
Если используется GNU, можно использовать его и вместе:
pg_dump dbname
| Split -b 2G --filter='gzip > $FILE.gz'
Его можно восстановить с помощью zcat
.
pg_dump -Fcdbname
>filename
Дамп пользовательского формата не является сценарием для , его необходимо восстановить с помощью , например:
pg_restore -ddbname
filename
См.
и
справочные страницы для получения подробной информации.
Для очень больших баз данных может потребоваться объединить split
с одним из двух других подходов.
Используйте функцию параллельного дампа.
Чтобы ускорить дамп большой базы данных, вы можете использовать параллельный режим. Это приведет к одновременному сбросу нескольких таблиц. Вы можете контролировать степень параллелизма с помощью -j
параметр. Параллельные дампы поддерживаются только для формата архива «каталог».
pg_dump -jnum
-Ф д -фout.dir
dbname
Вы можете использовать pg_restore -j
восстанавливать дамп параллельно. Это будет работать для любого архива в режиме архивирования «пользовательский» или «каталог», независимо от того, был ли он создан с помощью pg_dump -j
или нет.
.
Уже давно известно, что делать бэкапы в SQL-дампы (используя pg_dump
или pg_dumpall
) – не самая хорошая идея. Для резервного копирования СУБД PostgreSQL лучше использовать команду pg_basebackup
, которая делает бинарную реставрацию WAL-журналов. Но когда вы начнёте изучать весь процесс создания и восстановления, то поймёте, что нужно написать как минимум пару трёхколесных велосипедов, чтобы всё это работало и не вызывало у вас боли, как сверху, так и привычно. Дабы дополнительные трудности были разработаны WAL-G.
ВАЛ-Г
– этот инструмент, написанный на Go для резервного копирования и восстановления баз данных PostgreSQL ( в недавнее время и MySQL/MariaDB, MongoDB и FoundationDB
). Он поддерживает работу с хранилищами Amazon S3 (и аналогами, например, Yandex Object Storage), а также Google Cloud Storage, Azure Storage, Swift Object Storage и просто с файловой системой. Вся настройка сводится к простым шагам, но из-за того, что статьи о нем разрознены по интернету – нет полного практического руководства, которое бы учитывало все шаги от и до (на Хабре есть несколько постов, но некоторые моменты там упущены).
Данная статья написана в первую очередь для систематизации наших знаний. Я не являюсь администратором базы данных и где-то могу выражаться обывательско-разработочным языком, поэтому приветствуются любые исправления!
Отдельно отмечу, что всё нижеприведенное актуально и проверено для PostgreSQL 12.3 в Ubuntu 18.04, все команды должны выполняться от привилегированного пользователя.
На момент написания данной статьи стабильная версия WAL-G – v0.2.15 (март 2020 г.)
. Его мы и будем использовать ( , но если вы решите самостоятельно собрать его из мастер-ветки, то в репозитории на github есть все инструкции для этого
). Для скачивания и установки необходимо выполнить настройку:
#!/bin/bash
curl -L "https://github.com/wal-g/wal-g/releases/download/v0.2.15/wal-g.linux-amd64.tar.gz" -o "wal-g.linux-amd64.tar.gz"
tar -xzf wal-g.linux-amd64.tar.gz
mv wal-g /usr/local/bin/
После этого нужно сконфигурировать сначала WAL-G, а потом сам PostgreSQL.
Для обычного хранения бэкапов будет использоваться Amazon S3 ( , потому что он ближе к моим серверам и его использование обходится очень дёшево
). Для работы с ним нужен «s3-бакет» и ключи доступа.
Во всех предыдущих статьях о WAL-G использовалось конфигурирование с помощью окружения, но в этом релизе настройки можно ограничить в файле .walg.json .
в домашней директории пользователя postgres. Для его создания выполним следующий bash-скрипт:
#!/bin/bash
cat > /var/lib/postgresql/.walg.json << EOF
{
"WALG_S3_PREFIX": "s3://your_bucket/path",
"AWS_ACCESS_KEY_ID": "key_id",
"AWS_SECRET_ACCESS_KEY": "secret_key",
"WALG_COMPRESSION_METHOD": "brotli",
"WALG_DELTA_MAX_STEPS": "5",
"PGDATA": "/var/lib/postgresql/12/main",
"PGHOST": "/var/run/postgresql/.s.PGSQL.5432"
}
EOF
# обязательно меняем владельца файла:
chown postgres: /var/lib/postgresql/.walg.json
Немного поясню по всем параметрам:
- WALG_S3_PREFIX
– путь к вашему S3-бакету куда будут заливаться бэкапы (можно как в корень, так и в папку); - AWS_ACCESS_KEY_ID
– ключ доступа в S3 ( в случае восстановления на тестовом сервере – данные ключи должны иметь ReadOnly Policy! Об этом подробнее написано в разделе про восстановление
); - AWS_SECRET_ACCESS_KEY
– секретный ключ в хранилище S3; - WALG_COMPRESSION_METHOD
– метод компрессии, лучше использовать Brotli (так как это золотая середина между итоговым размером и скоростью сжатия/разжатия); - WALG_DELTA_MAX_STEPS
– количество «дельт» до создания полного бэкапа (позволяют экономить время и размер загружаемых данных, но могут чуть замедлить процесс восстановления, поэтому не желательно использовать большие значения); - PGDATA
– путь к директории с данными вашей базы ( можно узнать, выполнив команду pg_lsclusters
); - PGHOST
– подключение к базе, при локальном бэкапе лучше делать через unix-socket как в этом примере.
Остальные параметры можно посмотреть в документации: https://github.com/wal-g/wal-g/blob/v0.2.15/PostgreSQL.md#configuration
.
Чтобы архиватор внутри базы сам заливал WAL-журналы в облако и восстанавливался из них (в случае необходимости) – нужно задать несколько параметров в конфигурационном файле /etc/postgresql/12/main/postgresql.conf
. Только для начала
вам нужно убедиться
, что никакие из нижеприведенных настроек не заданы в какие-то другие значения, чтобы при перезагрузке конфигурации – СУБД не упала. Добавить эти параметры можно с помощью:
#!/bin/bash
echo "wal_level=replica" >> /etc/postgresql/12/main/postgresql.conf
echo "archive_mode=on" >> /etc/postgresql/12/main/postgresql.conf
echo "archive_command='/usr/local/bin/wal-g wal-push \"%p\" >> /var/log/postgresql/archive_command.log 2>&1' " >> /etc/postgresql/12/main/postgresql.conf
echo “archive_timeout=60” >> /etc/postgresql/12/main/postgresql.conf
echo "restore_command='/usr/local/bin/wal-g wal-fetch \"%f\" \"%p\" >> /var/log/postgresql/restore_command.log 2>&1' " >> /etc/postgresql/12/main/postgresql.conf
# перезагружаем конфиг через отправку SIGHUP сигнала всем процессам БД
killall -s HUP postgres
Описание устанавливаемых параметров:
- wal_level
– сколько информации писать в WAL журналы, «replica» – писать всё; - archive_mode
– включение загрузки WAL-журналов используя команду из параметра archive_command
; - archive_command
– команда, для архивации завершённого WAL-журнала; - archive_timeout
– архивирование журналов производится только когда он завершён, но если ваш сервер мало изменяет/добавляет данных в БД, то имеет смысл выставить тут лимит в секундах, по истечению которого команда архивации будет вызвана принудительно ( у меня интенсивная запись в базу каждую секунду, поэтому я отказался от установки этого параметра в продакшене
); - restore_command
– команда восстановления WAL-журнала из бэкапа, будет использоваться в случае если в «полном бэкапе» (base backup) будет недоставать последних изменений в БД.
Подробнее обо всех этих параметрах можно прочитать в переводе официальной документации: https://postgrespro.ru/docs/postgresql/12/runtime-config-wal
.
Настройка расписания резервного копирования
Как ни крути, но самым удобным способом для запуска – является cron. Именно его мы и настроим для создания резервных копий. Начнём с команды создания полного бэкапа: в wal-g это аргумент запуска backup-push
. Но для начала лучше выполнить эту команду вручную от пользователя postgres, чтобы убедиться что всё хорошо (и нет каких-то ошибок доступа):
#!/bin/bash
su - postgres -c '/usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main'
В аргументах запуска указан путь к директории с данными – напоминаю что его можно узнать, выполнив pg_lsclusters
.
Если всё прошло без ошибок и данные загрузились в хранилище S3, то далее можно настроить периодический запуск в crontab:
#!/bin/bash
echo "15 4 * * * /usr/local/bin/wal-g backup-push /var/lib/postgresql/12/main >> /var/log/postgresql/walg_backup.log 2>&1" >> /var/spool/cron/crontabs/postgres
# задаем владельца и выставляем правильные права файлу
chown postgres: /var/spool/cron/crontabs/postgres
chmod 600 /var/spool/cron/crontabs/postgres
В данном примере процесс бэкапа запускается каждый день в 4:15 утра.
Удаление старых резервных копий
Скорее всего вам не нужно хранить абсолютно все бэкапы с мезозойской эры, поэтому будет полезно периодически «подчищать» ваше хранилище (как «полные бэкапы», так и WAL-журналы). Мы сделаем это всё также через cron задачу ( обратите внимание на экранирование символов $ и %
):
#!/bin/bash
echo "30 6 * * * /usr/local/bin/wal-g delete before FIND_FULL \$(date -d '-5 days' '+\\%FT\\%TZ') --confirm >> /var/log/postgresql/walg_delete.log 2>&1" >> /var/spool/cron/crontabs/postgres
# ещё раз задаем владельца и выставляем правильные права файлу (хоть это обычно это и не нужно повторно делать)
chown postgres: /var/spool/cron/crontabs/postgres
chmod 600 /var/spool/cron/crontabs/postgres
Cron будет выполнять эту задачу каждый день в 6:30 утра, удаляя всё (полные бэкапы, дельты и WAL’ы) кроме копий за последние 10 дней, но оставит как минимум один бэкап
до
указанной даты, чтобы любая точка
после
даты попадала в PITR.
Восстановление из резервной копии
Ни для кого не секрет что залог здоровой базы – в периодическом восстановлении и проверке целостности данных внутри. Как восстановиться с помощью WAL-G – расскажу в этом разделе, а о проверках поговорим после.
Отдельно стоит отметить
что для восстановления на тестовом окружении (всё то, что не production) – нужно использовать Read Only аккаунт в S3, чтобы случайно не перезаписать бэкапы. В случае с WAL-G нужно задать пользователю S3 следующие права в Group Policy ( Effect: Allow
): s3:GetObject
, s3:ListBucket
, s3:GetBucketLocation
. И, конечно, предварительно не забыть выставить archive_mode=off
в файле настроек postgresql.conf
, чтобы ваша тестовая база не захотела сбэкапиться по-тихому.
Восстановление производится лёгким движением руки с удалением всех данных PostgreSQL
(в том числе пользователей), поэтому, пожалуйста, будьте предельно аккуратны когда будете запускать следующие команды.
#!/bin/bash
# если есть балансировщик подключений (например, pgbouncer), то вначале отключаем его, чтобы он не нарыгал ошибок в лог
service pgbouncer stop
# если есть демон, который перезапускает упавшие процессы (например, monit), то останавливаем в нём процесс мониторинга базы (у меня это pgsql12)
monit stop pgsql12
# или останавливаем мониторинг полностью
service monit stop
# останавливаем саму базу данных
service postgresql stop
# удаляем все данные из текущей базы (!!!); лучше предварительно сделать их копию, если есть свободное место на диске
rm -rf /var/lib/postgresql/12/main
# скачиваем резервную копию и разархивируем её
su - postgres -c '/usr/local/bin/wal-g backup-fetch /var/lib/postgresql/12/main LATEST'
# помещаем рядом с базой специальный файл-сигнал для восстановления (см. https://postgrespro.ru/docs/postgresql/12/runtime-config-wal#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY ), он обязательно должен быть создан от пользователя postgres
su - postgres -c 'touch /var/lib/postgresql/12/main/recovery.signal'
# запускаем базу данных, чтобы она инициировала процесс восстановления
service postgresql start
Для тех, кто хочет проверять процесс восстановления — ниже подготовлен небольшой кусок bash-магии, чтобы в случае проблем в восстановлении – скрипт упал с ненулевым exit code. В данном примере делается 120 проверок с таймаутом в 5 секунд (всего 10 минут на восстановление), чтобы узнать удалился ли сигнальный файл (это будет означать что восстановление прошло успешно):
#!/bin/bash
CHECK_RECOVERY_SIGNAL_ITER=0
while [ ${CHECK_RECOVERY_SIGNAL_ITER} -le 120 ]
do
if [ ! -f "/var/lib/postgresql/12/main/recovery.signal" ]
then
echo "recovery.signal removed"
break
fi
sleep 5
((CHECK_RECOVERY_SIGNAL_ITER+1))
done
# если после всех проверок файл всё равно существует, то падаем с ошибкой
if [ -f "/var/lib/postgresql/12/main/recovery.signal" ]
then
echo "recovery.signal still exists!"
exit 17
fi
После успешного восстановления не забудьте запустить обратно все процессы (pgbouncer/monit и тд).
Проверка данных после восстановления
Обязательно нужно проверить целостность базы после восстановления, чтобы не возникло ситуации с побитой/кривой резервной копией. И лучше делать это с каждым созданным архивом, но где и как – зависит только от вашей фантазии (можно поднимать отдельные сервера на почасовой оплате или запускать проверку в CI). Но как минимум – необходимо проверять данные и индексы в базе.
Для проверки данных достаточно прогнать их через дамп, но лучше чтобы при создании базы у вас были включены контрольные суммы ( data checksums
):
#!/bin/bash
if ! su - postgres -c 'pg_dumpall > /dev/null'
then
echo 'pg_dumpall failed'
exit 125
fi
Для проверки индексов – существует модуль amcheck
, sql-запрос к нему возьмём из тестов WAL-G
и вокруг выстроим небольшую логику:
#!/bin/bash
# добавляем sql-запрос для проверки в файл во временной директории
cat > /tmp/amcheck.sql << EOF
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check(c.oid), c.relname, c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
AND c.relpersistence != 't'
AND i.indisready AND i.indisvalid;
EOF
chown postgres: /tmp/amcheck.sql
# добавляем скрипт для запуска проверок всех доступных баз в кластере
# (обратите внимание что переменные и запуск команд – экранированы)
cat > /tmp/run_amcheck.sh << EOF
for DBNAME in \$(su - postgres -c 'psql -q -A -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" ')
do
echo "Database: \${DBNAME}"
su - postgres -c "psql -f /tmp/amcheck.sql -v 'ON_ERROR_STOP=1' \${DBNAME}" && EXIT_STATUS=\$? || EXIT_STATUS=\$?
if [ "\${EXIT_STATUS}" -ne 0 ]
then
echo "amcheck failed on DB: \${DBNAME}"
exit 125
fi
done
EOF
chmod +x /tmp/run_amcheck.sh
# запускаем скрипт
/tmp/run_amcheck.sh > /tmp/amcheck.log
# для проверки что всё прошло успешно можно проверить exit code или grep’нуть ошибку
if grep 'amcheck failed' "/tmp/amcheck.log"
then
echo 'amcheck failed: '
cat /tmp/amcheck.log
exit 125
fi
Выражаю благодарность Андрею Бородину за помощь в подготовке публикации и отдельное спасибо за его вклад в разработку WAL-G!
На этом данная заметка подошла к концу. Надеюсь что смог донести легкость настройки и огромный потенциал для применения этого инструмента у вас в компании. Я очень много слышал о WAL-G, но никак не хватало времени сесть и разобраться. А после того как внедрил его у себя – из меня вышла эта статья.
Отдельно стоит заметить, что WAL-G также может работать со следующими СУБД:
- MySQL/MariaDB
; - MongoDB
; - FoundationDB
; - И судя по коммитам – ожидается ещё несколько!
Тематические термины: PostgreSQL
, SQL
В данной инструкции рассмотрены варианты и примеры создания резервных копий и восстановления баз СУБД PostgreSQL.
Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.
Создание резервных копий
Базовая команда
pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>
Пользователь и пароль
Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U
с указанием пользователя:
* где dmosk
— имя учетной записи; опция W
потребует ввода пароля.
Сжатие данных
Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:
Скрипт для автоматического резервного копирования
Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.
Для начала, создадим каталог, в котором разместим скрипт, например:
И сам скрипт:
Вариант 1.
Запуск от пользователя root; одна база.
Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:
3 0 * * * /scripts/postgresql_dump.sh
* наш скрипт будет запускаться каждый день в 03:00.
Вариант 2.
Запуск от пользователя postgres; все базы.
* где /backup
— каталог, в котором будут храниться резервные копии; pathB
— путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.
Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, /backup/postgres
.
Зададим в качестве владельца файла, пользователя postgres:
Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:
crontab -e -u postgres
* мы откроем на редактирование cron для пользователя postgres
.
3 0 * * * /scripts/postgresql_dump.sh
* наш скрипт будет запускаться каждый день в 03:00.
Права и запуск
Разрешаем запуск скрипта, как исполняемого файла:
chmod +x /scripts/postgresql_dump.sh
Единоразово можно запустить задание на выполнение резервной копии:
su — postgres -c «/scripts/postgresql_dump.sh»
На удаленном сервере
Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:
* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL
.
Дамп определенной таблицы
Запускается с опцией -t <table>
или —table=<table>
:
Если наша таблица находится в определенной схеме, то она указывается вместе с ней, например:
Размещение каждой таблицы в отдельный файл
Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:
pg_dump -d customers > /tmp/folder
* где /tmp/folder
— путь до каталога, в котором разместяться файлы дампа для каждой таблицы.
Для определенной схемы
В нашей базе может быть несколько схем. Если мы хотим сделать дамп только для определенной схемы, то используем опцию -n, например:
pg_dump -n public peoples > /tmp/peoples.public.sql
* в данном примере мы заархивируем схему public
базы данных peoples
.
Только схемы (структуры)
Для резервного копирования без данных (только таблицы и их структуры):
Также, внутри каждой базы могут быть свои схемы с данными. Если нам нужно сделать дамп именно той схемы, которая внутри базы, используем ключ -n:
Или полный дамп с данными для схемы внутри базы данных:
Только данные
Использование pgAdmin
Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.
В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:
При желании, можно изучить дополнительные параметры для резервного копирования:
После нажимаем Резервная копия
— ждем окончания процесса и кликаем по Завершено
.
Не текстовые форматы дампа
Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные.
Бинарный с компрессией:
Использование pg_basebackup
pg_basebackup позволяет создать резервную копию для кластера PostgreSQL.
pg_basebackup -h node1 -D /backup
* в данном примере создается резервная копия для сервера node1
с сохранением в каталог /backup
.
pg_dumpall
Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта.
pg_dumpall > cluster.bak
Утилиту удобно использовать с ключом -g (—globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).
Для создание резервного копирования со сжатием:
Восстановление
Может понадобиться создать базу данных. Это можно сделать SQL-запросом:
Если мы получим ошибку:
ERROR: encoding «UTF8» does not match locale «en_US»
DETAIL: The chosen LC_CTYPE setting requires encoding «LATIN1».
Указываем больше параметров при создании базы:
Базовая команда
psql <имя базы> < <файл с дампом>
С авторизацией
При необходимости авторизоваться при подключении к базе вводим:
* где dmosk
— имя учетной записи; опция W
потребует ввода пароля.
Из файла gz
Сначала распаковываем файл, затем запускаем восстановление:
Или одной командой:
Определенную базу
Если резервная копия делалась для определенной базы, запускаем восстановление:
Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d:
Определенную таблицу
Если резервная копия делалась для определенной таблицы, можно просто запустить восстановление:
Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t:
С помощью pgAdmin
Выбираем наш файл с дампом:
И кликаем по Восстановить
:
Использование pg_restore
Данная утилита предназначена для восстановления данных не текстового формата (в одном из примеров создания копий мы тоже делали резервную копию не текстового формата).
С созданием новой базы:
Мы можем использовать опцию d для указания подключения к конкретному серверу и базе, например:
Работа с CSV
Мы можем переносить данные с использованием файлов csv. Это нельзя назвать напрямую резервным копированием, но в рамках данной инструкции материал будет интересен.
Создание файла CSV (экспорт)
Пример запроса (выполняется в командной оболочке SQL):
Также мы можем сделать выгрузку, но сделать вывод в оболочку и перенаправить его в файл:
Импорт данных из файла CSV
Также можно выполнить запрос в оболочке SQL:
Или перенаправить запрос через STDOUT из файла:
Возможные ошибки
Рассмотрим некоторые проблемы, с которыми можно столкнуться при работе с дампами PostgreSQL.
Input file appears to be a text format dump. please use psql.
Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore.
Решение: восстановить данные можно командой psql <имя базы> < <файл с дампом> или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор.
No matching tables were found
Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.
Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.
Too many command-line arguments
Причина: Утилита pg_dump чувствительна к лишним пробелам.
Решение: проверьте, что нет лишних пробелов.
Aborting because of server version mismatch
Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.
Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql/<version>/bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.
No password supplied
Причина: нет системной переменной PGPASSWORD или она пустая.
Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD).
Неверная команда \
Причина: при выполнении восстановления возникла ошибка, которую СУБД не показывает при стандартных параметрах восстановления.
Решение: запускаем восстановление с опцией -v ON_ERROR_STOP=1
, например:
Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.
- использовать периодическое резервное копирование средствами pg_dump;
- использовать резервное копирование на основе базовых копий и архивов WAL.
- возможность восстановить кластер базы на любой момент времени относительно времени создания базовой копии и времени сбоя;
- в качестве условия для восстановления может служить как временная отметка так и конкретная транзакция.
- базовая копия занимает приблизительный размер кластера базы данных;
- необходимость хранения WAL-архивов за период хранения базовой копии.
Как уже было сказано выше, этот способ резервного копирования предлагает гибкие возможности по восстановлению (можно восстановить состояния базы данных в четко указанный момент времени или в момент до или после выполнения определенной транзакции), но в то же время добавляет значительные требования к хранению резервных копий. Реализация выглядит следующим образом:
- настройка режима архивирования WAL-логов;
- настройка резервного копирования;
- хранение одной или более резервных копий;
- удаление самой старой резервной копии в случае успешного выполнения п.1;
- удаление соответствующих WAL-архивов от резервной копии из п.3;
- опционально можно проводить процедуру проверки резервных копий на предмет их «профпригодности».
Режим архивирования WAL-логов настраивается через включение параметров archive_mode и archive_command в postgresql.conf и создание директории где будут храниться архивы. Для начала стоит включить режим архивирования и оценить объем архивов создаваемых за одни сутки работы базы данных. Это позволит провести оценку требуемого места для хранения архивов и базовых копий. За архивирование отвечают опции:
archive_mode = on
archive_command = ‘cp %p /opt/pgsql/pgbackup/archive/%f’
Непосредственное резервное копирование настраиваем средствами pg_basebackup. Это программа из комплекта утилит идущих вместе с PostgreSQL которую можно использовать как для настройки потоковой репликации, так и для снятия резервных копий. Принцип работы позволяет снимать резервную копию не останавливая кластер базы данных. Исходя из задачи, нам всего лишь нужно запускать pg_basebackup по расписанию в cron. Учитывая требования по месту, нужно позаботиться о достаточном месте на диске, во избежание переполнения.
Хранение резервных копий задача опциональная, так как достаточно иметь хотя бы одну резервную копию. Подразумевается что на момент запуска создания резервной копии мы согласны с утверждением что база находится в «правильном» состоянии (мы ведь не будем копировать битую базу).
После завершения создания резервной копии, старую копию можно удалить вместе с архивами. Удаление архивов выполняется с помощью утилиты pg_archivecleanup. Утилита позволяет аккуратно удалить ненужные архивы основываясь на специальных файлах-метках, которые создаются при резервном копировании.
Также немаловажно настроить процедуру проверки резервной копии после её создания. Алгоритм достаточно простой: нужно скопировать базовую копию в некий каталог-песочницу (осторожно, место!), создать в ней минимально необходимые файлы конфигурации необходимые для запуска в режиме восстановления и запустить postgres относительно этого каталога-песочницы, после запуска необходимо проанализировать лог и сделать вывод является ли резервная копия пригодной для восстановления.
Таким образом процесс укладывается в три шага: создание базовой копии, её проверка и удаление старой, предыдущей базовой копии.
Теперь предположим что случилось наихудшее и нужно выполнить восстановление. Нужно остановить основной кластер postgres и переименовать каталог базы данных в произвольное имя. Каталог резервной копии нужно переименовать в каталог кластера базы данных. При необходимости скопировать файлы конфигурации. После определения конфигурационных файлов, запускаем postgres относительно нашего каталога. При запуске, Postgres обнаружит recovery.conf и запустится в режиме восстановления. Остается дождаться пока postgres восстановит свое состояние с помощью архивов, после чего можно будет подключаться к базе данных и продолжить работу. Вот и все, процедура восстановления завершена.
Вот так вот. Держите данные в сохранности! Скрипты для резервного копирования и валидации копий здесь
.
pg_restore — восстановить базу данных из файла архива, созданного командой
Описание
Утилита предназначена для восстановления базы данных из архива, созданного командой
в любом из не текстовых форматов. Она выполняет команды, необходимые для восстановления того состояния базы данных, в котором база была сохранена. При наличии файлов архивов, может восстанавливать данные избирательно или даже переупорядочить объекты перед восстановлением. Заметьте, что разработанный для файлов архива формат не привязан к архитектуре.
Утилита может работать в двух режимах. Если указывается имя базы данных, подключается к этой базе данных и восстанавливает содержимое архива непосредственно в неё. В противном случае создаётся SQL-скрипт с командами, необходимыми для пересоздания базы данных, который затем выводится в файл или в стандартное устройство вывода. Сформированный скрипт будет в точности соответствовать выводу в простом текстовом формате. Поэтому некоторые из параметров, управляющих выводом, аналогичны параметрам .
Разумеется, может восстановить информацию, только если она присутствует в файле архива, и только в существующем виде. Например, если архив был создан с указанием « выгрузить данные в виде команд INSERT
»
, не сможет загрузить эти данные, используя операторы COPY
.
Параметры
Утилита принимает следующие аргументы командной строки.
Указывает расположение восстанавливаемого файла архива (или каталога, для архива в формате каталога). По умолчанию используется устройство стандартного ввода.
Восстанавливать только данные, без схемы (определений данных). При этом восстанавливаются данные таблиц, большие объекты и значения последовательностей, имеющиеся в архиве.
Флаг похож на
--section=data
, но по историческим причинам не равнозначен ему.Удалить (DROP) объекты базы данных, прежде чем пересоздавать их. ( Без дополнительного указания
--if-exists
при этом могут выдаваться безвредные сообщения об ошибках, если таких объектов не окажется в целевой базе данных.)Создать базу данных, прежде чем восстанавливать данные. Если также указан параметр
--clean
, удалить и пересоздать целевую базу данных перед подключением к ней.С этим параметром база, заданная параметром
-d
, применяется только для подключения и выполнения начальных командDROP DATABASE
иCREATE DATABASE
. Все данные восстанавливаются в базу данных, имя которой записано в архиве.Подключиться к базе данных
имя_базы
и восстановить данные непосредственно в неё. В данном аргументе может задаваться строка подключения
. В этом случае параметры в строке подключения переопределяют одноимённые параметры, заданные в командной строке.Завершать работу в случае возникновения ошибки при выполнении команд SQL в базе данных. По умолчанию процесс восстановления продолжается, а по его окончании выдаётся число ошибок.
Задаёт файл для вывода сгенерированного скрипта или списка объектов, получаемого с параметром
-l
. Чтобы выбрать , используйте-
.Задаёт формат архива. Указывать формат необязательно, так как определяет формат автоматически. Но если формат задаётся, допускается один из этих вариантов:
Архив сохранён в специальном формате .
Архив сохранён в каталоге.
Архив сохранён в формате
tar
.
Восстановить определение только заданного индекса. Добавив дополнительные ключи
-I
, можно указать несколько индексов.Выполнять наиболее длительные этапы (в частности, загрузку данных, создание индексов или ограничений) параллельно, используя несколько заданий (в количестве, не превышающем
число-заданий
). Это позволяет кардинально сократить время восстановления большой базы данных, когда сервер работает на многопроцессорном компьютере. Данный параметр игнорируется, когда генерируется скрипт (нет прямого подключения к базе данных).Каждое задание выполняется в отдельном задании или потоке, в зависимости от операционной системы, и использует отдельное подключение к серверу.
Оптимальное значение этого параметра зависит от аппаратной конфигурации сервера, клиента и сети. В частности, имеет значение количество процессорных ядер и устройство дискового хранилища. В качестве начального значения можно выбрать число ядер на сервере, но и при увеличении этого значения во многих случаях восстановление будет быстрее. Конечно, при слишком больших значениях производительность упадёт из-за перегрузки.
Этот параметр поддерживается только с архивом в специальном формате или в каталоге. Входные данные должны поступать из обычного файла или каталога, а не из канала или стандартного устройства ввода. Кроме того, несколько заданий не могут выполняться в сочетании с параметром
--single-transaction
.Восстановить из архива только элементы, перечисленные в
файле-списке
, и в том порядке, в каком они идут в этом файле. Заметьте, что когда вместе с-L
применяются параметры фильтрации (например,-n
или-t
), они дополнительно ограничивают восстанавливаемые объекты.Данный
файл-список
обычно представляет собой отредактированный результат предыдущей операции-l
. Строки в нём могут быть переставлены или удалены, а также могут быть закомментированы точкой с запятой (;
), добавленной в начале строки. См. примеры ниже.Восстановить только объекты в указанной схеме. Добавив дополнительные ключи
-n
, можно указать несколько схем. Этот параметр можно сочетать с-t
, чтобы восстановить только определённую таблицу.Не восстанавливать объекты в указанной схеме. Добавив дополнительные ключи
-N
, можно исключить несколько схем.Когда и с ключом
-n
, и с ключом-N
передаётся имя одной схемы, ключ-N
выигрывает и схема исключается.Не генерировать команды, устанавливающие владение объектами, как в исходной базе данных. По умолчанию, генерирует команды
ALTER OWNER
илиSET SESSION AUTHORIZATION
, восстанавливающие исходных владельцев создаваемых элементов схемы. Однако эти команды можно будет выполнить, только если к базе данных первоначально подключается суперпользователь (или пользователь, владеющими всеми объектами в скрипте). Чтобы получить скрипт, который сможет восстановить любой подключающийся пользователь (но при этом он станет владельцем всех созданных объектов), используется-O
.Восстановить только указанную функцию. При этом важно записать имя функции и аргументы в точности так, как они фигурируют в оглавлении файла архива. Добавив дополнительные ключи
-P
, можно указать несколько функций.Параметр является устаревшим, но в целях совместимости ещё работает.
Восстановить только схему (определения данных), без данных, в объёме, в котором элементы схемы представлены в архиве.
Действие параметра противоположно действию
--data-only
. Это похоже на указание--section=pre-data --section=post-data
, но по историческим причинам не равнозначно ему.(Не путайте этот параметр с
--schema
, где слово используется в другом значении.)Задаёт имя суперпользователя, полномочия которого будут использоваться для отключения триггеров. Этот параметр применяется только с параметром
.
Восстановить только указанный триггер. Добавив дополнительные ключи
-T
, можно указать несколько триггеров.Сообщить версию и завершиться.
Не восстанавливать права доступа (не выполнять команды GRANT/REVOKE).
Произвести восстановление в одной транзакции (то есть, завернуть выполняемые команды в
BEGIN
/COMMIT
). При этом гарантируется, что либо все команды будут выполнены успешно, либо не будет никаких изменений. Этот режим подразумевает--exit-on-error
.Этот параметр действует только при выгрузке одних данных. С ним выполняет команды, отключающие триггеры в целевых таблицах на время восстановления данных. Используйте его, если в ваших таблицах определены проверки ссылочной целостности или другие триггеры, которые вы не хотели бы выполнять в процессе восстановления данных.
В настоящее время команды, генерируемые с
--disable-triggers
, должны выполнятся суперпользователем. Поэтому необходимо также задать имя суперпользователя в параметре-S
или, что предпочтительнее, запускать от имени суперпользователя .Этот параметр имеет смысл только при восстановлении содержимого таблицы, для которой включена защита строк. По умолчанию устанавливает для row_security
значение off для уверенности, что в таблице восстановлены все данные. Если у пользователя недостаточно прав для обхода защиты строк, выдаётся ошибка. Этот параметр указывает установить в row_security
значение on, чтобы пользователь мог попытаться восстановить содержимое таблицы с включённой защитой строк. Однако и при этом возможна ошибка, если пользователь не будет иметь права добавлять в эту таблицу выгруженные строки данных.Заметьте, что в настоящее время для этого требуется, чтобы выгрузка выполнялась в режиме
INSERT
, так какCOPY FROM
не поддерживает защиту строк.При удалении объектов базы использовать условные команды (то есть добавлять предложение
IF EXISTS
). Применяется только с параметром--clean
.По умолчанию данные восстанавливаются даже при ошибке команды создания таблицы (например, когда она уже существует). С этим параметром данные в таком случае не восстанавливаются. Это поведение полезно, если в целевой таблице уже содержатся нужные данные. Например, вспомогательные таблицы для расширений (в частности, ) могут быть уже заполнены; этот параметр позволяет предотвратить дублирование или загрузку устаревших данных в эти таблицы.
Этот параметр действует только при восстановлении непосредственно в базу данных (не при генерации SQL-скрипта).
Не выводить команды, восстанавливающие публикации, даже если они содержатся в архиве.
Не выводить команды, восстанавливающие метки безопасности, даже если они содержатся в архиве.
Не выводить команды, восстанавливающие подписки, даже если они содержатся в архиве.
Не выводить команды для указания табличных методов доступа. При восстановлении все объекты будут создаваться с табличным методом доступа, выбираемым по умолчанию.
Не формировать команды для указания табличных пространств. При восстановлении все объекты будут создаваться в табличном пространстве по умолчанию.
Восстановить только указанный раздел. В качестве имени раздела можно задать
pre-data
,data
илиpost-data
. Указав этот параметр неоднократно, можно выбрать несколько разделов. По умолчанию восстанавливаются все разделы.Раздел «data» содержит собственно данные таблиц и определения больших объектов. В разделе «post-data» содержатся определения индексов, триггеров, правил и ограничений (кроме отдельно проверяемых). Раздел «pre-data» содержит все остальные определения.
Требует, чтобы каждому указанию схемы (
-n
/--schema
) и таблицы (-t
/--table
) соответствовала минимум одна схема/таблица в файле резервной копии.Выводить команды
SET SESSION AUTHORIZATION
, соответствующие стандарту, вместоALTER OWNER
, для назначения владельцев объектов. В результате выгруженный скрипт будет более стандартизированным, но может не восстановиться корректно, в зависимости от истории объектов.Показать справку по аргументам командной строки и завершиться.
Восстановить определение и/или данные только указанной таблицы. В этом контексте под подразумеваются также представления, материализованные представления, последовательности и сторонние таблицы. Чтобы выбрать несколько таблиц, ключ -t
можно указать несколько раз. Этот параметр можно скомбинировать с -n
, чтобы выбрать таблицу(ы) в определённой схеме.
Примечание
Когда указывается -t
, не пытается восстанавливать объекты базы данных, от которых могут зависеть выбранные таблицы. Таким образом, в этом случае не гарантируется, что выгруженные таблицы будут успешно восстановлены в чистой базе данных.
Примечание
Этот флаг действует не совсем так, как флаг -t
утилиты . В настоящее время не поддерживает выбор объектов по маске, а также не позволяет указать имя схемы с -t
. И хотя с флагом -t
также выгружает подчинённые объекты (например, индексы) выбранных таблиц, с флагом -t
такие подчинённые объекты не обрабатывает.
Примечание
В версиях до 9.6 этот флаг выбирал только таблицы, но не другие типы отношений.
также принимает в качестве параметров соединения следующие аргументы командной строки:
Указывает имя компьютера, на котором работает сервер. Если значение начинается с косой черты, оно определяет каталог Unix-сокета. Значение по умолчанию берётся из переменной окружения
PGHOST
, если она установлена. В противном случае выполняется подключение к Unix-сокету.Указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения. Значение по умолчанию определяется переменной окружения
PGPORT
, если она установлена, либо числом, заданным при компиляции.Имя пользователя, под которым производится подключение.
Не выдавать запрос на ввод пароля. Если сервер требует аутентификацию по паролю и пароль не доступен с помощью других средств, таких как файл
.pgpass
, попытка соединения не удастся. Этот параметр может быть полезен в пакетных заданиях и скриптах, где нет пользователя, который вводит пароль.Принудительно запрашивать пароль перед подключением к базе данных.
Это несущественный параметр, так как запрашивает пароль автоматически, если сервер проверяет подлинность по паролю. Однако чтобы понять это, лишний раз подключается к серверу. Поэтому иногда имеет смысл ввести
-W
, чтобы исключить эту ненужную попытку подключения.Задаёт имя роли, которая будет осуществлять восстановление. Получив это имя, выполнит
SET ROLE
имя_роли
после подключения к базе данных. Это полезно, когда проходящий проверку пользователь (указанный в-U
) не имеет прав, необходимых для , но может переключиться на роль, наделённую этими правами. В некоторых окружениях правила запрещают подключаться к серверу непосредственно суперпользователю, и этот параметр позволяет выполнить восстановление, не нарушая их.
Переменные окружения
Параметры подключения по умолчанию
Выбирает вариант использования цвета в диагностических сообщениях. Возможные значения:
always
(всегда),auto
(автоматически) иnever
(никогда).
Как и большинство других утилит , эта утилита также использует переменные среды, поддерживаемые (см. Раздел 32.15
). Однако она не учитывает PGDATABASE
, когда имя базы не указано.
Диагностика
Когда с параметром -d
устанавливается прямое подключение к базе данных, выполняет обычные операторы SQL
. При этом применяются все свойства подключения по умолчанию и переменные окружения, которые использует клиентская библиотека . Если вы сталкиваетесь с проблемами при запуске , убедитесь в том, что вы можете получить информацию из базы данных, используя, например
.
Замечания
Если в вашей инсталляции база данных template1
содержит какие-либо дополнения, важно убедиться в том, что вывод загружается в действительно пустую базу; иначе вы, скорее всего, получите ошибки из-за дублирования определений создаваемых объектов. Чтобы получить пустую базу данных без дополнительных объектов, выберите в качестве шаблона template0
, а не template1
, например так:
CREATE DATABASE foo WITH TEMPLATE template0;
Ограничения описаны ниже.
При восстановлении данных в уже существующие таблицы с параметром
--disable-triggers
, выполняет команды, отключающие триггеры в пользовательских таблицах до добавления данных, а затем, после добавления данных, выполняет команды, снова включающие эти триггеры. Если восстановление прервётся в середине, системные каталоги могут оказаться в некорректном состоянии.Утилита не способна восстанавливать большие объекты избирательно; например, только для определённой таблицы. Если архив содержит большие объекты, они будут восстановлены все, либо не будут восстановлены никакие (если они были исключены параметрами
-L
,-t
и т. п.).
Также обратитесь к документации
, чтобы узнать о связанных ограничениях .
После восстановления имеет смысл запустить ANALYZE
для каждой восстановленной таблицы, чтобы оптимизатор получил актуальную статистику; за дополнительными сведениями обратитесь к Подразделу 23.1.3
и Подразделу 23.1.6
.