Рассмотрим резервирование объектов, баз или целых кластеров 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.


Сводка
Резервирование PostgreSQL
Имя статьи
Резервирование PostgreSQL
Описание
Рассмотрим резервирование объектов, баз или целых кластеров PostgreSQL. Посмотрим на физическое и логическое резервирование а также на протокол репликации

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *