Резервное копирование баз данных Postgresql в Linux

У нас в ресурсе уже описано много способов реализации резервного копирования информации. Продолжим данную тему. Теперь, рассмотрим резервное копирование баз данных Postgresql в операционной системе Linux.

Вот вариант резервного копирования для 3-х баз данных:

dump_path="/fw/backup/1cbases"

# Устанавливаем разделитель для элементов массива, предварительно резервируя системный:
oldIFS==$IFS
IFS=";"

# Названия баз данных, которые будем сохранять, перечисленные через разделитель, заданный выше
dump_bases="BASE1;BASE2;BASE2"

# Срок хранения резервных копий, дней:
dump_keepdays="7"


PGPASSWORD=ВАШ_ПАРОЛЬ
export PGPASSWORD


for dump_base in $dump_bases; do

# Создаём дамп от имени пользователя postgres, в сжатый файл с именем вида %dbname%-yyyy-mm-dd.sql.gz
pg_dump -h 192.168.77.75 $dump_base --username=postgres | gzip > $dump_path/$dump_base-`date \+\%Y-\%m-\%d`.sql.gz

find $dump_path/$dump_base*.sql.gz -mtime +$dump_keepdays -exec /bin/rm '{}' \;

done

PGPASSWORD=
export PGPASSWORD

# Восстанавливаем стандартный (системный) разделитель списков
IFS=$oldIFS

exit 0

dump_path="/fw/backup/1cbases"

# Устанавливаем разделитель для элементов массива, предварительно резервируя системный:

oldIFS==$IFS

IFS=";"

# Названия баз данных, которые будем сохранять, перечисленные через разделитель, заданный выше

dump_bases="BASE1;BASE2;BASE2"

# Срок хранения резервных копий, дней:

dump_keepdays="7"

PGPASSWORD=ВАШ_ПАРОЛЬ

export PGPASSWORD

for dump_base in $dump_bases; do

# Создаём дамп от имени пользователя postgres, в сжатый файл с именем вида %dbname%-yyyy-mm-dd.sql.gz

pg_dump -h 192.168.77.75 $dump_base --username=postgres | gzip > $dump_path/$dump_base-`date \+\%Y-\%m-\%d`.sql.gz

find $dump_path/$dump_base*.sql.gz -mtime +$dump_keepdays -exec /bin/rm '{}' \;

done

PGPASSWORD=

export PGPASSWORD

# Восстанавливаем стандартный (системный) разделитель списков

IFS=$oldIFS

exit 0

Сохранение бекапа на удаленном сервере по ssh

В условиях отсутствия локального или сетевого тома для хранения резервных копий (несколько натянутое условие, но мне пришлось столкнуться), пришлось доработать скрипт; отличие от изначального скрипта – результат дампа не сохраняется локально, а передаётся на удалённый сервер по ssh и там же производится удаление устаревших файлов:

#!/bin/sh
# Для инструкций по восстановлению см. postgresql.org/docs/8.1/static/backup.html
# Путь на сервере резервирования, куда будем складывать резервные копии
dump_path="/mnt/backup/pgsql"
# Устанавливаем разделитель для элементов массива, предварительно резервируя системный:
oldIFS==$IFS
IFS=";"
# Названия баз данных, которые будем сохранять, перечисленные через разделитель, заданный выше
dump_bases="office_upp;buh_retail;zup"
# Срок хранения резервных копий, дней:
dump_keepdays="7"
# Создаём информационный файл в каталоге назначения ("для будущих поколений")
ssh -i /root/nbs01/backup backup@192.168.122.1 \
 "echo `date \+\%F\ \%T` Dumps of databases. For restoring see postgresql.org/docs/9.3/static/backup.html > $dump_path/readme.txt"
for dump_base in $dump_bases; do
# Создаём дамп от имени пользователя postgres, передавая текстовый дамп по ssh на удалённый сервер,
# сжимая там результат gzip'ом и сохраняя в файл с именем вида %dbname%-yyyy-mm-dd-hh-mm-ss.pgdump.gz
  sudo -u postgres pg_dump -Fc $dump_base | ssh -i /root/nbs01/backup backup@192.168.122.1 \
                                        "gzip > $dump_path/$dump_base-`date \+\%Y-\%m-\%d-\%H-\%M-\%S`.pgdump.gz"
  # Ищем в каталоге резервных копий все файлы с именами похожими на бэкап текущей БД и старше срока хранения и удаляем
  ssh -i /root/nbs01/backup backup@192.168.122.1 \
      "find $dump_path/$dump_base* -mtime +$dump_keepdays -exec /bin/rm '{}' \;"
done
ssh -i /root/nbs01/backup backup@192.168.122.1 \
 "echo `date \+\%F\ \%T` All data keeps $dump_keepdays days, older files will be removed automatically. >> $dump_path/readme.txt"
