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

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

nano /etc/postgresql/9.6/main/start.conf 
заменяем auto на manual

apt-get install chkconfig -y 

/sbin/chkconfig --list

update-rc.d postgresql disable

/sbin/chkconfig  --list postgresql

apt-get install openssh-server rsync -y

passwd postgres 

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

nano /etc/ssh/ssh_config
   StrictHostKeyChecking no
   UserKnownHostsFile=/dev/null

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 ..

 ./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

 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 

su postgres
cd ~
nano .bashrc

PATH=$PATH:/usr/lib/postgresql/9.6/bin
export PATH
export PGDATA="$HOME/9.6/main"

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

listen_addresses='*'
wal_level = 'hot_standby'
archive_mode = on
wal_log_hints = on
wal_keep_segments = 0
archive_command = 'cd .'
max_replication_slots = 5 # Максимальное количество standby нод, подключенных к мастеру.
hot_standby = on
shared_preload_libraries = 'repmgr_funcs, pg_stat_statements' ####подключаемая библиотека repmgr и статистики postgres
max_connections = 800
max_wal_senders = 10#максимальное количество одновременных подключений от резервных серверов или клиентов потокового резервного копирования
port = 5433
pg_stat_statements.max = 10000
pg_stat_statements.track = all

# 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  #######Подключение для всех по паролю
#####################################

chown -R -v postgres /etc/postgresql

pg_ctl -D /etc/postgresql/9.6/main --log=/var/log/postgresql/postgres_screen.log start

su postgres 
cd ~

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;

create user test_user;
ALTER USER test_user WITH PASSWORD '1234';

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

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

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

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
 

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

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

PG_VERSION backup_label
pg_hba.conf  pg_ident.conf  postgresql.auto.conf  postgresql.conf  recovery.conf

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

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

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

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#######ТОЛЬКО НА 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

#######АВТОМАТИЧЕСКИЙ 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

#######АВТОМАТИЧЕСКИЙ 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

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

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

su postgres
pg_ctl -D /etc/postgresql/9.6/main  -m immediate stop

