Резервное копирование и восстановление

Существует три фундаментально разных подхода к резервному копированию данных в PostgreSQL:

• Выгрузка в SQL
• Копирование на уровне файлов
• Непрерывное архивирование

Идея, стоящая за методом выгрузки в sql, заключается в генерации текстового файла с командами SQL, которые при выполнении на сервере пересоздадут базу данных в том же самом состоянии, в котором она была на момент выгрузки. PostgreSQL предоставляет для этой цели вспомогательную программу pg_dump. Простейшее применение этой программы выглядит так:

pg_dump имя_базы > файл_дампа

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

Текстовые файлы, созданные pg_dump, предназначаются для последующего чтения программой psql. Общий вид команды для восстановления дампа:

psql имя_базы < файл_дампа

где файл_дампа — это файл, содержащий вывод команды pg_dump. База данных, заданная параметром имя_базы, не будет создана данной командой, так что вы должны создать её сами из базы template0 перед запуском psql (например, с помощью команды createdb -T template0 имя_базы)

Перед восстановлением SQL-дампа все пользователи, которые владели объектами или имели права на объекты в выгруженной базе данных, должны уже существовать. Если их нет, при восстановлении будут ошибки пересоздания объектов с изначальными владельцами и/или правами. (Иногда это желаемый результат, но обычно нет)

По умолчанию, если происходит ошибка SQL, программа psql продолжает выполнение. Если же запустить psql с установленной переменной ON_ERROR_STOP, это поведение поменяется и psql завершится с кодом 3 в случае возникновения ошибки SQL:

psql --set ON_ERROR_STOP=on имя_базы < файл_дампа

В любом случае вы получите только частично восстановленную базу данных. В качестве альтернативы можно указать, что весь дамп должен быть восстановлен в одной транзакции, так что восстановление либо полностью выполнится, либо полностью отменится. Включить данный режим можно, передав psql аргумент -1 или --single-transaction. Выбирая этот режим, учтите, что даже незначительная ошибка может привести к откату восстановления, которое могло продолжаться несколько часов

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

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

pg_dumpall > файл_дампа

Полученную копию можно восстановить с помощью psql:

psql -f файл_дампа postgres

pg_dump/pg_dumpall умеют сжимать и писать параллельно в несколько файлов дампа

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

tar -cf backup.tar /usr/local/pgsql/data

Однако существуют два ограничения, которые делают этот метод непрактичным или как минимум менее предпочтительным по сравнению с pg_dump:

