Перейти к основному содержимому

Уровень 2.0

Предусловие: изучена глава «Авторизация и аутентификация».

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

Резервное копирование​

Резервное копирование и восстановление данных — это ключевые аспекты управления базами данных, которые обеспечивают защиту информации.

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

Логическое резервное копирование – это копия данных, которая создана на уровне SQL-запросов. Оно позволяет гибко восстанавливать данные, отдельные таблицы или схемы и поддерживает переносимость между различными версиями PostgreSQL. Физическое резервное копирование сохраняет файлы данных непосредственно из файловой системы. Это метод более низкого уровня, который предполагает создание точной копии всех файлов базы данных, включая таблицы, индексы и другие объекты. Он позволяет быстро восстанавливать большие базы данных и полезен для создания резервных копий всего кластера баз данных.

Логическое резервное копирование​

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

Преимущества такого копирования в том, что скрипт может быть воспроизведен на другой мажорной версии PostgreSQL. Например, резервную копию, сделанную в 14-й версии, можно восстановить в 15-й. Более того, можно выполнить восстановление в другой ОС или даже на другой аппаратной платформе. Главные недостатки в медлительности копирования и восстановления, а также больших размерах получающихся копий и невозможности выполнить восстановление к моменту времени в прошлом.

Большие размеры копий получаются в простейшем случае логического копирования — «плоском формате» (plain format). В результате его выполнения появляется обычный текстовый файл со скриптом. Если этот скрипт выполнить в клиенте psql, можно воспроизвести те объекты, которые подверглись резервному копированию.

Средства логического резервного копирования​

В PostgreSQL применяется набор специализированных утилит и команд для выполнения логического резервного копирования. Основным инструментом является pg_dump, который позволяет создавать копии отдельных баз данных или объектов в различных форматах (текстовый SQL, пользовательский бинарный, каталог или tar), обеспечивая гибкость при восстановлении. Для восстановления из бинарных форматов используется утилита pg_restore, поддерживающая параллельную работу, тогда как текстовые скрипты исполняются через клиент psql.

Для резервного копирования всего кластера, включая глобальные объекты (например, роли), предназначена утилита pg_dumpall, работающая исключительно в «плоском» SQL-формате. На уровне данных активно применяются команда COPY (выполняется на стороне сервера) и метакоманда \copy (на стороне клиента), которые позволяют экспортировать и импортировать содержимое таблиц в текстовом виде и часто используются внутри скриптов дампов для переноса данных.

Копирование данных​

Команда SQL COPY работает на стороне сервера и предназначена для эффективного перемещения данных между таблицами и файлами или потоками ввода-вывода. Она поддерживает два основных направления операции:

  • COPY <таблица> TO — копирование данных из таблицы или результата запроса в стандартный поток вывода stdout или файл на сервере;
  • COPY <таблица> FROM — добавление данных в существующую таблицу из стандартного потока ввода stdin или файла на сервере.

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

Важной особенностью SQL-команды COPY является то, что она выполняется силами серверного процесса. Это означает, что она работает от имени пользователя операционной системы, который инициализировал кластер (обычно postgres). Следовательно, для успешной работы с файлами при копировании на стороне сервера у этого пользователя должны быть соответствующие права на чтение и запись в файловой системе сервера.

Если необходимо, чтобы копирование происходило не силами сервера, а с помощью клиента psql, предусмотрена метакоманда \copy. Ее возможности практически идентичны команде COPY, однако она работает на стороне клиента и использует права пользователя операционной системы, запустившего psql. Это позволяет избежать требований к правам доступа пользователя postgres на сервере при работе с локальными файлами клиента.

Утилита pg_dump​

Стандартная утилита PostgreSQL для логического резервного копирования — pg_dump. Утилита подключается к базе данных от имени указанной роли и позволяет с помощью опций командной строки выбирать объекты для копирования: базы данных, схемы, таблицы и так далее. Результатом выполнения является SQL-скрипт или архив в одном из поддерживаемых форматов, пригодный для воссоздания структур и данных. Для восстановления применяются psql (для SQL-скриптов) или pg_restore (для архивных форматов).

