Оптимизация работы баз данных с PostgreSQL 12
Содержание
- Введение
- Установка
- Начальная настройка инфраструктуры
- Установка Postgres
- Конфигурация Postgres
- Настройка резервного копирования и восстановления с помощью barman
- Полезные команды barman
- Восстановление резервной копии после аварийного завершения
- Жесткая перезагрузка кластера Postgres
- Установка и настройка repmgr
- Настройка переключения вручную
- Очищаем конфигурацию repmgr и начинаем сначала
- Заключение
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 или даже в разных системах управления базами данных.
А кроме того, мы еще не рассматривали то, как с переключением справляется прикладной уровень. Здесь возможны варианты: использовать средство балансировки нагрузки для принятия фактического решения о том, к какому серверу базы данных подключиться клиенту, или создать и настроить эту возможность на прикладном уровне.
Источник: