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

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

Постараемся здесь дать исчерпывающее, развернутое представление об этой замечательной системе управления базами данных  —  от установки, настройки, резервного копирования и восстановления с помощью 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 создаем файл ~/.bash_rc и добавляем следующее содержимое:
PATH=$PATH:/usr/lib/postgresql/12/bin
alias l='ls -al --color=auto'
  • И добавляем эти строчки привилегированному пользователю root в ~/.bashrc на обоих серверах.

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

  • конфигурируем /etc/postgresql/12/main/postgresql.conf следующим образом:
# прослушиваем любое устройство машины и ее IP-адрес
listen_addresses = '*'

# включаем режим архивации и настраиваем архивацию WAL-журнала barman rsync
archive_mode = on
archive_command = 'rsync -a %p barman@backup:/backup/barman/database-1/incoming/%f'
wal_level = replica

# закомментируем include_dir: это предотвратит запуск сервера во время восстановления
# include_dir = 'conf.d'
  • редактируем /etc/postgres/12/main/pg_hba.conf и добавляем следующие строки (здесь уже содержится конфигурация репликации, которая потом понадобится для repmgr):
host all all <your-c-net> trust
host repmgr repmgr <your-c-net> trust
host replication repmgr <your-c-net> trustSQLite: как организовывать таблицы
  • создаем начальную структуру базы данных (на этом этапе она может быть любой).

Настройка резервного копирования и восстановления с помощью 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 соответственно к
    ~/.ssh/authorized_keys:
on database-1 as postgres : cat <database-2.server id_rsa.pub> >> ./ssh/authorized_keys
on database-1 as postgres : cat <backup.server id_rsa.pub> >> ./ssh/authorized_keyson database-2 as postgres : cat <database-1.server id_rsa.pub> >> ./ssh/authorized_keys

on database-2 as postgres : cat <backup.server id_rsa.pub> >> ./ssh/authorized_keyson backup as postgres : cat <database-1.server id_rsa.pub> >> ./ssh/authorized_keys

on backup as postgres : cat <database-2.server id_rsa.pub> >> ./ssh/authorized_keys
  • С каждой из машин подключаем одну к другой, используя соответствующее имя пользователя и полное имя хоста:
from database-1 as postgres user : ssh barman@backup
from database-1 as postgres user : ssh postgres@database-2

from database-2 as postgres user : ssh barman@backup
from database-2 as postgres user : ssh postgres@database-1

from backup as barman user : ssh postgres@database-1
from backup as barman user : ssh postgres@database-2

Таким образом соответствующие хосты будут добавлены в файл known_hosts, необходимый для правильной работы barman
  • В backup перемещаем файл /etc/barman.conf в /etc/barman.conf.orig и воссоздаем его со следующим содержимым:
[barman]
barman_home = /backup/barman
barman_user = barman
log_file = /backup/barman/barman.log
compression = gzip
reuse_backup = link
backup_method = rsync
archiver = on
immediate_checkpoint = true
basebackup_retry_times = 3
basebackup_retry_sleep = 30
last_backup_maximum_age = 1 DAYS

[database-1]
description = "database-1"
ssh_command = ssh postgres@database-1
conninfo = host=database-1 user=postgres port=5432
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main
backup_options = exclusive_backup

[database-2]
description = "database-2"
ssh_command = ssh postgres@database-2
conninfo = host=database-2 user=postgres port=5432
retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days
wal_retention_policy = main
backup_options = exclusive_backup
  • Настраиваем входящие в 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@backup:~$ barman list-backup database-1