# Восстанавливаем стандартный (системный) разделитель списков
IFS=$oldIFS

#!/bin/sh

# Для инструкций по восстановлению см. postgresql.org/docs/8.1/static/backup.html

# Путь на сервере резервирования, куда будем складывать резервные копии

dump_path="/mnt/backup/pgsql"

# Устанавливаем разделитель для элементов массива, предварительно резервируя системный:

oldIFS==$IFS

IFS=";"

# Названия баз данных, которые будем сохранять, перечисленные через разделитель, заданный выше

dump_bases="office_upp;buh_retail;zup"

# Срок хранения резервных копий, дней:

dump_keepdays="7"

# Создаём информационный файл в каталоге назначения ("для будущих поколений")

ssh -i /root/nbs01/backup backup@192.168.122.1 \

"echo `date \+\%F\ \%T` Dumps of databases. For restoring see postgresql.org/docs/9.3/static/backup.html > $dump_path/readme.txt"

for dump_base in $dump_bases; do

# Создаём дамп от имени пользователя postgres, передавая текстовый дамп по ssh на удалённый сервер,

# сжимая там результат gzip'ом и сохраняя в файл с именем вида %dbname%-yyyy-mm-dd-hh-mm-ss.pgdump.gz

sudo -u postgres pg_dump -Fc $dump_base | ssh -i /root/nbs01/backup backup@192.168.122.1 \

"gzip > $dump_path/$dump_base-`date \+\%Y-\%m-\%d-\%H-\%M-\%S`.pgdump.gz"

# Ищем в каталоге резервных копий все файлы с именами похожими на бэкап текущей БД и старше срока хранения и удаляем

ssh -i /root/nbs01/backup backup@192.168.122.1 \

"find $dump_path/$dump_base* -mtime +$dump_keepdays -exec /bin/rm '{}' \;"

done

ssh -i /root/nbs01/backup backup@192.168.122.1 \

"echo `date \+\%F\ \%T` All data keeps $dump_keepdays days, older files will be removed automatically. >> $dump_path/readme.txt"

# Восстанавливаем стандартный (системный) разделитель списков

IFS=$oldIFS

Предварительно надо создать авторизационный ключ для пользователя (в приведённом скрипте пользователь – backup) на сервере резервного хранения и разместить приватный ключ в каталоге, доступном на чтение только пользователю root (в приведённом скрипте ключ лежит в файле /root/nbs01/backup) и настроить sshd удалённого сервера на авторизацию по ключам – об этом весьма подробно написано, например, в этой статье.
Да, трафик будет весьма серьёзным, несмотря на возможность сжатия ssh, но конкретно данное решение работает в виртуальной среде, где 3 гигабайта дампа передаются примерно за полторы минуты, что вполне приемлемо.

Восстановление базы данных из дампа

Тут всё просто – если владелец (имя “роли входа” или пользователь postgresql, указанный владельцем изначальной БД) уже существует, но нет самой базы данных, команда восстановления будет выглядеть примерно так:

/usr/pgsql-9.2/bin/pg_restore -e -j 8 -U root -W -d upp /root/files/upp-2013-11-20.pgdump

Восстановление будет выполнено в 8 потоков (для ускорения процедуры, в документации pgsql рекомендуется использовать потоков не меньше, чем доступно ядер CPU) от имени пользователя root с интерактивным вводом пароля. Файл /mnt/arc/1C8/upp-2013-11-20-09-45-51.pgdump – распакованный .gz из второго примера или изначальный дамп из первого. Целевая база (в данном примере – upp) должна уже существовать, и быть созданной из template0.

Если пользователя-владельца создать нет возможности/желания, можно добавить ключ --no-owner да и вообще, почитать что пишут на http://www.postgresql.org/docs/9.3/static/backup.html

И ещё, если на создание дампа в пару гигабайт (несжатых) уходит пара минут, то на восстановление данного дампа в один поток (если не указать ключ распараллеливания – “-j 8” в примере выше) потребуется уже полчасика, на том же железе. А если использовать текстовые дампы (не указать “-F c” при создании дампа, и для восстановления использовать стандартную команду psql dbname < infile или использовать конвейер типа pg_restore infile.pgdump | psql), времени потребуется ещё больше – данные методы целесообразно использовать не для полного восстановления, а когда требуется восстановить только определённую часть базы данных.

Для восстановления БД с удалённой машины (сервера хранилища) можно использовать конструкцию вида:

zcat upp-2014-10-22-07-30-03.pgdump.gz | ssh 192.168.1.2 “psql upp-copy > /root/files/log-create”