Выбор формата вывода осуществляется опцией -F. От формата зависят инструмент восстановления, наличие сжатия и возможность параллельной работы. Допустимые форматы:

  • Plain (-Fp) — SQL-скрипт в текстовом виде (восстановление через psql). Не поддерживает сжатие и параллельную обработку, но удобен для ручного просмотра и редактирования;
  • Custom (-Fc) — сжатый бинарный архив собственного формата (восстановление через pg_restore). Поддерживает параллельное восстановление (опция -j);
  • Directory (-Fd) — каталог, содержащий сжатые файлы для каждого объекта (восстановление через pg_restore). Позволяет распараллелить как создание копии, так и восстановление (опция -j);
  • Tar (-Ft) — архив в формате tar (восстановление через pg_restore). Не поддерживает сжатие и параллельную работу.
подсказка

Для производственных сред рекомендуется использовать форматы -Fc или -Fd, обеспечивающие сжатие и возможность распараллеливания процессов.

Копирование базы данных​

Выполнение копирования допускается как от имени стандартного пользователя ОС (в том числе с правами root), так и от имени пользователя postgres. Выбор учетной записи влияет исключительно на доступные пути для сохранения SQL-скрипта в силу различий в правах доступа к файловой системе.

При использовании команды pg_dump необходимо указать имя базы данных как для копирования всей базы данных, так и для отдельных объектов в ней. Готовый SQL-скрипт по умолчанию выводится в стандартный поток, но его можно сохранить в файл с помощью перенаправления вывода (>) или опции -f.

В нашем случае копирование будет выполняться от имени пользователя ОС student для всей базы данных my_db. Результат будет записан в файл my_db.dump.

[student@ServerName~]$ pg_dump -d my_db -c -C > my_db.dump
Разбор команды

  • опция -d позволяет указать целевую базу данных;
  • опция -c добавляет в скрипт SQL-команду для удаления целевой базы данных (my_db);
  • опция -C добавляет в скрипт SQL-команду для создания целевой базы данных (my_db);
  • символ > указывает, куда будет перенаправлен результат копирования из стандартного потока вывода (аналог опции -f).

Теперь восстановим базу данных из файла my_db.dump от имени роли postgres.

Восстановление базы данных my_db происходит путем последовательного выполнения SQL-команд из файла my_db.dump через утилиту psql. Для этого необходимо подключиться к базе данных и передать в поток ввода сам файл. При использовании опций -C и -c при копировании, как в нашем случае, подключение должно осуществляться к базе данных, отличной от my_db, от имени роли с правом CREATEDB (обычно выбираются база данных postgres и роль postgres).

[student@ServerName~]$ psql -U postgres -d postgres < my_db.dump

Роль для подключения указывается после опции -U, а база данных — -d. Передача файла в поток ввода идет за счет символа < (аналог опция -f).

Выполнение файла my_db.dump сопровождается выводом результатов SQL-команд.

Результаты выполнения SQL-команд

SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "my_db" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 0
COPY 0
COPY 0
COPY 12
COPY 0
GRANT
GRANT
GRANT
GRANT
GRANT
ALTER DEFAULT PRIVILEGES

В результатах выполнения SQL-команд есть вывод DROP DATABASE, соответствующий удалению базы данных (опция -c), и CREATE DATABASE — создание базы данных (опции -C).

примечание

В качестве шаблона при создании базы данных при восстановлении всегда выступает база данных template0. В отличие от template1 шаблон template0 не может быть изменен какой-либо ролью в СУБД, что гарантирует чистоту новой базы данных.

Убедиться в использовании шаблона template0 можно, найдя в файле my_db.dump SQL-команду с ключевыми словами create database.

[student@ServerName~]$ grep -i 'create database' my_db.dump
CREATE DATABASE my_db WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF8';
Какие команды выполняются перед удалением и созданием базы данных?

До выполнения каких-либо действий с базой данных необходимо привести окружение к состоянию, каким оно было на момент копирования базы данных.

Посмотреть на параметры, которые устанавливаются в первую очередь, можно с помощью команды sed и опции -n 'START,ENDp' вывести строки из заданного диапазона.

[student@ServerName~]$ sed -n '10,20p' my_db.dump
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

Попробуем подключиться к базе данных my_db и посмотреть список отношений.

