一、opengauss 的背景和行业现状
2022 年，七大 openGauss 商业版发布，是基于 openGauss3.0 推出商业发行版
目前海量数据库 Vastbase 表现最佳，一直是 TOP 1
作者认为之所以海量数据库 Vastbase 目前无法被同行超越，和各家研发实力和技术背景有关
众所周知，opengauss 起源于 postgresql，在此基础上做了改良
海量数据库从 postgresql 就已经开始做商业版，一直做到 opengauss，经验最丰富，其他产品例如 MongoDB 是紧随其后 (在此不讨论其存在的意义)，目前产品百花齐放，更显锦上添花
Vastbase G100 采用业界证明稳定高效的日志流复制技术实现集群内主备库的数据和状态传输，
并通过一主多备的部署和配置架构实现了多点可读、数据不丢失、异地灾备等关键高可用场景。
二、部署openGauss 3.0.0分布式
4台ECS服务器，配置4vCPUs | 16GiB | c7.xlarge.4 CentOS 7.9 64bit
2.1服务器/etc/hosts配置成一样的
echo momo488m > /etc/hostname
echo momo488s1 > /etc/hostname
echo momo488s2 > /etc/hostname
echo momo488s3 > /etc/hostname
[root@momo488s1 ~]# cat /etc/hosts
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.0.250 momo488m
192.168.0.213 momo488s1
192.168.0.89 momo488s2
192.168.0.24 momo488s3
scp /etc/hosts momo488s1:/etc/hosts
scp /etc/hosts momo488s2:/etc/hosts
scp /etc/hosts momo488s3:/etc/hosts
2.2配置互信
ssh-keygen -t rsa
cat .ssh/id_rsa.pub >>~/.ssh/authorized_keys
ssh-keyscan -t rsa momo488m >>~/.ssh/known_hosts
ssh-keyscan -t rsa momo488s1 >>~/.ssh/known_hosts
ssh-keyscan -t rsa momo488s2 >>~/.ssh/known_hosts
ssh-keyscan -t rsa momo488s3 >>~/.ssh/known_hosts
scp -r ~/.ssh momo488s1:~
scp -r ~/.ssh momo488s2:~
scp -r ~/.ssh momo488s3:~
测试
ssh momo488m date
ssh momo488s1 date
ssh momo488s2 date
ssh momo488s3 date
2.3下载软件
1>下载openGauss-distributed-solutions-master.zip并上传
https://gitee.com/opengauss/openGauss-distributed-solutions
2>下载openGauss分布式镜像
wget https://opengauss.obs.cn-south-1.myhuaweicloud.com/3.0.0/distributed/openGauss-distributed-CentOS-x86_64.tar.gz
3>创建目录/data，将软解压到/data
mkdir /data
unzip openGauss-distributed-solutions-master.zip
tar -zxvf openGauss-distributed-CentOS-x86_64.tar.gz
[root@momo488m data]# scp *tar* momo488s1:~
[root@momo488m data]# scp *tar* momo488s2:~
[root@momo488m data]# scp *tar* momo488s3:~
2.4创建配置文件profile
[root@momo488m simple_install]# pwd
/root/openGauss-distributed-solutions-master/simple_install
[root@momo488m simple_install]# ll
total 44
drwxr-xr-x 4 root root 4096 Mar 31 2022 addons
-rw-r--r-- 1 root root 415 Mar 31 2022 env_clean.sh
-rw-r--r-- 1 root root 1201 Mar 31 2022 install-addons.sh
-rw-r--r-- 1 root root 652 Mar 31 2022 install-docker.sh
-rw-r--r-- 1 root root 956 Mar 31 2022 install-k8s.sh
-rw-r--r-- 1 root root 4998 Mar 31 2022 install-master.sh
-rw-r--r-- 1 root root 957 Mar 31 2022 node-template.sh
-rw-r--r-- 1 root root 60 Mar 31 2022 profile
-rw-r--r-- 1 root root 1573 Mar 31 2022 pull-docker.sh
-rw-r--r-- 1 root root 1026 Mar 31 2022 setupconfig.sh
[root@momo488m simple_install]# vi profile
[root@momo488m simple_install]# cat profile
master=192.168.0.250
node1=192.168.0.213
node2=192.168.0.89
node3=192.168.0.24
[root@momo488m simple_install]# chmod +x *.sh
2.5执行安装脚本
清理
[root@momo488m simple_install]# sh env_clean.sh
安装
[root@momo488m simple_install]# sh install-master.sh
查看
[root@momo488m simple_install]# kubectl get nodes -o wide
NAME STATUS ROLES AGE VERSION INTERNAL-IP EXTERNAL-IP OS-IMAGE KERNEL-VERSION CONTAINER-RUNTIME
momo488m NotReady control-plane,master 10m v1.21.1 192.168.0.108 <none> CentOS Linux 7 (Core) 3.10.0-1160.53.1.el7.x86_64 docker://18.6.1
momo488s1 NotReady <none> 6m24s v1.21.1 192.168.0.70 <none> CentOS Linux 7 (Core) 3.10.0-1160.53.1.el7.x86_64 docker://18.6.1
momo488s2 NotReady <none> 3m24s v1.21.1 192.168.0.137 <none> CentOS Linux 7 (Core) 3.10.0-1160.53.1.el7.x86_64 docker://18.6.1
momo488s3 NotReady <none> 5s v1.21.1 192.168.0.173 <none> CentOS Linux 7 (Core) 3.10.0-1160.53.1.el7.x86_64 docker://18.6.1
[root@momo488m simple_install]# kubectl get pod --all-namespaces -o wide
NAMESPACE NAME READY STATUS RESTARTS AGE IP NODE NOMINATED NODE READINESS GATES
kube-system coredns-558bd4d5db-58c92 0/1 Pending 0 12m <none> <none> <none> <none>
kube-system coredns-558bd4d5db-zgsd7 0/1 Pending 0 12m <none> <none> <none> <none>
2.6修复coredns不启动故障
[root@momo488m ~]# docker pull quay.io/coreos/flannel:v0.15.0
[root@momo488m ~]# kubectl apply -f https://raw.githubusercontent.com/coreos/flannel/master/Documentation/kube-flannel.yml
2.7导入镜像
所有节点都执行
# docker load < haproxy100.tar.gz
# docker load < opengauss-3.0.0.tar
# docker load < shardingsphere-proxy.tar
2.8创建shardingphere集群分片
[root@momo488m ~]# cd /data/openGauss-distributed-solutions-master/distributed/
[root@momo488m distributed]# sh sharding.sh opengauss 3 3 "shared_buffers=2GB#max_connections=300#allow_concurrent_tuple_update=true"
设置密码 MoMo@488
查状态
kubectl get pod --all-namespaces -o wide
kubectl get svc --all-namespaces -o wide
查日志
kubectl logs -f --tail 200 opengauss-1-1 -n opengauss-1
2.9生成shardingphere配置文件
1>修改main_sharding_config.py
修改脚本
@classmethod
def load(cls):
with open(PathConf.get_src_yaml(), "rt", encoding='utf-8') as f:
count = f.read()
return yaml.safe_load(count)
@classmethod
def load_user_input(cls):
with open(PathConf.get_user_input_yaml(), "rt", encoding='utf-8') as f:
count = f.read()
return yaml.safe_load(count)
2>编辑user_input.yaml
dataSources:
- 10.244.1.4 5000 postgres admin MoMo@488
- 10.244.1.2 5000 postgres admin MoMo@488
- 10.244.1.3 5000 postgres admin MoMo@488
tables:
- table3 col1 2 col2 3
3>执行main_sharding_config.py
[root@momo488m distributed]# python3 main_sharding_config.py
{'schemaName': 'sharding_db', 'dataSources': {'ds_0': {'url': 'jdbc:opengauss://10.244.2.8:5000/postgres?serverTimezone=UTC&useSSL=false', 'username': 'admin', 'password': 'MoMo@488', 'connectionTimeoutMilliseconds': 30000, 'idleTimeoutMilliseconds': 60000, 'maxLifetimeMilliseconds': 1800000, 'maxPoolSize': 4096, 'minPoolSize': 10}, 'ds_1': {'url': 'jdbc:opengauss://10.244.2.9:5000/postgres?serverTimezone=UTC&useSSL=false', 'username': 'admin', 'password': 'MoMo@488', 'connectionTimeoutMilliseconds': 30000, 'idleTimeoutMilliseconds': 60000, 'maxLifetimeMilliseconds': 1800000, 'maxPoolSize': 4096, 'minPoolSize': 10}}, 'rules': {'tables': {'test_order': {'actualDataNodes': 'ds_0.test_order'}, 'table3': {'actualDataNodes': 'ds_${0..1}.table3_${0..2}', 'databaseStrategy': {'standard': {'shardingColumn': 'col1', 'shardingAlgorithmName': 'ds_table3_inline'}}, 'tableStrategy': {'standard': {'shardingColumn': 'col2', 'shardingAlgorithmName': 'tb_table3_inline'}}}}, 'defaultDatabaseStrategy': {'none': None}, 'defaultTableStrategy': {'none': None}, 'shardingAlgorithms': {'database_inline': {'props': {'algorithm-expression': 'ds_${user_id % 2}'}, 'type': 'INLINE'}, 'ds_table3_inline': {'props': {'algorithm-expression': 'ds_${col1 % 2}'}, 'type': 'INLINE'}, 'tb_table3_inline': {'props': {'algorithm-expression': 'table3_${col2 % 3}'}, 'type': 'INLINE'}}, 'keyGenerators': {'snowflake': {'type': 'SNOWFLAKE', 'props': {'worker-id': 123}}}}}
2.10检查复制状态
[root@momo488m distributed]# kubectl exec -ti opengauss-1-1 -n opengauss-1 -- bash
[root@opengauss-1-1 /]# patronictl -c /home/omm/patroni.yaml topology
+-----------------+------------+---------+---------+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+ Cluster: opengauss (12571552110593558695) -------+----+-----------+-----------------+
| opengauss-1-1 | 10.244.2.5 | Leader | running | 1 | | * |
| + opengauss-1-2 | 10.244.2.4 | Replica | running | 1 | 0 | * |
| + opengauss-1-3 | 10.244.3.6 | Replica | running | 1 | 0 | * |
+-----------------+------------+---------+---------+----+-----------+-----------------+
[root@momo488m distributed]# kubectl exec -ti opengauss-2-1 -n opengauss-2 -- bash
[root@opengauss-2-1 /]# patronictl -c /home/omm/patroni.yaml topology
+-----------------+------------+---------+---------+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+ Cluster: opengauss (1022351682206865095) --------+----+-----------+-----------------+
| opengauss-2-1 | 10.244.2.3 | Leader | running | 1 | | * |
| + opengauss-2-2 | 10.244.3.3 | Replica | running | 1 | 0 | * |
| + opengauss-2-3 | 10.244.3.5 | Replica | running | 1 | 0 | * |
+-----------------+------------+---------+---------+----+-----------+-----------------+
[root@momo488m distributed]# kubectl exec -ti opengauss-3-1 -n opengauss-3 -- bash
[root@opengauss-3-1 /]# patronictl -c /home/omm/patroni.yaml topology
+-----------------+------------+---------+---------+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Pending restart |
+ Cluster: opengauss (17450237737194323553) -------+----+-----------+-----------------+
| opengauss-3-1 | 10.244.2.6 | Leader | running | 1 | | * |
| + opengauss-3-2 | 10.244.3.4 | Replica | running | 1 | 0 | * |
| + opengauss-3-3 | 10.244.3.7 | Replica | running | 1 | 0 | * |
+-----------------+------------+---------+---------+----+-----------+-----------------+
2.11部署shardingphere服务
[root@momo488m distributed]# mkdir /etc/k8s/
[root@momo488m distributed]# cp server.yaml /etc/k8s/
[root@momo488m distributed]# cp logback.xml /etc/k8s/
如果部署到其他节点使用
scp server.yaml momo488s1:/etc/k8s/
scp logback.xml momo488s1:/etc/k8s/
[root@momo488m distributed]# sh delpoy_sharding.sh test /etc/k8s/ momo488m
pod "test-sha" deleted
service "test-service-sha" deleted
config-sharding_tmp.yaml 100% 1338 8.4MB/s 00:00
service/test-service-sha created
pod/test-sha created
2.12测试连接之前
cd /var/lib/docker/overlay2/51faaa1be0f81f56017b2bba8b2ee81123931a64814b4a20c0a0661cbcdcfc78/diff/usr/local/opengauss/lib/
把所有文件都拷贝到/usr/lib64/
2.13登录sharding_db
[root@momo488m lib64]# /var/lib/docker/overlay2/e3fb4661acf841646ac29cb23832779edd0880ab5afe2ff1f39f8f1b9a686037/diff/usr/local/opengauss/bin/gsql -d sharding_db -p 30400 -r -h momo488m -U root
Password for user root:密码是root
gsql ((openGauss 3.0.0 build ee30df52) compiled at 2022-03-30 20:04:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
sharding_db=>
2.14创建table插入数据
sharding_db=> create table table3 (col1 int primary key,col2 int);
CREATE TABLE
insert into table3 values (1,1);
insert into table3 values (2,2);
insert into table3 values (3,3);
insert into table3 values (4,4);
insert into table3 values (5,5);
insert into table3 values (6,6);
insert into table3 values (7,7);
insert into table3 values (8,8);
insert into table3 values (9,9);
2.15根据分库分表规则，分库数量2，分表数量3
sharding_db=> select * from table3;
col1 | col2
------+------
6 | 6
4 | 4
2 | 2
8 | 8
3 | 3
9 | 9
1 | 1
7 | 7
5 | 5
(9 rows)
table3将根据col1被分到2个数据库里，其中一个4行，一个5行
table3将在每个库里，以3个表的形式体现，根据col2被分到3个表里
2.16登录验证
[root@momo488m distributed]# kubectl exec -ti opengauss-1-1 -n opengauss-1 -- bash
[root@opengauss-1-1 /]# su omm
bash-4.2$ gsql -d postgres -p 5432 -r -U admin
Password for user admin:
gsql ((openGauss 3.0.0 build ee30df52) compiled at 2022-03-30 20:04:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=> \dt
No relations found.
[root@momo488m ~]# kubectl exec -ti opengauss-2-1 -n opengauss-2 -- bash
[root@opengauss-2-1 /]# su omm
bash-4.2$ gsql -d postgres -p 5432 -r -U admin
Password for user admin:
gsql ((openGauss 3.0.0 build ee30df52) compiled at 2022-03-30 20:04:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+-------+----------------------------------
admin | table3_0 | table | admin | {orientation=row,compression=no}
admin | table3_1 | table | admin | {orientation=row,compression=no}
admin | table3_2 | table | admin | {orientation=row,compression=no}
(3 rows)
openGauss=> select * from table3_0;
col1 | col2
------+------
6 | 6
(1 row)
openGauss=> select * from table3_1;
col1 | col2
------+------
4 | 4
(1 row)
openGauss=> select * from table3_2;
col1 | col2
------+------
2 | 2
8 | 8
(2 rows)
[root@momo488m ~]# kubectl exec -ti opengauss-3-1 -n opengauss-3 -- bash
[root@opengauss-3-1 /]# su omm
bash-4.2$ gsql -d postgres -p 5432 -r -U admin
Password for user admin:
gsql ((openGauss 3.0.0 build ee30df52) compiled at 2022-03-30 20:04:30 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+-------+----------------------------------
admin | table3_0 | table | admin | {orientation=row,compression=no}
admin | table3_1 | table | admin | {orientation=row,compression=no}
admin | table3_2 | table | admin | {orientation=row,compression=no}
(3 rows)
openGauss=> select * from table3_0;
col1 | col2
------+------
3 | 3
9 | 9
(2 rows)
openGauss=> select * from table3_1;
col1 | col2
------+------
1 | 1
7 | 7
(2 rows)
openGauss=> select * from table3_2;
col1 | col2
------+------
5 | 5
(1 row)
本文参考官方文档
https://docs.opengauss.org/zh/
https://docs.vastdata.com.cn/zh/