在業務對資料庫是讀多寫少，且單臺伺服器或者單個資料庫的效能已經不能滿足當前業務對資料庫大量讀取請求的時候，我們可能需要對資料庫做 讀寫分離。

常見的可以做讀寫分離的工具有mycat、ShardingSphere等。本文使用ShardingSphere-proxy實現了PostgreSQL的讀寫分離場景，如果對於PostgreSQL資料庫除讀寫分離外還需要連線池等需求，還可以瞭解一下PostgreSQL的pgpool-II。

一、本文相關ip地址

角色	版本	IP地址
主庫	PostgreSQL 13.4	172.20.10.9
從庫	PostgreSQL 13.4	172.20.10.10
ShardingSphere-proxy	5.1.2	172.20.10.3
用於測試連線ShardingSphere-Proxy（有psql客戶端）	psql版本 12.9	172.20.10.6

本次安裝部署是在CentOS Linux release 7.4上完成，開始已經部署好了PostgreSQL的主從流複製環境。在此不做贅述。

[postgres@primary ~]$ psql -c "select * from pg_stat_replication;"
  pid  | usesysid | usename | application_name | client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn |   
 write_lag    | flush_lag | replay_lag | sync_priority | sync_state |          reply_time          
-------+----------+---------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+---
--------------+-----------+------------+---------------+------------+------------------------------
 56107 |    16385 | replica | walreceiver      | 172.20.10.10 |                 |       46262 | 2022-08-02 12:02:49.504343+08 |              | streaming | 0/FB841B40 | 0/FB841B40 | 0/FB841B40 | 0/FB841B40 | 00
:00:00.037914 |           |            |             0 | async      | 2022-08-02 12:02:49.65561+08
(1 row)

二、資料庫配置

1.postgresql.conf裡配置

listen_addresses = '*'

2.pg_hba.conf白名單配置（這裡是我測試為了方便，如果實際使用，最好用範圍最小的實際ip段來約束）

host    all             all             0.0.0.0/0            md5

重啟資料庫

3.資料庫裡建立供測試ShardingSphere-proxy讀寫分離的使用者和資料庫（許可權在使用時候也要進行約束，可以給資料庫的owner，或根據需求調整）

create user ssp_user_test password 'Enmo@123' superuser;
create database ssp_db_test;

三、安裝部署ShardingSphere-proxy

在172.20.10.3節點安裝proxy

需要安裝jdk（must have Java JRE 8 or higher），我本地的jdk版本為

[root@primary conf]# java -version
openjdk version "1.8.0_342"
OpenJDK Runtime Environment (build 1.8.0_342-b07)
OpenJDK 64-Bit Server VM (build 25.342-b07, mixed mode)

1.獲取軟體包並安裝ShardingSphere-proxy

--下載地址
https://shardingsphere.apache.org/document/current/en/downloads/
--解壓
tar -xvf apache-shardingsphere-5.1.2-shardingsphere-proxy-bin.tar.gz
--做軟鏈
ln -s /root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin /opt/ssp

2.修改server.yaml,配置虛擬帳號密碼

配置虛擬帳號密碼，使用預設的root使用者及密碼root，且允許外網連線，同時許可權指定為“所有許可權無限制”，sql-show是用於在日誌中記錄下每條sql及執行的節點，其它為預設值

rules:
  - !AUTHORITY
    users:
      - root@%:root
#      - sharding@:sharding
    provider:
      type: type: ALL_PERMITTED
props:
#  max-connections-size-per-query: 1
#  kernel-executor-size: 16  # Infinite by default.
#  proxy-frontend-flush-threshold: 128  # The default value is 128.
#  proxy-hint-enabled: false
  sql-show: on

如圖所示

3.編輯讀寫分離檔案 config-readwrite-splitting.yaml

databaseName是我對映的資料庫名字，primary_ds和replica_ds_0分別為主庫/寫庫的相關配置和從庫/讀庫的相關被配置。

databaseName: xmaster_db
#
dataSources:
  primary_ds:
    url: jdbc:postgresql://172.20.10.9:5432/ssp_db_test
    username: ssp_user_test
    password: Enmo@123
#    connectiontimeoutMilliseconds: 30000
#    idletimeoutmilliseconds: 60000
#    maxlifetimemilliseconds: 1800000
#    maxpoolsize: 50
#   minpoolsize: 1
  replica_ds_0:
    url: jdbc:postgresql://172.20.10.10:5432/ssp_db_test
    username: ssp_user_test
    password: Enmo@123
rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      type: Static
      props:
        write-data-source-name: primary_ds
        read-data-source-names: replica_ds_0

四、啟動ShardingSphere-proxy並驗證