[student@ServerName~]$ psql -d my_db
student@my_db=> \d
             List of relations
 Schema |   Name    |  Type   |    Owner
--------+-----------+---------+--------------
 public | def_privs |  table  | student
 public | ptab      |  table  | student
 public | studentab |  table  | grp_students
 public | suptab    |  table  | postgres
(4 rows)

Подключение к базе данных my_db прошло успешно, все объекты, права и привилегии на них были восстановлены.

Копирование таблицы​

Как было сказано ранее, команда pg_dump позволяет выполнять резервное копирование не только всей базы данных, но и ее отдельных объектов.

Продемонстрируем резервное копирование отдельного объекта — содержание таблицы studentab. Однако в данный момент таблица пуста, поэтому для наглядности копирования выполним вставку данных.

Вставка данных из bash оболочки с помощью утилиты psql требует указания роли для подключения, имя базы данных с таблицей studentab и сам запрос с данными. Для удобства вставки применим функцию generate_series.

[student@ServerName ~]$ psql -U postgres -d my_db -c "INSERT INTO studentab SELECT g.i, g.i::text FROM generate_series(0,5) AS g(i);"
INSERT 0 6

При копировании только данных таблицы результатом работы pg_dump будет набор команд COPY. Поскольку таблица studentab небольшая, SQL-команды можно вывести в стандартный поток вывода и отфильтровать с помощью команды sed.

[student@ServerName ~]$ pg_dump -d my_db -t studentab -a | sed -n '/^COPY/,$p'
Разбор команды

  • опция -d позволяет указать базу данных, в которой находится нужный объект;
  • опция -t позволяет указать целевую таблицу;
  • опция -a указывает на копирование только данных таблицы;
  • символ | указывает на передачу вывода команды pg_dump команде sed;
  • выражение -n '/^COPY/,$p' выводит часть текста, начинающегося с COPY и до конца файла.
COPY public.studentab (id, str) FROM stdin;
0   0
1   1
2   2
3   3
4   4
5   5
0   0
1   1
2   2
3   3
4   4
5   5
\.

После фильтрации осталась одна команда COPY и строчки с данными из таблицы studentab. Обратите внимание, что в команде копирование данных идет из стандартного потока ввода stdin, поэтому сразу после перечислены строки данных таблицы, которые сервер последовательно читает до символа \., обозначающего конец данных.

Проверим, что эти данные действительно принадлежат таблице studentab.

[student@ServerName ~]$ psql -U postgres -d my_db -c "SELECT * FROM studentab;"
 id | str
----+-----
  0 | 0
  1 | 1
  2 | 2
  3 | 3
  4 | 4
  5 | 5
(6 rows)

Данные из команды COPY совпадают с данными, полученными из таблицы studentab с помощью команды SELECT.

Утилита pg_dumpall​

Утилита pg_dumpall предназначена для логического резервного копирования всего кластера PostgreSQL. В отличие от pg_dump, которая работает с одной базой данных, pg_dumpall способна сохранять глобальные объекты, такие как роли и права доступа, которые являются общими для всех баз данных. Результатом работы является единый SQL-скрипт в текстовом формате (plain), поэтому возможности сжатия и параллельной обработки недоступны. Восстановление данных выполняется посредством клиента psql. Утилита поддерживает опцию -g, позволяющую создать копию только глобальных объектов без данных баз данных, что часто используется в комбинации с индивидуальным дампом каждой базы через pg_dump.

Общий алгоритм резервного копирования кластера с помощью pg_dumpall выглядит так:

  1. Создается копия глобальных объектов — pg_dumpall -g;
  2. В цикле для каждой базы данных кластера применяется pg_dump -j.. -Fd.

Физическое резервное копирование​

Физическое резервное копирование является основным способом защиты от сбоев и представляет собой создание копий файлов каталога данных PostgreSQL на уровне файловой системы. Этот метод сохраняет бинарное представление всех объектов кластера, включая таблицы, индексы и служебную информацию, что отличает его от логического копирования на уровне SQL-запросов.

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

  • Холодное копирование выполняется после остановки сервера: при корректном завершении работы достаточно скопировать каталог данных, тогда как после аварийного останова необходимо также сохранить журналы предзаписи WAL;
  • Горячее копирование позволяет создавать резервные копии работающего сервера без прерывания обслуживания клиентов, но требует использования специальных инструментов и сохранения WAL-сегментов за время операции.

