Резервное копирование и восстановление баз данных PostgreSQL

Вступление

Регулярное резервное копирование базы данных - важная задача обслуживания и стратегия восстановления после сбоя для всех, кто отвечает за базу данных.

Распространенное заблуждение разработчиков программного обеспечения заключается в том, что будет администратор базы данных, который позаботится об этом за нас. К сожалению, по моему опыту, это далеко не так. Из множества проектов, над которыми я когда-либо работал для нескольких разных компаний, я ни разу не имел возможности возложить эти задачи на сказочного единорога, известного как администратор базы данных (DBA). Вместо этого мне пришлось сделать все возможное, чтобы сохранить базы данных в системах, над которыми я работаю, в максимально возможной ситуации, которая включала в себя настройку стандартной стратегии резервного копирования и знание того, как восстановить ее, если перед лицом такой ситуации. трагедия.

В этой статье я расскажу о методах, которые я использовал для резервного копирования и восстановления баз данных PostgreSQL на серверах Linux.

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

Есть два известных мне способа создания резервной копии базы данных PostgreSQL, которые я буду обсуждать в этом разделе. Оба способа используют копии базы данных на определенный момент времени. Один использует так называемое архивирование WAL, а другой использует дамп SQL.

A. Создание заархивированной копии вашей базы данных на уровне файловой системы с помощью архивации WAL

Прежде чем заходить слишком далеко в объяснении этого метода, я должен сказать, что в системе должен быть создан каталог, который будет хранить архивированные файлы и должен принадлежать пользователю postgres. В своих примерах я буду использовать каталог /home/username/pg который принадлежит postgres, и он будет содержать два подкаталога wals и cluster , также принадлежащие postgres.

Этот первый метод использует пару функций PostgreSQL pg_start_backup(...) и pg_stop_backup(...) . Чтобы использовать эти функции, пользователь, выполняющий их, должен иметь привилегии репликации, которые определены в конфигурационном файле pg_hba.conf. Кроме того, чтобы использовать этот метод резервного копирования, вам необходимо добавить некоторые настройки и команды в файл postgresql.conf, о котором я расскажу далее.

В файле postgresql.conf вам необходимо установить archive_mode в on и обеспечить команду архив , который будет выглядеть примерно так:

 archive_mode = on 
 archive_command = 'cp %p /home/username/pg/wals/%f'

Здесь «% p» заменяется на путь к архивируемому файлу WAL (журнал упреждающей записи), а «% f» - это имя фактического файла.

Вышеупомянутые функции Postgres работают путем создания контрольной точки (также известной как единичный момент времени) копии каталога данных Postgres в виде сжатого архива. Я обычно использовал эту комбинацию функций в сценарии bash, который запланирован для запуска с заданными интервалами в задаче cron.

Например, сценарий bash на сервере Linux, на котором размещена база данных Postgres, может реализовать такую задачу cron для резервного копирования базы данных следующим образом:

 1 1 * * * bash /path/to/backup.sh

В приведенном выше примере запись задачи cron будет запускать сценарий backup.sh в первую минуту первого часа каждого дня.

Ниже приведен пример сценария bash backup.sh для выполнения функции создания заархивированной копии:

 #! /bin/bash 
 
 function backup_pg() { 
 ARCHIVE=$1 
 echo Backing up database ... 
 psql -U username -h localhost -c "SELECT pg_start_backup('$ARCHIVE');" databasename 
 tar czf $ARCHIVE $SRC 
 psql -U username -h localhost -c "SELECT pg_stop_backup();" databasename 
 echo Completed! 
 } 
 
 SRC=/var/lib/pgsql/9.6/data 
 DST=/home/username/pg/cluster/ 
 
 TODAY=`date +%F` 
 ARCHIVE=databasename-backup-$TODAY.tgz 
 BAKDIR=Archive-$TODAY 
 
 mkdir -p $DST 
 cd $DST/$BACKDIR 
 
 backup_pg $ARCHIVE

Как я упоминал ранее, это создает копию базы данных на определенный момент времени и архивирует весь каталог данных. Вы хотите заменить databasename , username , а также SRC и DST значениями, которые имеют смысл для вашей настройки. Для получения дополнительной информации об использовании этого метода создания резервной копии обратитесь к официальной документации .

Б. Создание SQL-дампа базы данных

Этот второй способ резервного копирования базы данных, использующий команду pg_dump , является моим предпочтительным методом, поскольку я считаю, что он требует меньше шагов и, следовательно, менее сложен. Этот метод по-прежнему создает копию базы данных на определенный момент времени, но делает это путем создания единого файла, который содержит команды, которые воссоздают таблицы, ограничения и индексы, а также заполняют таблицы данными.

Есть разные способы использования этой команды, которые подробно описаны в документации PostgreSQL , но в основном они отличаются форматом вывода.

Два наиболее распространенных способа, которыми я их использовал, - это создание файла дампа базы данных в виде простого читаемого текста, заполненного командами SQL, или создание вывода в настраиваемом двоичном формате PostgreSQL. Я действительно ценю удобочитаемость вывода простого текста из любопытства и возможность взглянуть на то, что происходит, но на самом деле практического применения здесь очень мало, поскольку эти вещи обычно довольно большие.

