Оптимизация работы баз данных с PostgreSQL 12

Содержание

Андрей Шагин

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

Постараемся здесь дать исчерпывающее, разв е рнутое представление об этой замечательной системе управления базами данных — от установки, настройки, резервного копирования и восстановления с помощью barman 2.11 и до репликации и переключения с использованием repmgr 5.0.0 на Ubuntu 20.10. Акцент, впрочем, сделаем на достижении конкретных результатов, целей и задач. Поэтому особое внимание уделим соответствующим этапам и командам, оставив в стороне фоновые сведения и подробные объяснения.

Введение

Задействуем три физических компьютера или три виртуальные машины ( database-1 , database-2 и backup ). Каждый компьютер или машину запускаем на ubuntu 20.10 и подключаем к подсоединенному хранилищу, смонтированному в /backup :

Установка

Начальная настройка инфраструктуры

Убедитесь, что DNS правильно сконфигурирован на каждой из машин — наше руководство будет ссылаться не на IP-адреса, а только на имена хостов.

Установка Postgres

  • Устанавливаем закадычных друзей: sudo apt -y install vim bash-completion wget joe .
  • Теперь обновляем определения пакетов apt: apt-get update .
  • Устанавливаем postgresql 12: apt install postgresql .
  • Устанавливаем интерфейс командной строки barman: apt install barman-cli .
  • Проверяем состояние запущенной службы: systemctl status postgresql.service .
  • Проверяем подключение postgres: su — postgres .
  • Даем пользователю postgres более надежный пароль: psql -c alter user postgres with password 'MyStrongAdminP@ssw0rd' .
  • То же самое проделываем на уровне ОС (с тем же паролем). Выполняем с правами администратора passwd postgres .
  • Затем перезагружаем машину.
  • Повторяем эти пункты для машины database-2 .
  • На обоих серверах баз данных от имени пользователя postgres создаем файл

Конфигурация Postgres

  • конфигурируем /etc/postgresql/12/main/postgresql.conf следующим образом:
  • редактируем /etc/postgres/12/main/pg_hba.conf и добавляем следующие строки (здесь уже содержится конфигурация репликации, которая потом понадобится для repmgr ):
  • создаем начальную структуру базы данных (на этом этапе она может быть любой).

Настройка резервного копирования и восстановления с помощью barman

  • Запускаемся с нового сервера резервного копирования на ubuntu 20.10.
  • В backup устанавливаем barman: apt-get install barman barman-cli .
  • Создаем закрытый ключ для пользователя postgres и пользователя barman в
    database-1 и backup соответственно.
  • В database-1 меняем на пользователя postgres su — postgres и генерируем пару ключей: ssh-keygen -b 2048 -t rsa -N -C postgres@database-1 .
  • В database-2 меняем на пользователя postgres su — postgres и генерируем пару ключей: ssh-keygen -b 2048 -t rsa -N -C postgres@database-2 .
  • В backup меняем на пользователя barman su — barman и создаем пару ключей: ssh-keygen -b 2048 -t rsa -N -C barman@backup .
  • Добавляем открытый ключ postgres и barman соответственно к
  • С каждой из машин подключаем одну к другой, используя соответствующее имя пользователя и полное имя хоста:
  • В backup перемещаем файл /etc/barman.conf в /etc/barman.conf.orig и воссоздаем его со следующим содержимым:
  • Настраиваем входящие в WAL — в backup получаем этот каталог с barman show-server database-1 | grep incoming_wals_directory : should be incoming_wals_directory: /backup/barman/database-1/incoming .
  • Убеждаемся, что этот путь такой же, как и на postgresql.conf в archive_command .
  • Выводим список всех доступных серверов резервного копирования: barman list-server .
  • Проверяем архивацию WAL-журнала и все остальные части нашего сервера: barman check database-1 .

  • Тестируем архивацию WAL-журнала с
    barman switch-wal —force —archive database-1 .
  • Если видите строку WAL archive: FAILED (please make sure WAL shipping is setup) , это означает одно из трех: 1) возможно, база данных еще не создала никаких файлов в WAL; 2) они уже были удалены; 3) rsync не работает. Загляните в логи PostgreSQL: там все ответы.