Основными преимуществами метода являются высокая скорость копирования и восстановления, возможность восстановления данных на конкретный момент времени (Point-In-Time Recovery) и надежность защиты от сбоев. К недостаткам относится невозможность выборочного восстановления отдельных объектов или баз данных (копируется весь кластер целиком), а также требование бинарной совместимости среды. Это означает, что восстановление невозможно выполнить на другой мажорной версии PostgreSQL, иной операционной системе или аппаратной платформе.

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

Инструменты физического резервного копирования​

Для реализации физического резервного копирования используется ряд инструментов, различающихся по назначению и условиям применения. Основным штатным средством для горячего копирования работающего кластера является утилита pg_basebackup, которая создает полную копию файлов данных. Для обеспечения возможности восстановления на момент времени применяется утилита pg_receivewal, осуществляющая потоковое архивирование WAL-сегментов. При остановленном сервере допустимо использование стандартных утилит операционной системы (cp, tar, dd) для прямого копирования каталога данных. Дополнительно может применяться специализированная утилита pg_probackup, поставляемая в пакете компонента Pangolin Backup Tools и предлагающая расширенные возможности управления резервными копиями.

Инструмент pg_basebackup​

Утилита pg_basebackup — штатное средство PostgreSQL для физического резервного копирования работающего сервера без прерывания обслуживания клиентов. Подключение выполняется по протоколу репликации, результатом является точная копия каталога данных.

Процесс копирования включает следующие этапы:

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

Ключевые особенности:

  • Автономная копия — содержит все необходимое для запуска нового экземпляра сервера;
  • Полнота данных — копируется весь кластер целиком, выборочное резервирование отдельных баз данных невозможно;
  • WAL-сегменты — по умолчанию в копию включаются все сегменты журнала предзаписи, необходимые для согласованности данных на момент завершения копирования.

Выбор формата вывода осуществляется опцией -F, целевой каталог задается опцией -D:

  • Plain (-Fp, формат по умолчанию) — копия создается в виде директории с файлами кластера;
  • Tar (-Ft) — копия создается в виде tar-архива.

Для успешной работы pg_basebackup необходимо выполнение ряда требований: параметр wal_level должен иметь значение не ниже replica, а в файле pg_hba.conf должны быть настроены права доступа для подключения по протоколу репликации. Полученная копия может быть использована для развертывания нового экземпляра сервера, например, на другом порту.

Создание автономной копии​

Выполним физическое резервное копирование сервера при помощи pg_basebackup.

Перед непосредственным копированием необходимо подготовить каталог, куда будут помещены копии всех файлов работающего сервера.

[postgres@ServerName ~]$ mkdir Bkp

Теперь выполним копирование.

[postgres@ServerName ~]$ pg_basebackup -D Bkp/ -T /pgdata/06/newdisk=/home/postgres/newdisk
Разбор команды

  • опция -D позволяет указать целевой каталог;
  • опция -T перемещает указанный каталог табличного пространства в новое место.
Важно

Обратите внимание, что при создании автономной копии путь табличного пространства newtabspace (использовалось в в разделе Табличные пространства) был изменен с /pgdata/06/newdisk на /home/postgres/newdisk. Это связано с особенностями работы утилиты pg_basebackup, которая стремится воспроизвести точную физическую структуру кластера, включая абсолютные пути, указанные при создании табличных пространств, внутри целевой директории резервной копии. Возникает конфликт — путь /pgdata/06/newdisk уже существует как реальный каталог на файловой системе.

Для решения этой проблемы используется опция -T <старый_путь>=<новый_путь>, позволяющая переназначить расположение табличного пространства в процессе копирования. В результате данные помещаются непосредственно внутрь директории резервной копии, что делает ее самодостаточной и переносимой, устраняя требование к идентичной структуре файловой системы при восстановлении.

Проверим, что копирование прошло успешно.

