Настройка pgpool-II + PostgreSQL + Streaming replication + Hot standby в среде AWS

Всем привет!
Решил описать основные моменты настройки отказоустойчивого (HA) кластера БД PostgreSQL в IaaS среде от Amazon — AWS.

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

Терминология:

Streaming replication — означает, что ноды постгреса будут сами тянуть апдейты с мастера. Не нужен дополнительный функционал архива.
Hot standby — позволяет slave нодам обслуживать READ запросы для балансировки нагрузки, в отличии от warm standby, при котором slave сервер не обслуживает запросов клиентов, а только постоянно подтягивает себе с мастера актуальную базу. В свою очередь репликация может быть синхронной и асинхронной (sync or async).
В моем примере используется обычная связка серверов БД master-slave в ней использовать синхронную репликацию нельзя, потому, что при синхронной репликации, мастер, не сумев отослать реплику слейву, просто не выполнит запрос и будет висеть в ожидании слейва и весь смысл в подобной схеме потеряется.

Вводные данные:

Создаваемая конфигурация должна быть лишена единой точки отказа. На уровне pgpool мы будем использовать его родной функционал watchdog, чтоб иметь возможность отследить падение одной из нод и перетащить IP по которому подключается клиентское приложение. На уровне postgresql используем streaming replication + hot standby. В случае падения мастера его роль на себя оперативно возьмет slave, slave'а в master'а превратит pgpool путем создания триггер файла в $PGDATA. В случае падения слейва — будем возвращать его к жизни вручную, т.к. любые автоматические манипуляции с базами данных до добра не доводят и нештатная ситуация падения ноды в любом случае требует внимания. Во всех описаных случаях падения клиентское приложение должно продолжать функционировать с минимальным временем простоя.

В облаке AWS (Amazon Web Services) созданы 4 виртуальные машины: pgpool-1 (IP:10.0.3.11), pgpool-2(IP:10.0.3.12), db-1(IP:10.0.3.21), db-2(IP:10.0.3.22). Машины создаются сразу в VPC, чтоб иметь возможность назначать приватные адреса и они сохранялись бы между ребутами инстансов. При создании инстансов мной использовался образ ami-8e987ef9 c Ubuntu. Но если у вас есть возможность выбирать любой образ — берите Amazon Linux, почему я так думаю узнаете по тексту.

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

1. db-1 — мастер на этапе запуска связки

…
wal_level = hot_standby
#Следующий параметр игнорируется на master сервере, но ввиду того, что master и slave могут меняться местами включаем его в конфиг master'a
hot_standby = on
…

В соответствии со своими нуждами корректируем значения для
checkpoint_segments, max_wal_senders, wal_keep_segments
Для запуска репликации их можно оставить по умолчанию, а затем подтюнить, предварительно почитав wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

В pg_hba.conf настраиваем доступ к репликации, можно использовать пароль, т.к. у меня все в облаке VPC крутится и не имеет прямого доступа из вне, я просто прописал trust:

host replication postgres 10.0.3.0/24 trust

2. db-2 — слейв на этапе запуска связки

…
wal_level = hot_standby
hot_standby = on
...

Поведением standby для слейва управляет файлик recovery.conf, который должен находиться в $PGDATA.
В моем случае это была директория /var/lib/postgresql/9.3/main
recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=10.0.3.21 port=5432 user=postgres'
trigger_file = '/var/lib/postgresql/9.3/main/postgresql.trigger'

Не забываем про настройки для доступа в pg_hba.conf

3. pgpool-1

pgpool.conf:

…
backend_hostname0 = '10.0.3.21'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/9.3/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '10.0.3.22'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/9.3/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'

