Резервная копия postgresql
Резервное копирование
Существует два вида резервного копирования, логическое и физическое
Логическое - набор команд sql, восстановление кластера или базы данных или отдельного объекта с нуля
Такая копия представляет собой текстовый файл, который можно редактировать при необходимости
Текстовый файл с командами sql можно выполнить на другой версии СУБД или архитектуре, в данном случае требуется совместимость на уровне команд
https://postgrespro.ru/docs/postgresql/16/backup-dump
Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_14_backup_overview.html
Выгрузка в sql с помощью pg_dump
pg_dump — позволяет выгрузить базу данных PostgreSQL в виде скрипта или в архивном формате
Результатом работы pg_dump является либо SQL-скрипт, содержащий команды, создающие выбранные объекты, либо файл в специальном форматес оглавлением
pg_dump создаёт целостные копии, даже если база параллельно используется
pg_dump не препятствует доступу других пользователей к базе данных (ни для чтения, ни для записи)
pg_dump позволяет выполнять резервное копирование с удаленного компьютера при доступе к базе данных
pg_dump сможет выгрузить данные с учетом прав доступа к объекту ( база, схема, таблица и пр. )
pg_dump используется для подключения переменные PGHOST, PGUSER и PGPORT или явно указанные -h сервер, -U пользователь и -p порт
pg_dump будет работать при переносе базы данных на другую машинную архитектуру, например, при переносе с 32-битной на 64-битную версию сервера
Дампы, создаваемые pg_dump, являются внутренне согласованными, то есть, дамп представляет собой снимок базы данных на момент начала запуска pg_dump. pg_dump не блокирует другие операции с базой данных во время своей работы. (Исключение составляют операции, которым нужна исключительная блокировка, как например, большинство форм команды ALTER TABLE.)
Дампы сделанные в текстовом формате восстанавливаются с помощью psql, дампы сделанные не в текстовом формате, восстанавливаются с помощью pg_restore
По умолчанию, если происходит ошибка SQL, программа psql продолжает выполнение. Если же запустить psql с установленной переменной ON_ERROR_STOP, это поведение поменяется и psql завершится с кодом 3 в случае возникновения ошибки SQL:
psql --set ON_ERROR_STOP=on имя_базы < файл_дампа
В данном случае мы получим только частично восстановленную базу данных. В качестве альтернативы можно указать, что весь дамп должен быть восстановлен в одной транзакции, так что восстановление либо полностью выполнится, либо полностью отменится. Включить данный режим можно, передав psql аргумент -1 или --single-transaction
Благодаря способности pg_dump и psql писать и читать каналы ввода/вывода, можно скопировать базу данных непосредственно с одного сервера на другой, например:
pg_dump -h host1 имя_базы | psql -h host2 имя_базы
После восстановления резервной копии имеет смысл запустить ANALYZE для каждой базы данных, чтобы оптимизатор запросов получил полезную статистику
pg_dumpall делает резервную копию всех баз данных кластера, а также сохраняет данные уровня кластера, такие как роли и определения табличных пространств
Параметры
--data-only - Выводить только данные, но не схемы объектов (DDL)
-c --clean - Включить в выходной файл команды удаления DROP всех выгружаемых объектов базы данных перед командами создания этих объектов. Этот параметр полезен, если при восстановлении необходимо перезаписать существующую базу данных. Если дополнительно не указать флаг --if-exists, то при восстановлении в базу данных, где некоторые объекты отсутствуют, попытка удаления несуществующего объекта будет приводить к ошибке, которую можно игнорировать
Этот параметр игнорируется, когда данные выгружаются в архивных форматах (не в текстовом). Для таких форматов данный параметр можно указать при вызове pg_restore.
-E --encoding=кодировка - Создать копию в заданной кодировке
-F --format - Указывает формат вывода копии.
p plain - Сформировать текстовый SQL-скрипт. Это поведение по умолчанию
c custom Выгрузить данные в специальном архивном формате, пригодном для дальнейшего использования утилитой pg_restore
d directory - Выгрузить данные в формате каталога. Этот формат пригоден для дальнейшего использования утилитой pg_restore
t tar - Выгрузить данные в формате tar, для дальнейшего использования с утилитой pg_restore
-j число_заданий --jobs=число_заданий - Осуществить выгрузку в параллельном режиме, обрабатывая одновременно несколько таблиц (в количестве число_заданий). Это может сократить время, необходимое для выгрузки, но увеличивает нагрузку на сервер. Этот параметр можно использовать только с форматом вывода в каталог, так как это единственный формат, позволяющий нескольким процессам записывать данные одновременно
-n шаблон --schema=шаблон Выгрузить только схемы, соответствующие шаблону; вместе с этими схемами будут выгружены и все содержащиеся в них объекты
При использовании -n, pg_dump не выгружает объекты других схем, от которых выгружаемая схема может зависеть. Таким образом не гарантируется, что выгруженная схема будет успешно восстановлена в чистой базе данных
-N шаблон --exclude-schema=шаблон - Не выгружать схемы, соответствующие шаблону
-s --schema-only - Выгружать только определения объектов (схемы), без данных
Чтобы не выгружать данные отдельных таблиц, используйте параметр --exclude-table-data
-t шаблон --table=шаблон - Выгрузить только таблицы, соответствующие шаблону. Чтобы выбрать несколько таблиц, ключ -t можно указать несколько раз
pg_dump -t - не выгружает прочие объекты, от которых выгружаемые таблицы могут зависеть. Таким образом не гарантируется, что выгруженные таблицы будут успешно восстановлены в чистой базе данных
-T шаблон --exclude-table=шаблон - Не выгружать таблицы, соответствующие шаблону
-v --verbose - Включить подробный режим. pg_dump будет выводить в стандартный поток ошибок подробные комментарии к объектам, включая время начала и окончания выгрузки, а также сообщения о прогрессе выполнения
-x --no-privileges --no-acl - Не выгружать права доступа (команды GRANT/REVOKE)
-Z уровень --compress=уровень - Указывает метод и/или уровень сжатия
--column-inserts --attribute-inserts - Выгружать данные таблиц в виде команд INSERT с явным указанием столбцов (INSERT INTO таблица (столбец, ...) VALUES ...). Скорость восстановления при этом значительно снизится, но данный вариант оправдан, когда загружать данные нужно не в PostgreSQL. При этом в случае каких-либо ошибок при загрузке данных будут потеряны только строки INSERT, где возникли ошибки, но не всё содержимое таблицы
--disable-triggers - Используется при выгрузке одних данных. Указывает pg_dump включать в вывод команды для временного выключения триггеров при восстановлении в целевой базе данных. Применяется в ситуациях, когда существуют проверки ссылочной целостности или другие триггеры, которые необходимо выключить на время восстановления
Этот параметр игнорируется, когда данные выгружаются в архивных форматах (не в текстовом). Для таких форматов данный параметр можно указать при вызове pg_restore
Команда COPY не является стандартом SQL
--inserts - позволяет выгружать данные таблиц в виде команд INSERT вместо COPY. Скорость восстановления при этом значительно снизится, но данный вариант оправдан, когда загружать данные нужно не в PostgreSQL. При этом в случае каких-либо ошибок при загрузке данных будут потеряны только строки INSERT, где возникли ошибки, но не всё содержимое таблицы. Заметьте, что восстановление может провалиться полностью, если у таблицы изменён порядок столбцов. В такой ситуации можно использовать параметр --column-inserts, для которого порядок столбцов не важен, но он работает ещё медленнее.
--no-comments - Не выгружать комментарии
--no-tablespaces - Не формировать команды для указания табличных пространств
--on-conflict-do-nothing - Добавить предложения ON CONFLICT DO NOTHING в команды INSERT. Это указание допускается только при выборе режима --inserts, --column-inserts или --rows-per-insert
--quote-all-identifiers - Принудительно экранировать все идентификаторы. Этот параметр рекомендуется при выгрузке базы, когда основная версия сервера PostgreSQL, с которого выгружается база, отличается от версии pg_dump, или когда выгруженная копия предназначена для загрузки на сервере с другой основной версией
pg_dump на низком уровне выполняет команды SELECT
Если есть проблемы с работой pg_dump, необходимо убедиться, что в базе данных можно выполнить SELECT, например из psql
Обычно действия pg_dump в базе данных отслеживаются системой накопительной статистики. Если это нежелательно, то можно установить параметр track_counts в false в переменной окружения PGOPTIONS или в команде ALTER USER.
Источник: https://postgrespro.ru/docs/postgresql/16/app-pgdump
Примечания
Если в базу данных кластера template1 устанавливались дополнительные объекты, то следует убедиться, что выгрузка pg_dump загружается в пустую базу данных. Иначе существует вероятность возникновения ошибок дублирования создаваемых объектов. Чтобы создать пустую базу данных, копируйте её из шаблона template0, вместо template1, например:
CREATE DATABASE foo WITH TEMPLATE template0;
Если выгружаются только данные с одновременным использованием --disable-triggers, pg_dump сформирует команды для выключения табличных триггеров перед вставкой данных, а после них — команды, включающие триггеры обратно. Если восстановление будет прервано в середине процесса, системный каталог может остаться в неверном состоянии.
Так как pg_dump применяется для переноса данных в новые версии PostgreSQL, предполагается, что вывод pg_dump можно загрузить на сервер PostgreSQL более новой версии, чем версия pg_dump. pg_dump может также выгружать данные серверов PostgreSQL более старых версий, чем его собственная. (В настоящее время поддерживаются версии, начиная с 9.2.) Однако утилита pg_dump не может выгружать данные с серверов PostgreSQL более новых основных версий; она не будет даже пытаться делать это, во избежание некорректной выгрузки. Также не гарантируется, что вывод pg_dump может быть загружен на сервере более старой основной версии — даже если данные были выгружены с сервера той же версии. Для загрузки такого файла на старом сервере может потребоваться вручную исправить в нём синтаксис, не воспринимаемый старой версией. Имея дело с разными версиями, рекомендуется применять параметр --quote-all-identifiers, так как он может предотвратить проблемы, возникающие при изменении множества зарезервированных слов в разных версиях PostgreSQL.
Источник: https://postgrespro.ru/docs/postgresql/16/app-pgdump
Примеры
--Выгрузка базы данных в специальном формате:
$ pg_dump -Fc mydb > db.dump
--Выгрузка базы данных в формате каталога:
$ pg_dump -Fd mydb -f dumpdir
--Выгрузка базы данных в формате каталога в 5 параллельных потоков:
$ pg_dump -Fd mydb -j 5 -f dumpdir
--Восстановление из архива в чистую новую базу данных newdb:
$ pg_restore -d newdb db.dump
--Восстановление архива в ту же базу данных, из которой он был выгружен, с предварительным удалением текущего содержимого этой базы данных:
$ pg_restore -d postgres --clean --create db.dump
--Выгрузка отдельной таблицы mytab:
$ pg_dump -t mytab mydb > db.sql
--Выгрузка всех таблиц, имена которых начинаются с emp и которые принадлежат схеме detroit, кроме таблицы employee_log:
$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
--Выгрузка всех схем, имена которых начинаются с east или west, заканчиваются на gsm и не содержат test:
$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
--То же самое, но с использованием регулярного выражения:
$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
--Выгрузка всех объектов базы данных, кроме таблиц, имена которых начинаются с ts_:
$ pg_dump -T 'ts_*' mydb > db.sql
--Чтобы указать имя в верхнем или смешанном регистре в ключе -t и связанных с ним, это имя нужно заключить в кавычки; иначе оно будет приведено к нижнему регистру (см. Шаблоны поиска). Но кавычки являются спецсимволом для оболочки, поэтому и они, в свою очередь, должны заключаться в кавычки. Так, чтобы выгрузить одну таблицу с именем в смешанном регистре, нужно написать примерно следующее:
$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql
Источник: https://postgrespro.ru/docs/postgresql/16/app-pgdump
Управление большими базами данных
pg_dump может писать в стандартный вывод, так чтобы можно было использовать стандартные инструменты linux
#Использование gzip
sudo -u postgres pg_dumpall | gzip > dumptest.gz
#Обратный процесс восстановления
gunzip -c dumptest.gz | sudo -u postgres psql
Если при сборке PostgreSQL была подключена библиотека zlib, дамп в специальном формате будет записываться в файл в сжатом виде. В таком формате размер файла дампа будет близок к размеру, полученному с применением gzip, но он лучше тем, что позволяет восстанавливать таблицы выборочно
sudo -u postgres pg_dump -Fc testdb > testdb.dmp
#Восстановление из дампа
pg_restore -d testdb.dmp
Используйте split. Команда split может разбивать выводимые данные на небольшие файлы, размер которых удовлетворяет ограничению нижележащей файловой системы. Например, чтобы получить части по 2 гигабайта:
pg_dump имя_базы | split -b 2G - test1.sql
#Восстановление
cat имя_файла* | psql имя_базы
#Использовать GNU split можно вместе с gzip:
Восстановить данные после такого разбиения можно с помощью команды zcat.
Дополнительный материал
Резервное копирование на уровне файлов
https://postgrespro.ru/docs/postgresql/16/backup-file
COPY
Чтобы сохранть только одну таблицу, можно воспользоваться командой COPY
COPY перемещает данные между таблицами и файлами
Команда позволяет записывать таблицу в файл, лобо в поток вывода или на вход произвольной программы. Дополнительно можно указать формат (csv, текстовый, двоичный)
Другой вариант команды, это считывание из файла или потока для записи в таблицу
Команда COPY работает гораздо быстрее чем команда INSERT, так как не нужно много раз обращаться к серверу, чтобы тот в свою очередь анализировал каждый запрос
COPY TO копирует данные таблицы в файл
COPY FROM копирует данные из файла в таблицу
COPY TO может также копировать результаты запроса SELECT
Для COPY TO можно указать только определенные столбцы
COPY FROM вставляет каждое поле из файла в соответствующий ему по порядку столбец из указанного списка, В случае отсутствия в этом списке каких-либо столбцов таблицы при COPY FROM они получают значения по умолчанию
Заданный файл должен быть доступен пользователю PostgreSQL (от имени которого работает сервер)
Когда указывается параметр PROGRAM, сервер выполняет заданную команду и читает данные из стандартного вывода программы, либо записывает их в стандартный ввод. Команда должна определяться с точки зрения сервера и быть доступной для исполнения пользователю PostgreSQL. Когда указывается STDIN или STDOUT, данные передаются через соединение клиента с сервером.
Каждый процесс, выполняющий операцию COPY, будет выдавать информацию о ходе её выполнения, отображаемую в представлении pg_stat_progress_copy
Источник: https://postgrespro.ru/docs/postgresql/16/sql-copy
Синтаксис
COPY схема.имя_таблицы [ имя_столбца ] FROM файл [ или PROGRAM 'команда' или STDIN ] [ WITH параметр ] [ WHERE условие ]
COPY схема.имя_таблицы [ имя_столбца ] или ЗАПРОС TO имя_файла имя файла или PROGRAM 'команда' или STDOUT [ WITH параметр ]
Параметры
Запрос:
В качестве запроса может выполняться команды SELECT, VALUES, INSERT, UPDATE или DELETE, результаты которой будут скопированы. Запрос должен заключаться в скобки
Для запросов INSERT, UPDATE и DELETE должно задаваться предложение RETURNING и в целевом отношении не должно быть условного правила, правила ALSO или правила INSTEAD, разворачивающегося в несколько операторов.
PROGRAM:
COPY FROM читает стандартный вывод команды, COPY TO записывает ее в стандартный ввод
STDIN указывает, что данные будут поступать из пользовательского приложения
STDOUT указывает, что данные будут выдаваться клиентскому приложению
FORMAT выбирает формат чтения или записи ( по умолчанию text )
FREEZE Запросы копируют данные с уже замороженными строками, как после выполнения команды VACUUM FREEZE
При выполнении COPY FROM любые значения, совпадающие с этой строкой, сохраняются как значение NULL, так что при переносе данных важно убедиться в том, что это та же строка, что применялась в COPY TO.
DEFAULT Задаёт строку, представляющую значение по умолчанию. Каждый раз, когда указанная строка встречается во входном файле, вычисляется значение по умолчанию для соответствующего столбца. Этот параметр допускается только в команде COPY FROM и только не для формата binary
HEADER Указывает, что файл содержит строку заголовка с именами каждого столбца в файле.
QUOTE Указывает символ кавычек, используемый для заключения данных в кавычки
ENCODING Указывает, что файл имеет кодировку имя_кодировки
WHERE - WHERE условие
Строки, не удовлетворяющие этому условию, добавляться в таблицу не будут
Примечания
Команду COPY можно использовать только с простыми таблицами, не копируя строк из дочерних таблиц
COPY таблица TO копирует те же строки, что выдаёт запрос SELECT * FROM ONLY таблица
Для выгрузки всех строк представления или таблицы с учётом иерархии наследования или секционирования можно применить COPY (SELECT * FROM таблица) TO ....
COPY FROM можно применять с обычными, сторонними и секционированными таблицами или представлениями, в которых установлены триггеры INSTEAD OF INSERT
В таблице, данные которой читает команда COPY TO, требуется иметь право на выборку данных, а в таблице, куда вставляет значения COPY FROM, требуется право на добавление. При этом, если в команде перечисляются избранные столбцы, достаточно иметь права только для них
Выполнять COPY с указанием файла или внешней команды разрешено только суперпользователям базы данных или членам ролей pg_read_server_files, pg_write_server_files или pg_execute_server_program, так как это позволяет читать/записывать любые файлы и запускать любые программы, к которым имеет доступ сервер.
COPY прекращает операцию при первой ошибке. Это не должно приводить к проблемам в случае с COPY TO, но после COPY FROM в целевой таблице остаются ранее полученные строки. Эти строки не будут видимыми и доступными, но будут занимать место на диске. Если сбой происходит при копировании большого объёма данных, это может приводить к значительным потерям дискового пространства. При желании вернуть потерянный объём, это можно сделать с помощью команды VACUUM
Примеры
#Данные таблицы передаются клиенту с разделителем "|"
COPY pgbench_accounts TO STDOUT (DELIMITER '|');
megabase=# --Копирование существующей таблицы в файл
megabase=# COPY content_table TO '/var/lib/postgresql/content.copy';
COPY 235
megabase=#
megabase=#--Создание тестовой таблицы
megabase=# CREATE TABLE content_copy (
id_text integer NOT NULL,
tag_2 character varying(50) NOT NULL,
tag_3 character varying(150) NOT NULL,
name_content character varying(200) NOT NULL,
text_content text DEFAULT 'Данная статья еще не готова'::text,
date_update timestamp without time zone DEFAULT now(),
description text DEFAULT 'Неизвестно'::text NOT NULL,
tag_1 character varying(50) DEFAULT 'неизвестно'::character varying NOT NULL,
url_content character varying(100) DEFAULT 'not_url'::character varying NOT NULL,
tags_page character varying(100)
);
CREATE TABLE
megabase=#
megabase=# --Копирование данных из файла в таблицу
megabase=# COPY content_copy FROM '/var/lib/postgresql/content.copy' ;
COPY 235
megabase=#
megabase=#-- Копирование строк, которые соответствуют условию:
megabase=# COPY (SELECT * FROM content_table WHERE tag_2 = 'php') TO '/var/lib/postgresql/content.copy' ;
COPY 37
megabase=#
megabase=# --Для копирования данных в сжатый файл можно направить вывод через внешнюю программу сжатия:
megabase=# COPY content_table TO PROGRAM 'gzip > /var/lib/postgresql/content.copy.gz';
COPY 236
--Пустая строка и NULL — разные значения
COPY t TO STDOUT;
1 Привет!
2
3 \N
megabase=# -- Аналогично можно вводить данные:
megabase=# COPY content_copy 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.
>> 2000
>> Network
>> net
>> test
>> Name content
>> test
>> test
>> test
>> \.
Восстановление из резервной копии с помощью pg_restore
pg_restore — восстановить базу данных PostgreSQL из файла архива, созданного командой pg_dump ( не текстовых форматов )
pg_restore читает файл и преобразует его в обычные команды psql. Преимущество в том, что набор объектов можно ограничить не при создании резервной копии, а уже при восстановлении. Кроме того, создание резервной копии в специальном формате и восстановление из нее может выполняться параллельно
Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_14_backup_overview.html
Разработанный для файлов архива формат не привязан к архитектуре
Утилита pg_restore может работать в двух режимах:
Если указывается имя базы данных, pg_restore подключается к этой базе данных и восстанавливает содержимое архива непосредственно в неё. В противном случае создаётся SQL-скрипт с командами, необходимыми для пересоздания базы данных, который затем выводится в файл или в стандартное устройство вывода. Сформированный скрипт будет в точности соответствовать выводу pg_dump в простом текстовом формате
Источник: https://postgrespro.ru/docs/postgresql/16/app-pgrestore
Параметры
-a --data-only - Восстанавливать только данные, без схемы (определений данных). При этом восстанавливаются данные таблиц, большие объекты и значения последовательностей, имеющиеся в архиве
-c --clean - Прежде чем восстанавливать объекты базы данных, выполните команды DROP для всех восстанавливаемых объектов. Этот параметр полезен для перезаписи существующей базы данных. Без дополнительного указания --if-exists при этом могут выдаваться безвредные сообщения об ошибках, если таких объектов не окажется в целевой базе данных
-C --create Создать базу данных, прежде чем восстанавливать данные. Если также указан параметр --clean, удалить и пересоздать целевую базу данных перед подключением к ней
-d имя_бд --dbname=имя_бд - Подключиться к базе данных имя_базы и восстановить данные непосредственно в неё
-e --exit-on-error - Завершать работу в случае возникновения ошибки при выполнении команд SQL в базе данных. По умолчанию процесс восстановления продолжается, а по его окончании выдаётся число ошибок
-f имя_файла --file=имя_файла - Задаёт файл для вывода сгенерированного скрипта или списка объектов, получаемого с параметром -l. Чтобы выбрать stdout, используйте -
-I индекс --index=индекс - Восстановить определение только заданного индекса
-j число-заданий --jobs=число-заданий - Выполнять наиболее длительные этапы pg_restore параллельно, используя несколько заданий, это позволяет кардинально сократить время восстановления большой базы данных, когда сервер работает на многопроцессорном компьютере
Каждое задание выполняется в отдельном задании или потоке, в зависимости от операционной системы, и использует отдельное подключение к серверу
-l --list - Вывести оглавление архива. Вывод этой операции можно подать на вход этой же команде с параметром -L. Учтите, что когда вместе с -l применяются параметры фильтрации (например, -n или -t), они сокращают список выводимых объектов
-L файл-список --use-list=файл-список - Восстановить из архива только элементы, перечисленные в файле-списке, и в том порядке, в каком они идут в этом файле
-n схема --schema=схема - Восстановить только объекты в указанной схеме
-N схема --exclude-schema=схема - Не восстанавливать объекты в указанной схеме
-O --no-owner - Не генерировать команды, устанавливающие владение объектами, как в исходной базе данных
-s --schema-only - Восстановить только схему (определения данных), без данных, в объёме, в котором элементы схемы представлены в архиве
Больше информации в источнике
Источник: https://postgrespro.ru/docs/postgresql/16/app-pgrestore
Примеры
#Предположим, что мы выгрузили базу данных mydb в файл специального формата:
$ pg_dump -Fc mydb > db.dump
#Мы можем удалить базу данных и восстановить её из копии:
$ dropdb mydb
$ pg_restore -C -d postgres db.dump
#Параметр -C не используется, вместо этого подключаемся непосредственно к базе, в которую хотим восстановить данные
#База данных из шаблона template0, а не template1, чтобы изначально она была гарантированно пустой
$ createdb -T template0 newdb
$ pg_restore -d newdb db.dump
#Чтобы переупорядочить элементы базы данных, сначала необходимо получить оглавление архива
pg_restore -l db.dump > db.list
Файл оглавления содержит заголовок и по одной строке для каждого элемента
С точки с запятой начинаются комментарии, а число в начале строки обозначает внутренний идентификатор, назначаемый каждому элементу в архиве
Источник: https://postgrespro.ru/docs/postgresql/16/app-pgrestore
Примечания
Базу данных для восстановления надо создавать из шаблона template0, так как все изменения, сделанные в template1, также попадутв резервную копию. Кроме того, заранее должны быть созданы необходимые роли и табличные пространства, поскольку эти объекты относятся ко всему кластеру. После восстановления базы имеет смысл выполнить команду ANALYZE, которая соберет статистику
#!/bin/bash
#Резервная копия данных
mkdir /home/toly/backup
#дамп баз данных
sudo -u postgres pg_dump -F c -f /tmp/db_name.dmp db_name
cp /tmp/db_name.dmp ./backup
rm -rf /tmp/db_name.dmp
#На всякий случай можно добавить файл контрольных сумм
md5sum ./backup/* > ./backup/mk5sum.txt
#*****************************************************************************
#Перенос дампа базы с удаленного сервера
rsyns -e ‘ssh -p 22’ -Pzavr root@95.165.5.82:backup ./
#проверяем контрольные суммы
md5sum -c ./backup/md5sum.txt
#восстановление базы данных
service postgresql stop
service postgresql start
#При необходимости удалить старую базу данных
sudo -u postgres psql -Atc "DROP DATABASE db_name" postgres
#Проверим что базы данных больше нет
sudo -u postgres psql -c "\l" postgres
#После чего заново создать роль и базу данных
sudo -u postgres psql -Atc "CREATE ROLE role_name" postgres
sudo -u postgres psql -Atc "CREATE DATABASE db_name OWNER role_name" postgres
#Теперь у нас есть пустая база данных и можно импортировать данные
sudo -u postgres pg_restore -d db_name /root/backup/db_name.dmp
Если в шаблон template1 вносились какие-либо изменения, они также попадут в резервную копию. Поэтому при восстановлении базы данных имеет смысл предварительно создать ее из шаблона template0 (указанный ключ --create добавляет нужные команды автоматически)
pg_dump -d backup_overview --create
#Скопировать таблицу в другую базу
pg_dump -d backup_overview --table=t | psql -d backup_overview2
pg_dumpall
pg_dumpall по очереди подключается к каждой БД кластера и выгружает информацию, кроме того она выгружает данные всего кластера, роли и табличные пространства.
pg_dumpall не поддерживает параллельную выгрузку данных, что может оказаться проблемой при больших объемах данных. В таком случае можно воспользоваться ключом --globals-only, чтобы выгрузить только ролии табличные пространства, а сами базы данных выгрузить с помощью утилиты pg_dump
Параметры
-a --data-only - Выгружать только данные, без схемы
-c --clean - Выводить SQL-команды для DROP всех выгруженных баз данных, ролей и табличных пространств перед их воссозданием. Этот параметр полезен, когда при восстановлении необходимо перезаписать существующий кластер
-E кодировка --encoding=кодировка - Создать копию в заданной кодировке
-f имя_файла --file=имя_файла - Направить вывод в указанный файл
-g --globals-only - Выгружать только глобальные объекты (роли и табличные пространства), без баз данных
-r --roles-only Выгружать только роли, без баз данных и табличных пространств
-t --tablespaces-only - Выгружать только табличные пространства, без баз данных и ролей
--column-inserts --attribute-inserts - Выгружать данные в виде команд INSERT с явно задаваемыми именами столбцов (INSERT INTO таблица (столбец, ...) VALUES ...). При этом восстановление будет очень медленным; в основном это применяется для выгрузки данных, которые затем будут загружаться не в PostgreSQL
--disable-triggers - Этот параметр действует только при выгрузке одних данных. С ним pg_dumpall добавляет команды, отключающие триггеры в целевых таблицах на время загрузки данных
--inserts - Выгружать данные в виде команд INSERT, а не COPY
Источник: https://postgrespro.ru/docs/postgresql/16/app-pg-dumpall
Примечания к команде pg_dumpall
Так как pg_dumpall внутри себя вызывает pg_dump, часть диагностических сообщений будет относиться к pg_dump
Ключ --clean может быть полезен, даже если вы намереваетесь восстановить копию из скрипта в новом кластере. С --clean скрипт сможет удалить и пересоздать встроенные базы данных postgres и template1, так что они получат свойства, которые имели одноимённые базы в исходном кластере (например, локаль и кодировку). Без данного ключа эти базы сохранят свои свойства уровня базы данных, а также всё существующее содержимое
После восстановления имеет смысл запустить ANALYZE для каждой базы данных, чтобы оптимизатор получил актуальную статистику. Также можно запустить анализ для всех баз данных, выполнив команду vacuumdb -a -z
С ключом --clean весьма вероятны другие незначительные сообщения об ошибках, связанные с несуществующими объектами; их число можно минимизировать, добавив ключ --if-exists
Источник: https://postgrespro.ru/docs/postgresql/16/app-pg-dumpall
Физическая копия
Физическое резервирование использует механизм восстановления после сбоев. Для этого потребуется копия файлов кластера и набор журналов предзаписи для восстановления согл
асованности
Если файловая система уже согласована (копия снималась при корректно остановленном сервере), то журналы не требуются
Наличие архива журналов позволяет получить из базовой резервной копии состояние кластера на любой момент времени
Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_14_backup_overview.html
#Создать архив
tar -cf backup.tar /usr/local/pgsql/data
Ограничения при использовании физической копии
Чтобы полученная резервная копия была годной, сервер баз данных должен быть остановлен как и перед восстановления из такой резервной копии
Нельзя скопировать или восстановить только отдельные таблицы или базы данных в соответствующих файлах или каталогах. Это не будет работать, потому что информацию, содержащуюся в этих файлах, нельзя использовать без файлов журналов транзакций, pg_xact/*, которые содержат состояние всех транзакций. Без этих данных файлы таблиц непригодны к использованию. Разумеется также невозможно восстановить только одну таблицу и соответствующие данные pg_xact, потому что в результате нерабочими станут все другие таблицы в кластере баз данных. Таким образом, копирование на уровне файловой системы будет работать, только если выполняется полное копирование и восстановление всего кластера баз данных.
Ещё один вариант — копировать содержимое файловой системы с помощью rsync. Для этого rsync запускается сначала во время работы сервера баз данных, а затем сервер останавливается на время, достаточное для запуска rsync --checksum. (Ключ --checksum необходим, потому что rsync различает время только с точностью до секунд.) Во второй раз rsync отработает быстрее, чем в первый, потому что скопировать надо будет относительно немного данных; и в итоге будет получен согласованный результат, так как сервер был остановлен. Данный метод позволяет получить копию на уровне файловой системы с минимальным временем простоя
Источник: https://postgrespro.ru/docs/postgresql/16/backup-file
Источники
Здесь пока нет комментариев