Но если rsync работает правильно и никаких данных в базу данных фактически не записывается, то сервер не будет выдавать никаких WAL-файлов. Стало быть, резервную копию создавать не из чего. WAL-файлы создаются после получения определенного объема данных. Рекомендую создать таблицу и добавить в нее данные. Для принудительного закрытия текущего WAL-файла используйте: barman switch-wal —force —archive database-1 .

  • Создаем базовую резервную копию: barman backup database-1 .
  • Выводим список всех имеющихся резервных копий для одного сервера barman list-backup database-1 :
  • Более подробная информация из конкретной резервной копии получается с помощью: barman show-backup server-a 20220209T115342 .
  • Планируем резервное копирование с помощью cron:
  • barman cron выполняется каждую минуту (операции архивирования WAL-журнала проходят параллельно на серверной основе, при этом обеспечивается соблюдение политик хранения на этих серверах).
  • Выполняем резервное копирование базы данных каждый день в полночь.
  • Удаляем /etc/cron.d в backup .

Полезные команды barman

barman check database-1 — проверка конфигурации barman для конкретного сервера.
barman status database-1 — показ состояния конкретного сервера.
barman backup database-1 — создание резервной копии для конкретного сервера.
barman backup —reuse=link main — принудительное добавочное резервное копирование.
barman list-backup database-1 — вывод списка всех доступных резервных копий на конкретном сервере.
barman show-backup database-1 timestamp п — показ содержимого резервной копии.
barman show-backup database-1 latest — показ последней доступной резервной копии.

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

  • Подключаемся к схеме баз данных database-1 и удаляем часть данных, некоторые таблицы или базы данных, имитируя аварийную ситуацию.
  • Выключаем целевой сервер postgres на database-1 :
    systemctl stop postgresql.service .
  • В backup от имени пользователя barman просматриваем последнюю резервную копию barman barman show-backup database-1 latest :

  • Обращаем внимание на идентификатор резервной копии и время окончания резервного копирования.
  • Здесь же в backup от имени пользователя postgres выполняем следующую команду для восстановления этой резервной копии:
  • Ждем завершения резервного копирования с сообщением: Your PostgreSQL server has been successfully prepared for recovery! («Ваш сервер PostgreSQL готов к восстановлению!»).
  • От имени пользователя postgres в database-1 запускаем postgres в режиме восстановления:

  • Перезагружаем блок со 2-й базой данных database-2 .
  • Теперь сервер баз данных восстановлен из резервной копии.

Жесткая перезагрузка кластера Postgres

В случае если резервная копия базы данных не подлежит восстановлению и повреждена, практически ничего другого не остается, кроме как прибегнуть к жесткой перезагрузке кластера pg без переустановки контейнера базы данных. Для этого используют следующую процедуру. ВНИМАНИЕ. Это приведет к удалению всех данных.

  • А теперь выполните описанную выше процедуру настройки кластера PostgreSQL в /etc/postgres/. , ведь вся последняя конфигурация была удалена.
  • Если сервер Postgres не появляется после восстановления, посмотрите подробные логи запуска с помощью следующей команды:

Установка и настройка repmgr

