《童虎学习笔记》15分钟ShardingSphere搭建PostgreSQL分库分表

本文章配套视频	https://www.ixigua.com/7077056019024904717?id=7082741456641163790
本专栏全部文章	https://blog.csdn.net/tonghu_note/category_11713514.html
总目录	https://blog.csdn.net/tonghu_note/article/details/124333034

来我的dou音 aa10246666， 看配套视频

---

一、实战环境

角色	版本	机器名	IP地址
shard1	pg 14	node1	10.211.55.9
shard2	pg 14	node2	10.211.55.4
ShardingSphere-Proxy	5.1.0	node3	10.211.55.6
用于测试连接ShardingSphere-Proxy	pg 14	node4	10.211.55.7

---

二、 shard1和shard2上准备环境

1、创建ShardingSphere连接pg所使用的帐号

create user appuser with password '123';
grant appuser to postgres ;

vim /etc/postgresql/14/main/pg_hba.conf

host    all             appuser        0.0.0.0/0                md5 

重载访问控制文件 select pg_reload_conf();

postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

2、创建ShardingSphere连接pg所使用的数据库

shard1上创建数据库 shard1

create database shard1;

shard2上创建数据库 shard2

create database shard2;

三、ShardingSphere-Proxy上准备环境

1、安装ShardingSphere-Proxy

安装及配置jdk(jdk的版本要依据你cpu来选择，我用的是mac虚出来的虚机)

tar xzf jdk-8u202-linux-arm64-vfp-hflt.tar.gz

mv jdk1.8.0_202 /usr/local/

/etc/profile最后添加如下配置

PATH=/usr/local/jdk1.8.0_202/bin:$PATH

source /etc/profile

 安装及配置ShardingSphere-Proxy

tar xzf apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz

mv apache-shardingsphere-5.1.0-shardingsphere-proxy-bin /usr/local/

2、修改配置文件 server.yaml

rules:
  - !AUTHORITY
    users:
      - root@%:123
    provider:
      type: ALL_PRIVILEGES_PERMITTED

props:
  sql-show: true

3、修改配置文件 config-sharding.yaml

schemaName: testdb

dataSources:
  ds_0:
    url: jdbc:postgresql://10.211.55.9:5432/shard1
    username: appuser
    password: 123
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:postgresql://10.211.55.4:5432/shard2
    username: appuser
    password: 123
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..15}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
          column: order_id
          keyGeneratorName: snowflake
#    t_order_item:
#      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
#      tableStrategy:
#        standard:
#          shardingColumn: order_id
#          shardingAlgorithmName: t_order_item_inline
#      keyGenerateStrategy:
#        column: order_item_id
#        keyGeneratorName: snowflake
  bindingTables:
    - t_order
#    - t_order,t_order_item
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:
  
  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expres