0Pricing
SQL Academy · Урок

Логические и физические резервные копии

pg_dump и базовые резервные копии

«Логические и физические резервные копии» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое резервная копия базы данных

Резервная копия — это копия данных базы данных, которую можно использовать для восстановления системы после потери или повреждения данных либо катастрофы. Без надёжных резервных копий один аппаратный сбой или случайный DELETE может навсегда уничтожить данные, накопленные за месяцы или годы.

PostgreSQL предоставляет две основные категории стратегий резервного копирования: логические резервные копии и физические резервные копии. Каждая из них имеет свои особенности, сценарии использования и компромиссы, которые необходимо понимать каждому DBA.

Объяснение логических резервных копий

Логическая резервная копия экспортирует базу данных в виде понятных человеку операторов SQL — CREATE TABLE, INSERT, COPY и подобных команд. Наиболее распространённый инструмент для этого в PostgreSQL — pg_dump.

Поскольку результат представляет собой обычный SQL, логическая резервная копия переносима: Вы можете восстановить её в другой версии PostgreSQL, другой операционной системе или выборочно восстановить отдельные таблицы или схемы. Компромисс состоит в том, что создание и восстановление больших баз данных может занимать много времени.

Использование pg_dump для создания логической резервной копии

Утилита pg_dump запускается из командной строки, а не внутри SQL. Она подключается к работающему серверу PostgreSQL и экспортирует выбранную базу данных. Результат можно вывести в виде обычного SQL, пользовательского сжатого формата или формата каталога.

Приведённый ниже SQL имитирует то, что содержит логическая резервная копия, — структуру и данные таблицы в виде операторов, которые можно воспроизвести.

-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)

CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    customer  TEXT        NOT NULL,
    amount    NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (customer, amount, created_at) VALUES
    ('Alice',  149.99, '2024-01-15 09:30:00+00'),
    ('Bob',     89.50, '2024-01-16 14:00:00+00'),
    ('Carol',  210.00, '2024-01-17 11:15:00+00');

Форматы вывода pg_dump

pg_dump поддерживает четыре формата вывода, каждый из которых подходит для разных сценариев восстановления:

  • обычный — обычный SQL-скрипт, который можно прочитать в любом текстовом редакторе.
  • пользовательский — сжатый двоичный формат; наиболее гибкий вариант, поддерживающий параллельное восстановление.
  • каталог — отдельный файл для каждой таблицы, поддерживает параллельное создание и восстановление резервной копии.
  • tar — архив tar в формате каталога.

Пользовательский формат рекомендуется для больших баз данных, поскольку pg_restore может восстанавливать объекты параллельно, используя -j N рабочих процессов.

-- Checking which databases exist before choosing what to back up
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM   pg_database
WHERE  datname NOT IN ('template0', 'template1')
ORDER  BY pg_database_size(datname) DESC;

Восстановление логической резервной копии

Логическую резервную копию в формате обычного SQL восстанавливают с помощью psql. Для резервной копии в пользовательском формате требуется pg_restore. Оба инструмента воспроизводят операторы SQL, заново создавая таблицы, индексы, ограничения и данные.

Поскольку логические резервные копии содержат SQL, Вы можете отредактировать их перед восстановлением — например, чтобы восстановить только одну таблицу или изменить имя схемы. Такая гибкость — одно из главных преимуществ логического подхода.

-- After restoring a backup, verify row counts match expectations
SELECT
    schemaname,
    relname           AS table_name,
    n_live_tup        AS estimated_rows
FROM  pg_stat_user_tables
ORDER BY n_live_tup DESC;

Объяснение физических резервных копий

Физическая резервная копия (также называемая базовой резервной копией) копирует необработанные файлы данных, которые PostgreSQL использует на диске, — страницы, сегменты WAL (журнала опережающей записи) и конфигурационные файлы. Результатом является двоичный снимок всего кластера на определённый момент времени.

Физические резервные копии обычно позволяют гораздо быстрее восстановить большие базы данных, поскольку повторно выполнять SQL не нужно: PostgreSQL просто считывает файлы обратно в нужное место и воспроизводит WAL, чтобы достичь согласованного состояния.

pg_basebackup: создание физической резервной копии

pg_basebackup — стандартный инструмент PostgreSQL для создания физических резервных копий. Он передаёт каталог данных с работающего основного сервера через соединение репликации. Вам потребуется пользователь с правами репликации и значение wal_level, установленное в replica или более высокий уровень.

Внутри базы данных можно выполнить запрос к параметрам репликации, чтобы убедиться в правильной настройке сервера перед созданием базовой резервной копии.

-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM   pg_settings
WHERE  name IN (
    'wal_level',
    'max_wal_senders',
    'archive_mode',
    'archive_command'
)
ORDER  BY name;

