EN Обрати сервер

Резервні копії та резервний майданчик

Резервні копії баз даних: PostgreSQL, MS SQL Server і бази BAS

Оновлено 10 хв читання Read in English

Зміст статті

Для адміністратора, який відповідає за сервер з обліковою системою, сайтом чи іншим сервісом із базою даних. У підсумку ви матимете щоденні копії баз PostgreSQL або MS SQL Server за розкладом, перевірені відновленням, і зрозумілий порядок для баз BAS.

Що знадобиться#

  • Права адміністратора на сервері та в СУБД: суперкористувач PostgreSQL або роль sysadmin у SQL Server.
  • Каталог для копій, бажано на окремому від бази диску, і місце поза сервером, куди копії підуть далі.

У прикладах база називається accounting, користувач — admin. Команди звірено з документацією PostgreSQL 18 і SQL Server 2025 — поточних стабільних версій на початок жовтня 2026 року; у попередніх підтримуваних версіях вони такі самі.

PostgreSQL: pg_dump за розкладом#

pg_dump вивантажує одну базу в узгодженому стані й не заважає користувачам працювати. Формат custom (-Fc) стиснений і дає змогу відновлювати базу в кілька потоків. Ролі й табличні простори в таке вивантаження не потрапляють — їх зберігає pg_dumpall --globals-only.

Скрипт#

Збережіть скрипт як /usr/local/sbin/pg-backup.sh:

#!/bin/bash
set -euo pipefail
umask 077

DIR=/var/backups/postgresql
KEEP_DAYS=14
STAMP=$(date +%Y%m%d-%H%M)

# ролі й табличні простори
pg_dumpall --globals-only -f "$DIR/globals-$STAMP.sql"

# кожна база — окремим файлом
DBS=$(psql -XAtc "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate")
for DB in $DBS; do
    pg_dump -Fc -f "$DIR/$DB-$STAMP.dump.part" "$DB"
    mv "$DIR/$DB-$STAMP.dump.part" "$DIR/$DB-$STAMP.dump"
done

# виконується, лише якщо всі вивантаження вдалися
find "$DIR" -maxdepth 1 -type f -mtime +"$KEEP_DAYS" -delete

Скрипт пише кожне вивантаження у файл .part і перейменовує його лише після успіху. Через set -e він зупиняється на першій помилці, тож файли, старші за 14 діб, видаляє тільки тоді, коли нові копії вже є. Зробіть скрипт виконуваним і створіть каталог, доступний лише користувачеві postgres (у файлі з ролями є хеші паролів):

sudo chmod 755 /usr/local/sbin/pg-backup.sh
sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

Розклад#

Потрібні два файли systemd: служба виконує скрипт від імені postgres, а таймер запускає її щоночі о 01:30 за часом сервера.

