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.
Автор статьи: технический руководитель проектов внедрения 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, в частности при ее применении совместно с 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
Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.
Database · November 19, 2024
PostgreSQL is a powerful, open-source relational database management system that is widely used for its robustness and flexibility. One of the critical aspects of managing a PostgreSQL database is ensuring that data is regularly backed up to prevent loss in case of hardware failure, data corruption, or other unforeseen issues. This article will guide you through the process of implementing automatic backups for PostgreSQL on a Windows system.
Understanding PostgreSQL Backup Options
PostgreSQL offers several methods for backing up data, including:
- SQL Dump: This method creates a text file with SQL commands that can recreate the database. It is useful for smaller databases and for migrating data between different PostgreSQL installations.
- File System Level Backup: This involves copying the database files directly from the file system. It is suitable for larger databases but requires the database to be in a consistent state.
- Continuous Archiving and Point-in-Time Recovery (PITR): This advanced method allows for continuous backups and the ability to restore the database to any point in time.
For automatic backups, the SQL Dump method is commonly used due to its simplicity and ease of use.
Setting Up Automatic Backups Using pg_dump
The pg_dump utility is a command-line tool that comes with PostgreSQL and is used to create backups. To automate the backup process, you can create a batch file and schedule it using Windows Task Scheduler.
Step 1: Create a Backup Script
First, you need to create a batch file that will execute the pg_dump command. Open a text editor and create a new file named backup_postgresql.bat. Add the following lines to the file:
@echo off
set PGPASSWORD=your_password
set BACKUP_DIR=C:pathtobackupdirectory
set TIMESTAMP=%DATE:~-4,4%-%DATE:~-10,2%-%DATE:~-7,2%_%TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
pg_dump -U your_username -h localhost -F c -b -v -f "%BACKUP_DIR%your_database_%TIMESTAMP%.backup" your_database
Replace your_password, your_username, C:pathtobackupdirectory, and your_database with your actual PostgreSQL password, username, desired backup directory, and database name, respectively. The pg_dump command options used here are:
-U:Specifies the username.-h:Specifies the host (localhost in this case).-F c:Specifies the format of the backup (custom format).-b:Includes large objects in the backup.-v:Enables verbose mode.-f:Specifies the output file name.
Step 2: Schedule the Backup Task
Once you have created the batch file, the next step is to schedule it to run automatically using Windows Task Scheduler.
- Open Task Scheduler by searching for it in the Start menu.
- Click on “Create Basic Task” in the right-hand panel.
- Follow the wizard to name your task and provide a description.
- Select the frequency of the task (daily, weekly, etc.).
- Set the start time and configure the recurrence settings as needed.
- Choose “Start a Program” as the action.
- Browse to select your
backup_postgresql.batfile. - Finish the wizard and ensure the task is enabled.
Monitoring and Managing Backups
After setting up automatic backups, it is essential to monitor the backup process. You can check the backup directory for new files and verify their integrity by restoring them to a test database. Regularly review your backup strategy to ensure it meets your data protection needs.
Conclusion
Implementing automatic backups for PostgreSQL on a Windows system is a straightforward process that can significantly enhance your data security. By using the pg_dump utility and Windows Task Scheduler, you can ensure that your database is regularly backed up without manual intervention. This proactive approach to data management can save you from potential data loss and provide peace of mind.
For those looking for reliable solutions for their database hosting needs, consider exploring USA VPS Hosting options that can support your PostgreSQL databases effectively.