PostgreSQL 9.3 + Pgpool-II
Решил поделиться с вами опытом настройки кластера PostgreSQL 9.3, состоящего из двух нод, управлением которого занимается pgpool-II, без использования Stream Replication (WAL). Надеюсь, кому-нибудь будет интересно.
Как Вы понимаете, это будут два отдельно стоящих сервера, которыми будет управлять pgpool-II.
- 4vCPU;
- 16 Гб памяти;
- CentOS 6.5;
- 50 Гб — система;
- 100 Гб — pg_xlog
- 500 Гб — каталог с данными
- 4vCPU;
- 8 Гб памяти;
- CentOS 6.5;
- 50 Гб — система;
Про установку PostgreSQL в детали вдаваться не буду, так как она стандартная.
Настройка pgpool-II.
За основу настройки pgpool-II, взял инструкцию с официального сайта: www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html
Хотел бы обратить внимание только самые важные моменты:
После настройки конфигурационного файла, запускаем службу pgpool. Можно также настроить pgpooladmin для мониторинга состояния нод.
Также статус нод с PostgreSQL можно посмотреть, выполнив команду на pgpool:
Далее, проверяем работу нашей схемы — создаем пустую БД через pgpool. Сделать это можно двумя способами:
1) через pgadmin, подключившись к IP-адресу pgpool-II;
2) выполнив команду на ноде с pgpool: createdb -p 9999 bench_replication
Репликация и балансировка нагрузки базы данных¶
Репликация это механизм синхронизации содержимого нескольких копий базы данных. Это может быть использовано для обеспечения резервного копирования и отказоустойчивости. При падении одной СУБД ее запросы будет обслуживать другая копия.
Балансировка нагрузки заключается в распределнии запросов от пользователей по разным копиям базы данных. Это позволяет выполнять большее количество запросов одновременно.
Для обеспечения задачи репликации и балансировки нагрузки базы данных развернем кластер master-slave на СУБД PostgreSQL. Кластер должен содержать один ведущий сервер (Master), один ведомый (Slave) и один узел масштабирования (Balancer).
Для балансировки нагрузки будет использоваться ПО Pgpool-II. Данное ПО также будет использоваться для обеспечения отказоустойчивости (failover, online recovery).
Примерная схема решения представлена на ngw_admin_replcation_pic .
Схема репликации и балансировки нагрузки. ¶
Реализация решения будет описана на примере СУБД PostgreSQL 9.5 и ПО Pgpool-II в ОС Ubuntu 16.04.
Подготовка среды¶
Создадим 3 виртуальные машины (ОС Ubuntu 16.04 LTS):
pgpool (IP адрес 192.168.250.181)
pgmaster (IP адрес 192.168.250.182)
pgslave (IP адрес 192.168.250.183)
На виртуальных машинах pgmaster и pgslave установим СУБД PostgreSQL с модулем расширения PostGIS и дополнительным ПО Pgpool-II:
На виртуальной машине pgpool установим ПО Pgpool-II:
Настройка потоковой репликации¶
Отредактируйте конфигурационный файл postgresql.conf на виртуальной машине pgmaster следующим образом:
В файле должны быть следующие строки:
Строка с комментарием обязательна и нужна для переключения master-slave . Параметры конфигурационного файла имеют следующие значения:
-
wal_level = hot_standby — сервер начнет писать в WAL логи так же как и при режиме «archive», добавляя информацию, необходимуюдля восстановления транзакции
-
max_wal_senders = 2 — максимальное количество слейвов
-
wal_keep_segments = 32 — минимальное количество файлов c WAL сегментами в pg_xlog директории
Далее добавим строчку для репликации в файл pg_hba.conf :
В файле должны быть следующие строки:
Это разрешит доступ к СУБД для нужд репликации из подсети 192.168.250.х.
Перезапустим СУБД на виртуальной машине pgmaster:
Останавливаем СУБД на виртуальной машине pgslave:
Отредактируйте конфигурационный файл postgresql.conf на виртуальной машине pgslave следующим образом:
В файле должны быть следующие строки:
Далее добавим строчку для репликации в файл pg_hba.conf :
В файле должны быть следующие строки:
На виртуальной машине pgmaster под пользователем postgres создаем резервную копию кластера базы данных и пересылаем на виртуальну машину pgslave.
Для передачи данных при помощи утилиты rsync ее необходимо установить, а также обеспечить временный доступ к виртуальной машине pgslave пользователю root по ssh.
На виртуальной машине pgslave создаем конфигурационный файл репликации /var/lib/postgresql/9.5/main/recovery.conf :
Параметр trigger_file отвечает за путь, по которому PostgreSQL ищет файл, чтобы переключиться в режим ведущего. В данном случае файл ищется в папке с конфигурацией СУБД (/var/lib/postgresql/9.5/main/failover).
Дадим права на новый файл:
Запустим СУБД на виртуальной машине pgslave:
Проверим активность репликации на виртуальной машине pgmaster:
Проверим активность репликации на виртуальной машине pgslave:
Настройка узла масштабирования¶
Изменим конфигурационный файл /etc/pgpool2/pgpool.conf :
Изменим конфигурационный файл /etc/pgpool2/pool_hba.conf :
Добавим пароли в файл /etc/pgpool2/pool_passwd :
Проверим работоспособность pgpool:
Настройка автоматического failover¶
Автоматический failover предназначен для переключения одного из ведомых серверов СУБД в роль мастера при падении сервера с мастером. Это необходимо для обеспечения возможности выполнения запросов на запись.
Failover требует настройки удаленного соединения по протоколу ssh без пароля. Для этого нужно создать ssh-ключи пользователя postgres и разослать их на виртуальные машины pgmaster и pgslave.
Для начала назначим пароль для пользователя postgres на виртуальных машинах pgmaster и pgslave. Для этого выполним:
Далее на вирутальной машине pgpool выполним следующую команду (пароль оставим пустым):
Перешлем ключ на виртуальные машины pgmaster и pgslave:
Добавим в конфигурационный файл /etc/pgpool2/pgpool.conf следующую строчку:
Создадим скрипт /etc/pgpool2/failover.sh следующего сожержания:
Назначим ему права на исполнение:
Протестировать механизм автоматического failover можно следующим образом:
Отключить ведущий сервер
Выполнить запрос show pool_nodes; на узле масштабирования
Смотреть логи pgpool на предмет выполнения скрипта
Убедиться в том, что ведомый сервер после выполнения скрипта может принимать запросы на запись
Для тестирования механизма автоматического failover остановим СУБД Postgres на виртуальной машине pgmaster:
Из листинга видно, что старый master имеет статус 3 , а старый slave стал новым мастером.
Добавим старый мастер в качестве slave. Для этого на виртуальной машине pgmaster:
Создадим конфигурационный файл репликации /var/lib/postgresql/9.5/main/recovery.conf :
Дадим права на новый файл:
Исправим конфигурационный файл postgresql.conf следующим образом:
В файле должны быть следующие строки:
3. На виртуальной машине pgslave под пользователем postgres создаем резервную копию кластера базы данных и пересылаем на виртуальну машину pgmaster.
На виртуальной машине pgmaster запустим СУБД:
На виртуальной машине pgpool перезапустим сервис:
Используемые источники¶
При реализаци механизма репликации и балансировки нагрузки использовались следующие источники:
Записки угрюмого поднимателя пингвинов.
Небольшая хаутушечка по настройке pgpool-кластера с steaming replication postgres.
1. Подготовка посгреса.
Необходимо настроить потоковую репликацию.
Первым делом необходимо настроить авторизацию юзера postgres на машинах кластера по ssh-ключам без пароля (причем, не забыть про коннект по ssh к самому себе). ssh-keygen и ssh-copy-id в помощь.
Конфиг postgresql.conf лучше иметь одинаковый (за исключением директивы archive_command), что на мастере, что на слейве — так удобнее и проще, тоже самое касается каталогов с базой и архивлогами.
hot_standby = on // указывает что слейв будет в одноименном режиме, т.е. с него возможны селекты; на мастере она игнорируется
wal_level = hot_standby // директива для мастера, указывает режим репликации; на слейве игнорируется
max_wal_senders = 4 // число процессов репликации, должно быть не меньше числа слейвов
wal_keep_segments = 12 // сколько хранить wal-логов, если число будет слишком маленьким — слейв не сможет засинхронизироваться после длительного оффлайна
archive_mode = on // дополнение к потоковой репликации, ускоряет синхронизацию после длительного оффлайна
archive_command = ‘rsync -aq %p <slave-name>:/somedir/archive/%f’ // команда для отправки файликов архив-логов. единственный параметр, который будет различаться на мастере и слейве (мастер указывает на слейв, слейв на мастер)
Кроме того, listen_address не должен быть localhost, иначе слейв не сможет подключиться (извращения со слейвом на том же хосте опустим), а так же необходимо настроить pg_hba.conf для разрешения подключения replication-юзера, что-то вроде такого:
host replication postgres 192.168.1.0/24 trust
После этого запускаем мастер-сервер. Готовим слейв.
Проще всего сделать pg_basebackup с работающего мастера, указать в archive_command хостнейм мастера и добавить файлик recovery.conf:
standby_mode=’on’
primary_conninfo=’host=master port=5432 user=postgres’ // реквизиты подключения к мастеру
trigger_file=’/somedir/trigger_file’ // путь до триггер-файла. при его появлении посгрес будет считать что мастер сдох и нужно самому становиться мастером
restore_command=’cp /somedir/archive/%f %p’ // команда для применения архивлогов
archive_cleanup_command=’/usr/pgsql-9.2/bin/pg_archivecleanup /somedir/archive %r’ // очистка примененных архивлогов
После этого можно можно запускать слейва, он должен успешно подключиться к мастеру и войти в режим hot_standby.
2. Настройка pgpool
Поставить pgpool под rhel/centos можно из пакетов, так что про традиционное для подобных хаутушечек configure&&make&&make install можно забыть.
Правим pgpool.conf:
backend_hostname0 = ‘hostname’
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/opt/pgsql/9.2/data’
backend_flag0 = ‘ALLOW_TO_FAILOVER’
Такими директивами (с увеличением порядкового номера) описываются все ноды кластера.
Включаем авторизацию через pgpool:
enable_pool_hba = on
authentication_timeout = 60
pool_passwd = ‘pool_passwd’ #имя файлика с юзерами/паролями в каталоге /etc/pgpool-II
Для авторизации по паролям через pgpool необходимо чтобы юзер и пароль совпадали, что в pgpool-е, что в посгресе, тип авторизации в посгресе должен быть md5. Например, создаем юзера «root» с паролем «qwerty»:
createuser -lP root
в pg_hba.conf добавляем
host all root pgpool-host-ip md5
pg_md5 -u root -p
вывод добавляем в pool_passwd, права на файл должны позволять юзеру postgres его прочитать.
Возвращаемся к pgpool.conf:
load_balance_mode = on # включаем балансировку нагрузки — селекты смогут выполняться в т.ч. на слейве
master_slave_mode = on
master_slave_sub_mode = ‘stream’ # включаем streaming replication
sr_check_period = 10 # период проверки репликации
sr_check_user = ‘root’ # реквизиты для проверки репликации
sr_check_password = ‘qwerty’
health_check_period = 5 # проверка доступности нод
health_check_timeout = 10
health_check_user = ‘root’ # реквизиты для проверки
health_check_password = ‘qwerty’
failover_command = ‘/opt/pgsql/failover.sh %d %P %H %R’ # самое важное — команда для файловера, будет исполняться при падении какой-либо из нод
Скрипт файловера довольно простой: если упал слейв — не делать ничего, если мастер — создать триггер-файл на слейве. А чтобы все это получилось — ssh-ключики и создавались.
#!/bin/bash
failed_id=$1
old_primary_id=$2
new_master_host_name=$3
new_database_path=$4
trigger=trigger_file
if [ $failed_id = $old_primary_id ];then # master failed
ssh $new_master_host_name touch $new_database_path/$trigger # let standby take over
fi
На этом минимум — закончен. pgpool сможет обслуживать соединения, отслеживать состояния нод и промоутить слейва в случае падения мастера.
Остается решить вопрос с рекавери — восстановлением работы упавшей ноды. Для этого есть 2 варианта:
восстанавливать ноду ручками (т.е. без участия pgpool-а) и давать pgpool-у команду на присоединение ноды
настроить online-recovery в pgpool-е, но все равно восстанавливать ручками 😀 хотя бы потому, что команду на восстановление придется все же давать руками (через консоль или модный pgpoolAdmin)
Первый вариант довольно прост и по-сути полностью повторяет официальный посгресовый гайд: сделать бейсбэкап с работающего мастера, создать recovery.conf и запустить ноду как слейв. Это все можно оформить в удобный скрипт:
cluster_master=$1
recovery_host=$2
recovery_dir=$3
psql -h $cluster_master -c «SELECT pg_start_backup(‘Streaming Replication’, true)» postgres
rsync -C -a -c —delete —exclude postgresql.conf —exclude postmaster.pid \
—exclude postmaster.opts —exclude pg_log \
—exclude recovery.conf —exclude recovery.done \
—exclude pg_xlog \
$recovery_dir/ $recovery_host:$recovery_dir/
psql -h $cluster_master -c «SELECT pg_stop_backup()» postgres
ssh $recovery_host «mkdir $recovery_dir/pg_xlog; chmod 700 $recovery_dir/pg_xlog»
rec=`mktemp`
cat > $rec << EOF
standby_mode=’on’
primary_conninfo=’host=$cluster_master port=5432 user=postgres’
trigger_file=’$recovery_dir/trigger_file’
restore_command=’cp /opt/pgsql/archive/%f %p’
archive_cleanup_command=’/usr/pgsql-9.2/bin/pg_archivecleanup /opt/pgsql/archive %r’
EOF
scp $rec $recovery_host:$recovery_dir/recovery.conf
rm -f $rec
ssh $recovery_host sed -i «s/slave-hostname/$cluster_master/g» $recovery_dir/postgresql.conf
Соответственно алгоритм восстановления по этому варианту таков:
Запустить скрипт с работающего мастера, указать хостнеймы мастера, восстанавливаемого слейва и каталог pg_data. Он подготовит слейв
Запустить слейв. Если нигде ничего не напутано — он успешно подключится к мастеру и начнет работать хот-стендбаем.
Дать команду пгпулу на присоединение ноды: pcp_attach_node timeout hostname port# username password nodeID. nodeID восстанавливаемой ноды можно увидеть из вывода pcp_pool_status.
После этого нода будет вновь доступна в пгпуле.
Второй вариант — online-recovery в pgpool.
Механизм работы пгпула в этом варианте несколько неочевиден. В отличие от fileover и прочих функций — рекавери запускается не напрямую скриптом, а через вызов посгресовых функций pgpool_recovery и pgpool_remote_start, которые уже вызывают непосредственные скрипты (нафига такой изврат. ). Соответственно, чтобы все это работало — в посгрес должны быть загружены эти функции (в базу postgres и template), а для загрузки этих функций необходима шаред-библиотека под посгрес pgpool-recovery.so.
После добавления пгпуловских фукнций в посгрес нужно создать 2 скрипта в каталоге $pg_data: pgpool_remote_start (именно с таким названием!) и recover-online (тут уже название произвольное).
Первый скрипт, как видно из названия — скрипт удаленного запуска посгреса. Я предлагаю юзать системные инит-скрипты и судо, скрипт такого вида:
if [ $# -ne 2 ]
then
echo «pgpool_remote_start remote_host remote_datadir»
exit 1
fi
DEST=$1
DESTDIR=$2
PGCTL=/usr/pgsql-9.2/bin/pg_ctl
ssh -T $DEST «sudo service postgresql-9.2 start»
Естественно, для его работы в судоерс необходимо разрешить юзеру посгрес выполнять «service postgresql-9.2 start» без запроса пароля.
Второй скрипт — собственно процедура рекавери. Скрипт такой:
#!/bin/bash
if [ $2 = «slave.hostname» ]
then
cluster_master=»master.hostname»
else
cluster_master=»slave.hostname»
fi
psql -c «SELECT pg_start_backup(‘Streaming Replication’, true)» postgres
rsync -C -a -c —delete —exclude postmaster.pid \
—exclude postmaster.opts —exclude pg_log \
—exclude recovery.conf —exclude recovery.done \
$recovery_dir/ $recovery_host:$recovery_dir/
psql -c «SELECT pg_stop_backup()» postgres
rec=`mktemp`
cat > $rec << EOF
standby_mode=’on’
primary_conninfo=’host=$cluster_master port=5432 user=postgres’
trigger_file=’$recovery_dir/trigger_file’
restore_command=’cp /opt/pgsql/archive/%f %p’
archive_cleanup_command=’/usr/pgsql-9.2/bin/pg_archivecleanup /opt/pgsql/archive/%r’
EOF
scp $rec $recovery_host:$recovery_dir/recovery.conf
rm -f $rec
ssh -T $recovery_host sed -i «s/hostname/$cluster_master/g» $recovery_dir/postgresql.conf
Скрипт похож на «ручной» скрипт рекавери, но слегка изменен, т.к. приходится определять хостнейм текущего мастера (функция pgpool_recovery не передает имя мастера).
Когда все скрипты на месте, функции в базу загружены — можно переходить к конфигу пгпула.
Править нужно раздел «online recovery»:
recovery_user = ‘postgres’ # юзер (посгресовый)
recovery_password = ‘pass’ # пароль
recovery_1st_stage_command = ‘recover-online.sh’ # имя рекавери-скрипта
Остальные параметры можно не трогать. Перезапускаем пгпул и можно пробовать — механизм должен работать.
Тут и подходим к самому главному — нафига было столько геморроя? Ибо пгпул не будет пытаться самостоятельно восстанавливать ноду после падения — он будет терпиливо ждать команды pcp_recovery_node (или нажатия кнопки в pgpool-admin-е) и только после получения ее с нужными параметрами соизволит восстановить ноду.
А раз ручками лезть все равно надо — по-моему проще «ручным» скриптом восстановить ноду и жмакнуть pcp_attach_node.
3. Устраняем единую точку отказа.
Pgpool в единственном числе — общая точка отказа. Для устранения этой проблемы — необходим еще один (или более) пгпул и настроенный watchdog. В случае с 2-мя хостами посгреса, master/slave — очевидным вариантом будет установка пгпула на каждый из хостов.
Конфиги пгпула должны быть одинаковые (включая скрипты файловера, рекавери и т.п.), различия есть только в разделе watchdog в параметрах.
use_watchdog = on #включаем вотчдог
trusted_servers = ‘192.168.222.2’ #лучше указать один/несколько гарантированно доступных хостов — для исключения split-brain ситуации
wd_hostname = ‘hostname’ # имя хоста, на котором этот пгпул запущен; свое для каждого хоста
wd_port = 9000 # порт должен быть открыт на фаерволле
wd_authkey = ‘qwerty’ # ключик авторизации вотчдога, должен быть одинаковый. можно вообще без него
delegate_IP = ‘192.168.222.220’ # виртуальный айпи, по которому будет доступен кластер. именно на него нужно натравливать приложение, желающее соединиться с базой
wd_lifecheck_method = ‘heartbeat’ #метод работы вотчдога, heartbeat появился с версии 3.3. query у меня не заработал (как и вотчдог в 3.2 вообще)
wd_interval = 5
wd_heartbeat_port = 9694 #порт для хартбита. должен быть открыт в фаерволле
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 15
heartbeat_destination0 = ‘hostname2’ #имя другого хоста
heartbeat_destination_port0 = 9694
other_pgpool_hostname0 = ‘hostname2’ #имя другого хоста
other_pgpool_port0 = 5432
other_wd_port0 = 9000
Таким образом, конфиги различаются только параметрами wd_hostname, heartbeat_destination0 и other_pgpool_hostname0. Еще один важный момент — если пгпул запускается не от рута (а это так, если ставился из пакетов; да и вообще — нефиг), то для поднятия/опускания сетевых алиасов необходимо поставить suid-бит на /sbin/ifconfig и /usr/sbin/arping.
Если не ошиблись с хостнеймами и портами, то при запуске пулов они друг-друга увидят, один станет мастером и поднимет алиас, другой будет в стендбае, на случай падения мастера.