Для настройки репликации между узлами postgres database-1 и database-2 будем использовать repmgr:

  • В обоих блоках баз данных устанавливаем repmgr apt-get install postgresql-12-repmgr .
  • В database-1 (основной узел) создаем пользователя repmgr и базу данных и выполняем от имени пользователя postgres следующее: createuser —superuser repmgr createdb —owner=repmgr repmgr .
  • Меняем путь поиска пользователя repmgr по умолчанию:
    psql -c ALTER USER repmgr SET search_path TO repmgr, public; .
  • Редактируем /etc/postgresql/12/main/postgresql.conf и добавляем следующую строку shared_preload_libraries = 'repmgr' . При запуске PostgreSQL будет загружено расширение repmgr.
  • В database-1 создаем стандартный конфигурационный файл repmgr touch /etc/repmgr.conf и добавляем следующее:
  • В database-1 редактируем /etc/default/repmgrd и применяем следующее содержимое:
  • В database-2 создаем стандартный конфигурационный файл repmgr touch /etc/repmgr.conf и добавляем следующее:
  • В database-2 редактируем /etc/default/repmgrd и применяем следующее содержимое:
  • Чтобы репликация работала, database-1 должна принять подключение репликации от database-2 . Реплика запрашивает информацию о репликации, а не наоборот. В database-1 удостоверяемся в наличии следующей конфигурации в /etc/postgresql/12/main/pg_hba.conf (она у вас уже должна быть):
  • От имени пользователя postgres регистрируем в database-1 основной узел database-1 с repmgr /usr/bin/repmgr -f /etc/repmgr.conf primary register :
  • Проверяем состояние кластера /usr/bin/repmgr -f /etc/repmgr.conf cluster show :

  • Запускаем с правами администратора root repmgrd в database-1 : /etc/init.d/repmgrd start .
  • Настраиваем 2-й узел, переключаемся на database-2 и останавливаем postgresql /etc/init.d/postgresql stop .
  • Выполняем от имени пользователя postgres : /usr/bin/repmgr -h database-1 -U repmgr -d repmgr -p 5432 -F -f /etc/repmgr.conf standby clone —dry-run .
  • По завершении выполняем операцию клонирования /usr/bin/repmgr -h database-1 -U repmgr -d repmgr -p 5432 -F -f /etc/repmgr.conf standby clone :
  • На этом этапе PostgreSQL не работает в резервных узлах. Хотя у резервного узла есть скопированный из основного узла каталог данных Postgres, в том числе любые имеющиеся там конфигурационные файлы PostgreSQL.
  • Теперь запускаем службу postgresql на вторичном узле /etc/init.d/postgresql start .
  • Регистрируем от имени пользователя postgres вторичный узел с repmgr /usr/bin/repmgr -f /etc/repmgr.conf standby register :
  • Теперь проверим настройку кластера repmgr:
    /usr/bin/repmgr -f /etc/repmgr.conf cluster show —compact

  • Самое время протестировать репликацию: на основном сервере database-1 создаем базы данных, таблицы, записи и видим мгновенные изменения на database-2 → поддерживает идеальную синхронизацию со вспомогательным сервером. Отличная работа!

Настройка переключения вручную

  • Наша цель — с помощью repmgr переключиться с первичного узла/сервера на вторичный и обратно.
  • Перед переключением обратимся к настройке кластера sudo -u postgres /usr/bin/repmgr -f /etc/repmgr.conf cluster show —compact :

  • Выполняем переключение в database-2 :
    /usr/bin/repmgr standby switchover -f /etc/repmgr.conf :
  • На ubuntu с командой /usr/lib/postgresql/12/bin/pg_ctl -w -D '/var/lib/postgresql/12/main' start , скорее всего, возникнут проблемы: PQping() returned PQPING_NO_RESPONSE будет появляться несколько раз, пока не истечет время ожидания. В этом случае запустите postgres вручную на database-1 в то время, когда будут повторные попытки подключиться к /etc/init.d/postgresql start .
  • А теперь проверим настройку кластера repmgr: sudo -u postgres /usr/bin/repmgr -f /etc/repmgr.conf cluster show —compact :

  • Сейчас кластер repmgr работает с database-2 в качестве основного узла.
  • Выполняем обратное переключение в database-1 от имени пользователя postgres : /usr/bin/repmgr standby switchover -f /etc/repmgr.conf .
  • Опять же, в этом сценарии на ubuntu у postgres возможны проблемы с правильным возвращением на database-2 . Здесь тоже запускаем postgres с правами администратора root с помощью /etc/init.d/postgres start .
  • Проверим настройку кластера repmgr: sudo -u postgres /usr/bin/repmgr -f /etc/repmgr.conf cluster show —compact :

  • Предупреждающее сообщение подсказывает, что нужно выбрать вновь ставший основным сервер: в database-2 от имени пользователя postgres выполняем repmgr standby follow .
  • Вот мы и переключились в кластере с первичного узла repmgr на вторичный и обратно.

Очищаем конфигурацию repmgr и начинаем сначала

  • Возможны ситуации, когда проще удалить текущую и, быть может, поломанную конфигурацию repmgr и начать все сначала. В этом случае в основном узле от имени пользователя postgres выполняем:

Заключение

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

В добавок ко всему этому неплохо было бы выполнять полное текстовое резервное копирование с применением статической диспетчеризации (например, каждое воскресенье) с помощью pg_dumpall | gzip backup.gz . Ведь когда-нибудь может понадобиться полный дамп всего содержимого кластера баз данных в текстовом формате для использования на разных ОС, версиях PostgreSQL или даже в разных системах управления базами данных.

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


Источник: medium.com