Главная » Linux

Бд postgresql архивация linux

Содержание
  1. Резервное копирование PostgreSQL
  2. Создание резервных копий
  3. Базовая команда
  4. Пользователь и пароль
  5. Сжатие данных
  6. Скрипт для автоматического резервного копирования
  7. На удаленном сервере
  8. Дамп определенной таблицы
  9. Размещение каждой таблицы в отдельный файл
  10. Только схемы
  11. Только данные
  12. Использование pgAdmin
  13. Не текстовые форматы дампа
  14. Резервное копирование PostgreSQL на Linux
  15. Резервное копирование PostgreSQL на Linux
  16. Создание резервных копий
  17. Базовая команда
  18. Пользователь и пароль
  19. Сжатие данных
  20. Скрипт для автоматического резервного копирования
  21. На удаленном сервере
  22. Дамп определенной таблицы
  23. Размещение каждой таблицы в отдельный файл
  24. Только схемы
  25. Только данные
  26. Использование pgAdmin
  27. Не текстовые форматы дампа
  28. Использование pg_basebackup
  29. pg_dumpall
  30. Восстановление
  31. Базовая команда
  32. No matching tables were found
  33. Too many command-line arguments
  34. Aborting because of server version mismatch
  35. No password supplied
  36. Автоматический бэкап PostgreSQL в Linux
  37. Автоматический бэкап PostgreSQL в Linux: 6 комментариев

Резервное копирование PostgreSQL

В данной инструкции рассмотрены варианты создания резервных копий и восстановления баз СУБД PostgreSQL.

Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.

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

Базовая команда

pg_dump users > /tmp/users.dump

Пользователь и пароль

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.

Для начала, создадим каталог, в котором разместим скрипт, например:

Вариант 1. Запуск от пользователя root; одна база.

PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

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

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Вариант 2. Запуск от пользователя postgres; все базы.

find $pathB \( -name «*-1[^5].*» -o -name «*-[023]?.*» \) -ctime +61 -delete

for dbname in `echo «SELECT datname FROM pg_database;» | psql | tail -n +3 | head -n -2 | egrep -v ‘template0|template1|postgres’`; do
pg_dump $dbname | gzip > $pathB/$dbname-$(date «+%Y-%m-%d»).sql.gz
done;

* где /backup — каталог, в котором будут храниться резервные копии; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.

Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, /backup/postgres.

Читайте также:  Графический стандарт x windows

Зададим в качестве владельца файла, пользователя postgres:

chown postgres:postgres /scripts/postgresql_dump.sh

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

crontab -e -u postgres

* мы откроем на редактирование cron для пользователя postgres.

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Права и запуск

Разрешаем запуск скрипта, как исполняемого файла:

chmod +x /scripts/postgresql_dump.sh

Единоразово можно запустить задание на выполнение резервной копии:

. или от пользователя postgres:

su — postgres -c «/scripts/postgresql_dump.sh»

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

Дамп определенной таблицы

Запускается с опцией -t или —table= :

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Только схемы

Для резервного копирования без данных (только таблицы и их структуры):

pg_dump —schema-only users > /tmp/users.schema.dump

Только данные

pg_dump —data-only users > /tmp/users.data.dump

Использование pgAdmin

Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.

Запускаем pgAdmin — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:

В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:

При желании, можно изучить дополнительные параметры для резервного копирования:

После нажимаем Резервная копия — ждем окончания процесса и кликаем по Завершено.

Не текстовые форматы дампа

Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные.

Источник

Резервное копирование PostgreSQL на Linux

Резервное копирование PostgreSQL на Linux

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

Пример скрипта дампа всех баз postgresSQL

#/bin/sh

START_TIME=`date +%s`


BACKUP_DIR=»/usr/local/backups»
TIMESTAMP=$(date «+%d-%m-%Y_%H-%M-%S»)
DAYS_TO_STORE=7




echo «Starting SQL backup script»
echo «\t PID: $$»
echo «\t Start time: $TIMESTAMP\n»




echo -n «Starting pg_dumpall. «
/usr/bin/pg_dumpall | gzip > $BACKUP_DIR/pg_dumpall_$TIMESTAMP.sql.gz
echo «ok»


echo -n «Computing MD5 hash. «
/usr/bin/md5sum $BACKUP_DIR/pg_dumpall_$TIMESTAMP.sql.gz >> $BACKUP_DIR/md5sums.txt
echo «ok\n»




echo «Deleting backup older than $DAYS_TO_STORE days. «
for i in $(find /usr/local/backups -type f -mtime +14 -name «*.sql.gz» -print); do
echo -n «\tdeleting file $i . «
rm $i
echo «ok»
done


END_TIME=`date +%s`
echo «\n\nScript ended in `expr $END_TIME — $START_TIME` sec.»




# Docs:
# https://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html

Базовая команда

Пример (для дампа одной бд зайдите под su postgres):

pg_dump bd > /tmp/bd.dump

Пользователь и пароль

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

Читайте также:  Microsoft windows недостаточно памяти что делать

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

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

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL

Дамп определенной таблицы

Запускается с опцией -t или —table= :

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Только схемы