Архивирование WAL и восстановление на момент времени

Базовая резервная копия фиксирует определённый момент времени. Чтобы восстановиться в любой произвольный момент после создания этой копии, PostgreSQL воспроизводит архивированные сегменты WAL — это называется восстановлением на момент времени (PITR).

Когда archive_mode = on и archive_command настроены, PostgreSQL копирует завершённые сегменты WAL в архивное хранилище. Во время восстановления restore_command извлекает эти сегменты обратно, чтобы сервер мог воспроизвести их до нужного момента.

-- Inspect current WAL position and archive status
SELECT
    pg_current_wal_lsn()                        AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn())        AS current_wal_file,
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time
FROM  pg_stat_archiver;

Сравнение логических и физических резервных копий

Выбор между логическими и физическими резервными копиями зависит от Ваших требований:

  • Логические (pg_dump): переносимы между версиями, поддерживают частичное восстановление и понятны человеку, но медленно создаются для больших баз данных и не обеспечивают детализации до уровня подтранзакций.
  • Физические (pg_basebackup + WAL): обеспечивают быстрое восстановление больших кластеров, поддерживают PITR, зависят от версии (восстанавливать нужно в той же основной версии) и восстанавливают весь кластер — отдельную таблицу восстановить нельзя.

В производственных средах обычно используют оба подхода: ночное создание физических базовых резервных копий с непрерывным архивированием WAL, а также периодическое создание логических резервных копий для переносимости и выборочного восстановления.

Проверка целостности резервной копии

Резервная копия, которую никогда не проверяли, — это не резервная копия, а лишь надежда. Всегда проверяйте резервные копии, восстанавливая их в тестовой среде и проверяя данные.

Для логических резервных копий быстрая проверка целостности заключается в подсчёте строк и сравнении контрольных сумм. Для физических резервных копий в PostgreSQL 14+ появилась утилита pg_verifybackup, которая проверяет файл манифеста, созданный с помощью pg_basebackup.

-- After a test restore, compare row counts across critical tables
SELECT
    relname                              AS table_name,
    n_live_tup                           AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM  pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC
LIMIT  20;

Мониторинг и планирование резервного копирования

Автоматизация и мониторинг резервного копирования не менее важны, чем само его выполнение. Отслеживайте, когда резервные копии создавались в последний раз, сколько времени это занимало и успешно ли завершалась операция. PostgreSQL предоставляет полезные метаданные для этой цели.

Для физических резервных копий pg_stat_archiver показывает сведения о последнем успешном архивировании и всех сбоях. Для логических резервных копий оберните pg_dump в сценарий, который записывает время начала, время окончания, размер файла и код завершения в таблицу мониторинга или систему оповещений.

-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
    id          SERIAL PRIMARY KEY,
    backup_type TEXT        NOT NULL CHECK (backup_type IN ('logical', 'physical')),
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at TIMESTAMPTZ,
    size_bytes  BIGINT,
    status      TEXT        NOT NULL DEFAULT 'running',
    notes       TEXT
);

-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;

Логические и физические резервные копии: быстрая проверка

Проверьте, насколько хорошо Вы понимаете стратегии логического и физического резервного копирования в PostgreSQL.

Итоги урока: логические и физические резервные копии

В этом уроке Вы изучили две фундаментальные стратегии резервного копирования PostgreSQL:

  • Логические резервные копии используют pg_dump для экспорта баз данных в виде операторов SQL. Они переносимы, понятны человеку и поддерживают частичное восстановление, но могут медленно создаваться для очень больших баз данных.
  • Физические резервные копии используют pg_basebackup для копирования необработанных файлов данных. В сочетании с архивированием WAL они обеспечивают быстрое восстановление и восстановление на момент времени, однако зависят от версии и всегда восстанавливают весь кластер.
  • В производственных системах обычно сочетают оба подхода: физические базовые резервные копии с архивированием WAL для быстрого и детального восстановления, а также периодические логические резервные копии для переносимости.
  • Всегда проверяйте восстановление резервных копий. Непроверенной резервной копии нельзя доверять в случае настоящей катастрофы.

Понимание этих двух подходов необходимо для разработки надёжного плана аварийного восстановления любого развертывания PostgreSQL.

Часто задаваемые вопросы

Урок «Логические и физические резервные копии» бесплатный?

Да — полный текст урока «Логические и физические резервные копии» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Логические и физические резервные копии»?

pg_dump и базовые резервные копии Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Логические и физические резервные копии»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Логические и физические резервные копии
  2. Восстановление на момент времени
  3. Проверка восстановления
  4. Планирование аварийного восстановления
← Назад к SQL Academy