13,8 тыс подписчиков

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

16 апреля 202116 апр 2021

133

17 мин

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

Оглавление

Введение
Установка
Начальная настройка инфраструктуры

Источник: Nuances of Programming

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 20210209 T115342 - Tue Feb 9 11 :53 :45 2021 - Size : 70.7 MiB - WAL Size : 0 B
database -1 20210209 T114450 - Tue Feb 9 11 :44 :53 2021 - Size : 70.7 MiB - WAL Size : 32.3 KiB
database -1 20210209 T114054 - 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 20210210 T194412 /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 или даже в разных системах управления базами данных.

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