Напротив, пользовательский двоичный дамп не читается человеком, но он сжат для экономии места и лучшей обработки восстановления, не вызывая запутанных ошибок, связанных с нарушениями ограничений при загрузке данных таблицы, которые часто возникают в методе простого текста.

Опять же, вы можете использовать ту же запись задачи cron, которая запускает сценарий backup.sh в первую минуту первого часа каждого дня.

 1 1 * * * bash /path/to/backup.sh

Вот пример сценария bash для создания резервной копии с использованием pg_dump в настраиваемом двоичном формате:

 #!/bin/bash 
 
 TODAY=`date +%F` 
 ARCHIVE=databasename-$TODAY.bk 
 echo creating archive file $ARCHIVE 
 cd /var/lib/pgsql/9.6/backups 
 pg_dump -Fc databasename > $ARCHIVE 
 echo 'archive complete'

Если кто-то хочет получить простой текстовый вывод SQL, вы должны заменить строку pg_dump -Fc databasename > $ARCHIVE на pg_dump databasename > $ARCHIVE .

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

Восстановление базы данных может быть непростой задачей, поскольку обычно это делается в то время, когда вы пережили серьезный сбой, а также различные члены команды и заинтересованные стороны, которые находятся в эмоциональном безумии. Я надеюсь, что это то, что вам как разработчику никогда не придется испытать, но я пишу этот арткл, зная, что это, вероятно, будет неудачным событием для определенного процента его читателей. При этом не забывайте расслабляться и делать шаг или команду за раз. В конце концов, худшее уже случилось, и тебе некуда идти, кроме как наверх.

A. Восстановление из резервной копии файловой системы

Для восстановления из резервной копии WAL / файловой системы вам необходимо выполнить следующие действия:

Остановите сервер PostgreSQL, если он работает на новом сервере Linux.
Удалите существующие файлы и каталоги в каталоге данных, который обычно находится в / var / lib / pgsql / xx / data, где «xx» - это версия PostgreSQL, которую вы используете, например 9.6. Также обратите внимание на то, кому принадлежат эти файлы и каталоги, которые обычно принадлежат пользователю postgres.
Возьмите последнюю заархивированную копию базы данных, созданную в сценарии backup.sh, и распакуйте ее в каталог данных postresql. Убедитесь, что права собственности на эти файлы такие же, как вы отметили на шаге 2.
Отключите все другие приложения или службы, которые могут пытаться получить доступ к базе данных. Также рекомендуется изменить файл pg_hba.conf, чтобы другие пользователи не могли получить доступ к этой базе данных.
Создайте файл recovery.conf в каталоге данных и поместите в него следующую команду, которая по сути является обратной по отношению к archive_command в файле postgresql.conf, о котором говорилось ранее. Опять же, убедитесь, что у этого файла тот же владелец, что и на шаге 2.

recovery.conf

restore_command 'cp /home/username/pg/wals/%f %p'

Запустите службу PostgreSQL, которая переведет базу данных в режим восстановления с помощью команды файла recovery.conf для восстановления любых данных, которые хранились в WAL (журнал упреждающей записи) после того, как была сделана копия на определенный момент времени.

Ух! Готово.

Б. Восстановление из резервной копии дампа SQL

Восстановление базы данных из дампа SQL, созданного с помощью pg_dump включает в себя гораздо меньше шагов, чем предыдущий метод, но вы почти наверняка восстановите меньше данных с помощью этого метода, поскольку вы не будете использовать WAL для сбора каких-либо дополнительных данных, которые не превратить его в копию на определенный момент времени.

Чтобы восстановить базу данных, вам необходимо убедиться, что служба postgresql запущена на машине, на которой вы хотите восстановить базу данных. Затем все, что требуется, - это выполнить следующую команду от имени пользователя, имеющего полные права на запись в базу данных PostgreSQL.

 pg_restore -Fc databasename-YYYY-MM-DD.bk

Postgres прочитает файл резервной копии и воссоздает таблицы, индексы и ограничения, а также загрузит таблицы с данными, присутствующими на момент копирования исходной базы данных.

И вуаля, готово!

Я предпочитаю этот метод не только потому, что он менее сложен, но также я предпочитаю этот метод резервного копирования и восстановления, потому что как разработчик я часто нахожу его полезным или даже необходимым, чтобы я мог легко загрузить производственную базу данных на свой локальный компьютер. машина разработки. Это позволяет мне отлаживать или разрабатывать новую функцию с данными, которые были свежими и максимально похожими на данные реальной системы, а дампы SQL делают это возможным.

Таким образом, даже если вы используете метод WAL / файловой системы для резервного копирования и восстановления, вы все равно можете использовать pg_dump и pg_restore для создания копий и восстановления вашей локальной машины разработчика.

Заключение

В этой статье я продемонстрировал два подхода к созданию запланированных резервных копий для базы данных PostgreSQL, работающей на сервере Linux, и к выполнению восстановления в случае, если база данных окажется поврежденной или непригодной для использования по иным причинам. Я также обсудил плюсы и минусы каждого метода, а также обсудил необходимость того, чтобы разработчик знал, как выполнять эти задачи.

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