Postgresql backup database linux

Для резервного копирования баз данных PostgreSQL в Linux используйте утилиты pg_dump, pg_dumpall и pg_basebackup. Ниже — подробные инструкции.

1. pg_dump — резервное копирование отдельной базы

Базовый синтаксис:

pg_dump <опции> <имя_базы> > <путь_к_файлу>

Примеры:

• Простая выгрузка в текстовый файл:pg_dump mydb > /backups/mydb.sql
  
• С указанием пользователя и запросом пароля:pg_dump -U postgres -W mydb > /backups/mydb.sql
  
• Со сжатием через gzip:pg_dump mydb | gzip > /backups/mydb.sql.gz
  
• С компрессией (уровень 9) и указанием файла:pg_dump -Z9 mydb -f /backups/mydb.bak
  
• Выгрузка только схемы (без данных):pg_dump --schema-only mydb > /backups/mydb_schema.sql
  
• Выгрузка только данных (без схемы):pg_dump --data-only mydb > /backups/mydb_data.sql
  

Форматы дампа (опция -F):

• p (plain) — текстовый SQL‑скрипт (по умолчанию);
• c (custom) — бинарный сжатый формат;
• d (directory) — каталог с отдельными файлами для каждой таблицы;
• t (tar) — архив в формате tar.

Пример бинарного дампа:

pg_dump -Fc mydb -f /backups/mydb.backup

2. pg_dumpall — резервное копирование всего кластера

Создаёт дамп всех баз данных, ролей и табличных пространств.

Синтаксис:

pg_dumpall <опции> > <путь_к_файлу>

Примеры:

• Полный дамп кластера:pg_dumpall -U postgres > /backups/cluster.sql
  
• Только глобальные объекты (роли, табличные пространства):pg_dumpall --globals-only -U postgres > /backups/globals.sql
  
• Со сжатием:pg_dumpall | gzip > /backups/cluster.tar.gz
  

3. pg_basebackup — бинарное резервное копирование кластера

Создаёт полную копию файлов кластера (включая данные и конфигурацию). Нельзя выгрузить отдельную базу — только весь кластер.

Синтаксис:

pg_basebackup <опции> -D <каталог_назначения>

Примеры:

• Базовая копия локального кластера:pg_basebackup -D /backups/pg_data
  
• Сжатая копия в формате tar:pg_basebackup -Ft -z -D /backups/pg_data.tar.gz
  
• Подключение к удалённому серверу:pg_basebackup -h remote_host -U backup_user -D /backups/remote_data
  

Требования:

• Пользователь должен иметь право REPLICATION (настройка в pg_hba.conf).
• Достаточно свободного места на диске для полной копии.

4. Автоматизация резервного копирования (скрипт)

Пример скрипта для ежедневного бэкапа одной базы (/scripts/postgresql_dump.sh):

#!/bin/sh
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
PGPASSWORD=your_password
export PGPASSWORD

pathB=/backups
dbUser=backup_user
database=mydb

# Удаление старых копий (старше 61 дня)
find $pathB \( -name "*-1[^5].*" -o -name "*-[023]?.*" \) -ctime +61 -delete


# Создание сжатого дампа с датой в имени
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date "+%Y-%m-%d").sql.gz

unset PGPASSWORD

Запуск по расписанию (через cron):

crontab -e -u postgres

Добавьте строку для ежедневного запуска в 03:00:

0 3 * * * /scripts/postgresql_dump.sh

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

Из текстового дампа (pg_dump, pg_dumpall):

1. Создайте базу (если её нет):psql -U postgres -c "CREATE DATABASE mydb;"
   
2. Восстановите данные:psql -U postgres mydb < /backups/mydb.sql
   

Из бинарного дампа (pg_dump -Fc):

pg_restore -U postgres -d mydb /backups/mydb.backup

Из копии pg_basebackup:

1. Остановите PostgreSQL:sudo systemctl stop postgresql
   
2. Замените каталог данных на восстановленный:rm -rf /var/lib/postgresql/14/main
   cp -r /backups/pg_data /var/lib/postgresql/14/main
   
3. Запустите PostgreSQL:sudo systemctl start postgresql
   

Важные рекомендации

1. Права доступа: убедитесь, что у пользователя есть права на запись в каталог бэкапов.
2. Хранение паролей: используйте файл .pgpass (права 600) вместо передачи пароля в командной строке. Пример:echo "localhost:5432:mydb:backup_user:your_password" > ~/.pgpass
   chmod 600 ~/.pgpass
   
3. Проверка целостности: после создания бэкапа проверьте его размер и содержимое (для текстовых дампов).
4. Тестирование восстановления: регулярно проверяйте возможность восстановления из бэкапов.
5. Шифрование: для чувствительных данных используйте шифрование (например, gpg):pg_dump mydb | gzip | gpg --encrypt --recipient your_email@example.com > /backups/mydb.gpg