#Т.к. у нас Hot Standby:
load_balance_mode = on
#У нас будет работать Streaming replication
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = ''
delay_threshold = 100
#Имеет смысл когда слейвов > 1
follow_master_command = ''
#Что делать когда отпадет нода:
failover_command = '/etc/pgpool2/failover.sh %d %P %H %R'
#Что делать когда отпавшая нода вернется:
failback_command = ''
#Запускаем failover когда не можем соединиться с бекендом
fail_over_on_backend_error = on
search_primary_node_timeout = 10
#Под каким юзером будем делать online recovery
recovery_user = 'postgres'
recovery_password = ''
#На первой стадии pgpool продолжает принимать коннекты и запросы от клиентов, на второй нет.
#Выполняющийся скрипт должен лежать в $PGDATA
recovery_1st_stage_command = 'basebackup.sh'
recovery_2nd_stage_command = ''
#Сколько секунд ждем восстановления ноды
recovery_timeout = 90
#Будем использовать watchdog для мониторинга состояния pgpool
use_watchdog = on
wd_hostname = 'pgpool-1'
wd_port = 9000
wd_authkey = ''
#Виртуальный адрес на который будет коннектиться клиентское приложение:
delegate_IP = '10.0.3.10'
#Где будут лежать скрипты управления интерфейсами:
ifconfig_path = '/opt/AWS'
#Выполняем команду, чтоб назначить ноде виртуальный IP
if_up_cmd = 'if.sh up $_IP_$'
#Выполняем команду чтоб убрать у ноды виртуальный IP
if_down_cmd = 'if.sh down $_IP_$'
#Pgpool выполняет arping при перетягивании на себя виртуального интерфейса для скорейшего обновления ARP кеша
arping_cmd = ''
#Каким способом будем проверять живость соседней ноды pgpool:
#heartbeat или пытаться слать через нее запросы к базе
wd_lifecheck_method = 'heartbeat'
#Интервал в секундах между проверками:
wd_interval = 4
#На какой порт шлем:
wd_heartbeat_port = 9694
#Интервал между оправками keepalive пакетов
wd_heartbeat_keepalive = 2
#Время по истечению которого считаем молчащую ноду отпавшей:
wd_heartbeat_deadtime = 15
#Адрес соседней ноды:
heartbeat_destination0 = 'pgpool-2'
heartbeat_destination_port0 = 9694
#Можно указать на каком интерфейса работать heartbeat'у
heartbeat_device0 = ''
#Описываем параметры другой ноды:
other_pgpool_hostname0 = 'pgpool-2'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
...

4. pgpool-2
Конфиг идентичен pgpool-1, меняются описания соседней ноды с pgpool-2 на pgpool-1

В /etc/hosts на обоих нодах задаем привязку имени к ip:

10.0.3.11 pgpool-1
10.0.3.12 pgpool-2
10.0.3.21 db-1
10.0.3.22 db-2

5. Интеграция pgpool для работы с нашими базами
Со стороны pgpool-1 и pgpool-2 создаем скрипт из параметра failover_command, выполняющийся при падении ноды (у меня автоматическое действие выполняется, только при падении master ноды). Все что он делает собственно проверяет мастер упавшая нода или нет и если мастер, создает на слейве триггер-файл, который автоматически переводит слейва в режим READ-WRITE, т.е. делает его мастером:

#!/bin/bash -x
 FALLING_NODE=$1         # %d
 OLDPRIMARY_NODE=$2      # %P
 NEW_PRIMARY=$3          # %H
 PGDATA=$4               # %R
 KEY_PATH="/var/lib/postgresql/.ssh/id_rsa"

 if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then
     if [ $UID -eq 0 ]
     then
         sudo -u postgres ssh -T -i $KEY_PATH postgres@$NEW_PRIMARY "touch $PGDATA/postgresql.trigger"
        exit 0;
     fi
     ssh -T -i $KEY_PATH postgres@$NEW_PRIMARY "touch $PGDATA/postgresql.trigger"
 fi;

 exit 0;

Со стороны db-1 и db-2 устанавливаем схему pgpool для работы:

sudo -u postgres psql -f /usr/share/postgresql/9.3/extension/pgpool-recovery.sql template1

