КЛАСТЕР високої доступності на postgresql 9.6 + repmgr + pgbouncer + haproxy + keepalived + контроль через telegram

image
На сьогоднішній день процедура реалізації «failover» Postgresql є однією з найбільш простих і інтуїтивно зрозумілих. Для її реалізації необхідно визначитися зі сценаріями файловера — це запорука успішної роботи кластера, протестувати його роботу. В двох словах — налаштовується реплікація, найчастіше асинхронна, і в разі відмови поточного майстра, інша нода(standby) стає поточному «майстром», інші ноди standby починають слідувати за новим майстром.
На сьогоднішній день repmgr підтримує сценарій автоматичного Failover — autofailover, що дозволяє підтримувати кластер в робочому стані після виходу з ладу ноди-майстра без миттєвого втручання співробітника, що важливо, так як не відбувається великого падіння UPTIME. Для повідомлень використовуємо telegram.

З'явилася необхідність у зв'язку з розвитком внутрішніх сервісів реалізувати систему зберігання БД на Postgresql + реплікація + балансування + failover(відмовостійкість). Як завжди в інтернеті начебто і є, але все воно застаріле або на практиці не реалізується в тому вигляді, в якому воно подано. Було вирішено представити дане рішення, щоб в майбутньому у фахівців, які вирішили реалізувати подібну схему було уявлення, як це робиться, і щоб новачкам було легко це реалізувати дотримуючись даної інструкції. Постаралися описати все якомога детальніше, вникнути у всі нюанси і особливості.


Отже, що ми маємо: 5 VM з debian 8,Postgresql 9.6 + repmgr (для управління кластером), балансування і HA на базі HAPROXY (для балансування та високої доступності веб додатків і баз даних) і легкого менеджера підключень Pgbouncer, keepalived для міграції ip адреси(VIP) між нодами,5-я witness нода для контролю кластера і запобігання «split brain» ситуацій, коли не могла бути визначена наступна майстер нода після відмови поточного майстра. Повідомлення через telegram( без нього як без рук).
Пропишемо ноди /etc/hosts — для зручності, так як в подальшому все буде оперувати з доменними іменами.

файл /etc/hosts
10.1.1.195 - pghost195
10.1.1.196 - pghost196
10.1.1.197 - pghost197
10.1.1.198 - pghost198
10.1.1.205 - pghost205


VIP 10.1.1.192 — запис, 10.1.1.202 — roundrobin(балансування/тільки читання).

Установка Postgresql 9.6 pgbouncer haproxy repmgr
Ставимо на всі ноди
Установка Postgresql-9.6 та repmgr debian 8
touch /etc/apt/sources.list.d/pgdg.list
echo "deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
wget http://ftp.ru.debian.org/debian/pool/main/p/pkg-config/pkg-config_0.28-1_amd64.deb
dpkg -i pkg-config_0.28-1_amd64.deb
apt-get install postgresql-9.6-repmgr libevent-dev -y


Відключаємо автозапуск Postgresql при старті системи — всіма процесами буде керувати користувач postgres. Так само це необхідно, для того, щоб не було ситуацій, коли у нас зможе виявитися дві майстер-ноди, після відновлення однієї після збою живлення, наприклад.
Як відключити автозапуск
nano /etc/postgresql/9.6/main/start.conf 
замінюємо auto на manual


Налаштування ssh з'єднання без пароля — між усіма нодами(робимо на всіх серверах)
Налаштуємо підключення між усіма серверами і до самого себе через користувача postgres(через користувача postgres підключається також repmgr).
Встановимо пакети, які нам знадобляться для роботи(відразу ставимо)
Ставимо ssh і rsync
apt-get install openssh-server rsync -y


Для початку встановимо йому локальний пароль для postgres (відразу зробимо це на всіх ноди).
Прихований текст
passwd postgres 


Введемо новий пароль.
Ок.
Далі налаштуємо ssh з'єднання
Прихований текст
su postgres
cd ~
ssh-keygen


Генеруємо ключ — без пароля.
Ставимо ключ на інші ноди
Прихований текст
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@pghost195
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@pghost196
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@pghost197
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@pghost198
ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@pghost205


Для того, щоб ssh не питала чи довіряєте ви хосту і не видавала інші попередження і обмеження, що стосуються політики безпеки, можемо додати в файл
Прихований текст
nano /etc/ssh/ssh_config
StrictHostKeyChecking no
UserKnownHostsFile=/dev/null


Рестартуем ssh.
Дана опція зручна коли ви не занадто дбаєте про безпеку, наприклад для тестування кластера.
Перейдемо на ноду 2,3,4 і все повторимо. Тепер ми можемо гуляти без паролів між нодами для перемикання їх стану(призначення нового майстра і standby).
Ставимо pgbouncer з git
Встановимо необхідні пакети для складання
Прихований текст
apt-get install libpq-dev checkinstall build-essential libpam0g-dev libssl-dev libpcre++-dev libtool automake checkinstall gcc+ git -y 
cd /tmp
git clone https://github.com/pgbouncer/pgbouncer.git
cd pgbouncer
git submodule init
git submodule update
./autogen.sh
wget https://github.com/libevent/libevent/releases/download/release-2.0.22-stable/libevent-2.0.22-stable.tar.gz
tar -xvf libevent-2.0.22-stable.tar.gz
cd libevent*
./configure
checkinstall
cd ..


Якщо хочете postgresql з PAM авторизацією — то ставимо ще будинок модуль і при configure ставимо --with-pam
Прихований текст
./configure --prefix=/usr/local --with-libevent=libevent-prefix --with-pam
make -j4
mkdir -p /usr/local/share/doc;
mkdir -p /usr/local/share/man;
checkinstall


Ставимо версію — 1.7.2 (на листопад 2016 року).
Готове. Бачимо
Прихований текст
Done. The new package has been installed and saved to
/tmp/pgbouncer/pgbouncer_1.7.2-1_amd64.deb
You can remove it from your system anytime using: 
dpkg -r pgbouncer_1.7.2-1_amd64.deb 


Обов'язково налаштуємо оточення — додамо змінну PATH=/usr/lib/postgresql/9.6/bin:$PATH(на кожній ноде).
Додамо в файл ~/.bashrc
Прихований текст
su postgres
cd ~
nano .bashrc


Вставимо код
Прихований текст
PATH=$PATH:/usr/lib/postgresql/9.6/bin
export PATH
export PGDATA="$HOME/9.6/main"


Сохранимся.
Скопіюємо файл .bashrc інші ноди
Прихований текст
su postgres
cd ~
scp .bashrc postgres@pghost195:/var/lib/postgresql
scp .bashrc postgres@pghost196:/var/lib/postgresql
scp .bashrc postgres@pghost197:/var/lib/postgresql
scp .bashrc postgres@pghost198:/var/lib/postgresql
scp .bashrc postgres@pghost205:/var/lib/postgresql


Налаштування сервера в якості майстра(pghost195)
Відредагуємо конфіг /etc/postgresql/9.6/main/postgresql.conf — Приводимо до вигляду необхідні опції(просто додамо в кінець файлу).
Прихований текст
listen_addresses='*'
wal_level = 'hot_standby'
archive_mode = on
wal_log_hints = on
archive_command = 'cd .'
max_wal_senders = 10
max_replication_slots = 1 # Такий же в /etc/repmgr.conf !!must be!!
hot_standby = on
shared_preload_libraries = 'repmgr_funcs, pg_stat_statements' #### колективна бібліотека repmgr і статистики postgres
max_connections = 800
max_wal_senders = 10
wal_keep_segments = 3000 # чим більше, тим довше буде журнал тим простіше буде standby ноде наздогнати master'a.
max_replication_slots = 8
port = 5433
pg_stat_statements.max = 10000
pg_stat_statements.track = all