Для резервного копирования без данных (только таблицы и их структуры):

pg_dump —schema-only users > /tmp/users.schema.dump

Только данные

pg_dump —data-only users > /tmp/users.data.dump

Использование pgAdmin

Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.

Запускаем pgAdmin — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:

В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:

При желании, можно изучить дополнительные параметры для резервного копирования:

После нажимаем Резервная копия — ждем окончания процесса и кликаем по Завершено.

Не текстовые форматы дампа

Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные.

Бинарный с компрессией:

pg_dump -Fc users > users.bak

pg_dump -Ft users > users.tar

pg_dump -Fd users > users.dir

Использование pg_basebackup

pg_basebackup позволяет создать резервную копию для кластера PostgreSQL.

pg_basebackup -h node1 -D /backup

* в данном примере создается резервная копия для сервера node1 с сохранением в каталог /backup.

pg_dumpall

Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта.

Утилиту удобно использовать с ключом -g (—globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).

Восстановление

Может понадобиться создать базу данных. Это можно сделать SQL-запросом:

=# CREATE DATABASE users WITH ENCODING=’UTF-8′;

* где users — имя базы; UTF-8 — используемая кодировка.

Базовая команда

psql users или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор.

No matching tables were found

Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.

Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.

Too many command-line arguments

Причина: Утилита pg_dump чувствительна к лишним пробелам.

Читайте также:  Huawei matebook windows hello

Решение: проверьте, что нет лишних пробелов.

Aborting because of server version mismatch

Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.

Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql/ /bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.

No password supplied

Причина: нет системной переменной PGPASSWORD или она пустая.

Источник

Автоматический бэкап PostgreSQL в Linux

О том как делать автоматические резервные копии в PostgreSQL рассказано в статье Automated Backup on Linux. Скрипты из этой статьи всем хороши, кроме того, что если их прописать в cron, то ничего бэкапиться не будет. А происходит так из-за того, что нигде не указывается пароль пользователя PostgreSQL от имени которого делается бэкап.

Для того чтобы эти скрипты заработали в автоматическом режиме нужно в самый конец pg_backup.config добавить:
# Set PGUSER and PGPASSWORD
PGUSER=»postgres»
PGPASSWORD=»password»
export PGUSER PGPASSWORD
# End

Естественно, вместо пользователя postgres можно использовать любого нужного пользователя, а вместо password нужно указать пароль этого пользователя.

А в целях безопасности в самом конце pg_backup.sh нужно вставить:
# Unset PGUSER and PGPASSWORD
PGUSER=»»
PGPASSWORD=»»
export PGUSER PGPASSWORD
# End

Теперь можно сделать файлы исполняемыми:
chmod u+x pg_backup.sh pg_backup_rotated.sh

(для проверки работоспособности нужно выполнить bash pg_backup.sh)

Прописать их в cron пользователя postgres (на примере Ubuntu):
sudo crontab -u postgres -e

В файл нужно добавить строки:
SHELL=/bin/bash
15 3 * * * /path/to/script/pg_backup.sh > /dev/null 2>&1

Надо напоминать, что /path/to/script/ — это путь к директории с файлом?

Теперь ждём 03:15 утра и проверяем работоспособность.

Автоматический бэкап PostgreSQL в Linux: 6 комментариев

А почему бы не сделать по-элегантнее, без «засветки» пароля, например так:
1. Определим user map для root. Для этого добавим в файл $PGDATA/pg_ident.conf строчку :
root root postgres
2. Добавим этот user map в соответствующую запись в файле $PGDATA/pg_hba.conf (приписать опцию map=root в соответсвующую строку идентификации), например:
local all postgres peer map=root
3. Рестарт Postgres:
service postgresql-9.3 restart
После этого, все клиенты (тот же psql ), запускаемые под акком root , будут коннектиться к PostgresQL как postgres . Все должно запахать, и нигде никаких паролей указывать не надо.
Не нравится вам root – тоже самое можно проделать для любого системного акка, под которым вы хотите запускать бэкап скрипт.

Роман, спасибо за красивое решение! Постараюсь опробовать его и отписаться о том что получилось.

А не сделал я так из-за того, что я по роду деятельности не системный администратор и многого ещё не знаю — я только учусь (с) 🙂

Ребят, подскажите пожалуйста, как настроить все то же самое, только с выгрузкой backup-а на сторонний FTP сервер, а не локально на машину.

Или проще выгрузить dump локально и через отдельный скрипт загружать его на FTP?

Rolex, я вижу несколько вариантов:

  1. Взять скрипты из пакета backup-manager ( apt-get install backup-manager ) и либо позаимствовать оттуда код отправки файлов на FTP, либо использовать скритпы из пакета для этой цели, только учти, что пакет уже давно не поддерживается, однако, стабильно работает.
  2. Использовать вместо FTP SSH, что проще ( нужно добавить в существующий скрипт только одну строчку scp) и надёжнее.

Не думаю, что для кого-то открыл америку, но вот еще один вариант для создания бэкапов. Через утилиту barman

Источник

Оцените статью
© 2024 Советы по настройке компьютера