Рассмотрим резервирование объектов, баз или целых кластеров PostgreSQL. Посмотрим на физическое и логическое резервирование а также на протокол репликации.
Существует логическое и физическое резервирование PostgreSQL. Первый тип сохраняет SQL команды выполнив которые можно восстановить объекты, например создать базу данных, наполнить её таблицами, заполнить таблицы данными и тому подобное. А второй тип резервирует сами данные, то есть сохраняет каталог PGDATA.
Логическое резервирование PostgreSQL
Недостаток логического копирования — невысокая скорость. При этом логическую копию можно загрузить в более новую версию сервера postgres. Или выгрузить базу с сервера на Windows и загрузить её на Linux. Также логическое резервное копирование позволяет сохранять не только базы, но и отдельно таблички или весь кластер.
Есть 3 инструмента для логического копирования:
- COPY — команда SQL для копирования данных из таблицы в файл или наоборот из файла в таблицу. То есть мы можем сохранить сами данные в файл с помощью COPY. А затем используя скрипт создать таблицу (CREATE) и скопировать (COPY) данные из файла в эту таблицу.
- pg_dump — утилита postgresql для копирования всей базы данных. Она использует команду COPY для выгрузки самих данных. По умолчанию pg_dump выгружает в текстовом формате, тогда восстанавливаем с помощью psql. Можно создать дамп в другом формате, тогда при загрузке нужно использовать утилиту pg_restore. Перед восстановлением должна быть создана сама база данных из шаблона template0 (если вы не меняли template1, то можно и из него). Еще заранее нужно подготовить роли и табличные пространства, если они использовались. После восстановления имеет смысл выполнить сбор статистики так как pg_dump статистику не выгружает.
- pg_dumpall — утилита postgresql для копирования всего кластера. Выгружает только в текстовом формате. С помощью дополнительных опций можно выгрузить только глобальные объекты: роли, табличные пространства и подобное. Восстанавливать такой дамп нужно с помощью утилиты psql.
Физическое резервирование PostgreSQL
Физическое резервирование сохраняет файлы данных и журналы предварительной записи. Из плюсов такого копирования выделяют:
- скорость восстановления;
- возможность восстановить кластер на определённый момент времени.
Минусы тоже есть:
- нельзя восстановить отдельную БД, только весь кластер целиком;
- восстановление возможно только на той же версии и архитектуре.
Физическое резервное копирование разделяется на:
- Холодное резервирование (при выключенном сервере) — после корректного выключения можно перенести данные на другой сервер. Если был сбой или некорректное выключение, то вместе с данными нужно перенести журналы предварительной записи с момента последней контрольной точки.
- Горячее резервирование (при включенном сервере) -делается специальными средствами, при этом требуются все файлы предварительной записи с момента начала копирования и до его окончания.
Для горячего резервирования используется утилита pg_basebackup, которая выполняет следующие действия:
- подключается к серверу по протоколу репликации;
- выполняет контрольную точку;
- переключается на следующий сегмент WAL;
- копирует файловую систему в указанный каталог;
- переключается на следующий сегмент WAL;
- копирует все сегменты WAL, сгенерированные за время копирования.
При восстановлении подменяем каталог PGDATA у кластера сохраненной резервной копией и запускаем сервер.
Протокол репликации
Протокол репликации — специальный протокол, который позволяет:
- получать поток журнальных записей. То есть не ждать пока очередной wal файл заполнится а получать записи на лету;
- выполнять команды управления резервным копированием и репликацией.
Когда мы подключаемся по протоколу репликации нас начинает обслуживать процесс wal_sender.
Чтобы мы могли работать по протоколу репликации нужно выставить параметр сервера: wal_level=replica.
Слот репликации — механизм для резервирования wal файлов. Подключившись по протоколу репликации мы создаём слот репликации и через этот слот передаются wal файлы. Когда сервер захочет удалить wal файл, он проверит, был ли этот файл уже прочитан через этот слот и если не был, то wal файл не удаляется. Нужно следить за подключенными репликами, так как если отвалится реплика, которая создала слот репликации, начинают копиться wal файлы, что может привести к заполнению диска и выходу сервера из строя.
Подробнее про репликацию можете почитать ещё тут.
Архив журналов
Можно создавать архив wal файлов. Такой архив может быть файловым или потоковым.
Файловый архив:
- сегменты WAL копируются в архив по мере заполнения;
- механизм работает под управлением сервера;
- неизбежны задержки попадания данных в архив.
Потоковый архив:
- в архив постоянно записывается поток журнальных записей;
- требуются внешние средства;
- задержки минимальны.
Чтобы запустить файловый архив нужно запустить процесс archiver. Для этого нужно настроить 3 параметра:
- archive_mode = on;
- archive_command — команда shell для копирования сегмента WAL в отдельное хранилище (или скрипт);
- archive_timeout — максимальное время для переключения на новый сегмент WAL (если по окончанию этого времени сегмент wal не заполнится, то переключение всё равно произойдет на новый файл). Даже если wal файл до конца не заполнился он все равно будет весить 16 МБ, поэтому слишком маленьким таймаут лучше не делать.
При заполнении сегмента WAL вызывается команда archive_command если команда завершается со статусом 0, сегмент удаляется если команда возвращает не 0 (в частности, если команда не задана), сегмент остается до тех пор, пока попытка не будет успешной.
При настроенном архивировании, на резервном сервере мы можем восстановиться на любой момент времени.
Для потокового архива используется утилита pg_receivewal. Она подключается по протоколу репликации и может использовать слот репликации. Затем она направляет поток записей WAL в файлы-сегменты. Стартовая позиция — начало текущего сегмента. В отличии от файлового архивирования записи wal передаются постоянно.
При восстановлении базы данных, когда есть данные на определённый момент времени и архив wal файлов. Нужно создать файл $PGDATA/recovery.conf в котором указать, откуда брать wal файлы, и включить сервер.
Практика
Создадим базу данных и подключимся к ней. Создадим таблицу и вставим туда 3 строки:
postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# CREATE DATABASE backup_overview; CREATE DATABASE Time: 98,451 ms postgres@postgres=# \c backup_overview You are now connected to database "backup_overview" as user "postgres". postgres@backup_overview=# CREATE TABLE t(id numeric, s text); CREATE TABLE Time: 3,508 ms postgres@backup_overview=# INSERT INTO t VALUES (1, 'Привет!'), (2, ''), (3, NULL); INSERT 0 3 Time: 0,812 ms
Получилась такая табличка:
postgres@backup_overview=# SELECT * FROM t; id | s ----+--------- 1 | Привет! 2 | 3 | (3 rows) Time: 0,274 ms
Вот так выглядит таблица в выводе команды COPY:
postgres@backup_overview=# COPY t TO stdout; 1 Привет! 2 3 \N Time: 1,544 ms
Обратите внимание на то, что пустая строка и NULL — разные значения, хотя, выполняя запрос SELECT это не заметно.
Затрем табличку t и с помощью COPY из стандартного ввода введём данные. Переход к другому столбцу осуществляется табуляцией:
postgres@backup_overview=# TRUNCATE TABLE t; TRUNCATE TABLE Time: 1,348 ms postgres@backup_overview=# COPY t FROM STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> 1 Hi there! >> 2 >> 3 \N >> \. COPY 3 Time: 25427,695 ms (00:25,428)
Проверим:
postgres@backup_overview=# SELECT * FROM t; id | s ----+----------- 1 | Hi there! 2 | 3 | (3 rows) Time: 0,234 ms postgres@backup_overview=# COPY t TO stdout; 1 Hi there! 2 3 \N Time: 0,183 ms
Отключимся от СУБД и выполним pg_dump. Опция —create добавляет команды для последующего создания базы данных при загрузки из дампа. Без этой опции вначале пришлось бы создать базу а потом в неё загружать дамп.
postgres@backup_overview=# \q
postgres@s-pg13:~$ pg_dump -d backup_overview --create
--
-- PostgreSQL database dump
--
-- Dumped from database version 13.3
-- Dumped by pg_dump version 13.3
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;
CREATE DATABASE backup_overview WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'ru_RU.UTF-8';
***(сократил вывод)***
CREATE TABLE public.t (
id numeric,
s text
);
***(сократил вывод)***
COPY public.t (id, s) FROM stdin;
1 Hi there!
2
3 \N
\.
--
-- PostgreSQL database dump complete
--
В выводе у нас логическая копия базы данных, которая выводится на стандартный поток, то есть в терминал. Можно было бы перенаправить поток в файл для создания файла логической архивной копии.
Теперь из одной базы сделаем другую, при этом передавать будем только таблицу «t«:
postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# CREATE DATABASE backup_overview2; CREATE DATABASE Time: 62,386 ms postgres@postgres=# \q postgres@s-pg13:~$ pg_dump -d backup_overview --table=t | psql -d backup_overview2 Timing is on. SET Time: 0,133 ms SET Time: 0,094 ms SET Time: 0,064 ms SET Time: 0,050 ms SET Time: 0,046 ms set_config ------------ (1 row) Time: 0,416 ms SET Time: 0,045 ms SET Time: 0,046 ms SET Time: 0,038 ms SET Time: 0,038 ms SET Time: 0,038 ms SET Time: 0,062 ms CREATE TABLE Time: 2,933 ms ALTER TABLE Time: 0,248 ms COPY 3 Time: 0,293 ms postgres@s-pg13:~$ psql -d backup_overview2 Timing is on. psql (13.3) Type "help" for help. postgres@backup_overview2=# SELECT * FROM t; id | s ----+----------- 1 | Hi there! 2 | 3 | (3 rows) Time: 0,569 ms
Теперь поработаем с pg_basebackup и протоколом репликации. Убедимся что wal_level=replica и max_wal_senders больше нуля:
postgres@backup_overview2=# SELECT name, setting FROM pg_settings WHERE name IN ('wal_level','max_wal_senders');
name | setting
-----------------+---------
max_wal_senders | 10
wal_level | replica
(2 rows)
Time: 1,571 ms
Убедимся что в pg_hba.conf есть разрешение подключаться по протоколу репликации:
postgres@backup_overview2=# SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules WHERE database = '{replication}';
type | database | user_name | address | auth_method
-------+---------------+-----------+-----------+-------------
local | {replication} | {all} | | trust
host | {replication} | {all} | 127.0.0.1 | trust
host | {replication} | {all} | ::1 | trust
(3 rows)
Time: 1,078 ms
Отключимся от СУБД, очистим созданный на первом уроке каталог data2. После чего сделаем бекап с помощью pg_basebackup:
postgres@backup_overview2=# \q postgres@s-pg13:~$ rm -rf /usr/local/pgsql/data2/* postgres@s-pg13:~$ pg_basebackup --pgdata=/usr/local/pgsql/data2/
Посмотрим что у нас скопировалось:
postgres@s-pg13:~$ ls -l /usr/local/pgsql/data2 итого 525 -rw------- 1 postgres postgres 224 июн 30 10:42 backup_label -rw------- 1 postgres postgres 440132 июн 30 10:42 backup_manifest drwx------ 12 postgres postgres 12 июн 30 10:42 base drwx------ 2 postgres postgres 59 июн 30 10:42 global drwx------ 2 postgres postgres 2 июн 30 10:42 pg_commit_ts drwx------ 2 postgres postgres 2 июн 30 10:42 pg_dynshmem -rw------- 1 postgres postgres 4760 июн 30 10:42 pg_hba.conf -rw------- 1 postgres postgres 1636 июн 30 10:42 pg_ident.conf drwx------ 4 postgres postgres 5 июн 30 10:42 pg_logical drwx------ 4 postgres postgres 4 июн 30 10:42 pg_multixact drwx------ 2 postgres postgres 2 июн 30 10:42 pg_notify drwx------ 2 postgres postgres 2 июн 30 10:42 pg_replslot drwx------ 2 postgres postgres 2 июн 30 10:42 pg_serial drwx------ 2 postgres postgres 2 июн 30 10:42 pg_snapshots drwx------ 2 postgres postgres 2 июн 30 10:42 pg_stat drwx------ 2 postgres postgres 2 июн 30 10:42 pg_stat_tmp drwx------ 2 postgres postgres 2 июн 30 10:42 pg_subtrans drwx------ 2 postgres postgres 2 июн 30 10:42 pg_tblspc drwx------ 2 postgres postgres 2 июн 30 10:42 pg_twophase -rw------- 1 postgres postgres 3 июн 30 10:42 PG_VERSION drwx------ 3 postgres postgres 4 июн 30 10:42 pg_wal drwx------ 2 postgres postgres 3 июн 30 10:42 pg_xact -rw-r--r-- 1 postgres postgres 15 июн 30 10:42 postgres.conf -rw------- 1 postgres postgres 216 июн 30 10:42 postgresql.auto.conf -rw------- 1 postgres postgres 28037 июн 30 10:42 postgresql.conf
Перенастроим порт для второго кластера и запустим его. Подключимся к СУБД и проверим нашу базу данных «backup_overview2» с табличкой «t»:
postgres@s-pg13:~$ echo port=5433 >> /usr/local/pgsql/data2/postgresql.auto.conf postgres@s-pg13:~$ pg_ctl -w -l /home/postgres/logfile2 -D /usr/local/pgsql/data2 start waiting for server to start.... done server started postgres@s-pg13:~$ psql -p 5433 -d backup_overview2 Timing is on. psql (13.3) Type "help" for help. postgres@backup_overview2=# SELECT * FROM t; id | s ----+----------- 1 | Hi there! 2 | 3 | (3 rows) Time: 0,913 ms
Отключимся от СУБД и остановим второй кластер:
postgres@backup_overview2=# \q postgres@s-pg13:~$ pg_ctl -D /usr/local/pgsql/data2 stop waiting for server to shut down.... done server stopped
Можно было бы утилитой pg_basebackup подключиться к удалённому серверу и сделать бекап всего кластера себе на компьютер, а затем восстановить на каком-нибудь другом сервере подменив каталог PGDATA.
Добрый день.
«После восстановления имеет смысл выполнить сбор статистики так как pg_dump статистику не выгружает.» Зачем? Насколько я понимаю, статистика нужна для планировщика и оптимизатора выполнения запросов. Если дамп только развёрнут и обращений от клиентов к базе не было, как вообще построится статистика и как это улучшит работу БД?
P.S.: только начал изучать эту тему, много не понимаю, извиняюсь, если вопрос не корректный.