Як ми бачимо — будемо запускати postgresql на порту 5433 — тому-що дефолтний порт для додатків будемо використовувати для інших цілей, а саме для балансування, проксі серверів і failover'a. Ви ж можете використовувати будь-який порт, як вам зручно.

Налаштуємо файл підключень

nano /etc/postgresql/9.6/main/pg_hba.conf

Приведемо до вигляду
Прихований текст
# IPv6 local connections:
host all all ::1/128 md5

local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5

#######################################Тут ми налаштували з'єднання для управління реплікацією і управління станом нсд (MASTER, STAND BY).
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 10.1.1.0/24 trust
local repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 10.1.1.0/24 trust
######################################
host all all 0.0.0.0/32 md5 #######Підключення для всіх за паролем
#####################################


Застосуємо права до конфигам, інакше буде лаятися на pg_hba.conf
Прихований текст
chown -R -v postgres /etc/postgresql


Стартуємо postgres(від postgres user).
Прихований текст
pg_ctl -D /etc/postgresql/9.6/main --log=/var/log/postgresql/postgres_screen.log start


Налаштування користувачів і бази на Master-сервері(pghost195).
Прихований текст
su postgres 
cd ~


Створимо користувача repmgr.
Прихований текст
psql
# create role repmgr with superuser noinherit;
# ALTER ROLE repmgr WITH LOGIN;
# create database repmgr;
# GRANT ALL PRIVILEGES on DATABASE repmgr to repmgr;
# ALTER USER repmgr SET search_path TO repmgr_test, "$user", public;



Створимо користувача test_user з паролем 1234
Прихований текст
create user test_user;
ALTER USER test_user WITH PASSWORD '1234';


Конфігуруємо repmgr на master
Прихований текст
nano /etc/repmgr.conf


Вміст
Прихований текст
cluster=etagi_test
node=1
node_name=node1
use_replication_slots=1
conninfo='host=pghost195 port=5433 user=repmgr dbname=repmgr'
pg_bindir=/usr/lib/postgresql/9.6/bin


Сохраняемся.
Реєструємо сервер як майстер.
Прихований текст
su postgres
repmgr -f /etc/repmgr.conf master register


Дивимося наш статус
Прихований текст
repmgr -f /etc/repmgr.conf cluster show


Бачимо
Прихований текст
Role | Name | Upstream | Connection String
----------+-------|----------|--------------------------------------------------
* master | node1 | | host=pghost195 port=5433 user=repmgr dbname=repmgr


Йдемо далі.
Налаштування слейвов(standby) — pghost196,pghost197,pghost198
Конфігуруємо repmgr на slave1(pghost197)
nano /etc/repmgr.conf — створюємо конфіг
Вміст
Прихований текст
cluster=etagi_test
node=2
node_name=node2
use_replication_slots=1
conninfo='host=pghost196 port=5433 user=repmgr dbname=repmgr'
pg_bindir=/usr/lib/postgresql/9.6/bin