• Чтобы полученная резервная копия была годной, сервер баз данных должен быть остановлен. Такие полумеры, как запрещение всех подключений к серверу, работать не будут (отчасти потому что tar и подобные средства не получают мгновенный снимок состояния файловой системы, но ещё и потому, что в сервере есть внутренние буферы). Необходимо отметить, что сервер нужно будет остановить и перед восстановлением данных.
• Если вы ознакомились с внутренней организацией базы данных в файловой системе, у вас может возникнуть соблазн скопировать или восстановить только отдельные таблицы или базы данных в соответствующих файлах или каталогах. Это не будет работать, потому что информацию, содержащуюся в этих файлах, нельзя использовать без файлов журналов транзакций, pg_xact/*, которые содержат состояние всех транзакций. Без этих данных файлы таблиц непригодны к использованию. Разумеется также невозможно восстановить только одну таблицу и соответствующие данные pg_xact, потому что в результате нерабочими станут все другие таблицы в кластере баз данных. Таким образом, копирование на уровне файловой системы будет работать, только если выполняется полное копирование и восстановление всего кластера баз данных

Наличие WAL делает возможным Point-in-Time Recovery

• В качестве начальной точки для восстановления необязательно иметь полностью согласованную копию на уровне файлов. Внутренняя несогласованность копии будет исправлена при воспроизведении журнала (практически то же самое происходит при восстановлении после краха). Таким образом, согласованный снимок файловой системы не требуется, вполне можно использовать tar или похожие средства архивации
• Поскольку при воспроизведении можно обрабатывать неограниченную последовательность файлов WAL, непрерывную резервную копию можно получить, просто продолжая архивировать файлы WAL. Это особенно ценно для больших баз данных, полные резервные копии которых делать как минимум неудобно
• Воспроизводить все записи WAL до самого конца нет необходимости. Воспроизведение можно остановить в любой точке и получить целостный снимок базы данных на этот момент времени. Таким образом, данная технология поддерживает восстановление на момент времени: можно восстановить состояние базы данных на любое время с момента создания резервной копии
• Если непрерывно передавать последовательность файлов WAL другому серверу, получившему данные из базовой копии того же кластера, получается система тёплого резерва: в любой момент мы можем запустить второй сервер и он будет иметь практически текущую копию баз данных

Программы pg_dump и pg_dumpall не создают копии на уровне файловой системы и не могут применяться как часть решения по непрерывной архивации. Создаваемые ими копии являются логическими и не содержат информации, необходимой для воспроизведения WAL

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

WAL пишется бесконечно, а postgres физически делит эту последовательность на файлы сегментов WAL, которые обычно имеют размер 16 МиБ (хотя размер сегмента может быть изменён при initdb). Файлы сегментов получают цифровые имена, которые отражают их позицию в абстрактной последовательности WAL. Когда архивирование WAL не применяется, система обычно создаёт только несколько файлов сегментов и затем «перерабатывает» их, меняя номер в имени ставшего ненужным файла на больший. Предполагается, что файлы сегментов, содержимое которых предшествует последней контрольной точке, уже не представляют интереса и могут быть переработаны

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

Чтобы у администратора баз данных была гибкость в этом плане, PostgreSQL пытается не делать каких-либо предположений о том, как будет выполняться архивация. Вместо этого, PostgreSQL позволяет администратору указать команду оболочки, которая будет запускаться для копирования завершённого файла-сегмента в нужное место. Эта команда может быть простой как cp, а может вызывать сложный скрипт оболочки — это решать вам

Чтобы включить архивирование WAL, установите в параметре конфигурации wal_level уровень replica (или выше), в archive_mode — значение on, и задайте желаемую команду оболочки в параметре archive_command. На практике эти параметры всегда задаются в файле postgresql.conf

Например

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  

Будет преобразована для каждого нового wal-файла в что-то похожее на

test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_wal/00000001000000A900000065 /mnt/server/archivedir/00000001000000A900000065

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

Важно, чтобы команда архивирования возвращала нулевой код завершения, если и только если она завершилась успешно. Получив нулевой результат, PostgreSQL будет полагать, что файл успешно заархивирован и удалит его или переработает. Однако ненулевой код состояния скажет PostgreSQL, что файл не заархивирован; попытки заархивировать его будут периодически повторяться, пока это не удастся. Следовательно если что-то случится с бэкапилкой (например кончится место на удаленном сервере), то wal-файлы не будут удаляться и может закончиться место на сервере с postgres'ом, это нужно учитывать и писать скрипт-архиватор с умом

При написании команды архивирования вы должны иметь в виду, что имена файлов для архивирования могут иметь длину до 64 символов и содержать любые комбинации из цифр, точек и букв ASCII

Конфигурационные файлы (такие как postgresql.conf, pg_hba.conf и pg_ident.conf), измененяются вручную, а не через SQL. Поэтому имеет смысл разместить конфигурационные файлы там, где они будут заархивированы обычными процедурами копирования файлов

Команда архивирования вызывается, только когда сегмент WAL заполнен до конца, поэтому при маленьком трафике может возникать большая задержка между завершением транзакции и ее архивированием
Чтобы ограничить время жизни неархивированных данных, можно установить archive_timeout, чтобы сервер переключался на новый файл сегмента WAL как минимум с заданной частотой. Заметьте, что неполные файлы, архивируемые досрочно из-за принудительного переключения по тайм-ауту, будут иметь тот же размер, что и заполненные файлы. Таким образом, устанавливать очень маленький archive_timeout — неразумно; это приведёт к неэффективному заполнению архива

Если вы хотите на время остановить архивирование, это можно сделать, например, задав в качестве значения archive_command пустую строку (''). В результате файлы WAL будут накапливаться в каталоге pg_wal/, пока не будет восстановлена действующая команда archive_command

pg_dump creates a logical backup, that is a series of SQL statements that, when executed, create a new database that is logically like the original one
pg_basebackup creates a physical backup, that is a copy of the files that constitute the database cluster. You have to use recovery to make such a backup consistent
The main differences are:
- pg_dump typically takes longer and creates a smaller backup
- With pg_dump you can back up one database or parts of a database, while pg_basebackup always backs up the whole cluster
- A backup created by pg_dump is complete, while you need WAL archives to restore a backup created with pg_basebackup (unless you used the default option -X stream, in which case the backup contains the WAL segments required to make the backup consistent)
- With a logical backup you can only restore the state of the database at backup time, while with a physical backup you can restore any point in time after the end of the backup, provided you archived the required WAL segments
- You need pg_basebackup to create a standby server, pg_dump won't do

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

Создаем бэкап и перекачиваем его на другой сервер

root@pgsql-edu:/tmp# sudo -u postgres pg_basebackup -D /tmp/pgback/ -Ft -Xs -P
73159/73159 kB (100%), 2/2 tablespaces
root@pgsql-edu:/tmp# ls -lh pgback/
total 88M
-rw------- 1 postgres postgres 2.5K Jan 10 03:21 16421.tar
-rw------- 1 postgres postgres 264K Jan 10 03:21 backup_manifest
-rw------- 1 postgres postgres  72M Jan 10 03:21 base.tar
-rw------- 1 postgres postgres  17M Jan 10 03:21 pg_wal.tar
root@pgsql-edu:/tmp# scp -r pgback/* user@192.168.0.29:/tmp/back/.
user@192.168.0.29's password: 
16421.tar                                            100% 2560     7.8MB/s   00:00    
backup_manifest                                      100%  263KB 248.1MB/s   00:00    
base.tar                                             100%   71MB 278.8MB/s   00:00    
pg_wal.tar                                           100%   16MB 307.2MB/s   00:00    

На втором сервере выключаем постгрес и распаковываем это все (находясь в PGDATA)

tar -xf /tmp/back/16421.tar -C /mnt/tipasuperssd
tar -xf /tmp/back/base.tar -C .
tar -xf /tmp/back/pg_wal.tar -C ./pg_wal/

Запускаем, проверяем

root@pgsql-1:/tmp/back# sudo -u postgres psql
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.

postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# select * from testtable order by id desc limit 3;
 id  |             date             
-----+------------------------------
 181 | Mon Jan 10 00:35:09 UTC 2022
 180 | Mon Jan 10 00:35:09 UTC 2022
 179 | Mon Jan 10 00:35:09 UTC 2022
(3 rows)

По-нормальному про восстановление - https://postgrespro.ru/docs/postgresql/14/continuous-archiving

Перечитать вторую половину