PostgreSQL — Asynchronous Replication + Pooling + Failover

Вариант простой для понимания асинхронной master-slave репликации на базе Postgresql 9.1

Впервые встала задача единоличной реализации полноценной репликации и впервые был написан мини-мануал, который и хочу здесь представить.

Для системы репликации Мастер-Слейв использовалась комбинация

• PostgreSQL 9.1 (БД) +
• Bucardo 4.5 (репликатор) +
• PgPool-II (пулер и файловер)

Bucardo

Асинхронная Postgres replication system написанная на Perl5.

Удобна встраиваемостью в постгрес и легкой, отражаемой в бд, настройкой.

Создает свою БД, в которой прописываются реплицируемые сервера, базы, таблицы, заключаемые в листы (списки).
Используется тип связи Pushdelta (Trigger. One way master-slave).
Изменение структуры не поддерживает. Работает в обе стороны, т.е. в случае временного отключения мастера, при его восстановлении, он автоматически «догонит» слейв.

Примерный план установки bucardo на Мастере:

sudo aptitude install bucardo   
#  + install Perl mods (DBI, DBD::Pg, DBIx::Safe)
sudo bucardo_ctl install           # connection settings

Далее вручную создать управляющую бд bucardo, наполнить ее из bucardo.schema (по умолчанию он прицеплен к 8.4 и в старших версиях осечка с автосозданием управляющей бд)

 sudo bucardo_ctl add db bucardo_dbname name=master_dbname # Назначение БД-мастера
 # + в бд ручками пароль в bucardo.db (дабы не возникало проблем)
 sudo bucardo_ctl add db bucardo_dbname name=slave_dbname # Назначение БД-слейва
 sudo bucardo_ctl add table tbl_name db=bucardo_dbname herd=source_name # Добавление таблиц мастера в список(herd) 
 sudo bucardo_ctl add sync sync_name type=pushdelta source=herd_name targetdb=slave_dbname # Назначение списка для репликации
 sudo bucardo_ctl start # Запуск репликации

На слейве я его устанавливала, но не заполняла (т.к. слейв один и с него идти все равно некуда)

PgPool-II

Имеет широкие возможности, страдает нехваткой мануалов. Поддерживает параллельные запросы, балансировку нагрузки, распределение коннектов к БД по пулам, а так же является FailOver'ом, т.е. автоматическим переключателем с мастера на слейв и обратно в случаях проблем с соединениями.

На debian ставится из репозитория, от версии postgres не зависит.

 sudo aptitude install pgpool2

Конфиги хранит в /etc/pgpool2/

• pgpool.conf — Основные настройки (о них чуть ниже)
• pcp.conf — Системное — можно не трогать
• pool_hba.conf — Настройка доступов подключения. Можно юзать свой, можно — посгресовый. Лучше — постгресовый

Я использовала PgPool как пулер, распределитель нагрузки и, что самое главное, файловер.

Пример настройки /etc/pgpool2/pgpool.conf (частично) Для Мастера:

 listen_addresses = '*'
 port = 9999
 socket_dir = '/var/run/postgresql'
 pcp_port = 9898
 pcp_socket_dir = '/var/run/postgresql'
 
 backend_hostname0 = master_server
 backend_port0 = 5432
 backend_weight0 = 1
 backend_data_directory0 = '/master_data'
 backend_flag0 = 'ALLOW_TO_FAILOVER'
 	
 backend_hostname1 = slave_server
 backend_port1 = 5432
 backend_weight1 = 1
 backend_data_directory1 = '/slave_data'
 backend_flag1 = 'ALLOW_TO_FAILOVER'
 
 connection_cache = on
 
 replication_mode = off
 
 load_balance_mode = on
 
 master_slave_mode = on
 master_slave_sub_mode = 'stream'
 
 parallel_mode = on
 pgpool2_hostname = ''
 
 system_db_hostname  = master_server
 system_db_port = 5432
 system_db_dbname = 'pgpool'
 system_db_schema = 'pgpool_catalog'
 system_db_user = 'pgpool'
 system_db_password = ''
 
 failover_command = '/etc/pgpool2/failover.sh %d %P %H %R'

recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = '/etc/pgpool2/recovery_1st_stage.sh'
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0

Так же в /etc/pgpool2/ создаются скрипты следующего содержания, указанные в конфиге:

''failover.sh'' — Собственно скрипт, отрабатывающий в случае падения мастера

 #!/bin/bash -x
 FALLING_NODE=$1         # %d
 OLDPRIMARY_NODE=$2      # %P
 NEW_PRIMARY=$3          # %H
 PGDATA=$4               # %R
 
 if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then
     if [ $UID -eq 0 ]
     then
         su postgres -c "ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger"
     else
         ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger
     fi
     exit 0;
 fi;
 exit 0;

''recovery_1st_stage.sh'' — Скрипт с говорящим названием

 #!/bin/bash -x
 
 PGDATA=$1
 REMOTE_HOST=$2
 REMOTE_PGDATA=$3
 
 PORT=5432
 PGHOME=/home/yugo-n/pgsql-9.2.1
 ARCH=$PGHOME/data/arch
 
 rm  -rf $ARCH/*
 
 ssh -T postgres@$REMOTE_HOST "
 LD_LIBRARY_PATH=$PGHOME/lib:LD_LIBRARH_PATH;
 rm -rf $REMOTE_PGDATA
 $PGHOME/bin/pg_basebackup -h $HOSTNAME -U r_user -D $REMOTE_PGDATA -x -c fast
 rm $REMOTE_PGDATA/trigger"
 
 ssh -T postgres@$REMOTE_HOST "rm -rf $ARCH/*"
 ssh -T postgres@$REMOTE_HOST "mkdir -p $REMOTE_PGDATA/pg_xlog/archive_status"
 
 ssh -T postgres@$REMOTE_HOST "
 cd $REMOTE_PGDATA;
 cp postgresql.conf postgresql.conf.bak;
 sed -e 's/#*hot_standby = off/hot_standby = on/' postgresql.conf.bak > postgresql.conf;
 rm -f postgresql.conf.bak;
 cat > recovery.conf << EOT
 standby_mode = 'on'
 primary_conninfo = 'host="$HOSTNAME" port=$PORT user=r_user'
 restore_command = 'scp $HOSTNAME:$ARCH/%f %p'
 trigger_file = '$PGDATA/trigger'
 EOT
 "

Важный момент! Нужно, чтобы все файлы/папки используемые pgpool'ом были с owner postgres::postgres

Для Слейва все почти тоже самое, только с master_slave_mode = off и failover_command = ''

Запускается PgPool простейшим консольным

sudo pgpool

Таким образом получаем Мастер-моноСлейв репликацию с распределением нагрузки и файловером.

Подключение идет всегда только к мастеру через порт pgPool'a (здесь — 9999).
При нормальной работе идет запись в мастер, бэкэндово реплицируется на слейв, а чтение производится и с того и с другого.
В случае отключения слейва, при возобновлении его работы все данные времени простоя автоматически дореплицируются.
В случае отключения мастера, без разрыва пользовательского соединения pgpool перенаправляет чтение и запись полностью на реплику, временно делая ее «мастером». При поднятии мастера, он догоняет все данные со слейва и происходит обратное переключение, опять же без разрыва пользовательского соединения.

Есть определенная проблема в том, что не удалось как то создать управление правами, чтобы при живом мастере на слейв были бы права только RO, а при его падении переключались на RW (а потом при восстановлении — обратно), но поскольку внешнее обращение идет только к адресу Мастера, то опасность остается только в шаловливых ручках разработчиков.

Надеюсь, статейка найдет применение у начинающих DBA.
И всем спасибо за внимание!