Здесь zcat – команда вывода содержимого архива на stdout, upp-2013-10-22-07-30-03.pgdump.gz – имя восстанавливаемого архива, 192.168.1.2 – сервер postgresql, на который восстанавливаем дамп, upp-copy – имя базы данных, в которую разворачиваем дамп (на момент восстановления должна существовать, быть пустой, и иметь необходимые права для “роли входа”, использованной в изначальной БД); чтобы не засорять экран выводом psql о процессе создания объектов, перенаправим вывод в файл (сообщения об ошибках, в случае их наличия, будут выводиться в терминал). В данном примере предполагается, что у пользователя, от имени которого мы подключаемся по ssh к серверу postgres есть право работать с базами данных, поэтому авторизация в БД не описана.

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

Вариант для реализации резервного копирования MySQL:

#!/bin/bash

# user & password
USER=root
PASS=pass

# number of backups to be saved
KEEP=14

# dir to backup
DIR=/var/backups/mysql

NOW=$(date +"%Y-%m-%d")
DBS="$(mysql -u $USER -p$PASS -Bse 'show databases')"

for db in $DBS
do

  BACKUPS=`find $DIR -name "$db.*.gz" | wc -l | sed 's/\ //g'`
  while [ $BACKUPS -ge $KEEP ]
  do
    ls -tr1 $DIR/$db.*.gz | head -n 1 | xargs rm -f
    BACKUPS=`expr $BACKUPS - 1`
  done
  
  FILE=$DIR/$db.$NOW-$(date +"%T").sql.gz
  mysqldump -u $USER -p$PASS $db | gzip -9 > $FILE

done

exit 0

#!/bin/bash

# user & password

USER=root

PASS=pass

# number of backups to be saved

KEEP=14

# dir to backup

DIR=/var/backups/mysql

NOW=$(date +"%Y-%m-%d")

DBS="$(mysql -u $USER -p$PASS -Bse 'show databases')"

for db in $DBS

BACKUPS=`find $DIR -name "$db.*.gz" | wc -l | sed 's/\ //g'`

while [ $BACKUPS -ge $KEEP ]

ls -tr1 $DIR/$db.*.gz | head -n 1 | xargs rm -f

BACKUPS=`expr $BACKUPS - 1`

done

FILE=$DIR/$db.$NOW-$(date +"%T").sql.gz

mysqldump -u $USER -p$PASS $db | gzip -9 > $FILE

done

exit 0

Еще один способ резервного копирования Postgresql

#!/bin/bash

# user & password
USER=postgres
PASS=pass

# number of backups to be saved
KEEP=14

# dir to backup
DIR=/var/backups/pgsql

PGPASSWORD=$PASS
export PGPASSWORD

NOW=$(date +"%Y-%m-%d")
DBS="$(psql -U $USER -lt |awk '{ print $1}' |grep -vE '^-|^List|^Name|template[0|1]')"

for db in $DBS
do

  BACKUPS=`find $DIR -name "$db.*.gz" | wc -l | sed 's/\ //g'`
  while [ $BACKUPS -ge $KEEP ]
  do
    ls -tr1 $DIR/$db.*.gz | head -n 1 | xargs rm -f
    BACKUPS=`expr $BACKUPS - 1`
  done
  
  FILE=$DIR/$db.$NOW-$(date +"%T").sql.gz

  pg_dump -U $USER $db | gzip -c > $FILE

done

PGPASSWORD=
export PGPASSWORD

exit 0

#!/bin/bash

# user & password

USER=postgres

PASS=pass

# number of backups to be saved

KEEP=14

# dir to backup

DIR=/var/backups/pgsql

PGPASSWORD=$PASS

export PGPASSWORD

NOW=$(date +"%Y-%m-%d")

DBS="$(psql -U $USER -lt |awk '{ print $1}' |grep -vE '^-|^List|^Name|template[0|1]')"

for db in $DBS

BACKUPS=`find $DIR -name "$db.*.gz" | wc -l | sed 's/\ //g'`

while [ $BACKUPS -ge $KEEP ]

ls -tr1 $DIR/$db.*.gz | head -n 1 | xargs rm -f

BACKUPS=`expr $BACKUPS - 1`

done

FILE=$DIR/$db.$NOW-$(date +"%T").sql.gz

pg_dump -U $USER $db | gzip -c > $FILE

done

PGPASSWORD=

export PGPASSWORD

exit 0

Источники: http://www.bubnov.su/stati/rezervnoe-kopirovanie-baz-dannyh-postgresql

http://habrahabr.ru/post/82278/

К2® Cloud ERP Info

K2, Python, PHP, Linux

Резервное копирование баз данных Postgresql в Linux

Сохранение бекапа на удаленном сервере по ssh

Восстановление базы данных из дампа

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

Еще один способ резервного копирования Postgresql

Be the first to comment

Leave a Reply Скасувати відповідь