[postgres@ServerName ~]$ ls -F Bkp/
backup_label          pg_integrity/          pg_replslot/         PG_VERSION
backup_manifest       pg_logical/            pg_serial/           pg_wal/
base/                 pg_multixact/          pg_snapshots/        pg_xact/
global/               pg_notify/             pg_stat/             postgresql.auto.conf
pg_commit_ts/         pg_perf_insights/      pg_stat_tmp/         postgresql.conf
pg_dynshmem/          pg_pp_cache/           pg_subtrans/         PRODUCT_VERSION
pg_hba.conf           pg_prep_stats/         pg_tblspc/           tracing/
pg_ident.conf         pg_quota.conf          pg_twophase/

Целевой каталог содержит полную копию данных работающего кластера. Эти данные можно использовать для запуска нового экземпляра сервера. Чтобы обеспечить одновременную работу обоих экземпляров, достаточно изменить порт прослушивания в конфигурации новой копии.

В ходе физического копирования утилита pg_basebackup сохранила в каталог pg_wal все сегменты WAL, сгенерированные сервером за время выполнения операции. Проверим их.

[postgres@ServerName ~]$ ls -l Bkp/pg_wal/
total 16388
-rw------- 1 postgres postgres 16777216 окт 28 09:56 000000010000000000000008
drwx------ 2 postgres postgres     4096 окт 28 09:56 archive_status
Восстановление из копии​

Теперь запустим второй сервер, выполнив восстановление из сделанной ранее резервной копии.

Вы запускаете новый экземпляр сервера именно из этого каталога. Файлы данных базы должны быть доступны только пользователю ОС postgres, который будет владеть процессом сервера. Если оставить доступ для всех, любой пользователь сможет прочитать ваши данные напрямую через файловую систему.

Перед запуском сервера нужно изменить права на каталог Bkp/. Файлы этого каталога должны быть доступны только пользователю ОС postgres, который будет владеть процессом сервера. Если оставить доступ для всех, любой пользователь сможет прочитать данные напрямую через файловую систему.

[postgres@ServerName ~]$ chmod -R go= Bkp/
Разбор команды

  • chmod — данная команда позволяет поменять права на указанный объект;
  • опция -R указывает на рекурсивное изменение правил: для целевого каталога и всех его подкаталогов и файлов;
  • часть go= показывает, что никто из пользователей ОС кроме postgres (go сокращение от group, others, то есть все пользователи в той же группе и все остальные на сервере) не имеет прав (отсутствие символов после знака = означает нулевые права) на целевой каталог.

Для обеспечения одновременной работы двух экземпляров необходимо поменять прослушиваемый порт, поскольку порт по умолчанию (5432) занимает уже запущенный сервер. В нашем случае выберем порт 5433.

[postgres@ServerName ~]$ sed -i.bak 's/^port =.*$/port = 5433/' Bkp/postgresql.conf
Разбор команды

  • sed — команда потокового редактора текста;
  • опция -i.bak сохраняет историю изменений в копию исходного файла с расширением .bak;
  • регулярное выражение s/^port =.*$/port = 5433/ выделяет в исходном файле часть строки, начинающуюся на port = и заканчивающуюся точкой, а затем заменяет ее на port = 5433.

Подготовка завершена, можно запускать новый экземпляр из резервной копии. Каталог для восстановления указывается с помощью опции -D, а новый файл для системных сообщений — -l.

[postgres@ServerName ~]$ pg_ctl start -D ~/Bkp/ -l ~/PG_new.log
waiting for server to start..... done
server started

При запуске происходит восстановление по журналу предзаписи (WAL). Посмотреть это можно в системных сообщениях в файле PG_new.log.

[postgres@srv-67-29 ~]$ sed -n '/was interrupted/,$p' PG_new.log | head -8
Разбор команды

  • опция -n говорит команде sed ожидать завершения чтения файла, а не выводить все строки сразу;
  • регулярное выражение /idle terminator started/,$p ищет в файле PG_new.log сообщения, начиная с idle terminator started и до конца файла;
  • команда head -8 обрезает стандартный поток вывода (результат работы sed), оставляя только 7 строк.
