# Занятие 20: Резервное копирование / Логическое резервирование > Исходная страница в нативном Markdown: Резервное копирование / Логическое резервирование --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dev1_20_backup_logical.html](https://edu.postgrespro.ru/16/dev1-16/dev1_20_backup_logical.html) > **Archive:** [DEV1-handouts-16.zip](https://edu.postgrespro.ru/16/DEV1-handouts-16.zip) > **Страниц PDF:** 24 ## Page 1: Резервное копирование / Логическое резервирование {#source-page-001} ### Авторские права © Postgres Professional, 2017–2024 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Логические и физические резервные копии - Копирование и восстановление таблицы - Копирование и восстановление базы данных - Копирование и восстановление кластера ## Page 3: Логическая копия {#source-page-003} **Команды SQL для создания объектов и наполнения данными** - + можно сделать копию отдельного объекта или отдельной базы - + можно восстановиться на другой версии или архитектуре - + (не требуется двоичная совместимость) - + простота использования - − невысокая скорость работы - − восстановление только на момент создания резервной копии ### Комментарии преподавателя Логическая резервная копия — набор команд SQL, восстанавливающих кластер (или отдельную базу данных, или отдельную таблицу) с нуля: создаются необходимые объекты и наполняются данными. Команды можно выполнить на другой версии СУБД (при наличии совместимости на уровне команд) или на другой платформе и архитектуре (не требуется двоичная совместимость). В частности, логическую резервную копию можно использовать для долговременного хранения: ее можно будет восстановить и после обновления сервера на новую версию. Создание логической резервной копии — относительно несложный процесс. Обычно он требует выполнения одной команды или запуска одной утилиты. Однако при восстановлении большой базы команды могут выполняться очень долго. Восстановить систему из логической копии можно ровно на момент начала резервного копирования. ## Page 4: Физическая копия {#source-page-004} **Копия файловой системы кластера баз данных** - + быстрее, чем логическое резервирование - + восстанавливается статистика - − можно восстановиться только на совместимой системе - − и на той же самой основной версии PostgreSQL - − выборочная копия невозможна, копируется весь кластер - Архив журнала предзаписи - + возможность восстановления на определенный момент времени ### Комментарии преподавателя Физическое резервирование подразумевает копирование всех файлов, относящихся к кластеру БД, то есть создание полной двоичной копии. Копирование файлов работает быстрее, чем выгрузка SQL-команд при логическом резервировании; запустить сервер из созданной физической копии — дело нескольких минут, в отличие от восстановления из логической копии. Кроме того, нет необходимости заново собирать статистику — она также восстанавливается из копии. Но есть и минусы. Из физической резервной копии можно восстановить систему только на совместимой платформе (та же ОС, та же разрядность, тот же порядок байтов в представлении чисел и т. п.) и только на той же основной версии PostgreSQL. Кроме того, невозможно сделать физическую копию отдельных баз данных кластера, выполняется копирование всего экземпляра целиком. Как правило, создание физических резервных копий дополняют ведением архива файлов журнала предзаписи. Это позволяет восстанавливать систему не только на момент создания резервной копии, но и на произвольный момент времени. Физическое резервирование — обычный способ создания периодических резервных копий любой сколько-нибудь серьезной системы. Создание таких копий — ответственность администратора баз данных. ## Page 5: Копия таблицы в SQL {#source-page-005} ![Source slide page 5: Копия таблицы в SQL](images/page-005.png){#source-figure-005 caption="Source slide page 5: Копия таблицы в SQL"} ```text YKS Якутск (129.770 MJZ Мирный (114.039 KHV Хабаровск-Новый (1 PKC Елизово (158.453 UUS Хомутово (142.718 VVO Владивосток (1 LED Пулково (30.2625 KGD Храброво (20.5925 KEJ Кемерово (86.1072 CEK Челябинск (61.5033 MQF Магнитогорск (5 PEE Пермь (56.0211 SGC Сургут (73.4018 ``` - файл в ФС сервера и доступен владельцу экземпляра PostgreSQL можно ограничить столбцы (или использовать произвольный запрос) при восстановлении строки добавляются к имеющимся в таблице ### Комментарии преподавателя Если требуется сохранить только содержимое одной таблицы, можно воспользоваться командой COPY. Команда позволяет записать таблицу (или часть столбцов таблицы, или даже результат произвольного запроса) либо в файл, либо на консоль, либо отправить на вход какой-либо программе. При этом можно указать ряд параметров, таких как формат (текстовый, CSV или двоичный), разделитель полей, текстовое представление NULL и т. п. Другой вариант команды, наоборот, считывает из файла или из консоли строки с полями и записывает их в таблицу. Таблица при этом не очищается, новые строки добавляются к уже существующим. Команда COPY работает существенно быстрее, чем аналогичные команды INSERT — клиенту не нужно много раз обращаться к серверу, а серверу не нужно много раз анализировать команды. Тонкость: при выполнении команды COPY FROM не применяются правила (rules), хотя ограничения целостности и триггеры выполняются. ## Page 6: Копия таблицы в psql {#source-page-006} ![Source slide page 6: Копия таблицы в psql](images/page-006.png){#source-figure-006 caption="Source slide page 6: Копия таблицы в psql"} ```text YKS Якутск (129.770 MJZ Мирный (114.039 KHV Хабаровск-Новый (1 PKC Елизово (158.453 UUS Хомутово (142.718 VVO Владивосток (1 LED Пулково (30.2625 KGD Храброво (20.5925 KEJ Кемерово (86.1072 CEK Челябинск (61.5033 MQF Магнитогорск (5 PEE Пермь (56.0211 SGC Сургут (73.4018 ``` - файл в ФС клиента и доступен пользователю ОС, запустившему psql происходит пересылка данных между клиентом и сервером синтаксис и возможности аналогичны команде COPY ### Комментарии преподавателя В psql существует клиентский вариант команды COPY с аналогичным синтаксисом. Указание имени файла в SQL-команде COPY соответствует файлу на сервере БД. У пользователя, под которым работает PostgreSQL (обычно postgres), должен быть доступ к этому файлу. В клиентском же варианте команды файл располагается на клиенте, что позволяет сохранить локальную копию данных, даже не имея доступа к файловой системе сервера. Содержимое таблиц автоматически пересылается между клиентом и сервером. ## Page 7: Команда COPY {#source-page-007} Создадим базу данных и таблицу. ```sql => CREATE DATABASE backup_logical_dev_1; ``` ```sql CREATE DATABASE ``` ```sql => \c backup_logical_dev_1 ``` ```text You are now connected to database "backup_logical_dev_1" as user "student". ``` ```sql => CREATE TABLE t( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, s text ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t(s) VALUES ('Привет, мир!'), (''), (NULL); ``` ```sql INSERT 0 3 ``` Вот что показывает команда COPY (выдаем на консоль, а не в файл): ```text => COPY t TO stdout; ``` ```text 1 Привет, мир! 2 3 \N ``` Видно, как различаются в выводе пустые строки и неопределенные значения. Формат вывода настраивается достаточно гибко. Можно изменить разделитель, представление неопределенных значений, добавить строку с названиями столбцов и т.п. Например: ```text => COPY t TO stdout WITH (NULL '', DELIMITER ',', HEADER); ``` ```text id,s 1,Привет\, мир! 2, 3, ``` Обратите внимание, что символ-разделитель внутри строки был экранирован (символ для экранирования тоже настраивается). Вместо таблицы можно указать произвольный запрос. ```sql => COPY (SELECT * FROM t WHERE s IS NOT NULL) TO stdout; ``` ```text 1 Привет, мир! 2 ``` Таким образом можно сохранить результат запроса, данные представления и т. п. Команда может работать и с форматом CSV, который поддерживается множеством программ. ```text => COPY t TO stdout WITH (FORMAT csv); ``` ```text 1,"Привет, мир!" 2,"" 3, ``` Аналогично работает и ввод данных из файла или с консоли. При вводе с консоли требуется маркер конца файла — обратная косая черта с точкой. В обычном файле он не нужен. Все параметры при вводе должны совпадать с теми, что были указаны при выводе. ```text => TRUNCATE TABLE t; ``` ```text TRUNCATE TABLE ``` ```sql => COPY t FROM stdin; 1 Привет, мир! 2 3 \N \. ``` ```text COPY 3 ``` ## Page 8: Page 8 {#source-page-008} Если вводимые данные предваряются строкой-заголовком с именами столбцов, то возможно выполнение проверки на их соответствие столбцам принимающей таблицы. ```sql => COPY t FROM stdin (HEADER MATCH); id s 4 Строка четыре \. ``` ```text COPY 1 ``` При несовпадении получим ошибку: ```sql => COPY t FROM stdin (HEADER MATCH); Col_1 Col_2 5 Строка пять \. ``` ```text ERROR: column name mismatch in header line field 1: got "Col_1", expected "id" CONTEXT: COPY t, line 1: "Col_1 Col_2" ``` Еще одна попытка добавить строку в таблицу: ```sql => INSERT INTO t (s) VALUES ('Строка пять'); ``` ```text ERROR: duplicate key value violates unique constraint "t_pkey" DETAIL: Key (id)=(4) already exists. ``` Эта ошибка возникла из-за того, что успешно выполненная выше команда COPY не передвинула значение последовательности, обслуживающей столбец id. Исправим ситуацию: ```sql => SELECT pg_catalog.setval('t_id_seq', 4, true); ``` ```text setval -------- 4 (1 row) ``` ```sql => INSERT INTO t (s) VALUES ('Строка пять'); ``` ```sql INSERT 0 1 ``` Вот что загрузилось в таблицу (для наглядности настроим в psql вывод неопределенных значений): ```text => \pset null '\\N' ``` ```text Null display is "\N". ``` ```sql => SELECT * FROM t; ``` ```text id | s ----+--------------- 1 | Привет, мир! 2 | 3 | \N 4 | Строка четыре 5 | Строка пять (5 rows) ``` ## Page 9: Копия базы данных {#source-page-009} ![Source slide page 9: Копия базы данных](images/page-009.png){#source-figure-009 caption="Source slide page 9: Копия базы данных"} ```text -- -- PostgreSQL database dum -- ``` ```sql -- Dumped from database ve -- Dumped by pg_dump versi ``` ```text SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_sessio SET client_encoding = 'UTF SET standard_conforming_st ``` - формат: команды SQL при выгрузке можно выбрать отдельные объекты базы данных новая база должна быть создана из шаблона template0 заранее должны быть созданы роли и табличные пространства после загрузки имеет смысл выполнить ANALYZE ### Комментарии преподавателя Для создания полноценной резервной копии базы данных используется утилита pg\_dump. Если не указать имя файла (-f, --file), то утилита выведет результат на консоль. А результатом является скрипт, предназначенный для psql, который содержит команды для создания необходимых объектов и наполнения их данными. Дополнительными ключами утилиты можно ограничить набор объектов: выбрать указанные таблицы, или все объекты в указанных схемах (с учетом установленных расширений), или наложить другие фильтры. Чтобы восстановить объекты из резервной копии, достаточно выполнить полученный скрипт в psql. Следует иметь в виду, что базу данных для восстановления надо создавать из шаблона template0, так как все изменения, сделанные в template1, также попадут в резервную копию. Кроме того, заранее должны быть созданы необходимые роли и табличные пространства. Поскольку эти объекты не относятся к конкретной БД, они не будут выгружены в резервную копию. После восстановления базы имеет смысл выполнить команду ANALYZE: она соберет статистику, необходимую оптимизатору для планирования запросов. ## Page 10: Формат custom {#source-page-010} ![Source slide page 10: Формат custom](images/page-010.png){#source-figure-010 caption="Source slide page 10: Формат custom"} ```text ; ; Archive created at 2017- ; dbname: demo ; TOC Entries: 146 ; Compression: -1 ; Dump Version: 1.12-0 ; Format: CUSTOM ; ; Selected TOC Entries: ; 2297; 1262 475453 DATABASE 5; 2615 475454 SCHEMA - bo 2298; 0 0 COMMENT - SCHEMA ``` - внутренний формат с оглавлением отдельные объекты базы данных можно выбрать на этапе восстановления возможна загрузка в несколько параллельных потоков ### Комментарии преподавателя Утилита pg\_dump позволяет указать формат резервной копии. По умолчанию это plain — простые команды для psql. Формат custom (-F c, --format=custom) – формат резервной копии, содержащий не только объекты, но и оглавление. Наличие оглавления позволяет выбирать объекты для восстановления не при создании копии, а непосредственно при восстановлении. Файл формата custom по умолчанию сжат. Для восстановления потребуется другая утилита — pg\_restore. Она читает файл и преобразует его в команды psql. Если не указать явно имя базы данных (в ключе -d), то команды будут выведены на консоль. Если же база данных указана, утилита соединится с этой БД и выполнит команды без участия psql. Чтобы восстановить только часть объектов, можно воспользоваться одним из двух подходов. Во-первых, можно ограничить объекты аналогично тому, как они ограничиваются в pg\_dump. Вообще, утилита pg\_restore понимает многие параметры из репертуара pg\_dump. Во-вторых, можно получить из оглавления список объектов, содержащихся в резервной копии (ключ --list). Затем этот список можно отредактировать вручную, удалив ненужное и передать измененный список на вход pg\_restore (ключ --use-list). ## Page 11: Формат directory {#source-page-011} ![Source slide page 11: Формат directory](images/page-011.png){#source-figure-011 caption="Source slide page 11: Формат directory"} каталог с оглавлением и отдельными файлами на каждый объект базы отдельные объекты базы данных можно выбрать на этапе восстановления и выгрузка, и загрузка возможны в несколько параллельных потоков ### Комментарии преподавателя Еще один формат резервной копии — directory. В таком случае будет создан не один файл, а каталог, содержащий объекты и оглавление. По умолчанию файлы внутри каталога будут сжаты. Преимущество перед форматом custom состоит в том, что такая резервная копия может создаваться параллельно в несколько потоков (количество указывается в ключе -j, --jobs). Разумеется, несмотря на параллельное выполнение, копия будет содержать согласованные данные. Это обеспечивается общим снимком данных для всех параллельно работающих процессов. S-SNAPSHOT-SYNCHRONIZATION Восстановление также возможно в несколько потоков (это работает и для формата custom). В остальном возможности по работе с форматом directory не отличаются от ранее рассмотренных: поддерживаются те же ключи и подходы. ## Page 12: Сравнение форматов {#source-page-012} plain custom directory tar - утилита для восстановления psql pg\_restore - сжатие zlib - выборочное восстановление да да да - параллельное резервирование да - параллельное восстановление да да ### Комментарии преподавателя В приведенной таблице разные форматы сравниваются с точки зрения предоставляемых ими возможностей. Отметим, что имеется и четвертый формат — tar. Он не рассматривался, так как не привносит ничего нового и не дает преимуществ перед другими форматами. Фактически он соответствует созданию tar-файла из каталога в формате directory, но не поддерживает сжатие и параллелизм. ## Page 13: Утилита pg_dump {#source-page-013} При запуске без дополнительных параметров утилита pg\_dump выдает команды SQL, создающие все объекты в базе данных: ```sh student$ pg_dump -d backup_logical_dev_1 ``` ```text -- -- PostgreSQL database dump -- ``` ```text \restrict 3cutVS0EtJ1DeJVxxgugFk8mX1QevZhuoG0wiq67K1WJ6ykBGHGZw12HrPgPodG ``` ```sql -- Dumped from database version 16.11 (Ubuntu 16.11-1.pgdg24.04+1) -- Dumped by pg_dump version 16.11 (Ubuntu 16.11-1.pgdg24.04+1) ``` ```sql 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; ``` ```text SET default_tablespace = ''; ``` ```text SET default_table_access_method = heap; ``` ```text -- -- Name: t; Type: TABLE; Schema: public; Owner: student -- ``` ```sql CREATE TABLE public.t ( id integer NOT NULL, s text ); ``` ```sql ALTER TABLE public.t OWNER TO student; ``` ```text -- -- Name: t_id_seq; Type: SEQUENCE; Schema: public; Owner: student -- ``` ```sql ALTER TABLE public.t ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY ( SEQUENCE NAME public.t_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1 ); ``` ```text -- -- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: student -- ``` ```sql COPY public.t (id, s) FROM stdin; 1 Привет, мир! 2 3 \N 4 Строка четыре 5 Строка пять \. ``` ```text -- -- Name: t_id_seq; Type: SEQUENCE SET; Schema: public; Owner: student -- ``` ```sql SELECT pg_catalog.setval('public.t_id_seq', 5, true); ``` ## Page 14: Page 14 {#source-page-014} ```text -- -- Name: t t_pkey; Type: CONSTRAINT; Schema: public; Owner: student -- ``` ```sql ALTER TABLE ONLY public.t ADD CONSTRAINT t_pkey PRIMARY KEY (id); ``` ```text -- -- PostgreSQL database dump complete -- ``` ```text \unrestrict 3cutVS0EtJ1DeJVxxgugFk8mX1QevZhuoG0wiq67K1WJ6ykBGHGZw12HrPgPodG ``` Команда \\restrict запрещает выполнение других метакоманд psql (которые мог сгенерировать злоумышленник) до соответствующей ей команды \\unrestrict. Видно, что pg\_dump создал таблицу t, добавил автоматическую генерацию идентификатора, заполнил таблицу с помощью уже рассмотренной нами команды COPY, и наконец добавил ограничение целостности для первичного ключа. Ключ --column-inserts позволяет использовать команды INSERT, но загрузка будет работать существенно дольше. Рассмотрим некоторые полезные ключи. Могут пригодиться при восстановлении копии на системе с другим набором ролей: -O, --no-owner — не генерировать команды для установки владельца объектов; -x, --no-acl — не генерировать команды для установки привилегий. Полезны для выгрузки и загрузки данных частями: -s, --schema-only — выгрузить только определения объектов без данных; -a, --data-only — выгрузить только данные, без создания объектов. Удобны, если восстанавливать копию на системе, в которой уже есть данные (и наоборот, на чистой системе): -c, --clean — генерировать команды DROP для создаваемых объектов; -C, --create — генерировать команды создания БД и подключения к ней. Важный момент: в выгрузку попадают и изменения, сделанные в шаблонной БД template1. Поэтому восстанавливать резервную копию лучше на базе данных, созданной из template0. При использовании ключа --create это учитывается автоматически: ```sh student$ pg_dump --create -d backup_logical_dev_1 | grep 'CREATE DATABASE' ``` ```sql CREATE DATABASE backup_logical_dev_1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8'; ``` Команда для создания схемы public не формируется, но если права на схему или ее владелец были изменены, то соответствующие команды появятся в выводе pg\_dump: ```sql => ALTER SCHEMA public OWNER TO student; ``` ```sql ALTER SCHEMA ``` ```sh student$ pg_dump --create -d backup_logical_dev_1 | grep 'SCHEMA' ``` ```sql -- Name: public; Type: SCHEMA; Schema: -; Owner: student ALTER SCHEMA public OWNER TO student; ``` Существуют ключи для выбора объектов, которые должны попасть в резервную копию: -n, --schema — шаблон для имен схем; -t, --table — шаблон для имен таблиц. И наоборот, включить в копию все, кроме указанного: -N, --exclude-schema — шаблон для имен схем; -T, --exclude-table — шаблон для имен таблиц. Например, восстановим таблицу t в другой базе данных. ```sql => CREATE DATABASE backup_logical_dev_2; ``` ```sql CREATE DATABASE ``` ```sh student$ pg_dump --table=t -d backup_logical_dev_1 | psql -d backup_logical_dev_2 ``` ```text SET SET ``` ## Page 15: Page 15 {#source-page-015} ```text SET SET SET set_config ------------ (1 row) ``` ```sql SET SET SET SET SET SET CREATE TABLE ALTER TABLE ALTER TABLE COPY 5 setval -------- 5 (1 row) ``` ```sql ALTER TABLE ``` Подключимся к базе данных backup\_logical\_dev\_2 и проверим: ```sql => \c backup_logical_dev_2 ``` ```text You are now connected to database "backup_logical_dev_2" as user "student". ``` ```sql => SELECT * FROM t; ``` ```text id | s ----+--------------- 1 | Привет, мир! 2 | 3 | \N 4 | Строка четыре 5 | Строка пять (5 rows) ``` ### Утилита pg\\\_dump — формат custom Серьезное ограничение обычного формата (plain) состоит в том, что выбирать объекты нужно в момент выгрузки. Формат custom позволяет сначала сделать полную копию, а выбирать объекты уже при загрузке. ```sh student$ pg_dump --format=custom -d backup_logical_dev_1 -f /home/student/backup_logical_dev_1.custom ``` Для восстановления объектов из такой копии предназначена утилита pg\_restore. Повторим восстановление таблицы t. ```sql => DROP TABLE t; ``` ```sql DROP TABLE ``` Формат резервной копии указывать не обязательно — утилита распознает его сама. Утилита pg\_restore понимает те же ключи для фильтрации объектов, что и pg\_dump, и даже больше: -I, --index — загрузить определенные индексы; -P, --function — загрузить определенные функции; -T, --trigger — загрузить определенные триггеры. ```sh student$ pg_restore --table=t -d backup_logical_dev_2 /home/student/backup_logical_dev_1.custom ``` ```sql => SELECT * FROM t; ``` ```text id | s ----+--------------- 1 | Привет, мир! 2 | 3 | \N 4 | Строка четыре 5 | Строка пять (5 rows) ``` Еще один пример: восстановим целиком исходную базу данных backup\_logical\_dev\_1. ```sql => DROP DATABASE backup_logical_dev_1; ``` ## Page 16: Page 16 {#source-page-016} ```sql DROP DATABASE ``` В ключе -d мы указываем любую существующую базу данных; с ключом --create утилита сама создаст базу, указанную в архиве, и тут же переключится в нее. ```sh student$ pg_restore --create -d student /home/student/backup_logical_dev_1.custom ``` Проверим: ```sql => \c backup_logical_dev_1 ``` ```text You are now connected to database "backup_logical_dev_1" as user "student". ``` ```sql => SELECT * FROM t; ``` ```text id | s ----+--------------- 1 | Привет, мир! 2 | 3 | \N 4 | Строка четыре 5 | Строка пять (5 rows) ``` Резервную копию в обычном (plain) формате при необходимости можно изменить в текстовом редакторе. Резервная копия формата custom хранится в двоичном виде, но и для нее доступны более широкие возможности фильтрации объектов, чем рассмотренные ключи. Утилита pg\_restore может сформировать список объектов — оглавление резервной копии: ```sh student$ pg_restore --list /home/student/backup_logical_dev_1.custom ``` ```sql ; ; Archive created at 2026-04-10 15:42:31 MSK ; dbname: backup_logical_dev_1 ; TOC Entries: 10 ; Compression: gzip ; Dump Version: 1.15-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 16.11 (Ubuntu 16.11-1.pgdg24.04+1) ; Dumped by pg_dump version: 16.11 (Ubuntu 16.11-1.pgdg24.04+1) ; ; ; Selected TOC Entries: ; 5; 2615 2200 SCHEMA - public student 216; 1259 16388 TABLE public t student 215; 1259 16387 SEQUENCE public t_id_seq student 3433; 0 16388 TABLE DATA public t student 3440; 0 0 SEQUENCE SET public t_id_seq student 3288; 2606 16394 CONSTRAINT public t t_pkey student ``` Такой список можно записать в файл, отредактировать и использовать его для восстановления с помощью ключа --use-list. ### Утилита pg\\\_dump — формат directory Формат directory интересен тем, что позволяет выгружать данные в несколько параллельных потоков. При этом гарантируется согласованность данных: все потоки будут использовать один и тот же снимок данных. ```sh student$ pg_dump --format=directory --jobs=2 -d backup_logical_dev_1 -f /home/student/backup_logical_dev_1.directory ``` Заглянем внутрь каталога: ```sh student$ ls -l /home/student/backup_logical_dev_1.directory ``` ```text total 8 -rw-rw-r-- 1 student student 95 апр 10 15:42 3432.dat.gz -rw-rw-r-- 1 student student 2286 апр 10 15:42 toc.dat ``` В нем находится файл оглавления и по одному файлу на каждый выгружаемый объект (у нас он всего один): ```sh student$ zcat /home/student/backup_logical_dev_1.directory/3432.dat.gz ``` ```text 1 Привет, мир! 2 3 \N 4 Строка четыре ``` ## Page 17: Page 17 {#source-page-017} ```text 5 Строка пять \. ``` Восстановим базу backup\_logical\_dev\_1 из резервной копии, используя два потока. Здесь мы добавляем ключ --clean, который генерирует команду удаления БД, поскольку backup\_logical\_dev\_1 существует. И предварительно надо отключиться от backup\_logical\_dev\_1: ```sql => \c backup_logical_dev_2 ``` ```text You are now connected to database "backup_logical_dev_2" as user "student". ``` ```sh student$ pg_restore --clean --create --jobs=2 -d student /home/student/backup_logical_dev_1.directory ``` ## Page 18: Копия кластера БД {#source-page-018} ![Source slide page 18: Копия кластера БД](images/page-018.png){#source-figure-018 caption="Source slide page 18: Копия кластера БД"} ```text -- -- PostgreSQL database clu -- ``` ```text SET default_transaction_re SET client_encoding = 'UTF SET standard_conforming_st ``` ```text -- -- Roles -- ``` ```sql CREATE ROLE postgres; ``` - формат: команды SQL выгружает весь кластер, включая роли и табличные пространства пользователь должен иметь доступ ко всем объектам кластера не поддерживает параллельную выгрузку ### Комментарии преподавателя Чтобы создать резервную копию всего кластера, включая роли и табличные пространства, можно воспользоваться утилитой pg\_dumpall. Поскольку pg\_dumpall требуется доступ ко всем объектам всех БД, запускать ее следует суперпользователю или пользователю, включенному в предустановленную роль pg\_read\_all\_data. Утилита по очереди подключается к каждой БД кластера и выгружает информацию с помощью pg\_dump. Кроме того, она сохраняет и данные, относящиеся к кластеру в целом. Чтобы начать работу, утилите требуется подключиться хотя бы к какой-то базе данных. По умолчанию выбирается postgres или template1, но можно указать и другую. Результатом работы pg\_dumpall является скрипт для psql. Другие форматы не поддерживаются. Это означает, что pg\_dumpall не поддерживает параллельную выгрузку данных, что может оказаться проблемой при больших объемах данных. В таком случае можно воспользоваться ключом --globals-only, чтобы выгрузить только роли и табличные пространства, а сами базы данных выгружать отдельно с помощью pg\_dump в параллельном режиме. ## Page 19: Утилита pg_dumpall {#source-page-019} Утилита pg\_dump годится для выгрузки одной базы данных, но никогда не выгружает общие объекты кластера БД, такие, как роли и табличные пространства. Чтобы сделать полную копию кластера, нужна утилита pg\_dumpall. Все рассматриваемые в этой теме утилиты не требуют каких-то отдельных привилегий, но у выполняющей их роли должны быть привилегии на чтение (создание) всех затронутых объектов. Утилитой pg\_dump может, например, пользоваться владелец базы данных. Но поскольку для копирования кластера утилите pg\_dumpall надо иметь доступ ко всем базам данных, будем использовать роль с атрибутом суперпользователя. ```sh student$ pg_dumpall --clean -f /home/student/main.sql ``` В копию кластера дополнительно попадают такие команды, как: ```sh student$ grep 'ROLE' /home/student/main.sql ``` ```sql DROP ROLE postgres; DROP ROLE student; CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:VFactPz0/OIk23KN4tl9RA==$0IO5QZttKalKrZAG+gmjjnvTyjk6hqrGSJZk75D8D0s=: Ddi6HwMT/5s52zLiUJzMbbFRmWegm3UBrdd07IFE8NU='; CREATE ROLE student; ALTER ROLE student WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:INTKa+M9YgvWbjvGK2Bcyw==$+vlrcD2yKCYXLhg0pZahND/DoBZL7UJoesq76hrH3eM=: y1U20OYj6esdULduHVmq+mVgAzI67zuO/mgQF/0IYEg='; ``` Восстановление выполняется с помощью psql — никакой другой формат не поддерживается. Команда для восстановления (мы не будем ее выполнять): ```sh student$ psql -f main.sql ``` В процессе восстановления могут возникать ошибки из-за существующих объектов — обычно это нормально и не мешает процессу, хотя все сообщения стоит проанализировать. ## Page 20: Итоги {#source-page-020} **Логическое резервирование позволяет сделать копию всего кластера, базы данных или отдельных объектов** - Хорошо подходит - для данных небольшого объема - для длительного хранения, за время которого меняется версия сервера - для миграции на другую платформу - Плохо подходит - для восстановления после сбоя с минимальной потерей данных ## Page 21: Практика {#source-page-021} 1. Создайте резервную копию базы данных bookstore в формате custom; действуйте от лица сотрудника магазина. - «Случайно» удалите все записи из таблицы authorship. - Проверьте, что приложение перестало отображать названия - книг на вкладках «Магазин», «Книги», «Каталог». - Используйте резервную копию для восстановления - потерянных данных в таблице. - Проверьте, что нормальная работа книжного магазина - восстановилась. ### Комментарии преподавателя 1. Включите пользователя в предопределенную роль pg\_read\_all\_data. При восстановлении используйте ключ --data-only, чтобы избежать ошибки при попытке создания таблицы. ## Page 22: 1. Восстановление потерянных данных {#source-page-022} Включим пользователя employee в предопределенную роль pg\_read\_all\_data и от его лица выполним создание резервной копии: ```text => GRANT pg_read_all_data TO employee; ``` ```text GRANT ROLE ``` ```sh student$ pg_dump --format=custom 'host=localhost user=employee dbname=bookstore password=employee' > /home/student/bookstore.custom ``` Удаляем строки: ```sql => DELETE FROM authorship; ``` ```sql DELETE 9 ``` Выполнить восстановление у employee не получится — у него нет прав записи в таблицу, поэтому это делает student: ```sh student$ pg_restore -t authorship --data-only -d bookstore /home/student/bookstore.custom ``` ```sql => SELECT count(*) FROM authorship; ``` ```text count ------- 9 (1 row) ``` ## Page 23: Практика+ {#source-page-023} 1. Команда psql \\copy позволяет направить результат на вход произвольной программы. Воспользуйтесь этим, чтобы открыть результаты какого-нибудь запроса в электронной таблице LibreOffice Calc. ### Комментарии преподавателя 1. Команда должна перенаправить результат в файл, а затем запустить libreoffice, указав этот файл в качестве параметра. Файл должен быть записан в формате CSV. Конечно, такой способ зависит от платформы и без модификации не будет работать, например, в Windows. ## Page 24: 1. Открытие результата запроса в LibreOffice {#source-page-024} Создаем таблицу. ```sql => CREATE DATABASE backup_logical_dev; ``` ```sql CREATE DATABASE ``` ```sql => \c backup_logical_dev ``` ```text You are now connected to database "backup_logical_dev" as user "student". ``` ```sql => CREATE TABLE t( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, s text ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t(s) VALUES ('foo'), ('bar'), ('baz'); ``` ```sql INSERT 0 3 ``` Выгружаем содержимое таблицы в csv-файл: ```text => \copy t TO PROGRAM 'cat > t.csv' WITH (format csv); ``` ```text COPY 3 ``` Если вместо \\copy использовать SQL-команду COPY, программа будет запущена на сервере СУБД, что, конечно, неправильно. Открываем /home/student/t.csv... ```sh student$ xdg-open /home/student/t.csv ``` ```sql => \c postgres ``` ```text You are now connected to database "postgres" as user "student". ``` ```sql => DROP DATABASE backup_logical_dev; ``` ```sql DROP DATABASE ```