Создаем в $PGDATA скрипт pgpool_remote_start, который будет стартовать postgresql на соседней ноде [в зависимости от версии используемого вами postgresql вам может понадобиться второй, передаваемый pgpool'ом параметр, указывающий на $PGDATA директорию для ноды]:

#! /bin/sh

DEST=$1
PGCTL=/usr/bin/pg_ctlcluster
KEY_PATH="/var/lib/postgresql/.ssh/id_rsa"

ssh -T -i $KEY_PATH postgres@$DEST "$PGCTL 9.3 main stop --force;$PGCTL 9.3 main restart"

А также скрипт из параметра recovery_1st_stage_command который будет синхронизировать с текущим мастером новых слейвов (также лежит в $PGDATA рядом с pgpool_remote_start):

#! /bin/sh
datadir=$1
desthost=$2
destdir=$3
KEY_PATH="/var/lib/postgresql/.ssh/id_rsa"
PGCTL="/usr/bin/pg_ctlcluster"

ssh -T -i $KEY_PATH postgres@$desthost "$PGCTL 9.3 main stop --force"

psql -c "SELECT pg_start_backup('Streaming Replication', true)" postgres

rsync -C -a -c --delete -e ssh --exclude postgresql.conf --exclude postmaster.pid \
--exclude postmaster.opts --exclude pg_log \
--exclude recovery.conf --exclude recovery.done \
--exclude pg_xlog $datadir/ $desthost:$destdir/

ssh -T -i $KEY_PATH postgres@$desthost "cp $destdir/../recovery.done $destdir/recovery.conf;rm $destdir/postgresql.trigger"

psql -c "SELECT pg_stop_backup()" postgres

5. Для возможности выполнять команды связанные с перезапуском сервисов и копирования данных на удаленных хостах, необходимо настроить безпарольную возможность доступа:

pgpool-1 -> db-1, db-2
pgpool-2 -> db-1,db-2
db-1 -> db-2
db-2 -> db-1

Можно использовать ssh host-based, можно сгенерировать ssh ключи и разрешить их в authorized_keys.
После настройки доступа его нужно проверить от имени пользователя, который будет запускать скрипты в процессе работы pgpool, у меня это postgres:
С хоста pgpool-1 выполняем:

sudo -u postgres ssh -i /path_to_key -T postgres@db-1 id

И так для всех необходимых хостов, проверяем доступ и обновляем файлик known_hosts для ssh.

На данном этапе связку 4х нод уже можно было бы запустить для обычной работы не в среде AWS.

• Запускаем мастер хост (db-1)
• Синхронизируем с ним слейв (postgresql на нем пока не запущен), над директорией $PGDATA выполняем:
  

  mv main main.bak && sudo -u postgres pg_basebackup -h 10.0.3.21 -D /var/lib/postgresql/9.3/main -U postgres -v -P && cp recovery.done main/recovery.conf && chown postgres:postgres main/recovery.conf
  

  (recovery.done — созданный шаблон recovery.conf который ссылается на IP мастера)
• Запускаем postgresql на слейве:
  

  sudo service postgresql restart
  

• Смотрим состояние репликации через «select * from pg_stat_replication», видим примерно такую картину:
  

  application_name | walreceiver
  client_addr | 10.0.3.22
  state | streaming
  sent_location | 1/2A000848
  write_location | 1/2A000848
  flush_location | 1/2A000848
  replay_location | 1/2A000848
  sync_priority | 0
  sync_state | async

  либо же просто проверяем наличие wal sender/receiver в списке процессов на хостах db-1 и db-2.

После запуска первый запустившийся pgpool перетягивает на себя виртуальный адрес из параметра delegate_IP, выполнив для этого команду из параметра if_up_cmd (по умолчанию там просто ifconfig).
Запись в логах:

wd_escalation: escalated to master pgpool successfully

Через время, запустив второй pgpool в логах видим, что соседняя pgpool нода успешно опознана и связка заработала:

find_primary_node: primary node id is 0

Статус пула можно посмотреть одной из pcp_* команд — pcp_pool_status,pcp_node_info либо же запросами через pgpool ноды «show pool_nodes;», «show pool_pools;»
Все эти команды, а также статусы нод в пуле очень хорошо описаны в документации к pgpool — www.pgpool.net/docs/pgpool-II-3.3.2/doc/pgpool-en.html

При отключении первого pgpool'а второй перетянул бы на себя delegate_ip командой из параметра if_up_cmd.
При падении бекенда db-1 или db-2 выполняется команда из параметра failover_command.
Для возврата бекенда в pool используются команды pcp_attach_node и pcp_recovery_node

AWS Staff:

А что же происходит в среде AWS? Все тоже самое за исключением того, что IP адрес должен быть предварительно назначен сетевому интерфейсу через настройку «Assign a secondary private address» в меню Network Intefaces. Для Amazon Linux о котором я писал ранее существует возможность автоматического присвоения этого адреса работающему инстансу и дальнейшего его переползания между pgpool-1 и pgpool-2 в случае необходимости (Я лично amazon linux не тестировал, очень интересно было бы узнать насколько гладко там все это работает). В случае же неадаптированного под AWS образа мне необходимо использовать дополнительные скрипты из набора ec2-api-tools.
Последнюю версию api-tools лучше скачать с amazon.
Для работы ec2-api-tools нужна ява, поставим — apt-get install default-jre
В распакованном архиве api-tools в aws/bin будут лежать скрипты для управления aws через консоль.
Но для работы с amazon api необходимо наличие ключа авторизации.
Процесс получения аутентификационных данных подробно описан на amazon тут — docs.aws.amazon.com/IAM/latest/UserGuide/ManagingCredentials.html и тут docs.aws.amazon.com/IAM/latest/UserGuide/ManagingUserCerts.html
По первой ссылке мы узнаем как создать пользователя с ключами и назначить ему необходимую группу через меню IAM ( console.aws.amazon.com/iam/home?#users при создании ключ в открытом виде показывается владельцу один раз, если вы его не успеете записать — придется генерировать другой ). Amazon при первой попытке создать ключ настоятельно порекомендует создать отдельного пользователя в меню IAM для этих целей, вместо создания ключа под учеткой административного аккаунта AWS.
По второй ссылке мы узнаем как создать свой сертификат и прописать его все в том же IAM меню AWS:

openssl genrsa 1024 > private-key.pem
openssl pkcs8 -topk8 -nocrypt -inform PEM -in private-key.pem -out private-key-in-PCKS8-format.pem
openssl req -new -x509 -nodes -sha1 -days 3650 -key private-key.pem -outform PEM > certificate.pem

Содержимое certificate.pem заливаем на AWS в IAM. Управление сертификатами можно осуществлять через меню «Security Credentials» IAM:


После всех этих манипуляций у нас есть:
certificate.pem для параметра EC2_CERT
private-key-in-PCKS8-format.pem для EC2_PRIVATE_KEY, AWS_ACCESS_KEY и AWS_SECRET_KEY.

Можно начинать пользоваться ec2-api-tools.
Для этого я создал скрипт if.sh, который будет перетягивать delegate_IP для pgpool'ов между инстансами. Скрипт в качестве параметров получает действие, которое необходимо выполнить с интерфейсом (up/down) и желаемый ip адрес для интерфейса. Далее скрипт вычисляет подсеть для введенного IP (у меня используется /24 и я просто отрезаю последний октет, поэтому у кого маска не /24, скрипт придется допилить). Я учитываю подсеть т.к. на инстансах используются два интерфейса — основной и менеджмент, чтоб понимать на какой из них нужно вешать secondary ip.

Скрипт if.sh:

#!/bin/sh

if test $# -eq 0
    then
    echo "This scripts adds and removes ip to subinterfaces and to AWS VPC configuration."
    echo "Don't forget to set variables inside this script."
    echo
    echo Usage: $0' [up|down] IP_ADDRESS'
    echo
    exit 1
fi

#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
#CORRECT VALUES MUST BE SET PRIOR TO RUN THIS SCRIPT
#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

#Proxy if used:
export EC2_JVM_ARGS='-Dhttp.proxySet=true -Dhttps.proxySet=true -Dhttp.proxyHost=x.x.x.x -Dhttp.proxyPort=3128 -Dhttps.proxyHost=x.x.x.x -Dhttps.proxyPort=3128'
#Path to unpacked ec2-api from http://s3.amazonaws.com/ec2-downloads/ec2-api-tools.zip
export EC2_HOME=/opt
#Path to java
export JAVA_HOME=/usr
#Path to generated private key & cert (READ http://docs.aws.amazon.com/IAM/latest/UserGuide/ManagingUserCerts.html)
export EC2_PRIVATE_KEY=/opt/private-key-in-PCKS8-format.pem
export EC2_CERT=/opt/certificate.pem
#User access & secret key (READ http://docs.aws.amazon.com/IAM/latest/UserGuide/ManagingCredentials.html)
export AWS_ACCESS_KEY=YOUR_ACCESS_KEY
export AWS_SECRET_KEY=YOUR_SECRET_KEY
#Region for this EC2 instance
REGION=YOUR_REGION

#!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
AWS_PATH=$EC2_HOME
VIP=$2

subnet () {
SUB=`echo $VIP | awk '{ split($0,a,"."); print a[1]"."a[2]"."a[3]"."; }'`
SUBNET=`$AWS_PATH/bin/ec2-describe-subnets --region $REGION | grep -F $SUB | awk '{print $2}'`
echo Subnet-id: $SUBNET

if [ -z "$SUBNET" ]; then
echo "Wrong subnet!"
exit 1;
fi
Instance_ID=`/usr/bin/curl --silent http://169.254.169.254/latest/meta-data/instance-id`
echo Instance_ID=$Instance_ID
ENI_ID=`$AWS_PATH/bin/ec2-describe-instances $Instance_ID --region $REGION | cut -f 2,3 | grep $SUBNET | awk '{print $1}'`
echo ENI_ID=$ENI_ID
}

if_up () {
subnet
/usr/bin/sudo /sbin/ifconfig eth1:0 inet $VIP netmask 255.255.255.255
$AWS_PATH/bin/ec2-assign-private-ip-addresses -n $ENI_ID --secondary-private-ip-address $VIP --allow-reassignment --region $REGION
}

if_down (){
subnet
/usr/bin/sudo /sbin/ifconfig eth1:0 down
$AWS_PATH/bin/ec2-unassign-private-ip-addresses -n $ENI_ID --secondary-private-ip-address $VIP --region $REGION
}

case $1 in
              [uU][pP])
                       if_up
                       break
                       ;;
              [dD][oO][wW][nN])
                        if_down
                       break
                       ;;
              *) echo "Up/Down command missed!"
                 exit 1
esac

/usr/sbin/service networking restart > /dev/null 2>&1

Для управления реальным elastic IP можно использовать ec2-associate-address и ec2-unassign-private-ip-addresses.

Собственно вот такие телодвижения пришлось выполнить, чтобы подружить pgpool, работающий не на Amazon Linux инстанс, c AWS.