Сохраняемся.
Реєструємо сервер як standby
Прихований текст
su postgres
cd ~/9.6/
rm -rf main/*
repmgr -h pghost1 -p 5433 -U repmgr -d repmgr -D main -f /etc/repmgr.conf --copy-external-config-files=pgdata --verbose standby clone
pg_ctl -D /var/lib/postgresql/9.6/main --log=/var/log/postgresql/postgres_screen.log start


Будуть скопійовані конфіги на підставі яких відбувається перемикання станів master і standby серверів.
Переглянемо файли, які лежать в корені папки /var/lib/postgresql/9.6/main — обов'язково повинні бути ці файли.
Прихований текст
PG_VERSION backup_label
pg_hba.conf pg_ident.conf postgresql.auto.conf postgresql.conf recovery.conf


Реєструємо сервер в кластері
Прихований текст
su postgres
repmgr -f /etc/repmgr.conf standby register; repmgr -f /etc/repmgr.conf cluster show
Перегляд стану кластера
repmgr -f /etc/repmgr.conf cluster show
Бачимо
<spoiler title="">
<source lang="bash">
Role | Name | Upstream | Connection String
----------+-------|----------|--------------------------------------------------
* master | node195 | | host=pghost195 port=5433 user=repmgr dbname=repmgr
standby | node196 | node1 | host=pghost196 port=5433 user=repmgr dbname=repmgr


Налаштування другого stand-by — pghost197
Конфігуруємо repmgr на pghost197
nano /etc/repmgr.conf — створюємо конфіг
Вміст
Прихований текст
cluster=etagi_test
node=3
node_name=node3
use_replication_slots=1
conninfo='host=pghost197 port=5433 user=repmgr dbname=repmgr'
pg_bindir=/usr/lib/postgresql/9.6/bin


Сохраняемся.
Реєструємо сервер як standby
Прихований текст
su postgres
cd ~/9.6/
rm -rf main/*
repmgr -h pghost195 -p 5433 -U repmgr -d repmgr -D main -f /etc/repmgr.conf --copy-external-config-files=pgdata --verbose standby clone


або
Прихований текст
repmgr -D /var/lib/postgresql/9.6/main -f /etc/repmgr.conf -d repmgr -p 5433 -U repmgr -R postgres --verbose --force --rsync-only --copy-external-config-files=pgdata standby clone -h pghost195


Дана команда з опцією -r/--rsync-only — використовується у деяких випадках, наприклад, коли скопійований каталог — це каталог даних відмовив сервера з активним сайтом реплікації.
Також будуть скопійовані конфіги на підставі яких відбувається перемикання станів master і standby серверів.
Переглянемо файли, які лежать в корені папки /var/lib/postgresql/9.6/main — обов'язково повинні бути наступні файли:
Прихований текст
PG_VERSION backup_label
pg_hba.conf pg_ident.conf postgresql.auto.conf postgresql.conf recovery.conf


Стартуємо postgres(від postgres)
Прихований текст
pg_ctl -D /var/lib/postgresql/9.6/main --log=/var/log/postgresql/postgres_screen.log start


Реєструємо сервер в кластері
Прихований текст
su postgres
repmgr -f /etc/repmgr.conf standby register


Перегляд стану кластера
Прихований текст
repmgr -f /etc/repmgr.conf cluster show


Бачимо
Прихований текст
Role | Name | Upstream | Connection String
----------+-------|----------|--------------------------------------------------
* master | node1 | | host=pghost1 port=5433 user=repmgr dbname=repmgr
standby | node2 | node1 | host=pghost2 port=5433 user=repmgr dbname=repmgr
standby | node3 | node1 | host=pghost2 port=5433 user=repmgr dbname=re



Налаштування каскадної реплікації.
Ви також можете налаштувати каскадну реплікацію. Розглянемо приклад.
Конфігуруємо repmgr на pghost198 від pghost197
nano /etc/repmgr.conf — створюємо конфіг
Вміст
Прихований текст
cluster=etagi_test
node=4
node_name=node4
use_replication_slots=1
conninfo='host=pghost198 port=5433 user=repmgr dbname=repmgr'
pg_bindir=/usr/lib/postgresql/9.6/bin
upstream_node=3


Сохраняемся. Як ми бачимо, що в upstream_node ми вказали node3, якою є pghost197.
Реєструємо сервер як standby від standby
Прихований текст
su postgres
cd ~/9.6/
rm -rf main/*
repmgr -h pghost197 -p 5433 -U repmgr -d repmgr -D main -f /etc/repmgr.conf --copy-external-config-files=pgdata --verbose standby clone


Стартуємо postgres(від postgres)
Прихований текст
pg_ctl -D /var/lib/postgresql/9.6/main --log=/var/log/postgresql/postgres_screen.log start


Реєструємо сервер в кластері
Прихований текст
su postgres
repmgr -f /etc/repmgr.conf standby register


Перегляд стану кластера
Прихований текст
repmgr -f /etc/repmgr.conf cluster show


Бачимо
Прихований текст
Role | Name | Upstream | Connection String
----------+-------|----------|--------------------------------------------------
* master | node1 | | host=pghost195 port=5433 user=repmgr dbname=repmgr
standby | node2 | node1 | host=pghost196 port=5433 user=repmgr dbname=repmgr
standby | node3 | node1 | host=pghost197 port=5433 user=repmgr dbname=repmgr
standby | node4 | node3 | host=pghost198 port=5433 user=repmgr dbname=repmgr


Настройка Автоматичного Failover'а.
image
Отже ми закінчили налаштування потокової реплікації. Тепер перейдемо до налаштування автоматичного перемикання — активації нового майстра з stand-by сервера. Для цього необхідно додати нові секції в файл /etc/repmgr.conf на stand-by серверах. На майстрі цього бути не повинно!!!
!!! Конфіги на standby(slave's) повинні відрізнятися — як у прикладі нижче. Виставимо різний час(master_responce_timeout)!!!
Додаємо рядка на pghost196 в /etc/repmgr.conf
Прихований текст
####### АВТОМАТИЧНИЙ FAILOVER#######ТІЛЬКИ НА STAND BY##################
master_response_timeout=20
reconnect_attempts=5
reconnect_interval=5
failover=automatic
promote_command='sh /etc/postgresql/failover_promote.sh'
follow_command='sh /etc/postgresql/failover_follow.sh'
#loglevel=NOTICE
#logfacility=STDERR
#logfile='/var/log/postgresql/repmgr-9.6.log'
priority=90 # a value of zero or less prevents the node being promoted to master



Додаємо рядка на pghost197 в /etc/repmgr.conf
Прихований текст
####### АВТОМАТИЧНИЙ FAILOVER#######ТІЛЬКИ НА STAND BY##################
master_response_timeout=20
reconnect_attempts=5
reconnect_interval=5
failover=automatic
promote_command='sh /etc/postgresql/failover_promote.sh'
follow_command='sh /etc/postgresql/failover_follow.sh'
#loglevel=NOTICE
#logfacility=STDERR
#logfile='/var/log/postgresql/repmgr-9.6.log'
priority=70 # a value of zero or less prevents the node being promoted to master



Додаємо рядка на pghost198 в /etc/repmgr.conf
Прихований текст
####### АВТОМАТИЧНИЙ FAILOVER#######ТІЛЬКИ НА STAND BY##################
master_response_timeout=20
reconnect_attempts=5
reconnect_interval=5
failover=automatic
promote_command='sh /etc/postgresql/failover_promote.sh'
follow_command='sh /etc/postgresql/failover_follow.sh'
#loglevel=NOTICE
#logfacility=STDERR
#logfile='/var/log/postgresql/repmgr-9.6.log'
priority=50 # a value of zero or less prevents the node being promoted to master


Як ми бачимо всі налаштування автофейоловера ідентичні, різниця лише в priority. Якщо 0, то даний Standby ніколи не стане Master. Цей параметр визначатиме черговість спрацьовування failover'a, тобто менше число говорить про більший пріоритет, значить після відмови master сервера його функції на себе візьме pghost197.

Також необхідно додати наступні рядки в файл /etc/postgresql/9.6/main/postgresql.conf (stand-by сервера!!!!!!)
Прихований текст
shared_preload_libraries = 'repmgr_funcs'


Для запуску демона детектування автоматичного перемикання необхідно:
Прихований текст
su postgres
repmgrd -f /etc/repmgr.conf -p /var/run/postgresql/repmgrd.pid -m -d -v >> /var/log/postgresql/repmgr.log 2>&1


Процес repmgrd буде запущений як демон. Дивимося
Прихований текст
ps aux | grep repmgrd


Бачимо
Прихований текст
postgres 2921 0.0 0.0 59760 5000 ? S 16:54 0:00 /usr/lib/postgresql/9.6/bin/repmgrd -f /etc/repmgr.conf -p /var/run/postgresql/repmgrd.pid -m -d -v
postgres 3059 0.0 0.0 12752 2044 pts/1 S+ 16:54 0:00 grep repmgrd


Все ок. Йдемо далі.

Перевіримо роботу автофейловера
Прихований текст
su postgres
psql repmgr
repmgr # SELECT * FROM repmgr_etagi_test.repl_nodes ORDER BY id;


id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active 
----+---------+------------------+------------+-------+---------------------------------------------------+---------------+----------+--------
1 | master | | etagi_test | node1 | host=pghost195 port=5433 user=repmgr dbname=repmgr | repmgr_slot_1 | 100 | t
2 | standby | 1 | etagi_test | node2 | host=pghost196 port=5433 user=repmgr dbname=repmgr | repmgr_slot_2 | 100 | t
3 | standby | 1 | etagi_test | node3 | host=pghost197 port=5433 user=repmgr dbname=repmgr | repmgr_slot_3 | 100 | t


Поки все нормально — тепер проведемо тест. Зупинимо майстер — pghost195
Прихований текст
su postgres
pg_ctl -D /etc/postgresql/9.6/main -m immediate stop


В логах на pghost196
Прихований текст
tail -f /var/log/postgresql/*


Бачимо
Прихований текст
[2016-10-21 16:58:34] [NOTICE] promoting standby
[2016-10-21 16:58:34] [ПОВІДОМЛЕННЯ] promoting server using '/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main promote'
[2016-10-21 16:58:36] [ПОВІДОМЛЕННЯ] STANDBY PROMOTE successful



В логах на pghost197
Прихований текст
tail -f /var/log/postgresql/*


Бачимо
Прихований текст
2016-10-21 16:58:39] [NOTICE] node 2 is the best candidate for new master, attempting to follow...
[2016-10-21 16:58:40] [ERROR] connection failed to database: could not connect to server: Connection refused
Is running on the server host "pghost195" (10.1.1.195) and accepting
TCP/IP connections on port 5433?


[2016-10-21 16:58:40] [ПОВІДОМЛЕННЯ] restarting server using '/usr/lib/postgresql/9.6/bin/pg_ctl -w -D /var/lib/postgresql/9.6/main -m fast restart'
[2016-10-21 16:58:42] [ПОВІДОМЛЕННЯ] node 3 now following new upstream node 2


Все працює. У нас новий майстер — pghost196, pghost197,pghost198 — тепер слухає stream від pghost2.

Повернення впав майстра в лад!!!
image
Не можна просто так взяти і повернути впав майстер в дію. Але він повернеться в якості слейва.
Postgres повинна бути зупинена перед процедурою повернення.
На ноде, яка відмовила створюємо скрипт. У цьому скрипт вже налаштовано повідомлення телеграм, і налаштована перевірка за тригера — якщо створений файл /etc/postgresql/disabled, то відновлення не відбудеться. Так само створимо файл /etc/postgresql/current_master.list з вмістом — іменем master.
/etc/postgresql/current_master.list
pghost196


Назвемо скрипт «register.sh» і розмістимо в каталозі /etc/postgresql
Скрипт відновлення ноди в кластер як standby
/etc/postgresql/register.sh.

trigger="./etc/postgresql/disabled"
TEXT=""hostname -f'_postgresql_disabled_and_don't_be_started.You_must_delete_file_/etc/postgresql/disabled'"
TEXT
if [ -f "$trigger" ]
then
echo "Current server is disabled"
sh /etc/postgresql/telegram.sh $TEXT
else

pkill repmgrd
pg_ctl stop
rm -rf /var/lib/postgresql/9.6/main/*;
mkdir /var/run/postgresql/9.6-main.pg_stat_tmp;
repmgr -D /var/lib/postgresql/9.6/main -f /etc/repmgr.conf -d repmgr -p 5433 -U repmgr -R postgres --verbose --force --rsync-only --copy-external-config-files=pgdata standby clone -h $(cat /etc/postgresql/current_master.list);
/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main --log=/var/log/postgresql/postgres_screen.log start;
/bin/sleep 5;
repmgr -f /etc/repmgr.conf --force standby register;
echo "Вывод стану кластера";
repmgr -f /etc/repmgr.conf cluster show;
sh /etc/postgresql/telegram.sh $TEXT
sh /etc/postgresql/repmgrd.sh;
ps aux | grep repmgrd;
fi


Як ви бачите у нас також є в скрипті файл repmgrd.sh і telegram.sh. Вони також повинні знаходиться в каталозі /etc/postgresql.
/etc/postgresql/repmgrd.sh
#!/bin/bash
pkill repmgrd
rm /var/run/postgresql/repmgrd.pid;
repmgrd -f /etc/repmgr.conf -p /var/run/postgresql/repmgrd.pid -m -d -v >> /var/log/postgresql/repmgr.log 2>&1;
ps aux | grep repmgrd;


Скрипт для відправки в телеграм.
telegram.sh.

USERID="Юзер_ид_пользователей_телеграм_через_пробел"
CLUSTERNAME="PGCLUSTER_RIES"
KEY="Ключ_бота_телеграм"
TIMEOUT="10"
EXEPT_USER="root"
URL="https://api.telegram.org/bot$KEY/sendMessage"
DATE_EXEC="$(date "+%d %b %Y %H:%M")"
TMPFILE='/etc/postgresql/ipinfo-$DATE_EXEC.txt'
IP=$(echo $SSH_CLIENT | awk '{print $1}')
PORT=$(echo $SSH_CLIENT | awk '{print $3}')
HOSTNAME=$(hostname -f)
IPADDR=$(hostname -I | awk '{print $1}')
curl http://ipinfo.io/$IP -s -o $TMPFILE
#ORG=$(cat $TMPFILE | jq '.org' | sed 's/"//g')
TEXT=$1
for IDTELEGRAM in $USERID
do
curl -s --max-time $TIMEOUT -d "chat_id=$IDTELEGRAM&disable_web_page_preview=1&text=$TEXT" $URL > /dev/null
done
rm $TMPFILE



Відредагуємо конфіг repmgr на такому майстрі
/etc/repmgr.conf
cluster=etagi_cluster1
node=1
node_name=node195
use_replication_slots=8
conninfo='host=pghost195 port=5433 user=repmgr dbname=repmgr'
pg_bindir=/usr/lib/postgresql/9.6/bin
#######АВТОМАТИЧНИЙ FAILOVER#######ТІЛЬКИ НА STAND BY##################

master_response_timeout=20
reconnect_attempts=5
reconnect_interval=5
failover=automatic
promote_command='sh /etc/postgresql/failover_promote.sh'
follow_command='sh /etc/postgresql/failover_follow.sh'
#loglevel=NOTICE
#logfacility=STDERR
#logfile='/var/log/postgresql/repmgr-9.6.log'
priority=95 # a value of zero or less prevents the node being promoted to master


Сохранимся.
Тепер запустимо наш скрипт, на відмовила ноде. Не забуваємо про права(postgres) для файлів.
sh /etc/postgresq/register.sh
Побачимо
Прихований текст
[2016-10-31 15:19:53] [NOTICE] notifying master about backup completion...
ЗАУВАЖЕННЯ: команда pg_stop_backup завершена, всі необхідні сегменти WAL заархівовані
[2016-10-31 15:19:54] [ПОВІДОМЛЕННЯ] standby clone (using rsync) complete
[2016-10-31 15:19:54] [ПОВІДОМЛЕННЯ] you can now start your server, PostgreSQL
[2016-10-31 15:19:54] [HINT] for example : pg_ctl -D /var/lib/postgresql/9.6/main start
[2016-10-31 15:19:54] [HINT] After starting the server, you need to register this standby with "repmgr standby register"
сервер запускається
[2016-10-31 15:19:59] [ПОВІДОМЛЕННЯ] standby node correctly for registered cluster etagi_cluster1 with id 2 (conninfo: host=pghost196 port=5433 user=repmgr dbname=repmgr)
Висновок стану кластера
Role | Name | Upstream | Connection String
----------+---------|----------|----------------------------------------------------
* standby | node195 | | host=pghost195 port=5433 user=repmgr dbname=repmgr
master | node196 | node195 | host=pghost197 port=5433 user=repmgr dbname=repmgr
standby | node197 | node195 | host=pghost198 port=5433 user=repmgr dbname=repmgr
standby | node198 | node195 | host=pghost196 port=5433 user=repmgr dbname=repmgr
postgres 11317 0.0 0.0 4336 716 pts/0 S+ 15:19 0:00 sh /etc/postgresql/repmgrd.sh
postgres 11322 0.0 0.0 59548 3632 ? R 15:19 0:00 /usr/lib/postgresql/9.6/bin/repmgrd -f /etc/repmgr.conf -p /var/run/postgresql/repmgrd.pid -m -d -v
postgres 11324 0.0 0.0 12752 2140 pts/0 S+ 15:19 0:00 grep repmgrd
postgres 11322 0.0 0.0 59548 4860 ? S 15:19 0:00 /usr/lib/postgresql/9.6/bin/repmgrd -f /etc/repmgr.conf -p /var/run/postgresql/repmgrd.pid -m -d -v
postgres 11327 0.0 0.0 12752 2084 pts/0 S+ 15:19 0:00 grep repmgrd


Як ми бачимо скрипт відпрацював, ми отримали попередження і побачили стан кластера.
Реалізації процедури Switchover(зміни майстри вручну).
Припустимо настала така ситуація, коли вам необхідно поміняти місцями майстер і певний standby.
Припустимо хочемо зробити майстром pghost195 замість став по фейловеру pghost196, після його відновлення в якості слейва. Наші кроки.
На pghost195
Прихований текст
su postgres
repmgr -f /etc/repmgr.conf standby switchover 
Бачимо
[2016-10-26 15:29:42] [ПОВІДОМЛЕННЯ] replication slot "repmgr_slot_1" deleted on former master
[2016-10-26 15:29:42] [ПОВІДОМЛЕННЯ] switchover was successful


Тепер нам необхідно дати команду реплік, крім старого майстра, дати команду на перенесення на новий майстер
На pghost197
Прихований текст
su postgres
repmgr -f /etc/repmgr.conf standby follow
repmgr -f /etc/repmgr.conf cluster show;


Бачимо що ми слідуємо за новим майстром.
На pghost198 — те ж саме
Прихований текст
su postgres
repmgr -f /etc/repmgr.conf standby follow
repmgr -f /etc/repmgr.conf cluster show;


Бачимо що ми слідуємо за новим майстром.
На pghost196 — він був попереднім майстром, у якого ми відібрали права
Прихований текст
su postgres
repmgr -f /etc/repmgr.conf standby follow


Бачимо помилку
Прихований текст
[2016-10-26 15:35:51] [ERROR] Slot 'repmgr_slot_2' already exists as an active slot


Стопаем pghost196
Прихований текст
pg_ctl stop


Для її виправлення йдемо на phgost195(новий майстер)
Прихований текст
su postgres
psql repmgr
#select pg_drop_replication_slot('repmgr_slot_2');


Бачимо
Прихований текст
pg_drop_replication_slot 
--------------------------
(1 row)


Йдемо на pghost196, і робимо все за аналогією з пунктом.

Створення і використання witness ноди
image
Witness нода використовується для керування кластером, у разі настання файловера і виступає свого роду арбітром, стежить за тим щоб не наступали конфліктні ситуації при виборі нового майстра. Вона не є активною нодою в плані використання як standby сервера, може бути встановлена на тій же ноде що і postgres або на окремій ноде.

Додамо ще одну ноду pghost205 для управління кластером( настройка абсолютно аналогічна налаштуванні слейва), толь буде відрізнятися спосіб копіювання:
Прихований текст
repmgr -h pghost195 -p 5433 -U repmgr -d repmgr -D main -f /etc/repmgr.conf --force --copy-external-config-files=pgdata --verbose witness create;
або
repmgr -D /var/lib/postgresql/9.6/main -f /etc/repmgr.conf -d repmgr -p 5433 -U repmgr -R postgres --verbose --force --rsync-only --copy-external-config-files=pgdata witness create -h pghost195;


Побачимо висновок
Прихований текст
2016-10-26 17:27:06] [WARNING] --copy-external-config-files can only be used when executing STANDBY CLONE
[2016-10-26 17:27:06] [ПОВІДОМЛЕННЯ] using configuration file "./etc/repmgr.conf"
Файли, що відносяться до цієї СУБД, будуть належати користувачеві "postgres".
Від його імені також буде запускатися процес сервера.
Кластер баз даних буде ініціалізований з локаллю "uk_ua.UTF-8".
Кодування БД за замовчуванням, обрана у відповідності з налаштуваннями: "UTF8".
Обрана конфігурація текстового пошуку за умовчанням "russian".


Контроль цілісності сторінок даних відключений.


виправлення прав для існуючого каталогу main ок... 
створення підкаталогів... ок
вибирається значення max_connections... 100
вибирається значення shared_buffers... 128MB
вибір реалізації динамічної поділюваної пам'яті ... posix
створення конфігураційних файлів... ок
виконується підготовчий скрипт ... ок
виконується заключна ініціалізація ... ок
збереження даних на диску... ок


ПОПЕРЕДЖЕННЯ: використовується перевірка справжності "trust" для локальних підключень.
Інший метод можна вибрати, відредагувавши pg_hba.conf або використовуючи ключі -A,
--auth-local або --auth-host при наступному виконанні initdb.


Готове. Тепер ви можете запустити сервер баз даних:


/usr/lib/postgresql/9.6/bin/pg_ctl -D main -l logfile start


очікування запуску сервера....ПОВІДОМЛЕННЯ: система БД була вимкнена: 2016-10-26 17:27:07 YEKT
ПОВІДОМЛЕННЯ: Захист від накладення мультитранзакций зараз включена
ПОВІДОМЛЕННЯ: система БД готова приймати підключення
ПОВІДОМЛЕННЯ: процес запуску автоочищення створений
готово
сервер запущений
Warning: Permanently added 'pghost1,10.1.9.1' (ECDSA) to the list of known hosts.
receiving incremental file list
pg_hba.conf
1,174 100% 1.12 MB/s 0:00:00 (xfr#1, to-chk=0/1)
ПОВІДОМЛЕННЯ: отримано SIGHUP, файли конфігурації перезавантажуються
сигнал відправлений сервера
[2016-10-26 17:27:10] [ПОВІДОМЛЕННЯ] configuration has been successfully copied to the witness

/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main -l logfile start


Готове. Йдемо далі. Правимо файл repmgr.conf для witness ноди
Відключаємо автоматичний файловер на ноді witness
nano /etc/repmgr.conf
cluster=etagi_test
node=5
node_name=node5
use_replication_slots=1
conninfo='host=pghost205 port=5499 user=repmgr dbname=repmgr'
pg_bindir=/usr/lib/postgresql/9.6/bin

#######FAILOVER#######ТІЛЬКИ НА WITNESS NODE#######
master_response_timeout=50
reconnect_attempts=3
reconnect_interval=5
failover=manual
promote_command='repmgr standby promote -f /etc/repmgr.conf'
follow_command='repmgr standby follow -f /etc/repmgr.conf'


На witness ноде обов'язково змінити порт на 5499 в conninfo.

Обов'язково (пере)запускаємо repmgrd на всіх ноди, крім майстра
Прихований текст
su postgres
pkill repmgr
repmgrd -f /etc/repmgr.conf -p /var/run/postgresql/repmgrd.pid -m -d -v >> /var/log/postgresql/repmgr.log 2>&1
ps aux | grep repmgr


Налаштування менеджера сполук Pgbouncer і балансування через Haproxy. Відмовостійкості через Keepalived.
image
Налаштування Pgbouncer
Pgbouncer ми вже встановили заздалегідь. Для чого він потрібен…
Навіщо Pgbouncer
Мультиплексором сполук. Він виглядає як звичайний процес Postgres, але всередині він управляє чергами запитів, що дозволяє значно прискорити роботу сервера. З тисяч запитів, що надійшли до PgBouncer до бази даних дійде всього кілька десятків. 


Перейдемо до його налаштуванні.
Скопіюємо встановлений pgbouncer в папці /etc/(для зручності)
Прихований текст
cp -r /usr/local/share/doc/pgbouncer /etc
cd /etc/pgbouncer


Приведемо до вигляду файл
nano /etc/pgbouncer/pgbouncer.ini
[databases]
################################ПОДКЛ ДО БАЗИ###########
web1 = host = localhost port=5433 dbname=web1
web2 = host = localhost port=5433 dbname=web2
#######################################################
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt

;;; Pooler personality questions

; When server connection is released back to pool:
; session - after client disconnects
; transaction - after transaction finishes
; statement - after statement finishes
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 500
default_pool_size = 30


Відредагуємо файл
/etc/pgbouncer/userlist.txt
"test_user" "passworduser"
"postgres" "passwordpostgres"
"pgbouncer" "fake"


Застосуємо права
Прихований текст
chown -R postgres /etc/pgbouncer


Після редагування запустимо командою як демон (-d)
Запуск pgbouncer
su postgres
pkill pgbouncer
pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini 


Дивимося порт
Прихований текст
netstat -4ln | grep 6432


Дивимося лог
Прихований текст
tail -f /var/log/postgresql/pgbouncer.log


Пробуємо підключитися. Повторюємо все теж на всіх ноди.

Установка і настройка Haproxy.
image
Ставимо Xinetd і Haproxy
Прихований текст
apt-get install xinetd haproxy -y


Додаємо рядок в кінець файлу
Прихований текст
nano /etc/services
pgsqlchk 23267/tcp # pgsqlchk


Встановлюємо скрипт для перевірки стану postgres — pgsqlcheck
nano /opt/pgsqlchk
#!/bin/bash
# /opt/pgsqlchk 
# This script checks if a postgres server is healthy running on localhost. It will
# return:
#
# "HTTP/1.x 200 OK\r" (if postgres is running smoothly)
#
# - OR -
#
# "HTTP/1.x 500 Internal Server Error\r" (else)
#
# The purpose of this script is make haproxy capable of monitoring postgres properly
#
#
# It is recommended that a low-privileged postgres user is created to be used by
# this script.
# For eg. create user pgsqlchkusr login password 'pg321';
#

PGSQL_HOST="localhost"
PGSQL_PORT="5433"
PGSQL_DATABASE="template1"
PGSQL_USERNAME="pgsqlchkusr"
export PGPASSWORD="pg321"

TMP_FILE="/tmp/pgsqlchk.out"
ERR_FILE="/tmp/pgsqlchk.err"


#
# We perform a simple query that should return a few results :-p
#
psql -h $PGSQL_HOST -p $PGSQL_PORT -U $PGSQL_USERNAME \
$PGSQL_DATABASE -c "show port;" > $TMP_FILE 2> $ERR_FILE

#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
# Postgres is fine, return http 200
/bin/echo -e "HTTP/1.1 200 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "Postgres is running.\r\n"
/bin/echo -e "\r\n"
else
# Postgres is down, return http 503
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo -e "Postgres is *down*.\r\n"
/bin/echo -e "\r\n"
fi



Відповідно нам необхідно додати користувача pgsqlchkusr для перевірки стану postgres
Прихований текст
plsq
#create user pgsqlchkusr;
#ALTER ROLE pgsqlchkusr WITH LOGIN;
#ALTER USER pgsqlchkusr WITH PASSWORD 'pg321';
#\q


Робимо скрипт виконуваним і даємо права тимчасових файлів — інакше check не спрацює.
Прихований текст
chmod +x /opt/pgsqlchk;touch /tmp/pgsqlchk.out; touch /tmp/pgsqlchk.err; chmod 777 /tmp/pgsqlchk.out; chmod 777 /tmp/pgsqlchk.err;


Створюємо конфіг файл xinetd для pgsqlchk
nano /etc/xinetd.d/pgsqlchk

# /etc/xinetd.d/pgsqlchk
# # default: on
# # description: pqsqlchk
service pgsqlchk
{
flags = REUSE
socket_type = stream
port = 23267
wait = no
user = nobody
server = /opt/pgsqlchk
log_on_failure += USERID
disable = no
only_from = 0.0.0.0/0
per_source = UNLIMITED

}


Сохраняемся.
Налаштовуємо haproxy.
Редагуємо конфіг — видалимо старий і вставимо вмісту. Цей конфіг для першої ноди, на якій крутиться майстер, на даний момент припустимо, що це pghost195. Відповідно для даного хоста ми зробимо активним в пулі сполук свій-ж хост, що працює на порте 6432(через pgbouncer).
nano /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
#chroot /usr/share/haproxy
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid

user postgres
group postgres
daemon
maxconn 20000
defaults
log global
http mode
option tcplog
option dontlognull
retries 3
option redispatch
timeout connect 30000ms
timeout client 30000ms
timeout server 30000ms

frontend stats-front
bind *:8080
http mode
default_backend stats-back

frontend pxc-onenode-front
bind *:5432
mode tcp
default_backend pxc-onenode-back

backend stats-back
http mode
stats uri /
stats auth admin:adminpassword

backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
default-server port 6432 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxqueue 128 weight 100
server pghost195 10.1.1.195:6432 check port 23267 



Сам порт haproxy для підключення до бази крутиться на порте 5432. Адмінка доступна на порт 8080. Користувач admin з паролем adminpassword.
Рестартим сервіси
Прихований текст
/etc/init.d/xinetd restart;
/etc/init.d/haproxy restart;


Теж саме робимо ще на всіх ноди.
На тій ноде, яку ви хочете зробити балансировщиком, наприклад pghost198(запити на неї будуть йти тільки на читання) конфіг haproxy наводимо до такого виду.
nano /etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
#chroot /usr/share/haproxy
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
user postgres
group postgres
daemon
maxconn 20000
defaults
log global
http mode
option tcplog
option dontlognull
retries 3
option redispatch
timeout connect 30000ms
timeout client 30000ms
timeout server 30000ms

frontend stats-front
bind *:8080
http mode
default_backend stats-back

frontend pxc-onenode-front
bind *:5432
mode tcp
default_backend pxc-onenode-back

backend stats-back
http mode
stats uri /
stats auth admin:adminpassword

backend pxc-onenode-back
mode tcp
balance roundrobin
option httpchk
default-server port 6432 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxqueue 128 weight 100
server pghost196 10.1.1.196:6432 check port 23267
server pghost197 10.1.1.196:6432 check port 23267
server pghost198 10.1.1.196:6432 check port 23267


Дивись Статистику на hostip:8080

Установка keepalived.
Keepalived дозволяє використовувати віртуальну ip-адресу (VIP) і в разі виходу з ладу однієї з нсд(вимкнення живлення або інша подія) ip адреса перейде на іншу ноду. Наприклад у нас буде VIP 10.1.1.192 між нодою pghost195,pghost196,pghost197. Відповідно при виключенні живлення на ноді pghost195 нода pghost196 автоматично присвоїть собі ip addr 10.1.1.192 і так як вона є другою в пріоритеті на просування до ролі майстра стане доступною для запису або завдяки haproxy або pgbouncer — тут все залежить від вашого вибору. У нашому сценарії — це Haproxy.
Ставимо keepalived
Прихований текст
apt-get install keepalived -y


Налаштовуємо keepalived. Приводимо до вигляду. НА 1-ій ноде(pghost195)
nano /etc/keepalived/keepalived.conf
! this is who emails will go to on alerts
notification_email {
admin@domain.com

! add a few more email addresses here if you would like
}
notification_email_from servers@domain.com
! I use the local machine to relay mail
smtp_server smt.local.domain
smtp_connect_timeout 30
! each load balancer should have a different ID
! this will be used in SMTP alerts, so you should make
! each router easily identifiable
lvs_id LVS_HAPROXY-pghost195
}

}
vrrp_instance haproxy-pghost195 {
інтерфейс eth0
state MASTER
virtual_router_id 192
priority 150
! send an alert when this instance changes from state MASTER to BACKUP
smtp_alert
authentication {
auth_type PASS
auth_pass passwordforcluster
}
track_script {
chk_http_port
}
virtual_ipaddress {
10.1.1.192/32 dev eth0
}
notify_master "sh /etc/postgresql/telegram.sh 'MASTER pghost195.etagi.com отримав VIP'" 
notify_backup "sh /etc/postgresql/telegram.sh 'BACKUP pghost195.etagi.com отримав VIP'"
notify_fault "sh /etc/postgresql/telegram.sh 'FAULT pghost195.etagi.com отримав VIP'"

}


Рестартим
/etc/init.d/keepalived restart

Налаштовуємо keepalived на 2-ой ноде(pghost196)

nano /etc/keepalived/keepalived.conf
! this is who emails will go to on alerts
notification_email {
admin@domain.com

! add a few more email addresses here if you would like
}
notification_email_from servers@domain.com
! I use the local machine to relay mail
smtp_server smt.local.domain
smtp_connect_timeout 30
! each load balancer should have a different ID
! this will be used in SMTP alerts, so you should make
! each router easily identifiable
lvs_id LVS_HAPROXY-pghost196
}

}
vrrp_instance haproxy-pghost196 {
інтерфейс eth0
state MASTER
virtual_router_id 192
priority 80
! send an alert when this instance changes from state MASTER to BACKUP
smtp_alert
authentication {
auth_type PASS
auth_pass passwordforcluster
}
track_script {
chk_http_port
}
virtual_ipaddress {
10.1.1.192/32 dev eth0
}
notify_master "sh /etc/postgresql/telegram.sh 'MASTER pghost196.etagi.com отримав VIP'" 
notify_backup "sh /etc/postgresql/telegram.sh 'BACKUP pghost196.etagi.com отримав VIP'"
notify_fault "sh /etc/postgresql/telegram.sh 'FAULT pghost196.etagi.com отримав VIP'"

}


Налаштовуємо keepalived на 3-ій ноде(pghost197)

nano /etc/keepalived/keepalived.conf
! this is who emails will go to on alerts
notification_email {
admin@domain.com

! add a few more email addresses here if you would like
}
notification_email_from servers@domain.com
! I use the local machine to relay mail
smtp_server smt.local.domain
smtp_connect_timeout 30
! each load balancer should have a different ID
! this will be used in SMTP alerts, so you should make
! each router easily identifiable
lvs_id LVS_HAPROXY-pghost197
}

}
vrrp_instance haproxy-pghost197 {
інтерфейс eth0
state MASTER
virtual_router_id 192
priority 50
! send an alert when this instance changes from state MASTER to BACKUP
smtp_alert
authentication {
auth_type PASS
auth_pass passwordforcluster
}
track_script {
chk_http_port
}
virtual_ipaddress {
10.1.1.192/32 dev eth0
}
notify_master "sh /etc/postgresql/telegram.sh 'MASTER pghost197.etagi.com отримав VIP'" 
notify_backup "sh /etc/postgresql/telegram.sh 'BACKUP pghost197.etagi.com отримав VIP'"
notify_fault "sh /etc/postgresql/telegram.sh 'FAULT pghost197.etagi.com отримав VIP'"

}


Рестартим
Прихований текст
/etc/init.d/keepalived restart


Як ми бачимо, ми також можемо використовувати скрипти, наприклад для попередження при зміні стану. Дивимося наступну секцію
Прихований текст
notify_master "sh /etc/postgresql/telegram.sh 'MASTER pghost195.etagi.com отримав VIP'" 
notify_backup "sh /etc/postgresql/telegram.sh 'BACKUP pghost195.etagi.com отримав VIP'"
notify_fault "sh /etc/postgresql/telegram.sh 'FAULT pghost195.etagi.com отримав VIP'"


Так само з конфига видно що ми налаштували VIP на 10.1.8.111 який буде жити на eth0. У разі падіння ноди pghost195 він перейде на pghost196, тобто підключення ми так само будемо налаштовувати через IP 10.1.1.192. так само встановимо на pghost197, тільки змінимо vrrp_instance і lvs_id LVS_.
На ноди pghost196,pghost197 відключимо keepalived. Він буде запускатися тільки після процедури failover promote, яка описана у файлі. Ми вказали
Прихований текст
promote_command='sh /etc/postgresql/failover_promote.sh'
follow_command='sh /etc/postgresql/failover_follow.sh'


у файлі /etc/repmgr.conf (див. в конфігах вище).
Дані скрипти будуть запускатися при виникненні failover ситуації -відмову майстра.
promote_command='sh /etc/postgresql/failover_promote.sh — выпоняет номінований на master host,
follow_command='sh /etc/postgresql/failover_follow.sh' — виконують ноди, які слідують за майстром.
Конфіги
promote_command='sh /etc/postgresql/failover_promote.sh'
#!/bin/bash
CLHOSTS="pghost195 pghost196 pghost197 pghost198 pghost205 "
repmgr standby promote -f /etc/repmgr.conf;
echo "Надсилання оповіщень";
sh /etc/postgresql/failover_notify_master.sh;
echo "Виводимо список необхідних хостів у файл"
repmgr -f /etc/repmgr.conf cluster show | grep node | awk '{print $7} ' | sed "s/host=//g" | sed '/port/d' > /etc/postgresql/cluster_hosts.list
repmgr -f /etc/repmgr.conf cluster show | grep FAILED | awk '{print $6} ' | sed "s/host=//g" | sed "s/>//g" > /etc/postgresql/failed_host.list
repmgr -f /etc/repmgr.conf cluster show | grep master | awk '{print $7} ' | sed "s/host=//g" | sed "s/>//g" > /etc/postgresql/current_master.list
repmgr -f /etc/repmgr.conf cluster show | grep standby | awk '{print $7} ' | sed "s/host=//g" | sed '/port/d' > /etc/postgresql/standby_host.list

####КОПІЮЮ ІНФО ФАЙЛИ І ФАЙЛИ-ТРИГЕРИ НА ІНШІ НОДИ КЛАСТЕРА#####################
for CLHOST in $CLHOSTS
do
rsync -arvzSH --include ".*.list" --exclude ".*". /etc/\postgresql/ postgres@$CLHOST:/etc/postgresql/
done

echo "Починаю процедуру відновлення впав сервера,якщо не тригера /etc/postgresql/disabled"

for FH in $(cat /etc/postgresql/failed_host.list)
do
ssh postgres@$FH <<OFF
sh /etc/postgresql/register.sh;
echo "Рестартуем repmgrd на інших ноди"
sh /etc/postgresql/repmgrd.sh;
sh /etc/postgresql/failover_notify_restoring_ended.sh;
OFF
done


echo "Стопаем repmgrd на ноде, стала майстром"
pkill repmgrd

echo "Працюємо з Keepalived"



follow_command='sh /etc/postgresql/failover_follow.sh'
repmgr standby follow -f /etc/repmgr.conf;
echo "Надсилання оповіщень";
sh /etc/postgresql/failover_notify_standby.sh;
pkill repmgrd;
repmgrd -f /etc/repmgr.conf -p /var/run/postgresql/repmgrd.pid -m -d -v >> /var/log/postgresql/repmgr.log 2>&1;


Скрипт зупинки майстра — примусового failover, зручно використовувати для тестування процедур «перевиборів» в кластері.

follow_command='sh /etc/postgresql/stop_master.sh'#!/bin/bash
repmgr -f /etc/repmgr.conf cluster show | grep master | awk '{print $7} '| sed 's/host=//g» | sed' s/>//g» > /etc/postgresql/current_master.list
for CURMASTER in $(cat /etc/postgresql/current_master.list)
do
ssh postgres@$CURMASTER <<OFF
cd ~/9.6;
/usr/lib/postgresql/9.6/bin/pg_ctl -D /etc/postgresql/9.6/main -m immediate stop;
touch /etc/postgresql/disabled;
OFF
sh /etc/postgresql/telegram.sh «ПОТОЧНИЙ МАЙСТЕР ЗУПИНЕНИЙ»
done


З допомогою скриптів можна зрозуміти логіку роботу і налаштувати сценарії під себе. Як ми бачимо з коду, нам буде потрібен доступ до root користувача від користувача postgres. Отримуємо його таким же чином — через ключі.
Доступ до root від postgres
su postgres
ssh-copy-id -i ~/.ssh/id_rsa.pub root@pghost195
ssh-copy-id -i ~/.ssh/id_rsa.pub root@pghost196
ssh-copy-id -i ~/.ssh/id_rsa.pub root@pghost197
ssh-copy-id -i ~/.ssh/id_rsa.pub root@pghost198
ssh-copy-id -i ~/.ssh/id_rsa.pub root@pghost205


Повторюємо на всіх ноди.
Для особливих параноїків, можемо налаштувати скрипт перевірки станів і додати його в крон наприклад раз у 2 хвилини. Зробити це можна, використовуючи конструкції і використовуючи отримані значення з файлу.
Прихований текст
repmgr -f /etc/repmgr.conf cluster show | grep node | awk '{print $7} ' | sed "s/host=//g" | sed '/port/d' > /etc/postgresql/cluster_hosts.list
repmgr -f /etc/repmgr.conf cluster show | grep FAILED | awk '{print $6} ' | sed "s/host=//g" | sed "s/>//g" > /etc/postgresql/failed_host.list
repmgr -f /etc/repmgr.conf cluster show | grep master | awk '{print $7} ' | sed "s/host=//g" | sed "s/>//g" > /etc/postgresql/current_master.list
repmgr -f /etc/repmgr.conf cluster show | grep standby | awk '{print $7} ' | sed "s/host=//g" | sed '/port/d' > /etc/postgresql/standby_host.list


Доповнення та усунення несправностей.
Збір статистики запитів до бази
Ми додали бібліотеку pg_stat_statements( необхідно зробити рестарт)
Прихований текст
su postgres
cd ~
pg_ctl restart;


Далі активуємо розширення:
Прихований текст
# CREATE EXTENSION pg_stat_statements;


Приклад зібраної статистики:
Прихований текст
# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;


Для скидання статистики є команда pg_stat_statements_reset:
Прихований текст
# SELECT pg_stat_statements_reset();



Видалення ноди з кластера якщо вона 'FAILED'
Прихований текст
DELETE FROM repmgr_etagi_test.repl_nodes WHERE name = 'node1';


де — etagi_test — назва кластера;
node1 — ім'я ноди в кластері

Перевірка стану реплікації
Прихований текст
plsq
#SELECT EXTRACT(the EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;

00:00:31.445829
(1 рядок)


Якщо в базі давно не було Insert'ов — то це значення буде збільшуватися. На hiload базах це значення буде прагнути до нуля.

Усунення помилки Slot 'repmgr_slot_номер слоти' already exists as an active slot
Зупиняємо postgresql на тій ноде, на якій виникла помилка
Прихований текст
su postgres
pg_ctl stop;


На ноді master'e
Прихований текст
su postgres 
psql repmgr
#select pg_drop_replication_slot('repmgr_slot_4');



Усунення помилки INSERT або UPDATE в таблиці «repl_nodes» порушує обмеження зовнішнього ключа ПОМИЛКА: INSERT або UPDATE в таблиці «repl_nodes» порушує обмеження зовнішнього ключа «repl_nodes_upstream_node_id_fkey»
DETAIL: Ключ (upstream_node_id)=(-1) відсутня у таблиці «repl_nodes».
Якщо у вас виникла ця помилка при спробі ввести впала ноду назад у кластер то необхідно зробити Процедуру switchover будь ноди в кластері(standby)
Прихований текст
repmgr -f /etc/repmgr.conf standby switchover


Standby стане майстром
На «Старому Майстрі» став standby
Прихований текст
repmgr -f /etc/repmgr.conf standby follow


Помилка ВАЖЛИВО: не вдалося відкрити каталог "/var/run/postgresql/9.6-main.pg_stat_tmp":
Просто створюємо каталог
Прихований текст
su postgres
mkdir -p /var/run/postgresql/9.6-main.pg_stat_tmp



Усунення помилки при реєстрації кластера no password supplied.
При реєстрації кластера після того як ми злили з ноди дані буває виникає помилка
«no password supplied»
Не стали з нею довго розбиратися, допомогла перезавантаження, мабуть, якийсь сервіс не зміг завантажитися нормально.

Backup кластера
Бекап кластера робиться командою
pg_dumpall dbname > gzip > filename.gz



Скрипт бекапа баз даних Postgres
backup_pg.sh
#!/bin/bash
DBNAMES="db1 db2 db3" 
DATE_Y=`/bin/date '+%y"
DATE_M=`/bin/date '+%m"
DATE_D=`/bin/date '+%d"
SERVICE="pgdump"
#DB_NAME="repmgr";

#`psql -l | awk '{print $1}' `
for DB_NAME in $DBNAMES
do
echo "CREATING DIR /Backup/20${DATE_Y}/${DATE_M}/${DATE_D}/${DB_NAME} "
BACKUP_DIR="/Backup/20${DATE_Y}/${DATE_M}/${DATE_D}/${DB_NAME}"
mkdir -p $BACKUP_DIR;
pg_dump -Fc --verbose ${DB_NAME} | gzip > $BACKUP_DIR/${DB_NAME}.gz
# Робимо dump бази без дати, для того що далі неї витягти їх функції
pg_dump -Fc -s -f $BACKUP_DIR/${DB_NAME}_only_shema ${DB_NAME} 
/bin/sleep 2;
# Створюємо список функція
pg_restore -l $BACKUP_DIR/${DB_NAME}_only_shema | grep FUNCTION > $BACKUP_DIR/function_list
done



##Як відновити функції

#########################
#pg_restore -h localhost -U username -d ім'я_бази -L function_list db_dump
########################


### ЯК ВІДНОВИТИ ОДНУ ТАБЛИЦЮ З БЕКАПА, наприклад, таблицю payment.
#pg_restore --dbname db1 --table=table1 имядампаБД
####ЯКЩО Ж ВИ ХОЧЕТЕ ЗЛИТИ ТАБЛИЦЮ В ПОРОЖНЮ БАЗУ, ТО НЕОБХІДНО ВІДТВОРИТИ СТРУКТУРУ БД
###pg_restore --dbname ldb1 имядампаБД_only_shema



Висновок
Отже, що ми отримали в результаті:
-кластер master-standby з чотирьох нсд;
-автоматичний failover у разі відмови майстра(з допомогою repmgr'a);
-балансування навантаження(на читання) через haproxy і pgbouncer(менеджер сеансів);
-відсутність єдиної точки відмови — keepalived переносить ip адресу на іншу ноду, яка була автоматично «підвищена» до майстра у разі відмови;
— процедура відновлення(повернення відмовив сервера в кластер) не є трудомісткою — якщо розібратися);
— гнучкість системи — repmgr дозволяє налаштувати і інші події в разі настання інциденту з допомогою bash скриптів;
— можливість налаштувати систему «під себе».
Для початківця фахівця налаштування даної схеми може здатися трохи складною, на практиці ж, один раз варто з усім добре розібратися і ви зможете створити HA системи на базі Postgresql і самі керувати сценаріями реалізації механізму Failover.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.