database-1 20210209T115342 - Tue Feb  9 11:53:45 2021 - Size: 70.7 MiB - WAL Size: 0 B
database-1 20210209T114450 - Tue Feb  9 11:44:53 2021 - Size: 70.7 MiB - WAL Size: 32.3 KiB
database-1 20210209T114054 - Tue Feb  9 11:40:58 2021 - Size: 70.7 MiB - WAL Size: 32.2 KiB
  • Более подробная информация из конкретной резервной копии получается с помощью: barman show-backup server-a 20210209T115342.
  • Планируем резервное копирование с помощью cron:
  • barman cron выполняется каждую минуту (операции архивирования WAL-журнала проходят параллельно на серверной основе, при этом обеспечивается соблюдение политик хранения на этих серверах).
  • Выполняем резервное копирование базы данных каждый день в полночь.
*  *    * * *   barman  /usr/bin/barman cron
0  0    * * *   barman  /usr/bin/barman backup database-1
  • Удаляем /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 выполняем следующую команду для восстановления этой резервной копии:
barman recover \\
  --target-time "2021-02-10 19:44:39.214745+00:00" \\
  --remote-ssh-command "ssh postgres@database-1" \\
  database-1 20210210T194412 /var/lib/postgresql/12/main
  • Ждем завершения резервного копирования с сообщением: Your PostgreSQL server has been successfully prepared for recovery!(«Ваш сервер PostgreSQL готов к восстановлению!»).
  • От имени пользователя postgres в database-1 запускаем postgres в режиме восстановления:
# запускаем postgresql вручную, чтобы начать восстановление
/usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/main start
  • Перезагружаем блок со 2-й базой данных database-2.
  • Теперь сервер баз данных восстановлен из резервной копии.

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

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

# выводим список всех имеющихся в системе кластеров pg
pg_lsclusters

# удаляем кластер (на основе версии и идентификатора кластера)
pg_dropcluster 12 main

# создаем новый кластер
pg_createcluster 12 main
  • А теперь выполните описанную выше процедуру настройки кластера PostgreSQL в /etc/postgres/..., ведь вся последняя конфигурация была удалена.
  • Если сервер Postgres не появляется после восстановления, посмотрите подробные логи запуска с помощью следующей команды:
/usr/lib/postgresql/12/bin/postgres -d 3 -D /var/lib/postgresql/12/main/ -c config_file=/etc/postgresql/13/main/postgresql.conf

Установка и настройка 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 и добавляем следующее:
node_id=1
node_name=database-1
conninfo='host=database-1 user=repmgr dbname=repmgr port=5432'
data_directory='/var/lib/postgresql/12/main'
barman_host=backup
barman_server=database-1
restore_command='/usr/bin/barman-wal-restore -U barman backup database-1 %f %p'
pg_bindir='/usr/lib/postgresql/12/bin'
log_file='/backup/barman/repmgr.log'
  • В database-1 редактируем /etc/default/repmgrd и применяем следующее содержимое:
# отключение repmgrd по умолчанию, чтобы не запускался после установки
# допустимые значения: да/нет
REPMGRD_ENABLED=yes

# конфигурационный файл (обязательно)
REPMGRD_CONF=/etc/repmgr.conf

# дополнительные параметры
#REPMGRD_OPTS=""

# запуск repmgrd от имени пользователя
#REPMGRD_USER=postgres

# двоичный repmgrd
#REPMGRD_BIN=/usr/bin/repmgrd

# pid-файл
#REPMGRD_PIDFILE=/var/run/repmgrd.pid
  • В database-2 создаем стандартный конфигурационный файл repmgr touch /etc/repmgr.conf и добавляем следующее:
node_id=2
node_name=database-2
conninfo='host=database-2 user=repmgr dbname=repmgr port=5432 connect_timeout=2'
data_directory='/var/lib/postgresql/12/main'
log_file='/backup/barman/repmgr.log'
  • В database-2 редактируем /etc/default/repmgrd и применяем следующее содержимое:
# отключение repmgrd по умолчанию, чтобы не запускался после установки
# допустимые значения: да/нет
REPMGRD_ENABLED=yes

# конфигурационный файл (обязательно)
REPMGRD_CONF="/etc/repmgr.conf"

