Automated Backup on Windows
Updated to reflect changes from 8.3 to 11
- This method uses pg_dump.exe along with a batch file to call it. This batch file will create a file/directory for each day it is run.
- Keep in mind pg_dump and pg_dumpall are version specific meaning do not use pg_dump from 9.3 to backup version 11.0. The option -i & —ignore-version are ignored
Files needed to run pg_dump & pg_dumpall
- Getting the pg_dump, pg_dumpall binaries one has to extract it from a PostgreSQL Server Install, compile, or download binaries from EDB. There is no package available to get just these files.
- Should download and install the Windows C/C++ runtime libraries from Microsoft for the version Postgresql being used, version 11.0 uses VS-2013
- Go to backup server/location create a Directory called Drive:\PostgresqlBack then create a sub directory called «bin» in the Drive:\PostgresqlBack and place the following files in this directory.
libeay32.dll
libiconv-2.dll
libintl-8.dll
libintl-9.dll
libpg.dll
libwinpthread-1.dll
msvr120.dll
pg_dump.exe
pg_dumpall.exe
ssleay32.dll
zlib1.dll
Using pgdump, creates a new file for each day
- Create batch file called something, example is postgresqlBackup.bat. The file must be located in PostgresqlBack directory not the bin folder.
- Open the File then Copy/Paste the following
@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set BACKUP_FILE=<NameOfTheFile>_%datestr%.backup
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=<PassWord>
echo on
bin\pg_dump -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>
- Change <NameOfTheFile> to something. One idea is use the name of the database.(make sure there are no spaces after the word BACKUP_FILE any spaces will cause this setting not to work.)Setting is the first part of the file name then followed by the date the file was created with the extension .backup
- Change the <PassWord > setting above to the correct password for the backup users. (make sure there is no spaces after the word PGPASSWORD any spaces will cause this setting not to work. Description of pgPassword
- Change <HostName> either to ip address or dns name of the server hosting Postgresql.
- Change <UserName> to backup user make sure this users has access to database for backup purposes
- Change <DATABASENAME> to the database name being backed up.
- Save the File
- Create Task for the MS Task Scheduler
- Once you have chosen the security context the Task in going to run in, it is advised to change the directory security where the backup is run and the files are stored, as a high level user name and password are stored in plain text.
- Another option is to modify the pg_hba.conf file adding the backup server as a trusted connection
New feature in pg_dump as of 9.1
The option -F d. This changes the backup from creating a single large TAR file, it will now will create a directory and contain individual files for each table.
the pg_dump command looks like this
bin\pg_dump -h <HostName> -p 5432 -U <UserName> -F d -b -v -f %BACKUP_FILE% <DATABASENAME>
There are a few advantages to the -F d option. One restores can be made significantly faster as pg_restore has the option to run parallel connections instead of one table at a time. Two its faster to extract specific table(s) to restore when using this option vs TAR format. Three when coping/moving the files off-site and it fails in mid-stream it does not have to restart at the beginning unlike one large TAR file, for large databases this is a big plus.
Using pg_dumpall
It is necessary to use pg_dumpall to get the logins/roles, and schema information as pg_dump does not include this information
set BACKUP_FILE=Globals\Globals_%datestr%.backup.sql
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=<PassWord>
echo on
bin\pg_dumpall -g -h <HostName> -p 5432 -U <UserName> -v -f %BACKUP_FILE% <DATABASENAME>
This creates a sql script used to restore roles and schema information. Keep in mind if not restoring to the same server settings such TableSpaces are most likely not valid and will error.
Резервное копирование — одна из важнейших задач системного администратора. Хорошо если копии вам никогда не пригодятся, но они должны быть. Сегодня мы рассмотрим некоторые аспекты резервного копирования популярной СУБД PostgreSQL, в частности при ее применении совместно с 1С:Предприятие. Начнем с самого простого и понятного способа — использования утилиты pg_dump, которая, кстати, может использоваться не только для резервного копирования, но и для переноса баз между различными серверами.
Онлайн-курс по устройству компьютерных сетей
На углубленном курсе «Архитектура современных компьютерных сетей» вы с нуля научитесь работать с Wireshark и «под микроскопом» изучите работу сетевых протоколов. На протяжении курса надо будет выполнить более пятидесяти лабораторных работ в Wireshark.
Как и любой иной способ использование pg_dump для копирования имеет свои плюсы и минусы. К основному минусу можно отнести то, что создаваемый дамп является срезом базы данных на некоторый момент времени и позволяет откатиться только на это состояние. Восстановление на произвольный момент времени невозможно.
Иные способы, позволяющие такое восстановление, работают на уровне инстанса и позволяют восстановить сразу весь кластер, т.е. все базы. Поэтому рекомендации по продуктовому применению PostgreSQL предусматривают основную схему: 1 база — 1 инстанс, что для небольших внедрений может быть избыточно как по ресурсам, так и по накладным расходам на администрирование.
В тоже время pg_dump работает на уровне базы данных и позволяет копировать и откатывать именно определенную базу, не затрагивая соседей по кластеру. Это несомненный плюс.
Также pg_dump может использоваться для переноса баз данных, она кроссплатформенна и кроссверсионна, т.е. позволяет переносить базы между разными платформами и разными версиями PostgreSQL. При этом следует помнить, что версии PostgreSQL совместимы снизу вверх, совместимость сверху вниз не поддерживается, либо поддерживается на ограниченное число версий. Т.е. вы всегда сможете загрузить дамп из PostgreSQL 9.6 в PostgreSQL 15, но не наоборот.
Подготовка сервера
Чтобы удобно работать с утилитами PostgreSQL добавим путь к ним в переменную окружения PATH. Для этого перейдем в Свойства системы — Дополнительно — Переменные окружения.
Затем найдем в списке переменную PATH и изменим ее, добавив новой строкой путь к папке bin вашего экземпляра PostgreSQL, в нашем случае это C:\Program Files\PostgreSQL\15.5-10.1C\bin:
Теперь вы можете обращаться к утилитам PostgreSQL просто по имени. Однако при написании скриптов всегда указывайте полный путь, что позволит избежать ошибок, если у запустившего пользователя скрипт в переменной PATH не окажется указанного выше пути.
Следующий вопрос — пароль суперпользователя СУБД, либо другого пользователя, имеющего нужные права для выгрузки и загрузки базы. В подавляющем большинстве случаев с пользователями никто не заморачивается и все работают от суперпользователя postgres. Для этого можно пойти несколькими путями, один из них создание специального файла паролей, для этого создайте в указанные ниже директорию и файл:
%APPDATA%\postgresql\pgpass.confПосле чего внесите в него следующие строки:
#имя_узла:порт:база_данных:имя_пользователя:парольlocalhost:*:*:postgres:MyPa$$Word_1
Первая строка — это подсказка-комментарий, чтобы вам потом не приходилось вспоминать синтаксис. А ниже мы указали, что при подключении к инстансу расположенному на локальном узле localhost, использующему любой порт, для любой базы данных, при подключении пользователем postgres использовать указанный пароль. Первые четыре поля могут использовать подстановочный знак * указывающий на любое значение. Например, если у вас множество серверов с одинаковым паролем, то можете указать так:
*:*:*:postgres:MyPa$$Word_1Данный способ является рекомендуемым, но у него есть недостаток в виде хранения пароля в открытом виде. Поэтому можно использовать альтернативу, разрешить локальные подключения без пароля. Для этого найдите файл pg_hba.conf который находится в директории кластера data, в нашем случае он расположен по умолчанию в C:\Program Files\PostgreSQL\15.5-10.1C\data. В данный файл добавьте строку:
host all all localhost trustСохраните файл и перезапустите службу PostgreSQL. После чего вы можете без пароля подключаться к базе через localhost, обратите внимание, что в данном случае localhost не равнозначен 127.0.0.1 и если вы укажете адрес, то подключиться не получится.
Создание резервной копии базы данных
Утилита pg_dump умеет создавать копии в разных форматах, каждый из которых имеет свои достоинства и недостатки.
- plain — выгрузка в текстовом SQL формате. Наиболее универсальна, а при необходимости позволяет вручную откорректировать дамп или выполнить частичную загрузку или восстановление, например отдельной таблицы. Не сжимается, имеет большой размер.
- custom — собственный формат pg_dump, предусматривает сжатие данных и возможность многопоточной загрузки, выгружается всегда однопоточно
- directory — выгрузка в виде директории, на каждую таблицу выгружается отдельный сжатый файл, позволяет многопоточную выгрузку и загрузку.
- tar — представляет, по сути, выгрузку в виде директории, но упакованной в tar-архив, сжатие не предусмотрено, поэтому размер выгрузки будет больше, чем у директории, однопоточен.
Таким образом, наиболее удобными с практической точки зрения является формат custom, либо directory — если вам требуется многопоточная выгрузка. При переносе между разными системами предпочтительно использовать plain, так как он представляет набор SQL команд и может быть легко отредактирован вручную.
Итак приступим. Прежде всего следует узнать какие базы данных есть на нашем сервере и как они называются, для этого выполним:
psql -h localhost -U postgres -lВ выводе мы увидим список баз и их параметры:
В нашем случае мы будем бекапить базу данных bkp1 и местом хранения резервных копий определим D:\Backup. Начнем с текстового формата, он используется по умолчанию и отдельно указывать его не нужно:
pg_dump -h localhost -U postgres -f D:\Backup\bkp1.sql bkp1Общий синтаксис команды такой: сначала указываем все используемые ключи, первый аргумент без ключа считается именем базы данных, и оно должно быть последним в команде. Указывать ключи после имени базы не следует. В команде мы использовали ключи:
- -h сервер — указывает имя или адрес компьютера, на котором работает сервер СУБД
- -U имя_пользователя — имя пользователя, под которым производится подключение
- -f файл — файл, в который производится выгрузка
Также ниже мы будем использовать ключи:
- -F формат — формат выгрузки
- -j число_заданий — количество потоков
С полным перечнем ключей можно ознакомиться в официальной документации.
В некоторых источниках можно встретить команду в виде:
pg_dump -h localhost -U postgres bkp1 > D:\Backup\bkp1.sqlВажно! В среде Windows не используйте перенаправление для выгрузки и загрузки резервных копий!
Мы неоднократно сталкивались с тем, что выгруженные через перенаправление копии внешне выглядели вполне нормально, в том числе и копии в текстовом формате, но при восстановлении отказывались загружаться. Это происходит не всегда и не везде, но для исключения подобных ситуаций перенаправление в Windows использовать не следует.
Теперь создадим выгрузку в формате custom:
pg_dump -h localhost -U postgres -Fc -f D:\Backup\bkp1.dump bkp1Здесь у нас добавился еще один ключ, указывающий на формат выгрузки, если вы хотите выгрузить в формате tar, просто замените -Fc на -Ft.
И, наконец, в формате директории:
pg_dump -h localhost -U postgres -Fd -f D:\Backup\bkp1_dir -j 4 bkp1Для данного формата у нас появился еще один ключ, указывающий число потоков выгрузки. Число потоков не должно превышать количество ядер процессора, но не все так просто: pg_dump спокойно нагрузит каждый поток, создав 100% загрузку ядер процессора, но сможет ли его принять устройство хранения? Может получиться так, что скорость записи на накопитель окажется бутылочным горлышком и вместо ускорения вы получите замедление как выгрузки дампа, так и всей системы вообще.
Поэтому начните с небольшого количества, двух или четырех потоков, оцените нагрузку на систему и остановитесь на некотором оптимальном значении, это особенно важно, если на сервере в момент выгрузки будут работать пользователи. Нам ведь совершенно ни к чему чтобы они каждый час жаловались на тормоза.
Еще одна тонкость, что будет если указанный файл выгрузки существует? Форматы, custom и tar молча перезапишут его. При выгрузке в формате directory вы получите сообщение, что целевая директория не пуста и выгрузка выполнена не будет. Это следует учитывать при написании скриптов, потому как при ошибке вы либо останетесь без старых копий, либо не будут создаваться новые. Хотя визуально все будет нормально.
Особенно легко ошибиться в случае с directory, если вы используете для выгрузки одну и ту же директорию, потом дополнительно архивируете ее и отправляете на устройство хранения. В этом случае вы будете архивировать одну и ту же старую копию. Поэтому директорию выгрузки надо всегда очищать или удалять.
Восстановление базы данных из резервной копии
Начнем с того, что восстановить базу данных PostgreSQL можно только в новую, пустую базу. Если мы хотим восстановить ее в существующую, то ее придется сначала удалить, а потом создать новую с таким же именем. Исключение — формат plain, это просто набор SQL-команд, которые мы можем выполнить на рабочей базе, выборочно туда что-то подгрузив. Но это требует определенных знаний и квалификации, что выходит за рамки нашей статьи.
Итак, прежде всего удалим старую базу:
dropdb -h localhost -U postgres -i bkp1В приведенной команде мы использовали ключ -i который запросит интерактивное подтверждение действия:
База данных "bkp1" будет удалена навсегда.
Продолжить? (y/n)Почему мы это сделали и советуем вам поступать также? Как показывает практика, данные команды часто вводятся методом копирования, неважно откуда, из статьи, документации, собственных записей, истории команд. В этом случае интерактивный запрос послужит предохранителем, который позволит остановиться и задуматься что вы делаете.
А теперь создадим новую:
createdb -h localhost -U postgres -T template0 bkp1Ключ -T указывает использовать при создании базы полностью пустой шаблон template0. Если вы хотите выполнить восстановление в отдельную новую базу, то просто создайте ее приведенной выше командой.
Начнем восстановление с формата plain, так как это не дамп, а набор SQL команд, то для их исполнения мы будем использовать утилиту psql:
psql -h localhost -U postgres -d bkp1 D:\Backup\bkp1.sqlФормат команды здесь такой же, сначала ключи, потом файл или директория, из которой идет восстановление. Ключ -d указывает имя базы данных, в которую мы загружаем выгрузку.
Для остальных форматов следует использовать утилиту pg_restore, например восстановим дамп формата custom, в два потока:
pg_restore -h localhost -U postgres -d bkp1 -j 2 D:\Backup\bkp1.dumpСинтаксис тот же самый, просто указываем базу и файл, с форматом утилита разберется самостоятельно. Если же вы попытаетесь подсунуть ей формат plain, то утилита откажется делать загрузку и любезно посоветует вам использовать для этого psql.
Что касается выбора количества потоков, то исходим из тех же соображений: один поток — одно ядро и обязательно тестируем, чтобы производительность диска не стала узким горлышком. Если вы выполняете восстановление в рабочее время, то учтите также влияние на работу пользователей, чтобы не вышло что вы запустили восстановление, а у всех остальных все стало.
При восстановлении из формата directory вместо файла укажите путь к папке:
pg_restore -h localhost -U postgres -d bkp1 -j 2 D:\Backup\bkp1_dirНапоминаем, что формат tar многопоточную загрузку не поддерживает, но вы можете его распаковать и загрузить многопоточно в формате directory.
Также есть способ несколько упростить себе жизнь, чтобы не удалять и не создавать заново базу данных мы можем выполнить:
pg_restore -h localhost -U postgres -d postgres -C -c D:\Backup\bkp1.dumpКлючи -C -c предписывают перед загрузкой удалить и создать заново базу имя которой записано в дампе, в ключе -d при этом потребуется указать любую существующую базу, обычно указывается стандартная postgres. В результате выполнения данной команды вы можете получать некоторые безвредные сообщения об ошибках.
Однако использовать эту команду следует очень осторожно и только на том сервере, откуда был сделан дамп. Также убедитесь, что вы взяли именно тот дамп, что надо. Но мы не рекомендуем так делать, особенно на продуктовых серверах, лучше осознанно удалите базу руками.
Онлайн-курс по устройству компьютерных сетей
На углубленном курсе «Архитектура современных компьютерных сетей» вы с нуля научитесь работать с Wireshark и «под микроскопом» изучите работу сетевых протоколов. На протяжении курса надо будет выполнить более пятидесяти лабораторных работ в Wireshark.
Обновлено:
Опубликовано:
Тематические термины: PostgreSQL, SQL
В данной инструкции рассмотрены варианты и примеры создания резервных копий и восстановления баз СУБД PostgreSQL.
Создание копий
Базовая команда
Пользователь и пароль
Сжатие данных
Скрипт
На удаленном сервере
Дамп определенной таблицы
Каждая таблица в свой файл
Для определенной схемы
Только схемы
Только данные
pgAdmin
Не текстовые форматы
pg_basebackup
pg_dumpall (все базы данных)
Восстановление
Базовая команда
С авторизацией
Из файла gz
Определенную базу
Определенную таблицу
С помощью pgAdmin
pg_restore (бинарные бэкапы)
Работа с CSV
Возможные проблемы
Input file appears to be a text format dump. please use psql
No matching tables were found
Too many command-line arguments
Aborting because of server version mismatch
No password supplied
Неверная команда \
Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.
Создание резервных копий
Базовая команда
Синтаксис:
pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>
Пример:
pg_dump users > /tmp/users.dump
Также путь к файлу можно указать с помощью опции -f:
pg_dump users -f /tmp/users.dump
Пользователь и пароль
Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:
pg_dump -U dmosk -W users > /tmp/users.dump
* где dmosk — имя учетной записи; опция W потребует ввода пароля.
Сжатие данных
Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив. Можно для этого использовать разные подходы — использовать опцию -Z с указанием уровня компрессии от 0 до 9 или передать результат архиватору gzip. Рассмотрим оба примера.
а) С помощью опции -Z:
pg_dump -Z9 users > users.sql.gz
б) С использованием gzip:
pg_dump users | gzip > users.sql.gz
В обоих случаях будет использоваться gzip и перед восстановлением данных необходимо будет извлечь архив с помощью gunzip. Подробнее об этом ниже.
Скрипт для автоматического резервного копирования
Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL в системах Linux, а также приведем пример скрипта для Powershell (Windows).
Linux (bash)
Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.
Для начала, создадим каталог, в котором разместим скрипт, например:
mkdir /scripts
И сам скрипт:
vi /scripts/postgresql_dump.sh
Вариант 1. Запуск от пользователя root; одна база.
#!/bin/sh
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db
find $pathB \( -name «*-1[^5].*» -o -name «*-[023]?.*» \) -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date «+%Y-%m-%d»).sql.gz
unset PGPASSWORD
* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.
Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:
crontab -e
3 0 * * * /scripts/postgresql_dump.sh
* наш скрипт будет запускаться каждый день в 03:00.
Вариант 2. Запуск от пользователя postgres; все базы.
#!/bin/bash
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
pathB=/backup/postgres
find $pathB \( -name «*-1[^5].*» -o -name «*-[023]?.*» \) -ctime +61 -delete
for dbname in `echo «SELECT datname FROM pg_database;» | psql | tail -n +3 | head -n -2 | egrep -v ‘template0|template1|postgres’`; do
pg_dump $dbname | gzip > $pathB/$dbname-$(date «+%Y-%m-%d»).sql.gz
done;
* где /backup — каталог, в котором будут храниться резервные копии; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.
Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, /backup/postgres.
Зададим в качестве владельца файла, пользователя postgres:
chown postgres:postgres /scripts/postgresql_dump.sh
Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:
crontab -e -u postgres
* мы откроем на редактирование cron для пользователя postgres.
3 0 * * * /scripts/postgresql_dump.sh
* наш скрипт будет запускаться каждый день в 03:00.
Права и запуск
Разрешаем запуск скрипта, как исполняемого файла:
chmod +x /scripts/postgresql_dump.sh
Единоразово можно запустить задание на выполнение резервной копии:
/scripts/postgresql_dump.sh
… или от пользователя postgres:
su — postgres -c «/scripts/postgresql_dump.sh»
Windows (Powershell)
Данный скрипт создаст бэкапы для всех баз, кроме служебных:
$Env:PGPASSWORD = ‘password’;
$DateStr = (Get-Date).ToString(«yyyy-MM-dd»)
$BackupPath = ‘C:\TmpBackup’
psql -Atc «SELECT datname FROM pg_database;» | foreach {
if ($_ -notmatch ‘postgres|template1|template0’) {
pg_dump $_ > $BackupPath\$_.$DateStr.sql
}
}
* все резервные копии будут размещены в каталоге C:\TmpBackup.
На удаленном сервере
Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:
pg_dump -h 192.168.0.15 users > /tmp/users.dump
* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.
Дамп определенной таблицы
Запускается с опцией -t <table> или —table=<table>:
pg_dump -t students users > /tmp/students.dump
* где students — таблица; users — база данных.
Если наша таблица находится в определенной схеме, то она указывается вместе с ней, например:
pg_dump -t public.students users > /tmp/students.dump
* где public — схема; students — таблица; users — база данных.
Размещение каждой таблицы в отдельный файл
Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:
pg_dump -d customers > /tmp/folder
* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.
Для определенной схемы
В нашей базе может быть несколько схем. Если мы хотим сделать дамп только для определенной схемы, то используем опцию -n, например:
pg_dump -n public peoples > /tmp/peoples.public.sql
* в данном примере мы заархивируем схему public базы данных peoples.
Только схемы (структуры)
Для резервного копирования без данных (только таблицы и их структуры):
pg_dump —schema-only users > /tmp/users.schema.dump
Также, внутри каждой базы могут быть свои схемы с данными. Если нам нужно сделать дамп именно той схемы, которая внутри базы, используем ключ -n:
pg_dump —schema-only users -n production > /tmp/users.schema_production.dump
* в данном примере мы создадим дамп структуры базы данных users только для схемы production.
Или полный дамп с данными для схемы внутри базы данных:
pg_dump users -n production > /tmp/users.production.dump
Только данные
pg_dump —data-only users > /tmp/users.data.dump
Использование pgAdmin
Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.
Запускаем pgAdmin — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:
В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:
При желании, можно изучить дополнительные параметры для резервного копирования:
После нажимаем Резервная копия — ждем окончания процесса и кликаем по Завершено.
Не текстовые форматы дампа
Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные.
Бинарный с компрессией:
pg_dump -Fc users > users.bak
Тарбол:
pg_dump -Ft users > users.tar
Directory-формат:
pg_dump -Fd users > users.dir
Использование pg_basebackup
Утилита pg_basebackup идет в комплекте с СУБД и позволяет создать резервную копию кластера PostgreSQL. При этом, с ее помощью нельзя снять дамп определенной базы — только целиком все данные и конфигурационные файлы. Для восстановления информации нужно будет разместить полученные файлы в рабочий каталог СУБД.
Пример команды:
pg_basebackup -D /backup
* в данном примере создается резервная копия локального сервера с сохранением данных в каталог /backup.
Если мы хотим забрать данные, подключившись к удаленному серверу, нам нужно обеспечить доступ с правами replication. Для этого в файл pg_hba.conf добавляем строку:
…
host replication all 192.168.0.15/32 trust
…
* где 192.168.0.15 — компьютер, на котором мы будем запускать pg_basebackup.
Не забываем перезапустить службу postgresql, например:
systemctl restart postgresql-14
Теперь можно снимать бэкап кластера:
pg_basebackup -d postgresql://postgres@node1 -D /backup
* в данном примере создается резервная копия для сервера node1 с сохранением ее в каталог /backup.
** обратите внимание, что у нас должен быть возможность подключения к серверу node1 под пользователем postgres с компьютера, где мы запускаем pg_basebackup (для этого мы и меняли настройку в файле pg_hba.conf).
pg_dumpall
Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта.
pg_dumpall > cluster.bak
Утилиту удобно использовать с ключом -g (—globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).
Для создание резервного копирования со сжатием:
pg_dumpall | gzip > cluster.tar.gz
Восстановление
Нам может понадобиться удалить старую базу. Это можно сделать с помощью SQL-запроса:
=# DROP DATABASE users;
* в данном примере будет удалена база с именем users.
Убедитесь, что удаляете базу с нужным названием на правильном сервере.
Если получаем ошибку на подобие:
ERROR: database «users» is being accessed by other users
… значит база используется приложением. Либо останавливаем его, либо выполняем:
=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = ‘users’; DROP DATABASE users;
Также может понадобиться создать базу данных (не потребуется, если делали дамп с опцией -C). Для этого используем SQL-запрос:
=# CREATE DATABASE users WITH ENCODING=’UTF-8′;
* где users — имя базы; UTF-8 — используемая кодировка.
Если мы получим ошибку:
ERROR: encoding «UTF8» does not match locale «en_US»
DETAIL: The chosen LC_CTYPE setting requires encoding «LATIN1».Указываем больше параметров при создании базы:
CREATE DATABASE users WITH OWNER ‘postgres’ ENCODING ‘UTF8’ LC_COLLATE = ‘ru_RU.UTF-8’ LC_CTYPE = ‘ru_RU.UTF-8’ TEMPLATE = template0;
Базовая команда
Синтаксис:
psql <имя базы> < <файл с дампом>
Пример:
psql users < /tmp/users.dump
С авторизацией
При необходимости авторизоваться при подключении к базе вводим:
psql -U dmosk -W users < /tmp/users.dump
* где dmosk — имя учетной записи; опция W потребует ввода пароля.
Из файла gz
Сначала распаковываем файл, затем запускаем восстановление:
gunzip users.sql.gz
psql users < users.sql
Или одной командой:
zcat users.sql.gz | psql users
Определенную базу
Если резервная копия делалась для определенной базы, запускаем восстановление:
psql users < /tmp/database.dump
Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d:
pg_restore -d users cluster.bak
Определенную таблицу
Если резервная копия делалась для определенной таблицы, можно просто запустить восстановление:
psql users < /tmp/students.dump
Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t:
pg_restore -a -t students users.dump
С помощью pgAdmin
Запускаем pgAdmin — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим восстановить данные — выбираем Восстановить:
Выбираем наш файл с дампом:
И кликаем по Восстановить:
Использование pg_restore
Данная утилита предназначена для восстановления данных не текстового формата (в одном из примеров создания копий мы тоже делали резервную копию не текстового формата).
Из бинарника:
pg_restore -Fc users.bak
Из тарбола:
pg_restore -Ft users.tar
С созданием новой базы:
pg_restore -Ft -C users.tar
Мы можем использовать опцию d для указания подключения к конкретному серверу и базе, например:
pg_restore -d «postgresql://dmosk_user:dmosk_pass@localhost/dmosk_base» -Fc users.bak
* в данном примере мы подключимся к локальной базе (localhost) с названием dmosk_base от пользователя dmosk_user с паролем dmosk_pass.
Работа с CSV
Мы можем переносить данные с использованием файлов csv. Это нельзя назвать напрямую резервным копированием, но в рамках данной инструкции материал будет интересен.
Создание файла CSV (экспорт)
Пример запроса (выполняется в командной оболочке SQL):
> COPY (SELECT * FROM public.users WHERE name LIKE ‘А%’) TO ‘/tmp/users.csv’ WITH CSV DELIMITER ‘;’ HEADER;
* в данном примере мы выгрузим все данные для таблицы users в схеме public, где значение поля name начинается с буквы А. Результат будет сохранен в файл /tmp/users.csv. Также мы указываем, что в качестве разделителя данных нужно использовать точку с запятой и первой строкой сделать заголовок.
Также мы можем сделать выгрузку, но сделать вывод в оболочку и перенаправить его в файл:
psql -d «postgresql://pg_user:pg_pass@localhost:5432/pg_databasename» -c «COPY (SELECT * FROM public.users WHERE name LIKE ‘А%’) TO STDIN WITH CSV DELIMITER ‘;’ HEADER;» > /tmp/users.csv
Импорт данных из файла CSV
Также можно выполнить запрос в оболочке SQL:
> COPY public.users FROM ‘/tmp/test.csv’ DELIMITER ‘;’ CSV HEADER;
Или перенаправить запрос через STDOUT из файла:
psql -d «postgresql://pg_user:pg_pass@localhost:5432/pg_databasename» -c «COPY public.users FROM STDOUT DELIMITER ‘;’ CSV HEADER;» < /tmp/users.csv
* в нашем примере мы выполним импорт данных из ранее созданного файла /tmp/users.csv в таблицу users.
Возможные ошибки
Рассмотрим некоторые проблемы, с которыми можно столкнуться при работе с дампами 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, например:
psql -v ON_ERROR_STOP=1 users < /tmp/users.dump
Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.
Автор статьи: технический руководитель проектов внедрения 1С:ERP Внедренческого центра «Раздолье» Дмитрий Малышев.
Введение
pgAdmin — это интерфейс для администрирования баз данных PostgreSQL, в моём понимании это аналог MS SQL Management Studio. Ставится pgAdmin отдельно от PostgreSQL. Инструкцию установки найдите, пожалуйста, в интернет поисковиках. В данной инструкции будет описано как с помощью pgAdmin, bat-файлов и Планировщика заданий Windows организовать резервное копирование, восстановление и хранение копий баз данных.
Открытие pgAdmin
Через пуск или в проводнике открываем приложение.
«C:\Program Files\pgAdmin 4\v6\pgAdmin4.ico»
Вводим пароль доступа (за дается ранее пользователем).
Откроется интерфейс управления базами данных.
Создание резервной копии
Рассмотрим создание резервной копии из pgAdmin и командным bat-файлом.
2.1. С помощью pgAdmin
Выбираем базу в дереве, правой кнопкой мыши открываем контекстное меню, где выбираем создание резервной копии/Backup… Указываем полный путь для сохранения копии, формат Custom и жмем [Создать / Create].
***
***
2.2. С помощью командного файла *.bat
Запускаем двойным кликом мыши командный файл backup_pdadmin_UH_IMD_everyday.bat, в котором уже прописан вызов архиватора формат и путь файла копии.
Копии сохраняются сюда.
Содержимое командного файла:
REM СОЗДАНИЯ РЕЗЕРВНОЙ КОПИИ БАЗЫ ДАННЫХ POSTGRESQL
CLS
ECHO OFF
CHCP 1251
REM Установка переменных окружения
SET PGDATABASE=IMD_UH
SET PGHOST=localhost
SET PGPORT=5432
SET PGUSER=postgres
SET PGPASSWORD=ЗДЕСЬ_УКАЖИТЕ_ПАРОЛЬ_для_пользователя_postgres
REM Формирование имени файла резервной копии и файла-отчета
SET DATETIME=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
SET DUMPFILE=%PGDATABASE% %DATETIME%.backup
SET LOGFILE=%PGDATABASE% %DATETIME%.log
SET DUMPPATH=»E:\UH_IMD\Backup\%DUMPFILE%»
SET LOGPATH=»E:\UH_IMD\Backup\%LOGFILE%»
REM Создание резервной копии
IF NOT EXIST Backup MD Backup
CALL «C:\Program Files\pgAdmin 4\v6\runtime\pg_dump.exe» —format=custom —verbose —file=%DUMPPATH% 2>%LOGPATH%
REM Анализ кода завершения
IF NOT %ERRORLEVEL%==0 GOTO Error
GOTO Successfull
REM В случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале
:Error
DEL %DUMPPATH%
MSG * «ERROR to create backup!!! See the information E:\UH_IMD\Backup\backup.log.»
ECHO %DATETIME% Ошибка при создании резервной копии %DUMPFILE%. Смотрите %LOGFILE%. >> backup.log
GOTO End
REM В случае удачного резервного копирования просто делается запись в журнал
:Successfull
ECHO %DATETIME% Успешное создание резервной копии %DUMPFILE% >> backup.log
GOTO End
:End
Пояснения:
SET PGDATABASE=IMD_UH — здесь имя базы данных на СУБД равно IMD_UH, у вас будет свое поменяйте обязательно.
E:\UH_IMD\Backup — здесь путь хранения backup у вас будет свой, поменяйте.
C:\Program Files\pgAdmin 4\v6\runtime — папка утилиты pg_dump.exe для создания дампов, пусть может чуть отличаться, например, вместо v6 будет v4. И не забудьте pgAdmin установить, он ставится отдельно.
SET PGPASSWORD=ЗДЕСЬ_УКАЖИТЕ_ПАРОЛЬ_для_пользователя_postgres — тут укажите реальный пароль от пользователя postgres СУБД PostgreSQL
Восстановление резервной копии
Есть несколько способов: Из командной строки, из pgAdmin, заранее подготовленным командным файлом. Мы рассмотрим: pgAdmin.
3.1. С помощью pgAdmin
3.1.1. В существующую базу
Выбираем базу, вызываем правой кнопкой ее контекстное меню, где выбираем действие Восстановить / Restore.
Далее указываем путь к резервной копии, и в настройках ставим предварительно очищать существующую базу (иначе она не восстановится из-за конфликта таблиц).
***
***
***
3.1.2. В новую базу
3.1.2.1. Создаем новую базу в PostgreSQL
На корне дерева баз вызываем правой кнопкой мыши контекстное меню и действие Создать / Create – Базу данных / Database.
Задаем имя новой базы.
Выбираем обязательно схему создания template0 (иначе на следующем шаге база не развернется из backup из-за конфликта таблиц).
Для контроля смотрим итоговый запрос, и жмем кнопку [Сохранить]/[Save].
3.1.2.2. Восстанавливаем базу из архива в PostgreSQL
После создания пустой новой базы, её нужно восстановить из архива. Для этого смотрите пункт выше 3.1.1. Для восстановления в существующую базу, выполняем всё тоже самое только для базы с именем NewBaseName
3.1.2.3. Создаем новую базу 1С NewBaseName
После того как развернули базу на СУБД PostgreSQL её требуется опубликовать на сервере 1С, чтобы пользователи получили к ней доступ. Для этого выполним действия по созданию базы 1С и связывании её с существующей базой на СУБД.
***
***
***
***
Не забываем ставь флаг «Установить блокировку регламентных заданий», если это копия.
Удаление старых резервных копий
4.1. Вручную
Удаляем архивы старше 30 дней вручную. Затем чистим корзину на рабочем столе.
В проводнике папка E:\UH_IMD\Backup.
***
4.2. С помощью командного файла *.bat
Запускаем двойным щелчком мыши командный файл. В файле указана очистка в каталоге файлов старше 30 дней.
Его содержимое:
forfiles /p «E:\UH_IMD\Backup» /S /D -30 /C «cmd /c del /f /a /q @file»
Пояснения:
E:\UH_IMD\Backup — здесь путь хранения backup’ов, у вас будет свое поменяйте обязательно.
30 — срок в днях хранения backup’ов
Автоматическое выполнение резервного копирования
Использован стандартный планировщик заданий Windows каждый день в 5:00 утра запуск, выполнения командного файла (архив рабочей базы примерно 1 час создается).
***
***
***
***
***
***
Автоматическое выполнение очистки копий старше 30 дней
Использован стандартный планировщик заданий Windows каждую субботу в 10:00 утра запуск, выполнения командного файла.
***
***
***
***
***
***
Приложения
Пример содержимого общего файла логов backup.log.
Пример содержимого файла лога конкретной выгрузки UH_IMD 2022-10-07 5-00-00.log
P.S. Коллеги, сразу скажу, что я не системный администратор, а программист 1С. Системщик решил бы, может быть, элегантнее.
Хотя ситуация сложилась такая, что я делал настройки и эту инструкцию с bat-никами по просьбе системных администраторов (как бы странно это ни звучало). Нечасто такими настройками занимаюсь, поэтому не судите строго.
Добавил 2 батника, по обновлению статистики и реиндексации:
Они для регламентного обслуживания. 1С завершать для их работы не обязательно, но 1С будет притормаживать или пойдет блокировка на период реиндексации. В планировщик заданий Windows на эти батники добавьте задачи раз в неделю на свои базы, в Нерабочее время. Также есть служебная база postgres на нее тоже раз в неделю добавьте обслуживание.
Файл vacuumdb_BaseName.bat — обновление статистики.
Его содержимое:
vacuumdb -d UH_IMD -Z -v -j 1
Пояснения:
Обновление статистики базы с именем UH_IMD (тут поставьте свою) в 1 поток.
Можно поменять на обновление статистики во всех базах в 4 потока, тогда будет текст:
vacuumdb -a -Z -v -j 4
Файл reindexdb_BaseName.bat — реиндексация таблиц в базе.
Его содержимое:
reindexdb -d UH_IMD -v -j 1
Пояснения:
Обновление индексов в базе с именем UH_IMD (тут поставьте свою) в 1 поток.
Можно поменять на обновление статистики во всех базах в 4 потока, тогда будет текст:
reindexdb -a -v -j 4
Также обратите внимание на программу Effector Saver — программа резервного копирования 1С:Предприятия (поищите в инете).
Делает копии в MS и PostgreSQL, настройка хранения и удаления. Есть возможность подключать скрипты и выполнять тестирование исправление 1С. Есть бесплатная версия (которой должно хватить), и есть также платная с плюшками.
Ищите для работы вот этот материал с инструкциями для скачивания и использования:
-
backup_pgadmin_BaseName_everyday.bat
-
delete_backup_BaseName_older than 30 days.bat
-
Резервное копирование и восстановление баз PostgreSQL в Windows с помощью pgAdmin, bat-файлов и планировщика.docx
-
vacuumdb_BaseName.bat
-
reindexdb_BaseName.bat
Задача резервного копирования — одна из основных при сопровождении и поддержке PostgreSQL. Для резервного копирования логической схемы и данных можно использовать как встроенные инструменты СУБД, так и внешние. В этой статье мы разберем оба варианта.
Для начала подготовим сервер. Для демо-стенда закажем виртуальный сервер в Облачной платформе. Для этого откроем панель управления my.selectel.ru, перейдем в меню Облачная платформа и нажмем на кнопку Создать сервер.
В статье будем использовать виртуальный сервер с конфигурацией 2 vCPU, 4 ГБ RAM и 10 ГБ HDD с операционной системой CentOS 8 Stream 64-bit.
Теперь прокрутим представление ниже, где находятся настройки сети. Важно, чтобы у сервера был внешний плавающий IP-адрес для доступа извне.
После выбора операционной системы, конфигурации сервера и выполнения сетевых настроек переходим к завершению заказа и нажимаем на кнопку Создать. Через несколько минут сервер будет готов.
Перед началом демонстрации возможностей резервного копирования, мы подготовили PostgreSQL. Для целей наполнения базы данных и создания непрерывного потока записи, развернули там Zabbix (некоторое время назад публиковали о нем статью).
Доверьте нам развертывание и администрирование баз данных в облаке.
Создание резервных копий и восстановление из командной строки
В этом разделе мы расскажем как сделать дамп базы данных PostgreSQL в консоли при подключении по SSH, разберем синтаксис и покажем примеры использования утилит pg_dump, pg_dumpall, pg_restore, pg_basebackup и wal-g.
Утилита pg_dump
В PostgreSQL есть встроенный инструмент для создания резервных копий — утилита pg_dump. Утилита имеет простой синтаксис:
# pg_dump <параметры> <имя базы> > <файл для сохранения копии> В простейшем случае достаточно указать имя базы данных, которую в дальнейшем нужно будет восстановить. Резервная копия создается следующей командой:
# pg_dump zabbix > /tmp/zabbix.dumpЕсли требуется авторизация под определенным пользователем, можно воспользоваться ключом -U:
# pg_dump -U zabbix -W zabbix > /tmp/zabbix.dump # pg dump u postgresКлюч -U определяет пользователя, а -W обязывает ввести пароль.
Чтобы сэкономить место на диске, можно сразу же сжимать дамп:
# pg_dump -U zabbix -W zabbix | gzip > /tmp/zabbix.gzРезервное копирование обычно выполняется по расписанию, например, ежедневно в 3 часа ночи. Нижеприведенный пример скрипта не только выполняет бэкап, но и удаляет все файлы старше 61 дня (за исключением 15-го числа месяца).
#!/bin/sh
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
PGPASSWORD=some_password
export PGPASSWORD
pathB=/mnt/backup
dbUser=dbadmin
database=zabbix
find $pathB \( -name "*-1[^5].*" -o -name "*-[023]?.*" \) -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date "+%Y-%m-%d").sql.gz
unset PGPASSWORDЧтобы настроить регулярное выполнение, выполним следующую команду в планировщике crontab:
# crontab -e
3 0 * * * /etc/scripts/pgsql_dump.sh # postgres pg dumpЧтобы выполнить аналогичную команду на удаленном сервере, достаточно добавить ключ -h:
# pg_dump -h 192.168.56.101 zabbix > /tmp/zabbix.dumpКлюч -t задает таблицу, для которой нужно создать резервную копию:
# pg_dump -t history zabbix > /tmp/zabbix.dump # postgres dump tableПри помощи специальных ключей можно создавать резервные копии структуры данных или непосредственно данных:
# pg_dump --schema-only zabbix > /tmp/zabbix.dump
# pg_dump --data-only zabbix > /tmp/zabbix.dumpУ утилиты pg_dump также есть ключи для сохранения дампа в другие форматы. Чтобы сохранить копию в виде бинарного файла используются ключи -Fc:
# pg_dump -Fc zabbix > /tmp/zabbix.bakЧтобы создать архив — -Ft:
# pg_dump -Ft zabbix > /tmp/zabbix.tarЧтобы сохранить в directory-формате — -Fd:
# pg_dump -Fd zabbix > /tmp/zabbix.dirРезервное копирование в виде каталогов позволяет выполнять процесс в многопоточном режиме.
Ниже мы перечислим возможные параметры утилиты pg_dump.
-d <имя_бд>, —dbname=имя_бд — база данных, к которой выполняется подключение.
-h <сервер>, —host=сервер — имя сервера.
-p <порт>, —port=порт — порт для подключения.
-U <пользователь>, —username=пользователь) — учетная запись, используемое для подключения.
-w, —no-password — деактивация требования ввода пароля.
-W, —password — активация требования ввода пароля.
—role=имя роли — роль, от имени которой генерируется резервная копия.
-a, —data-only — вывод только данных, вместо схемы объектов (DDL).
-b, —blobs — параметр добавляет в выгрузку большие объекты.
-c, —clean — добавление команд DROP перед командами CREATE в файл резервной копии.
-C, —create — генерация реквизитов для подключения к базе данных в файле резервной копии.
-E <кодировка>, —encoding=кодировка — определение кодировки резервной копии.
-f <файл>, —file=файл — задает имя файла, в который будет сохраняться вывод утилиты.
-F <формат>, —format=формат — параметр определяет формат резервной копии. Доступные форматы:
- p, plain) — формирует текстовый SQL-скрипт;
- c, custom) — формирует резервную копию в архивном формате;
- d, directory) — формирует копию в directory-формате;
- t, tar) — формирует копию в формате tar.
-j <число_заданий>, —jobs=число_заданий — параметр активирует параллельную выгрузку для одновременной обработки нескольких таблиц (равной числу заданий). Работает только при выгрузке копии в формате directory.
-n <схема>, —schema=схема — выгрузка в файл копии только определенной схемы.
-N <схема>, —exclude-schema=схема — исключение из выгрузки определенных схем.
-o, —oids — добавляет в выгрузку идентификаторы объектов (OIDs) вместе с данными таблиц.
-O, —no-owner — деактивация создания команд, определяющих владельцев объектов в базе данных.
-s, —schema-only —добавление в выгрузку только схемы данных, без самих данных.
-S <пользователь>, —superuser=пользователь — учетная запись привилегированного пользователя, которая должна использоваться для отключения триггеров.
-t <таблица>, —table=таблица — активация выгрузки определенной таблицы.
-T <таблица>, —exclude-table=таблица —исключение из выгрузки определенной таблицы.
-v, —verbose — режим подробного логирования.
-V, —version — вывод версии pg_dump.
-Z 0..9, —compress=0..9 — установка уровня сжатия данных. 0 — сжатие выключено.
Утилита pg_dumpall
Утилита pg_dumpall реализует резервное копирование всего экземпляра (кластера или инстанса) базы данных без указания конкретной базы данных на инстансе. По принципу схожа с pg_dump. Добавим, что только утилиты pg_dump и pg_dumpall предоставляют возможность создания логической копии данных, остальные утилиты, рассматриваемые в этой статье, позволяют создавать только бинарные копии.
# pg_dumpall > /tmp/instance.bakЧтобы сразу сжать резервную копию экземпляра базы данных, нужно передать вывод на архиватор gzip:
# pg_dumpall | gzip > /tmp/instance.tar.gzНиже приведены параметры, с которыми может вызываться утилита pg_dumpall.
-d <имя_бд>, —dbname=имя_бд — имя базы данных.
-h <сервер>, —host=сервер — имя сервера.
-p <порт>, —port=порт — TCP-порт, на который принимаются подключения.
-U <пользователь>, —username=пользователь — имя пользователя для подключения.
-w, —no-password — деактивация требования ввода пароля.
-W, —password — активация требования ввода пароля.
—role=<имя роли> — роль, от имени которой генерируется резервная копия.
-a, —data-only — создание резервной копии без схемы данных.
-c, —clean — добавление операторов DROP перед операторами CREATE.
-f <имя_файла>, —file=имя_файла — активация направления вывода в указанный файл.
-g, —globals-only — выгрузка глобальных объектов без баз данных.
-o, —oids — выгрузка идентификаторов объектов (OIDs) вместе с данными таблиц.
-O, —no-owner — деактивация генерации команд, устанавливающих принадлежность объектов, как в исходной базе данных.
-r, —roles-only — выгрузка только ролей без баз данных и табличных пространств.
-s, —schema-only — выгрузка только схемы без самих данных.
-S <имя_пользователя>, —superuser=имя_пользователя — привилегированный пользователь, используемый для отключения триггеров.
-t, —tablespaces-only — выгрузка табличных пространства без баз данных и ролей.
-v, —verbose — режим подробного логирования.
-V (—version — вывод версии утилиты pg_dumpall.
Утилита pg_restore
Утилита позволяет восстанавливать данные из резервных копий. Например, чтобы восстановить только определенную БД (в нашем примере zabbix), нужно запустить эту утилиту с параметром -d:
# pg_restore -d zabbix /tmp/zabbix.bakЧтобы этой же утилитой восстановить определенную таблицу, нужно использовать ее с параметром -t:
# pg_restore -a -t history /tmp/zabbix.bakТакже утилитой pg_restore можно восстановить данные из бинарного или архивного файла. Соответственно:
# pg_restore -Fc zabbix.bak
# pg_restore -Ft zabbix.tarПри восстановлении можно одновременно создать новую базу:
# pg_restore -Ft -С zabbix.tarВосстановить данные из дампа также возможно при помощи psql:
# psql zabbix < /tmp/zabbix.dumpЕсли для подключения нужно авторизоваться, вводим следующую команду:
# psql -U zabbix -W zabbix < /tmp/zabbix.dumpНиже приведен синтаксис утилиты pg_restore.
-h <сервер>, —host=сервер — имя сервера, на котором работает база данных.
-p <порт>, —port=порт — TCP-порт, через база данных принимает подключения.
-U <пользователь>, —username=пользователь — имя пользователя для подключения..
-w, —no-password — деактивация требования ввода пароля.
-W, —password — активация требования ввода пароля.
—role=имя роли — роль, от имени которой выполняется восстановление резервная копия.
<имя_файла> — расположение восстанавливаемых данных.
-a, —data-only — восстановление данных без схемы.
-c, —clean — добавление операторов DROP перед операторами CREATE.
-C, —create — создание базы данных перед запуском процесса восстановления.
-d <имя_бд>, —dbname=имя_бд — имя целевой базы данных.
-e, —exit-on-error — завершение работы в случае возникновения ошибки при выполнении SQL-команд.
-f <имя_файла>, —file=имя_файла — файл для вывода сгенерированного скрипта.
-F <формат>, —format=формат — формат резервной копии. Допустимые форматы:
- p, plain — формирует текстовый SQL-скрипт;
- c, custom — формирует резервную копию в архивном формате;
- d, directory — формирует копию в directory-формате;
- t, tar — формирует копию в формате tar.
-I <индекс>, —index=индекс — восстановление только заданного индекса.
-j <число-заданий>, —jobs=число-заданий — запуск самых длительных операций в нескольких параллельных потоках.
-l, —list) — активация вывода содержимого архива.
-L <файл-список>, —use-list=файл-список — восстановление из архива элементов, перечисленных в файле-списке в соответствующем порядке.
-n <пространство_имен>, —schema=схема — восстановление объектов в указанной схеме.
-O, —no-owner — деактивация генерации команд, устанавливающих владение объектами по образцу исходной базы данных.
-P <имя-функции(тип-аргумента[, …])>, —function=имя-функции(тип-аргумента[, …]) — восстановление только указанной функции.
-s, —schema-only — восстановление только схемы без самих данных.
-S <пользователь>, —superuser=пользователь — учетная запись привилегированного пользователя, используемая для отключения триггеров.
-t <таблица>, —table=таблица — восстановление определенной таблицы.
-T <триггер>, —trigger=триггер — восстановление конкретного триггера.
-v, —verbose — режим подробного логирования.
-V, —version — вывод версии утилиты pg_restore.
Утилита pg_basebackup
Утилитой pg_basebackup можно выполнять резервное копирования работающего кластера баз данных PostgreSQL. Результирующий бинарный файл можно использовать для репликации или восстановления на определенный момент в прошлом. Утилита создает резервную копию всего экземпляра базы данных и не дает возможности создавать слепки данных отдельных сущностей. Подключение pg_basebackup к PostgreSQL выполняется при помощи протокола репликации с полномочиями суперпользователя или с правом REPLICATION.
Для выполнения резервного копирования локальной базы данных достаточно передать утилите pg_basebackup параметр -D, обозначающий директорию, в которой будет сохранена резервная копия:
# pg_basebackup -D /tmpЧтобы создать сжатые файлы из табличных пространств, добавим параметры -Ft и -z:
# pg_basebackup -D /tmp -Ft -z То же самое, но со сжатием bzip2 и для экземпляра базы с общим табличным пространством:
# pg_basebackup -D /tmp -Ft | bzip2 > backup.tar.bz2Ниже приведен синтаксис утилиты pg_basebackup.
-d <строка_подключения>, —dbname=строка_подключения — определение базы данных в виде строки для подключения.
-h <сервер>, —host=сервер — имя сервера с базой данных.
-p <порт>, —port=порт — TCP-порт, через база данных принимает подключения.
-s <интервал>, —status-interval=интервал — количество секунд между отправками статусных пакетов.
-U <пользователь>, —username=пользователь — установка имени пользователя для подключения.
-w, —no-password — отключение запроса на ввод пароля.
-W, —password — принудительный запрос пароля.
-V, —version — вывод версии утилиты pg_basebackup.
-?, —help — вывод справки по утилите pg_basebackup.
-D каталог, —pgdata=каталог — директория записи данных.
-F <формат>, —format=формат — формат вывода. Допустимые варианты:
- p, plain — значение для записи выводимых данных в текстовые файлы;
- t, tar — значение, указывающее на необходимость записи в целевую директорию в формате tar.
-r <скорость_передачи>, —max-rate=скорость_передачи — предельная скорость передачи данных в Кб/с.
-R, —write-recovery-conf — записать минимальный файл recovery.conf в директорию вывода.
-S <имя_слота>, —slot=имя_слота — задание слота репликации при использовании WAL в режиме потоковой передачи.
-T <каталог_1=каталог_2>, —tablespace-mapping=каталог_1=каталог_2 — активация миграции табличного пространства из одного каталога в другой каталог при копировании.
—xlogdir=каталог_xlog — директория хранения журналов транзакций.
-X <метод>, —xlog-method=метод — активация вывода файлов журналов транзакций WAL в резервную копию на основе следующих методов:
- f, fetch — включение режима сбора файлов журналов транзакций при окончании процесса копирования;
- s, stream — включение передачи журнала транзакций в процессе создания резервной копии.
-z, —gzip — активация gzip-сжатия результирующего tar-файла.
-Z <уровень>, —compress=уровень — определение уровня сжатия механизмом gzip.
-c , —checkpoint=fast|spread — активация режима реперных точек.
-l <метка>, —label=метка — установка метки резервной копии.
-P, —progress — активация в вывод отчета о прогрессе.
-v, —verbose — режим подробного логирования.
Утилита wal-g
Wal-g — утилита для резервного копирования и восстановления базы данных PostgreSQL. При помощи wal-g можно выполнять сохранение резервных копий на хранилищах S3 или просто на файловой системе. Ниже мы разберем установку, настройку и работу с утилитой. Покажем как выполнить резервное копирование в Объектное хранилище S3 от Selectel.
Создадим пользователя для облачного хранилища, учетные данные которого будем потом использовать для сохранения резервной копии. Перейдем в меню Пользователи и нажмем кнопку Создать пользователя:
Дополнительную информацию можно получить в нашей Базе знаний. Первую часть логина изменить нельзя — это идентификатор пользователя в панели управления. Вторая часть логина задается произвольно. Например, 123456_wal-g:
Теперь перейдем к установке wal-g. Скачаем готовый установочный пакет из репозитория на github.com, распакуем и скопируем папку содержающую исполняемые файлы:
# cd /tmp
# curl -L "https://github.com/wal-g/wal-g/releases/download/v0.2.19/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 и изменим его владельца на учетную запись postgres:
# cat > //6ef4e6a1-9d49-47ac-bfed-170f67a815cf.selcdn.net/var/lib/pgsql/.walg.json << EOF
{
"WALG_S3_PREFIX": "s3://container",
"AWS_ENDPOINT": "https://s3.selcdn.ru"
"AWS_ACCESS_KEY_ID": "123456_wal-g",
"AWS_SECRET_ACCESS_KEY": "password",
"WALG_COMPRESSION_METHOD": "brotli",
"WALG_DELTA_MAX_STEPS": "5",
"PGDATA": "/var/lib/pgsql/data",
"PGHOST": "/var/run/postgresql/.s.PGSQL.5432"
}
EOF
# chown postgres: /var/lib/pgsql/.walg.jsonДалее настроим автоматизированное создание резервных копий в PostgreSQL и перезагрузим процессы базы данных:
# echo "wal_level=replica" >> /var/lib/pgsql/data/postgresql.conf
# echo "archive_mode=on" >> /var/lib/pgsql/data/postgresql.conf
# echo "archive_command='/usr/local/bin/wal-g wal-push \"%p\" >> /var/log/postgresql/archive_command.log 2>&1' " >> /var/lib/pgsql/data/postgresql.conf
# echo “archive_timeout=60” >> /var/lib/pgsql/data/postgresql.conf
# echo "restore_command='/usr/local/bin/wal-g wal-fetch \"%f\" \"%p\" >> /var/log/postgresql/restore_command.log 2>&1' " >> /var/lib/pgsql/data/postgresql.conf
# killall -s HUP postgresТеперь проверим корректность проведения настроек и загрузим резервную копию в хранилище:
# su - postgres -c '/usr/local/bin/wal-g backup-push /var/lib/pgsql/data'После выполнения процесса резервного копирования, в созданном контейнере появится директория с резервными копиями баз данных:
Такой процесс в продакшене может выполняться при помощи планировщика заданий на регулярной основе.
Утилита pgAdmin
Управлять созданием резервных копий возможно также и в графическом интерфейсе. Для этого мы будем использовать утилиту pgAdmin (в примере — работа с утилитой на локальном устройстве, но то же самое можно сделать на сервере). Актуальную версию для Windows или другой поддерживаемой ОС можно свободно скачать с официального сайта.
После скачивания утилиту нужно установить и запустить. Она работает в виде веб-приложения через браузер.
После добавления сервера с базой данных, в интерфейсе появляется возможность создания резервной копии. Аналогичным образом здесь же можно выполнить восстановление из резервной копии.
После выполнения команды Backup резервная копия сохраняется в заранее определенную директорию.
Работа с облачной базой данных в панели управления Selectel
В облачной платформе Selectel есть возможность создавать управляемые базы данных (Managed Databases). Такие БД разворачиваются в несколько кликов мыши, однако, их основные преимущества — автоматическое резервное копирование, отказоустойчивость, быстрое масштабирование и управление различными характеристиками из графического интерфейса. Ниже мы создадим экземпляр управляемой базы данных, создадим резервную копию базы данных на виртуальном сервере и восстановим ее в управляемую базу данных.
Чтобы создать управляемую базу данных, перейдем в меню Базы данных и нажмем кнопку Создать кластер:
Появится форма создания кластера. Здесь можно выбрать версию PostgreSQL, конфигурацию кластера, настройки сети, режим пулинга и размер пула.
Обращаем внимание на блок Резервные копии, в котором указаны частота резервного копирования, время и срок хранения выгрузок. Под капотом используется механизм wal-g, о котором мы писали выше.
Автоматическое создание резервных копий отключить нельзя.
Следующий шаг — создание пользователя, от имени которого мы позже будем обращаться к базе данных. Для этого перейдем на вкладку Пользователи и нажмем на кнопку Создать пользователя.
После этого появится приглашение ввести имя пользователя и пароль. После ввода этих данных нажимаем Сохранить.
Пользователь создан и отображается в списке пользователей.
Теперь создадим базу данных. Для этого перейдем на вкладку Базы данных и нажмем на кнопку Создать базу данных.
Заполняем необходимые поля и нажимаем кнопку Сохранить.
База данных создана и отображается в списке баз данных.
Теперь проверим возможность подключения. Для этого откроем консоль и вводим реквизиты:
# psql "host=192.168.0.3 \
port=6432 \
user=rosella \
dbname=zabbix \
sslmode=disable"В консоли должно появиться приглашение к вводу SQL-запроса или других управляющих команд.
Выполним резервное копирование при помощи команды pg_dump:
# pg_dump zabbix > /tmp/zabbix.dumpИ следом резервное восстановление в созданную управляемую базу данных:
# psql -h 192.168.0.3 -U rosella -d zabbix < /tmp/zabbix.dumpВ результате выполнения команды выше мы восстановили резервную копию в управляемую базу данных.
Чтобы воспользоваться восстановлением из резервной копии, которая автоматически создается на платформе Selectel, необходимо нажать на символ с тремя точками. В открывшемся меню нужно нажать на опцию Восстановить. После этого появится модальное окно, в котором можно выбрать резервную копию, а также дату и время, на которое нужно восстановить базу данных. Это так называемый Point-in-Time Recovery из WAL-файлов.
Услуга «Управляемые базы данных в облаке» позволяет перенести существующий кластер PostgreSQL на сервис управляемых баз данных бесшовно и без простоя, обратившись в техническую поддержку. Инженеры Selectel готовы помочь с переносом, а также проконсультировать по всем связанным с этим процессом вопросам.
Заключение
Мы рассмотрели возможности выполнения резервного копирования и показали отличия утилит pg_dump, pg_dumpall, pg_restore, pg_basebackup и wal-g. Вы увидели как можно создать управляемую базу данных, чтобы переложить часть административных задач на облачного провайдера.
Узнать подробнее об управляемых базах данных можно в документации Selectel.