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.
И всем спасибо за внимание!