2024-10-28 10:19:30.323 MSK [48089] LOG: idle terminator started
2024-10-28 10:19:38.988 MSK [48088] LOG: redo starts at 0/8000028
2024-10-28 10:19:39.023 MSK [48088] LOG: consistent recovery state reached at 0/8000130
2024-10-28 10:19:39.023 MSK [48088] LOG: redo done at 0/8000130 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.03 s
2024-10-28 10:19:39.401 MSK [48086] LOG: checkpoint starting: end-of-recovery immediate wait
2024-10-28 10:19:39.549 MSK [48086] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.031 s, sync=0.011 s, total=0.184 s; sync files=2, longest=0.006 s, average=0.006 s; distance=16384 kB, estimate=16384 kB
2024-10-28 10:19:39.569 MSK [48084] LOG: database system is ready to accept connections

Процесс восстановления завершается сообщением о готовности базы данных к подключениям; сервер успешно запущен. Для проверки его статуса командой pg_ctl нужно указать каталог со всеми данными. В нашем случае это каталог Bkp/, из которого восстановлен экземпляр.

[postgres@ServerName ~]$ pg_ctl status -D ~/Bkp/
pg_ctl: server is running (PID: 48084)
/usr/pangolin-{pangolin_version}/bin/postgres "-D" "/home/postgres/Bkp"

В статусе описаны текущее состояние сервера (server is running), идентификатор процесса (PID: 48084) и команда, выполненная при старте.

Попробуем выполнить запрос на новом сервере. Поскольку запрос будет обработан сервером, прослушивающим порт по умолчанию, нужно явно указать порт 5433 с помощью опции -p.

[postgres@ServerName ~]$ psql -p 5433 -c 'SELECT product_version()'
product_version
---------------------------
Platform V Pangolin {pangolin_version}
(1 row)

Функция product_version() вернула ожидаемый результат, сервер работает.

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

[postgres@srv-67-29 ~]$ psql -p 5433 -l
                                                 List of databases
   Name     |     Owner    |  Encoding |   Collate  |    Ctype   | ICU Locale |  Locale Provider |   Access priv
------------+--------------+-----------+------------+------------+------------+------------------+-------------
 my_db      | grp_students | UTF8      | en_US.UTF8 | en_US.UTF8 |            | libc             |
 postgres   | postgres     | UTF8      | en_US.UTF8 | en_US.UTF8 |            | libc             |
 template0  | postgres     | UTF8      | en_US.UTF8 | en_US.UTF8 |            | libc             | =c/postgres  +
            |              |           |            |            |            |                  | postgres=CTc/p
 template1  | postgres     | UTF8      | en_US.UTF8 | en_US.UTF8 |            | libc             | =c/postgres  +
            |              |           |            |            |            |                  | postgres=CTc/p
(4 rows)

Архив сегментов WAL​

Архивирование сегментов журнала предзаписи (WAL) позволяет расширить возможности восстановления, обеспечивая поддержку восстановления на произвольный момент времени (Point-In-Time Recovery — PITR). Регулярное сохранение заполненных сегментов WAL в надежное хранилище позволяет восстановить состояние базы данных не только на момент создания последней физической копии, но и на любую точку между резервными копиями.

Существует два основных способа организации архива:

  • средствами сервера (файловый архив). Настраивается через параметры archive_mode = on и archive_command. Сервер самостоятельно копирует заполненные сегменты в указанный каталог после переключения на новый сегмент. Недостатком метода является возможная задержка: сегмент попадет в архив только после завершения заполнения и переключения;
  • средствами клиента (потоковый архив). Используется штатная утилита pg_receivewal. Она подключается по протоколу репликации и принимает сегменты WAL в реальном времени по мере их записи, что исключает задержки, характерные для файлового архива.
Сохранение сегментов WAL​

В штатном режиме сервер PostgreSQL периодически удаляет старые сегменты журнала предзаписи (WAL), которые больше не требуются для восстановления после сбоя. Это необходимо для предотвращения бесконечного роста дискового пространства. Однако такая очистка создает риски при выполнении длительных операций, таких как резервное копирование через pg_basebackup. Если сервер удалит сегменты WAL, созданные в начале процесса копирования, до того как утилита успеет их считать, резервная копия станет несогласованной и непригодной для использования.

Для решения этой проблемы применяются слоты репликации. Слот резервирует сегменты WAL на стороне сервера, запрещая их удаление до тех пор, пока клиент не подтвердит их получение. Это гарантирует, что все необходимые изменения будут включены в копию. Утилита pg_basebackup поддерживает работу со слотами: можно использовать существующий слот (опция -S) или создать временный автоматически (опция -C, включена по умолчанию). Также слот может быть создан вручную через функцию pg_create_physical_replication_slot().