# дополнительные параметры
#REPMGRD_OPTS=""

# запуск repmgrd от имени пользователя
#REPMGRD_USER=postgres

# двоичный repmgrd
#REPMGRD_BIN=/usr/bin/repmgrd

# pid-файл
#REPMGRD_PIDFILE=/var/run/repmgrd.pid
  • Чтобы репликация работала, database-1 должна принять подключение репликации от database-2. Реплика запрашивает информацию о репликации, а не наоборот. В database-1 удостоверяемся в наличии следующей конфигурации в /etc/postgresql/12/main/pg_hba.conf (она у вас уже должна быть):
host all all <your-c-net> trust
host repmgr repmgr <your-c-net>trust
host replication repmgr <your-c-net> trust
  • От имени пользователя postgres регистрируем в database-1 основной узел database-1 с repmgr /usr/bin/repmgr -f /etc/repmgr.conf primary register:
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered
  • Проверяем состояние кластера /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.
ИНФОРМАЦИЯ: все необходимые условия для standby clone («резервного клона») соблюдены
  • По завершении выполняем операцию клонирования /usr/bin/repmgr -h database-1 -U repmgr -d repmgr -p 5432 -F -f /etc/repmgr.conf standby clone:
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /var/lib/postgresql/12/main start
HINT: after starting the server, you need to register this standby with "repmgr standby register"
  • На этом этапе PostgreSQL не работает в резервных узлах. Хотя у резервного узла есть скопированный из основного узла каталог данных Postgres, в том числе любые имеющиеся там конфигурационные файлы PostgreSQL.
  • Теперь запускаем службу postgresql на вторичном узле /etc/init.d/postgresql start.
  • Регистрируем от имени пользователя postgres вторичный узел с repmgr /usr/bin/repmgr -f /etc/repmgr.conf standby register:
INFO: connecting to local node "database-2" (ID: 2)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID 1)
INFO: standby registration complete
NOTICE: standby node "database-2" (ID: 2) successfully registered
  • Теперь проверим настройку кластера 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:
NOTICE: executing switchover on node "database-2" (ID: 2)
NOTICE: local node "database-2" (ID: 2) will be promoted to primary; current primary "database-1" (ID: 1) will be demoted to standby
NOTICE: stopping current primary node "database-1" (ID: 1)
NOTICE: issuing CHECKPOINT
DETAIL: executing server command "/usr/lib/postgresql/12/bin/pg_ctl  -D '/var/lib/postgresql/12/main' -W -m fast stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
INFO: checking for primary shutdown; 2 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/25000028
NOTICE: promoting standby to primary
DETAIL: promoting server "database-2" (ID: 2) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "database-2" (ID: 2) was successfully promoted to primary
INFO: local node 1 can attach to rejoin target node 2
DETAIL: local node's recovery point: 0/25000028; rejoin target node's fork point: 0/250000A0
NOTICE: setting node 1's upstream to node 2
WARNING: unable to ping "host=database-1 user=repmgr dbname=repmgr port=5432"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "/usr/lib/postgresql/12/bin/pg_ctl  -w -D '/var/lib/postgresql/12/main' start"
WARNING: unable to ping "host=database-1 user=repmgr dbname=repmgr port=5432"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
WARNING: unable to ping "host=database-1 user=repmgr dbname=repmgr port=5432"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
  • На 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 выполняем:
psql -c "delete from repmgr.events;" repmgr
psql -c "delete from repmgr.monitoring_history;" repmgr
psql -c "delete from repmgr.nodes;" repmgr
psql -c "delete from repmgr.voting_term;" repmgr

Заключение

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

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

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

Читайте также:

Читайте нас в Telegram, VK и Яндекс.Дзен

Перевод статьи Thomas Reinecke: Database operational excellence with PostgreSQL 12

Предыдущая статьяОбработка событий по времени в бессерверной архитектуре
Следующая статьяДолгожданные инструкции Switch-Case в Python