pgpool+PG流复制高可用架构搭建

介绍

大家好，我是本际云服务器推荐网的小编小本本。今天给大家介绍一下如何使用pgpool+PG流复制搭建高可用架构。

架构搭建

PG流复制可以是一主多从架构，类似OracleADG,都是采用物理复制，从库可提供实时查询业务，流复制在不借助插件的情况下，本身并不提供自动failover等功能。PGPOOL是一款较流行的Postgres的数据库中间件，提供了连接池、自动故障转移、负载均衡、看门狗等功能。在基于流复制架构下，架构图如下（转自官方文档）：注：本文采用3个主机，进行一主两从架构部署示例。

Postgresql软件安装提前安装好Postgres数据库，并搭建好流复制架构，本文不再做描述。

Pgpool软件安装pgpool最新版本为4.2.2，本文采用4.1.2版作为示例。可选用rpm或者源码包安装，本文采用源码编译的方式安装。

软件安装

tar xvf pgpool-II-4.1.2.tar.gz
cd pgpool-II-4.1.2
./configure --prefix=/usr/local/pgpool412 --with-openssl
make
make install

插件安装

cd pgpool-II-4.1.2/src/sql/pgpool-recovery
make
make install psql -c "create extension pgpool_recovery;"
cd pgpool-II-4.1.2/src/sql/pgpool-regclass
make
make install psql -c "CREATE EXTENSION pgpool_regclass;"

修改PG数据库参数文件pgpool.pg_ctl=/usr/local/postgres/bin/pg_ctl

PGPOOL配置

配置SSH等效性

ssh-keygen -t rsa -f ~/.ssh/id_rsa_pgpool
cd ~/.ssh
ssh-copy-id -i id_rsa_pgpool.pub postgres@host01
ssh-copy-id -i id_rsa_pgpool.pub postgres@host02
ssh-copy-id -i id_rsa_pgpool.pub postgres@host03
ssh postgres@host01 -i ~/.ssh/id_rsa_pgpool

配置Pgpool参数

cp -p /usr/local/pgpool412/etc/pgpool.conf.sample-stream /usr/local/pgpool412/etc/pgpool.conf

# 以下为所有节点通用配置

listen_addresses=*

pid_file_name=/usr/local/pgpool412/pgpool.pid

sr_check_user=pgpool

sr_check_password=

health_check_period=5

health_check_timeout=30

health_check_user=pgpool

health_check_password=

health_check_max_retries=3

backend_hostname0=host01

backend_port0=5432

backend_weight0=1

backend_data_directory0=/pgdata

backend_flag0=ALLOW_TO_FAILOVER

backend_hostname1=host02

backend_port1=5432

backend_weight1=1

backend_data_directory1=/pgdata

backend_flag1=ALLOW_TO_FAILOVER

backend_hostname2=host03

backend_port2=5432

backend_weight2=1

backend_data_directory2=/pgdata

backend_flag2=ALLOW_TO_FAILOVER

backend_application_name0=host01

backend_application_name1=host02

backend_application_name2=host03

failover_command=/usr/local/pgpool412/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S

follow_master_command=/usr/local/pgpool412/etc/follow_master.sh %d %h %p %D %m %H %M %P %r %R

recovery_user=postgres

recovery_password=

1st_stage_command = 'pgpool_recovery'

1st_stage_command_retry_delay = 5

enable_pool_hba=on

# 看门狗通用配置

use_watchdog=on

delegate_IP=192.168.56.5

if_cmd_path=/usr/sbin

if_up_cmd=/usr/bin/sudo /usr/sbin/ip addr add $_IP_/24 dev enp0s3 label enp0s3:0

if_down_cmd=/usr/bin/sudo /usr/sbin/ip addr del $_IP_/24 dev enp0s3

arping_path=/usr/sbin

arping_cmd=/usr/bin/sudo /usr/sbin/arping -U $_IP_ -w 1 -I enp0s3

# 以下不同主机分别配置

# host01

wd_hostname=host01

wd_port=9000

other_pgpool_hostname0=host02

other_pgpool_port0=9999

other_wd_port0=9000

other_pgpool_hostname1=host03

other_pgpool_port1=9999

other_wd_port1=9000

# host02

wd_hostname=host02

wd_port=9000

other_pgpool_hostname0=host01

other_pgpool_port0=9999

other_wd_port0=9000

other_pgpool_hostname1=host03

other_pgpool_port1=9999

other_wd_port1=9000

# host03

wd_hostname=host03

wd_port=9000

other_pgpool_hostname0=host01

other_pgpool_port0=9999

other_wd_port0=9000

other_pgpool_hostname1=host02

other_pgpool_port1=9999

other_wd_port1=9000

#

                                                        
原创文章，作者：小编小本本，如若转载，请注明出处：https://www.benjiyun.com/yunzhujiyunwei/vps-yunwei/6028.html