Восстановление при наличии архива​

Наличие архива WAL позволяет создавать компактные базовые копии, исключая сегменты журнала из результата резервного копирования. Для этого утилита pg_basebackup запускается с опцией -Xn, что снижает объем копируемых данных и экономит дисковое пространство.

Процесс восстановления из физической копии с использованием архива WAL включает следующие шаги:

  • настройка restore_command — параметр указывает серверу команду операционной системы для извлечения WAL-сегментов из архива (работает в обратном направлении по сравнению с archive_command);
  • запуск восстановления — в каталоге данных создается пустой сигнальный файл recovery.signal, после чего сервер запускается стандартным образом и автоматически переходит в режим восстановления;
  • ограничение точки восстановления — по умолчанию сервер восстанавливает все доступные WAL-сегменты до конца архива, однако целевую точку можно задать параметрами recovery_target*, например, указав конкретное время через recovery_target_time.

Посмотреть установленные recovery_target* параметры можно с помощью метакоманды \dconfig в оболочке psql.

postgres@postgres=# \dconfig recovery_target*
  List of configuration parameters
         Parameter        | Value
--------------------------+----------
recovery_target           |
recovery_target_action    | pause
recovery_target_inclusive | on
recovery_target_lsn       |
recovery_target_name      |
recovery_target_time      |
recovery_target_timeline  | latest
recovery_target_xid       |
(8 rows)

Итоги​

  • pg_dump выполняет логическое копирование отдельной базы данных, pg_dumpall — всего кластера включая роли;
  • Логическое копирование сохраняет SQL-команды и данные таблиц в формате COPY, результат восстановления не требует бинарной совместимости;
  • Форматы вывода pg_dump: plain (-Fp), custom (-Fc), directory (-Fd), tar (-Ft) — последние три поддерживают pg_restore;
  • Восстановление из SQL-скрипта выполняется через psql, из бинарных форматов — через pg_restore с возможностью параллельного восстановления (-j);
  • Команда COPY (сторона сервера) и метакоманда \copy (сторона клиента) экспортируют и импортируют данные таблиц;
  • pg_basebackup создает физическую копию работающего кластера по протоколу репликации — основной инструмент резервного копирования;
  • Физическая копия содержит бинарные файлы всего кластера и WAL-сегменты, восстановление требует бинарной совместимости;
  • Опция -T <старый_путь>=<новый_путь> переназначает пути табличных пространств внутри копии, делаяее самодостаточной;
  • Слоты репликации резервируют WAL-сегменты на сервере, запрещая удаление до подтверждения получения клиентом;
  • Архивирование WAL обеспечивает восстановление на произвольный момент времени (PITR) с параметрами recovery_target*;
  • Для восстановления из физической копии создается файл recovery.signal и настраивается restore_command.

Самопроверка​

Вопрос 1

Какая утилита нужна для восстановления из резервной копии, полученной с помощью pg_dumpall?

Вопрос 2

Что попадает в резервную копию, выполненную с помощью pg_dump? Выберите все верные варианты.

Вопрос 3

В чем различие между SQL-командой COPY и метакомандой \copy?

Вопрос 4

Какие форматы вывода pg_dump поддерживают сжатие и параллельную обработку? Выберите все верные варианты.

Вопрос 5

Администратор выполняет:

pg_dump -d my_db -c -C > my_db.dump

Что делают опции -c и -C?

Вопрос 6

Что позволяет выполнить утилита pg_basebackup?

Вопрос 7

Табличное пространство newtabspace расположено в /pgdata/06/newdisk. При выполнении pg_basebackup -D Bkp/ возникает конфликт путей. Какую опцию нужно добавить?

Вопрос 8

Какой атрибут должна иметь роль для выполнения потокового физического резервного копирования (pg_basebackup, pg_receivewal)?

Вопрос 9

Какие способы сохранения WAL-сегментов в архив существуют? Выберите все верные варианты.

Вопрос 10

Какие шаги необходимы для восстановления из физической копии с использованием архива WAL?