[root@localhost bin]# pwd
/root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/bin
[root@localhost bin]# sh start.sh 
we find java version: java8, full_version=1.8.0_342
Starting the ShardingSphere-Proxy ...
The classpath is /root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/conf:.:/root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/lib/*:/root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/ext-lib/*
Please check the STDOUT file: /root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/logs/stdout.log

檢視ShardingSphere-proxy輸出的日誌內容

[root@localhost bin]# cat /root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/logs/stdout.log
Thanks for using Atomikos! This installation is not registered yet. 
REGISTER FOR FREE at 
- tips & advice 
- working demos 
- access to the full documentation 
- special exclusive bonus offers not available to others 
- everything you need to get the most out of using Atomikos!
[INFO ] 2022-07-07 20:18:07.629 [main] o.a.s.p.v.ShardingSphereProxyVersion - Database name is `PostgreSQL`, version is `13.4`, database name is `xmaster_db`
[INFO ] 2022-07-07 20:18:07.889 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy Memory mode started successfully
Thanks for using Atomikos! This installation is not registered yet. 
REGISTER FOR FREE at 
- tips & advice 
- working demos 
- access to the full documentation 
- special exclusive bonus offers not available to others 
- everything you need to get the most out of using Atomikos!
[INFO ] 2022-07-07 20:19:27.088 [main] o.a.s.p.v.ShardingSphereProxyVersion - Database name is `PostgreSQL`, version is `13.4`, database name is `xmaster_db`
[INFO ] 2022-07-07 20:19:27.349 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy Memory mode started successfully

ShardingSphere-Proxy已經啟動起來了。ShardingSphere-Proxy的預設埠3307也已經起來了

[root@localhost bin]# ss -tnl
State       Recv-Q Send-Q       Local Address:Port                      Peer Address:Port              
LISTEN      0      128                      *:6000                                 *:*                  
LISTEN      0      128                      *:22                                   *:*                  
LISTEN      0      128                     :::3307                                :::*                  
LISTEN      0      128                     :::6000                                :::*                  
LISTEN      0      128                     :::22                                  :::*

這時候我們透過另外的終端，進行連線驗證，即我們的第四臺伺服器172.20.10.6，透過server.yaml裡的虛擬賬號密碼，可以連線進來

[postgres@localhost ~]$ ip a | grep ens33 | grep global
    inet 172.20.10.6/28 brd 172.20.10.15 scope global ens33
[postgres@localhost ~]$ psql -h 172.20.10.3  -U root xmaster_db -p 3307
Password for user root: 
psql (12.9, server 13.4-ShardingSphere-Proxy 5.1.2)
WARNING: psql major version 12, server major version 13.
         Some psql features might not work.
Type "help" for help.
xmaster_db=> create table t1(id int);
CREATE TABLE
xmaster_db=> insert into t1 values(1);
INSERT 0 1
xmaster_db=> select * from t1;
 id 
----
  1
(1 row)

插入資料期間可以去資料庫的主庫檢視資料情況

ssp_db_test=# \dt
           List of relations
 Schema | Name | Type  |     Owner     
--------+------+-------+---------------
 public | t1   | table | ssp_user_test
(1 row)
ssp_db_test=# select * from t1;
 id 
----
  1
(1 row)
ssp_db_test=# select * from t1;
ERROR:  relation "t1" does not exist
LINE 1: select * from t1;
                      ^
ssp_db_test=# \dt
Did not find any relations.

去檢視ShardingSphere-proxy的輸出日誌，發現create、insert、drop的操作都是在主節點進行的，而select在備機節點執行。

[root@localhost logs]# ll /root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/logs/stdout.log 
-rw-r--r-- 1 root root 4926 Aug  2 14:46 /root/apache-shardingsphere-5.1.2-shardingsphere-proxy-bin/logs/stdout.log

[INFO ] 2022-08-02 14:45:26.965 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: create table t1(id int);
[INFO ] 2022-08-02 14:45:26.965 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLCreateTableStatement(containsNotExistClause=false)
[INFO ] 2022-08-02 14:45:26.965 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: primary_ds ::: create table t1(id int);
[INFO ] 2022-08-02 14:45:29.655 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into t1 values(1);
[INFO ] 2022-08-02 14:45:29.655 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)
[INFO ] 2022-08-02 14:45:29.655 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: primary_ds ::: insert into t1 values(1);
[INFO ] 2022-08-02 14:45:31.953 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select * from t1;
[INFO ] 2022-08-02 14:45:31.953 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-08-02 14:45:31.953 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: replica_ds_0 ::: select * from t1;
[INFO ] 2022-08-02 14:46:00.073 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: drop table t1;
[INFO ] 2022-08-02 14:46:00.073 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLDropTableStatement(containsExistClause=false)
[INFO ] 2022-08-02 14:46:00.073 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: primary_ds ::: drop table t1;