Postgresql笔记（三）repmgr+pgpool实现双机流式复制、集群

实现目标：主库可读可写，从库只读，两库之间为流式复制，若主库宕机，从库自动升级成主库

环境 Debian 9

软件介绍

Repmgr是一个开源工具套件，用于管理PostgreSQL服务器集群中的复制和故障转移。它使用工具来增强PostgreSQL的内置热备份功能，以设置备用服务器，监控复制以及执行管理任务，例如故障转移或手动切换操作。

Repmgr 4.1版文档地址 https://repmgr.org/docs/4.1/index.html

Pgpool是一个工作在PostgreSQL多服务器和PostgreSQL数据库客户端之间的中间件。

pgpool http://www.pgpool.net/docs/latest/en/html/

pgpoolAdmin http://www.pgpool.net/docs/pgpoolAdmin/index_en.html

Pgpool

Pgpool

安装步骤

wget http://www.pgpool.net/download.php?f=pgpool-II-4.0.1.tar.gz

tar -xzvf download.php\?f\=pgpool-II-4.0.1.tar.gz

./configure

make

make install

ln -s /usr/local/lib/libpcp.so.1 /usr/lib/libpcp.so.1

cd /usr/local/etc

cp pgpool.conf.sample-replication pgpool.conf

#配置局域网段/指定IP trust

#host all all 192.168.0.0/16 trust

cp pool_hba.conf.sample pool_hba.conf

cp pcp.conf.sample pcp.conf

chmod 644 pgpool.conf pool_hba.conf pcp.conf

chown www-data pgpool.conf pool_hba.conf pcp.conf

#配置service

cd /lib/systemd/system

vim pgpool.service

ln -s /lib/systemd/system/pgpool.service /etc/systemd/system/multi-user.target.wants/pgpool.service

useradd pgpool

passwd pgpool

systemctl start pgpool

# 配置pgpool.conf的图形化页面工具

wget http://www.pgpool.net/download.php?f=pgpoolAdmin-4.0.0.tar.gz

tar -xzvf download.php?f=pgpoolAdmin-4.0.0.tar.gz

apt -y install php-fpm php-pgsql

mv pgpoolAdmin-4.0.0 /var/www/pgpoolAdmin

vim /etc/nginx/sites-avaliable/pgpool-nginx.conf

cd /var/www/pgpoolAdmin

chmod 777 templates_c

chown www-data /var/www/pgpoolAdmin/conf/pgmgt.conf.php

chmod 644 /var/www/pgpoolAdmin/conf/pgmgt.conf.php

# 配置好nginx后 访问 /install/index.php

Repmgr

apt-get install postgresql-11-repmgr

# 分别copy主库从库的repmgr.conf文件

# ----master start-----

createuser -s repmgr

createdb repmgr -O repmgr

psql -U repmgr

# 执行SQL

ALTER USER repmgr SET search_path TO repmgr, "$user", public;

#

repmgr -f /etc/postgresql/11/main/repmgr.conf primary register

# -----master end ----

# -----slave start ----

/usr/lib/postgresql/11/bin/pg_ctl -w -D /var/lib/postgresql/11/main stop

repmgr -h node1 -U repmgr -d repmgr -f /etc/postgresql/11/main/repmgr.conf standby clone --force

/usr/lib/postgresql/11/bin/pg_ctl -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log -w -o "-c config_file=/etc/postgresql/11/main/postgresql.conf" start

repmgr standby register -f /etc/postgresql/11/main/repmgr.conf

repmgr -f /etc/postgresql/11/main/repmgr.conf cluster show

vim ~/.bashrc

alias repmgrf='repmgr -f /etc/postgresql/11/main/repmgr.conf'

# -----slave end ----

# repmgrd (监控repmgr)

repmgrd -f /etc/postgresql/11/main/repmgr.conf --pid-file /tmp/repmgrd.pid --daemonize > /var/log/postgresql/repmgr.log 2>&1

postgres配置

postgresql主要修改配置

#Master: postgresql.conf

shared_preload_libraries = 'repmgr'

wal_level = 'hot_standby'

archive_mode = on

#Slave: postgresql.conf

shared_preload_libraries = 'repmgr'

hot_standby = on

hot_standby_feedback = on

从库的recovery.conf

# slave

# /var/lib/postgresql/11/main/recovery.conf

standby_mode = 'on'

primary_conninfo = 'host=node1 user=repmgr application_name=pg_node2 connect_timeout=2'

recovery_target_timeline = 'latest'

部分配置文件sample

pgpool.service

[Unit]

Description=Pgpool-II

After=syslog.target network.target

Wants=network.target

[Service]

User=pgpool

Type=forking

ExecStartPre=/bin/sh -c "rm -f /tmp/.s.PGSQL.9999”

ExecStartPre=/bin/sh -c "rm -f /tmp/.s.PGSQL.9898"

RestartSec=5s

ExecStart=/usr/local/bin/pgpool -f /usr/local/etc/pgpool.conf -a /usr/local/etc/pool_hba.conf -F /usr/local/etc/pcp.conf

ExecStop=/usr/local/bin/pgpool -f /usr/local/etc/pgpool.conf -a /usr/local/etc/pool_hba.conf -F /usr/local/etc/pcp.conf stop

ExecReload=/usr/local/bin/pgpool -f /usr/local/etc/pgpool.conf -a /usr/local/etc/pool_hba.conf -F /usr/local/etc/pcp.conf reload

Restart=always

StandardOutput=syslog

StandardError=syslog

SyslogIdentifier=my_pgpool

PIDFile=/var/run/pgpool/pgpool.pid

[Install]

WantedBy=multi-user.target

pgpool-nginx.conf

server {

listen 80;

server_name pgpool.xxx.com;

location ~ \.php$ {

root /var/www/html/pgpoolAdmin;

index index.php;

#fastcgi_pass 127.0.0.1:9000;

fastcgi_pass unix:/run/php/php7.0-fpm.sock;

fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;

include fastcgi_params;

}

location / {

root /var/www/html/pgpoolAdmin;

}

}

repmgr.conf

# https://raw.githubusercontent.com/2ndQuadrant/repmgr/master/repmgr.conf.sample

node_id=1

node_name=pg_node1

conninfo='host=node1 user=repmgr dbname=repmgr connect_timeout=2'

data_directory='/var/lib/postgresql/11/main'

monitoring_history=yes

log_file='/var/log/postgresql/repmgr.log'

service_start_command = '/usr/lib/postgresql/11/bin/pg_ctl -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log -w -o "-c config_file=/etc/postgresql/11/main/postgresql.conf" start'

service_restart_command = '/usr/lib/postgresql/11/bin/pg_ctl -w -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log restart'

service_reload_command = '/usr/lib/postgresql/11/bin/pg_ctl -w -D /var/lib/postgresql/11/main -l /var/log/postgresql/postgresql-11-main.log reload'

failover=automatic

promote_command='repmgr standby promote -f /etc/postgresql/11/main/repmgr.conf --log-to-file'

follow_command='repmgr standby follow -f /etc/postgresql/11/main/repmgr.conf --log-to-file --upstream-node-id=2'

promote_check_timeout=30 # The length of time (in seconds) to wait

# for the new primary to finish promoting

primary_follow_timeout=30 # The max length of time (in seconds) to wait

# for the new primary to become available

#event_notification_command='/data/send_mail "repmgr event notification" "node Id： %n \n事件： %e \n成功(1?)： %s \n时间： %t \n详情： %d"'