tail -f /var/log/postgresql/*

[2016-10-21 16:58:34] [NOTICE] promoting standby
[2016-10-21 16:58:34] [NOTICE] 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] [NOTICE] STANDBY PROMOTE successful

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 to database failed: could not connect to server: Connection refused
	Is the server running on host "pghost195" (10.1.1.195) and accepting
	TCP/IP connections on port 5433?

[2016-10-21 16:58:40] [NOTICE] 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] [NOTICE] node 3 now following new upstream node 2

pghost196

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);
repmgr -h $(cat /etc/postgresql/current_master.list) -p 5433 -U repmgr -d repmgr -D /var/lib/postgresql/9.6/main -f /etc/repmgr.conf --copy-external-config-files=pgdata --verbose standby clone
/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

#!/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;

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

cluster=etagi_cluster1
node=1
node_name=node195
use_replication_slots=1
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

[2016-10-31 15:19:53] [NOTICE] notifying master about backup completion...
ЗАМЕЧАНИЕ:  команда pg_stop_backup завершена, все требуемые сегменты WAL заархивированы
[2016-10-31 15:19:54] [NOTICE] standby clone (using rsync) complete
[2016-10-31 15:19:54] [NOTICE] you can now start your PostgreSQL server
[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] [NOTICE] standby node correctly registered for 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

su postgres
repmgr -f /etc/repmgr.conf standby switchover 
Видим
[2016-10-26 15:29:42] [NOTICE] replication slot "repmgr_slot_1" deleted on former master
[2016-10-26 15:29:42] [NOTICE] switchover was successful

su postgres
repmgr -f /etc/repmgr.conf standby follow
repmgr -f /etc/repmgr.conf cluster show;

su postgres
repmgr -f /etc/repmgr.conf standby follow
repmgr -f /etc/repmgr.conf cluster show;

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

pg_ctl stop

su postgres
psql repmgr
#select pg_drop_replication_slot('repmgr_slot_2');

 pg_drop_replication_slot 
--------------------------
(1 row)

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] [NOTICE] using configuration file "/etc/repmgr.conf"
Файлы, относящиеся к этой СУБД, будут принадлежать пользователю "postgres".
От его имени также будет запускаться процесс сервера.
Кластер баз данных будет инициализирован с локалью "ru_RU.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.12MB/s    0:00:00 (xfr#1, to-chk=0/1)
СООБЩЕНИЕ:  получен SIGHUP, файлы конфигурации перезагружаются
сигнал отправлен серверу
[2016-10-26 17:27:10] [NOTICE] 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

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'

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

 Мультиплексором соединений. Он выглядит как обычный процесс Postgres, но внутри он управляет очередями запросов что позволяет в разы ускорить работу сервера. Из тысяч запросов поступивших к PgBouncer до базы данных дойдет всего несколько десятков. 

cp -r /usr/local/share/doc/pgbouncer /etc
cd /etc/pgbouncer

[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

"test_user" "passworduser"
"postgres" "passwordpostgres"
"pgbouncer" "fake"

chown -R postgres /etc/pgbouncer

su postgres
pkill pgbouncer
pgbouncer  -d --verbose /etc/pgbouncer/pgbouncer.ini 

 netstat -4ln | grep 6432

tail -f /var/log/postgresql/pgbouncer.log

apt-get install xinetd haproxy -y

nano /etc/services
pgsqlchk        23267/tcp               # 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

plsq
#create user pgsqlchkusr;
#ALTER ROLE pgsqlchkusr WITH LOGIN;
#ALTER USER pgsqlchkusr WITH PASSWORD 'pg321';
#\q

chmod +x  /opt/pgsqlchk;touch /tmp/pgsqlchk.out; touch /tmp/pgsqlchk.err; chmod 777  /tmp/pgsqlchk.out;  chmod 777 /tmp/pgsqlchk.err;

# /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

}

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
mode http
option tcplog
option dontlognull
retries 3
option redispatch
timeout connect 30000ms
timeout client 30000ms
timeout server 30000ms

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

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

backend stats-back
mode http
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 

/etc/init.d/xinetd restart;
/etc/init.d/haproxy restart;

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
mode http
option tcplog
option dontlognull
retries 3
option redispatch
timeout connect 30000ms
timeout client 30000ms
timeout server 30000ms

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

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

backend stats-back
mode http
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

apt-get install keepalived -y

net.ipv4.ip_forward=1

sysctl -p

  ! 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 {
        interface eth0
        state MASTER
        virtual_router_id 192
        priority 150
  ! send an alert when this instance changes state from 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'"

}

  ! 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 {
        interface eth0
        state MASTER
        virtual_router_id 192
        priority 80
  ! send an alert when this instance changes state from 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'"

}

  ! 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 {
        interface eth0
        state MASTER
        virtual_router_id 192
        priority 50
  ! send an alert when this instance changes state from 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'"

promote_command='sh /etc/postgresql/failover_promote.sh'
follow_command='sh /etc/postgresql/failover_follow.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"

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;

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

 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

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;

# SELECT pg_stat_statements_reset();

DELETE FROM repmgr_etagi_test.repl_nodes WHERE name = 'node1';

plsq
#SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;

 00:00:31.445829
(1 строка)

su postgres
pg_ctl stop;

su postgres 
psql repmgr
#select pg_drop_replication_slot('repmgr_slot_4');

plsq
# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'dbname';
# DROP DATABASE dbname;

repmgr -f /etc/repmgr.conf standby switchover

repmgr -f /etc/repmgr.conf standby follow

su postgres
mkdir -p /var/run/postgresql/9.6-main.pg_stat_tmp

pg_dumpall | gzip -c  > filename.gz

#!/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