Как создать резервную копию PostgreSQL и восстановиться из неё?
Резервное копирование является одним из главных способов защиты информации. В этой статье мы расскажем о том, какие его виды существуют в PostgreSQL и как они выполняются.
Что такое резервная копия PostgreSQL?
Резервная копия в PostgreSQL представляет собой копию данных базы, которая хранится отдельно и служит для восстановления в случае сбоев. Есть два типа резервного копирования: логическое и физическое.
- Логическое резервирует SQL-команды, после выполнения которых можно восстанавливать объекты: например, пересоздать БД, затем поместить в нее таблицы, в таблицы — данные и т. д.
- Физическое же резервирует непосредственно данные путем сохранения каталога PGDATA: файлов данных и журналы предварительной записи.
Рассмотрим плюсы, минусы и способы каждого из этих типов резервирования.
Логическое резервирование
Сильными сторонами логического резервирования считается возможность сохранять не только базы, но и отдельные таблицы, а также — возможность загружать логические копии в более новые версии PostgreSQL. К минусам обычно относят большие требования по времени и ресурсам.
Существует следующие инструменты для логического копирования:
- COPY: SQL-команда, посредством которой данные из таблиц копируются в файл и наоборот. Т.е. с помощью COPY данные можно перенести в файл, затем командой CREATE создать таблицу и перенести данные из файла в нее.
- pg_dump: утилита PostgreSQL, позволяющая копировать всю БД. С помощью COPY данные здесь выгружаются в текстовом формате, а восстанавливаются — как PSQL. Использование утилиты pg_restore позволяет загружать дампы, созданные в другом формате (из tar.gz — командой pg_restore -Ft users.tar, из двоичных файлов - pg_restore -Fc users.bak).
- pg_dumpall: утилита PostgreSQL, позволяющая копировать весь кластер; выгрузка осуществляется только в текстовом формате, а восстановление — в PSQL. Пример команды: pg_dumpall > cluster.bak. Дополнительные опции (ключ -g (--globals-only)) позволяют при необходимости выгружать табличные пространства, роли и т. д.
Физическое резервирование
Сильными сторонами физического резервирования принято считать скорость восстановления и возможность восстановить состояние кластера на любой момент времени от создания бэкапа. К минусам относят невозможность восстанавливать отдельные БД (только кластеры целиком), а также невозможность загружать копии в другие версии PostgreSQL.
Принято выделять два вида физического резервирования:
- Холодное (сервер отключен). Если отключение штатное — данные просто копируются на другой сервер. Если отключение произошло в результате сбоя — вместе с данными нужно скопировать журналы предварительной записи с момента последней контрольной точки.
- Горячее (сервер включен). Осуществляется с помощью утилиты pg_basebackup. Такое резервирование требует всех файлов предварительной записи с начала копирования и до его завершения. Пример команды: pg_basebackup -D /backup
Выбор между логическим и физическим резервным копированием зависит от конкретной ситуации и требований к восстановлению данных. Часто на практике рекомендуется комбинировать оба вида резервного копирования для обеспечения полной защиты данных и возможности быстрого восстановления в случае необходимости.
Как сделать резервную копию БД PostgreSQL?
Резервирование данных является одним из ключевых аспектов администрирования любой базы данных. Ниже мы рассмотрим основные команды для упомянутых выше утилит, с помощью которых в PostgreSQL создаются резервные копии.
При этом отметим, что гарантией сохранности данных при физической поломке сервера, взлома БД или ошибке пользователя может выступать только помощь профессионалов: например инженеров «ДБ-сервис», специализирующихся на работе с PostgreSQL.
- Синтаксис базовой команды создания резервной копии выглядит следующим образом: pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>.
- Если нужно сделать бэкап конкретной таблицы, используется опция -t <table> или —table=<table>: pg_dump -t students users > /tmp/students.dump
- При нахождении таблицы в определенной схема, команда выглядит как pg_dump -t public.students users > /tmp/students.dump
- В том случае, если нужно сделать только копию данных, используется pg_dump --data-only users > /tmp/users.data.dump
- Для создания резервной копии не из учетки PostgreSQL, в базовую команду добавляется опции -U (имя пользователя) и -W (пользовательский пароль).
- Чтобы дамп базы не занимал слишком много места на сервере и его было быстрее переслать по сети, можно прибегнуть к сжатию, которое осуществляется с помощью команды pg_dump users | gzip > users.dump.gz.
- Если БД находятся на удаленном сервере, нужно удостовериться, что PostgreSQL позволяет подключаться удаленно, после чего добавить опцию -h: pg_dump -h 192.168.0.15 users > /tmp/users.dump
Эти команды и опции помогут вам эффективно создавать и управлять резервными копиями данных в PostgreSQL в зависимости от ваших потребностей и условий среды.
Как восстановить бэкап в PostgreSQL
Если резервная копия была создана правильно, восстановить из нее БД не составит труда. Для этого нужно лишь использовать следующие команды.
- Базовая команда выглядит как psql <имя базы> < <файл с дампом>.
- Для восстановления резервной копии не из учетки PostgreSQL, в базовую команду добавляется опции -U (имя пользователя) и -W (пользовательский пароль).
- Если дамп базы находится в архиве .gz, он сначала распаковывается, а потом запускается восстановление: zcat users.dump.gz | psql users
- Конкретная база, если делалась только ее копия, восстанавливается командой psql users < /tmp/database.dump
- При полном бэкапе, восстановление конкретной базы осуществляется в pg_restore с параметром -d: pg_restore -d users cluster.bak
- Конкретная таблица, если делалась только ее копия, восстанавливается командой psql users < /tmp/students.dump
- При полном бэкапе, восстановление конкретной таблицы осуществляется в pg_restore с параметром -t: pg_restore -a -t students users.dump
Эксперт ДБ-сервис
Список литературы:
- Резервирование PostgreSQL. – Текст: электронный // Sysadminium: интернет-портал. – URL: Резервирование PostgreSQL - Sysadminium – Дата публикации: 18.06.2021.
- Резервное копирование PostgreSQL. – Текст: электронный // Дмитрий Моск. – URL: Резервное копирование PostgreSQL. PG_DUMP. Резервирование и восстановление данных (dmosk.ru) – Дата обновления: 09.12.2023
- Reliable PostgreSQL Backup & Restore. – Текст: электронный // The PostgreSQL Global Development Group. – URL: pgBackRest - Reliable PostgreSQL Backup & Restore – Дата обновления: 27.11.2023.