Резервное копирование СУБД PostgreSQL

Особенности обслуживания СУБД PostgreSQL

  • Большие объекты (large objects) хранятся в отдельной таблице pg_largeobjects. Примером больших объектов являются прикрепленные файлы (если они не вынесены в файловое хранилище), а также письма, полученные в результате процесса обработки почты. В таблицах базы приложения хранятся только ссылки на эти объекты. При удалении файла в интерфейсе, в базе удаляется только ссылка, а сам объект остается в таблице pg_largeobjects. Это связано с особенностями работы Postgresql. В результате описанного поведения база данных может разрастись за счет уже удаленных больших объектов.

    Чтобы этого избежать, существует утилита vacuumlo, включенная в пакет postgresql-contrib. Утилита удаляет из pg_largeobjects объекты, на которые нет ссылок в других таблицах, что позволяет очистить место. Для регулярной очистки больших объектов поместите в cron команду $path_to_u/vacuumlo $database, где $database — имя базы данных, $path_to_u — полный путь до vacuumlo.

  • UPDATE или DELETE не удаляют старую версию столбца немедленно (потому что она может быть еще видна другим транзакциям). Место от удаленных столбцов будет свободным только после специальной операции СУБД: “vacuum”. Вызов VACUUM помечает место свободным для нового использования. Обычно место не возвращается в ОС, за исключением случая, когда удаляются страницы в конце таблицы. Кроме того vacuum отвечает за ряд других важных аспектов работы СУБД, поэтому он должен выполняться регулярно. Существует два способа регулярно запускать VACUUM: запуск по расписанию и использование autovacuum. Предпочтительней использовать второй способ, поэтому в Postgresql демон autovacuum включен по умолчанию. Отключайте его только в случае, если уверены в такой необходимости.
  • При настроенном архивировании WAL-логов (по умолчанию выключено) нужно внимательно следить за доступностью хранилища, куда отправляются файлы. Если команда архивирования WAL будет возвращать ошибку, то СУБД не будет удалять файлы из директории pg_xlog, оставляя их для последующей архивации. Это может привести к заполнению раздела, на котором расположен pg_xlog, и аварийной остановке СУБД.

Резервное копирование PostgreSQL для ОС Linux

Команды выполняются на сервере СУБД в командной строке. Пользователь в вашей системе может быть другим.

Создание резервной копии

Команда для создания резервной копии базы данных:

# su - postgres
pg_dump --blobs --verbose --format "custom" --username "nausd4" --file "/opt/backup/nausd4.bak" nausd4

где,

  • --username "nausd4" — имя пользователя для входа в СУБД. Пользователь в вашей системе может быть другим;
  • --file "/opt/backup/nausd4.bak"— расположение и название файла резервной копии (расположение должно существовать);
  • nausd4 — имя базы данных, резервную копию которой необходимо создать.

Удаление существующей базы данных

Команда для удаления существующей базы данных:

# su - postgres
dropdb  nausd4

где nausd4 — имя базы данных, которую необходимо удалить.

Восстановление резервной копии

Перед восстановлением базы данных из резервной копии необходимо: остановить приложение, удалить существующую базы данных и создать новую (см. Особенности настройки СУБД и создания базы данных.

Команда для восстановления резервной копии базы данных:

# su - postgres
pg_restore -h 127.0.0.1 -v -O -x --username nausd4 --dbname nausd4 /opt/backup/nausd4.bak

где:

  • --username "nausd4" — имя пользователя для входа в СУБД. Пользователь в вашей системе может быть другим;
  • --dbname nausd4 — имя базы данных для восстановления;
  • /opt/backup/nausd4.bak — расположение и название файла резервной копии.

При стандартных настройках Postgresql во время восстановления будет запрошен пароль пользователя nausd4.