pgpool4.2.2+postgreSQL11.6读写分离高可用架构搭建测试

目录

pgpool最新版本的一些变化

本地服务器说明

流复制配置

同步复制环境准备

主节点配置（172.16.104.90）

从节点1配置

pg_basebackup生成从节点

从服务器修改postgresql.conf参数如下

从节点2配置

测试流复制

查看进程

查询数据库是否属于主库

查看主备同步状态。

主备切换

pgpool安装准备

pgdata 以及sudo免密配置

 配置Pg密码

配置134、130、136服务器互信

Pgpool相关插件安装

安装pgpool 4.2.2

配置pgpool 配置文件

 pgpool 启动

pgpool整体测试

使用pgpool 恢复从节点数据库（在线恢复）

切换pgpool 主从节点

故障转移

注意事项

---

pgpool最新版本的一些变化

1. 语句级别的负载平衡
2. 集群pgpool配置完全一致

   ---

    

本地服务器说明

pgpool版本： pgpool4.2.2

postgreSQL版本：postgreSQL11.6

服务器：

172.16.100.172

172.16.104.90（主节点）

172.16.104.91

---

流复制配置

同步复制环境准备

所有服务器执行

chown -R postgres:postgres /data/pgsql/

配置各主机pg环境变量

Vim /etc/profile

export PATH=/usr/package/pgsql/11/bin/:$PATH

source /etc/profile

mkdir /data/pgsql/archivedir

主节点配置（172.16.104.90）

修改 主节点服务器 pg_hba.conf ,添加如下

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# 表示允许任意网段的用户通过MD5进行认证连接  local一定要添加在故障转移时会切换脚本

local   all             all                                     trust

host    all             all             0.0.0.0/0               md5  

# 表示允许该网段10.10.56.0 的repl 用户进行流复制

host    replication     repl            0.0.0.0/0            md5

host    replication     all             ::1/128                 trust

修改主节点服务器 postgresqlconf 参数配置如下, # rtm 为注释

listen_addresses = '*'                       # rtm

port = 5432                                  # rtm

max_connections = 100                        # rtm

superuser_reserved_connections = 10          # 为超级用户保留的连接槽，实际并发用户为 max_connections 减去superuser_reserved_connections

wal_level = replica                          # rtm

archive_mode = on                            # rtm

archive_command = 'cp "%p" "/data/pgsql/archivedir/%f"'

max_wal_senders = 50                         # rtm

hot_standby = on

wal_log_hints = on

#日志

log_destination = 'csvlog ,stderr '

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1d

log_rotation_size = 100MB

# 记录执行慢的SQL

log_min_duration_statement = 1000

log_checkpoints = on

log_connections = on

log_disconnections = on

log_duration = on

log_line_prefix = '%m'

# 监控数据库中长时间的锁

log_lock_waits = on

# 记录DDL操作

log_statement = 'mod'

主服务器修改默认用户 postgres 的密码和创建用于 流复制的用户 repl

postgres=# ALTER USER postgres WITH PASSWORD '123456';

ALTER ROLE

postgres=# CREATE USER  repl WITH PASSWORD '123456' REPLICATION;

CREATE ROLE

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 repl      | Replication                                                | {}

————————————————

创建使用的测试数据库 pgpool 和 表 pgpool

postgres=# CREATE DATABASE pgpool ;

CREATE DATABASE

postgres=# \c pgpool

You are now connected to database "pgpool" as user "postgres".

pgpool=#

pgpool=# CREATE TABLE pgpool (id serial,age bigint,insertTime timestamp default now());

CREATE TABLE

pgpool=# insert into pgpool (age) values (1);

INSERT 0 1

pgpool=# select * from pgpool;

 id | age |         inserttime

----+-----+----------------------------

  1 |   1 | 2018-07-02 15:07:03.329849

(1 row)

————————————————

看数据库是否为主库f 表示为主库

postgres=# select * from pg_is_in_recovery();

 pg_is_in_recovery

-------------------

 f

(1 row)

 

postgres=#

 

重启主服务器

Postgres 用户

./pg_ctl -D /data/pgsql/sport/   restart

从节点1配置

pg_basebackup生成从节点

从服务器使用 pg_basebackup 命令在线创建一个备库,使用该命令请确保 主库已经启动，清除data目录下的所有文件。

postgres@CLWDB2: /data/pgsql/sport> pg_basebackup -h 192.168.233.130 -p 5432 -U repl -w -Fp -Xs -Pv -R -D /data/pgsql/sport

pg_basebackup: initiating base backup, waiting for checkpoint to complete

pg_basebackup: checkpoint completed

pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1

pg_basebackup: starting background WAL receiver

31133/31133 kB (100%), 1/1 tablespace

pg_basebackup: write-ahead log end point: 0/20000F8

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

参数说明

-h  启动的主库数据库地址               -p  主库数据库端口

-U  流复制用户                        -w  不使用密码验证

-Fp 备份输出正常的数据库目录           -Xs 使用流复制的方式进行复制

-Pv 输出复制过程的详细信息             -R  为备库创建recovery.conf文件

-D  指定创建的备库的数据库目录

服务器 在 recovery.conf 添加 application_name 为slave1,配置如下

stan