# /etc/systemd/system/pg-backup.service
[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/sbin/pg-backup.sh

# /etc/systemd/system/pg-backup.timer
[Timer]
OnCalendar=*-*-* 01:30:00
Persistent=true

[Install]
WantedBy=timers.target

Увімкніть таймер, один раз запустіть службу вручну і перегляньте журнал:

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
sudo systemctl start pg-backup.service
sudo journalctl -u pg-backup.service -n 20 --no-pager

Замість таймера підійде cron: додайте рядок 30 1 * * * postgres /usr/local/sbin/pg-backup.sh у файл /etc/cron.d/pg-backup.

Відновлення в окрему базу#

Розгорніть вивантаження в окрему базу поруч із робочою — це і перевірка копії, і репетиція відновлення.

Увага. Остання команда видаляє базу без підтвердження — перевірте її назву.

sudo -u postgres createdb -T template0 accounting_check
sudo -u postgres pg_restore --exit-on-error -j 4 -d accounting_check /var/backups/postgresql/accounting-20261002-0130.dump
sudo -u postgres psql -d accounting_check -c "ANALYZE" -c "SELECT count(*) FROM pg_stat_user_tables"
sudo -u postgres dropdb accounting_check

Кількість таблиць має бути така сама, як у робочій базі. На новому сервері від імені postgres спершу відновіть ролі (psql -f globals-20261002-0130.sql postgres), потім базу: pg_restore -C -d postgres accounting-20261002-0130.dump. Версія PostgreSQL там має бути та сама або новіша.

Архівування WAL і pg_basebackup#

Якщо копія лише нічна, збій о 17:00 забирає день роботи. Цю прогалину закриває безперервне архівування журналу випереджального запису (WAL): сервер складає в архів кожен заповнений сегмент журналу, а pg_basebackup робить базову копію каталогу даних. Разом вони дають змогу відновити весь кластер на будь-який момент часу. Це потрібно, коли втратити день роботи неприпустимо або коли pg_dump великої бази триває годинами. Налаштовувати це складніше, а відновити кластер можна лише на тій самій основній версії PostgreSQL, тому pg_dump залишайте як другий, незалежний спосіб. Докладніше — в документації PostgreSQL.

MS SQL Server: повна й різницева копії, копія журналу#

Модель відновлення#

Від моделі відновлення — SIMPLE, FULL або BULK_LOGGED, яка потрібна рідко, — залежить, які копії робити. Перевірте її запитом SELECT name, recovery_model_desc FROM sys.databases;.

МодельЖурнал транзакційЩо можна відновити
SIMPLE (типова в Express)Очищується автоматично, копії журналу неможливіСтан на момент останньої повної або різницевої копії
FULL (типова в Standard і Enterprise)Росте, доки не зроблено копію журналуБудь-який момент часу, якщо є всі копії журналу

Якщо досить повертатися до нічної копії, переведіть базу в SIMPLE: ALTER DATABASE [accounting] SET RECOVERY SIMPLE;. Якщо втрачати день роботи не можна, залишайте FULL і копіюйте журнал кожні 15–30 хвилин. Після повернення з SIMPLE до FULL одразу зробіть повну копію: без неї копії журналу неможливі.

Команди#

DECLARE @f nvarchar(260) = N'D:\Backup\SQL\accounting_full_'
    + FORMAT(SYSDATETIME(), 'yyyyMMdd_HHmm') + N'.bak';
BACKUP DATABASE [accounting] TO DISK = @f
    WITH COMPRESSION, CHECKSUM, INIT, STATS = 10;
RESTORE VERIFYONLY FROM DISK = @f WITH CHECKSUM;

CHECKSUM перевіряє контрольні суми сторінок і додає контрольну суму всієї копії, а RESTORE VERIFYONLY перевіряє, що файл повний і читається. Для різницевої копії (зміни після останньої повної) додайте до WITH слово DIFFERENTIAL. Для копії журналу замініть BACKUP DATABASE на BACKUP LOG, а розширення — на .trn. Відновлюють у такому порядку: повна копія, остання різницева, далі по черзі копії журналу, зроблені після неї; усі кроки, крім останнього, — з параметром NORECOVERY.

Файл створює служба SQL Server, тому каталог має існувати, а обліковий запис служби — мати право запису в нього. Типово це NT Service\MSSQLSERVER, для іменованого екземпляра SQLEXPRESS — NT Service\MSSQL$SQLEXPRESS.

Розклад: агент SQL Server або Планувальник завдань#

У редакціях Standard і Enterprise розклад веде агент SQL Server. У SQL Server Management Studio: SQL Server Agent → Jobs → New Job, на сторінці Steps — крок типу Transact-SQL script (T-SQL) з командами вище, на сторінці Schedules — час. Типова схема — три завдання: повна копія щотижня або щоночі, різницева щодня, копії журналу протягом дня. Старі файли прибирає Maintenance Cleanup Task у планах обслуговування.

В Express немає ні агента, ні стиснення копій, тому розклад веде Планувальник завдань Windows, а команди виконує sqlcmd. Збережіть наведені вище команди без слова COMPRESSION і коми після нього у файл C:\Scripts\backup-full.sql, а поруч створіть sqlbackup.cmd:

@echo off
set BKP=D:\Backup\SQL
sqlcmd -S .\SQLEXPRESS -E -C -b -i C:\Scripts\backup-full.sql -o %BKP%\last-run.log
if errorlevel 1 exit /b 1
forfiles /P %BKP% /M *.bak /D -14 /C "cmd /c del @path" >nul 2>&1
exit /b 0

З ключем -b sqlcmd повертає код помилки, якщо копіювання не вдалося: без нього Планувальник покаже успіх за будь-якого результату. Ключ -C потрібен для локального з’єднання із сервером, що має самопідписаний сертифікат: sqlcmd зі складу SQL Server 2025 типово шифрує з’єднання. Додайте завдання в командному рядку, відкритому від імені адміністратора, і запустіть його вручну:

schtasks /Create /TN "SQL backup" /TR C:\Scripts\sqlbackup.cmd /SC DAILY /ST 23:30 /RU admin /RL HIGHEST
schtasks /Run /TN "SQL backup"
schtasks /Query /TN "SQL backup" /V /FO LIST

Перша команда запитає пароль користувача admin; йому потрібна роль sysadmin у SQL Server. Остання показує результат запуску — після завершення завдання він має бути нульовим.

Відновлення в окрему базу#

Увага. Для перевірки відновлюйте копію тільки під іншою назвою бази і в інші файли: RESTORE з назвою робочої бази і параметром REPLACE перезапише її.

RESTORE FILELISTONLY FROM DISK = N'D:\Backup\SQL\accounting_full_20261002_2330.bak';

RESTORE DATABASE [accounting_check]
    FROM DISK = N'D:\Backup\SQL\accounting_full_20261002_2330.bak'
    WITH MOVE N'accounting' TO N'D:\SQLData\accounting_check.mdf',
         MOVE N'accounting_log' TO N'D:\SQLData\accounting_check_log.ldf';
DBCC CHECKDB ([accounting_check]) WITH NO_INFOMSGS;
DROP DATABASE [accounting_check];

Перша команда показує логічні імена файлів (стовпець LogicalName) — підставте їх у MOVE. Якщо DBCC CHECKDB не вивела жодного повідомлення, структура даних ціла. Версія SQL Server має бути та сама або новіша.

Облікові бази BAS#

Посібник адміністратора платформи описує резервне копіювання окремо для файлового і клієнт-серверного варіантів бази.

  • Файлова база. Копіюйте весь каталог бази, коли в ній ніхто не працює: закрито конфігуратор і всі сеанси користувачів, зокрема підключення через веб-сервер. Копія, знята з відкритої бази, може не відкритися.
  • Клієнт-серверна база. Дані лежать у PostgreSQL або MS SQL Server, тому копіюйте їх засобами СУБД, як описано вище. Зупиняти роботу користувачів не потрібно.
  • Вивантаження у файл .dt (у конфігураторі: Адміністрування → Вивантажити інформаційну базу) радимо залишити додатковим способом, наприклад перед оновленням конфігурації. Вивантажуйте, коли в базі ніхто не працює. Для великої бази це триває довго, а перевірити файл можна лише завантаженням в окрему порожню базу.

Винесіть копії за межі сервера#

Файли на тому самому сервері рятують від помилки користувача, але не від втрати сервера. За правилом 3-2-1 копія має потрапити на окреме сховище і ще в одне, третє місце.

  • Передавайте вивантаження і файли .bak, а не каталог даних запущеної СУБД: з файлів, скопійованих як звичайні під час роботи бази, вона може не відновитися.
  • У Linux каталог із копіями зручно копіювати на сховище за допомогою restic: він шифрує дані до надсилання. Для Windows є вбудовані засоби Windows Server.
  • Шифруйте все, що виходить за межі сервера, а копію пароля сховища тримайте окремо від сервера: без пароля копію не відкрити.

На основних лінійках United Cloud у сервер входить місце для резервних копій на окремому сховищі (обсяг — у картці сервера). Копіювання налаштовуєте ви самі; параметри підключення надішле підтримка.

Як перевірити результат#

  1. Уранці в каталозі є файли з нічною датою і правдоподібним розміром.
  2. Запуск завершився без помилок: journalctl -u pg-backup.service, історія завдання агента або результат у Планувальнику завдань.
  3. Раз на місяць відновлюйте свіжу копію в окрему базу і записуйте, скільки часу це тривало. Копію бази BAS підключіть як окрему інформаційну базу і відкрийте звіт за останній робочий день.

Типові помилки#

  • Копії лежать поруч із базою. Відмова диска, шифрувальник чи помилкова команда знищують і базу, і копії.
  • Журнал транзакцій росте без копій журналу. База в моделі FULL, а копіюють тільки саму базу: файл .ldf росте, доки не заповнить диск, і база перестає приймати зміни. Налаштуйте копії журналу або свідомо перейдіть на SIMPLE; видаляти файл журналу не можна.
  • Відновлення не перевіряли. RESTORE VERIFYONLY підтверджує лише, що файл читається. Чи копія робоча, показує тільки відновлення в окрему базу.

Що далі#