Appearance
海量数据库单节点部署及补丁更新
单节点部署
配置hosts
bash
$ vi /etc/hosts
192.168.10.10 vastbase更新主机名
bash$ hostnamectl set-hostname vastbase
关闭透明大页
bash
$ echo never > /sys/kernel/mm/transparent_hugepage/enabled
$ echo never > /sys/kernel/mm/transparent_hugepage/defrag关闭防火墙或开放数据库端口
bash
# 关闭防火墙
$ systemctl status firewalld.service
$ systemctl stop firewalld.service
$ systemctl disable firewalld
# 开放数据库端口
$ firewall-cmd --zone=public --permanent --add-port=5432/tcp
$ firewall-cmd --reload
$ firewall-cmd --list-portSELINUX设置
bash
# 查看是否开启 SELINUX ，如果是未开启则是 diabled ,已开启则是 enforcing
$ getenforce
# 临时关闭 SELINUX
$ setenforce 0
# 通过修改配置文件永久关闭 SELINUX
（1）vi 编辑配置文件 /etc/selinux/config
（2）将 SELINUX=enforcing 修改为 SELINUX=disabled
（3）重启系统 rebootIPC参数设置
bash
# 修改 /etc/systemd/logind.conf 文件，修改 RemoveIPC=no
$ echo "RemoveIPC=no" >> /etc/systemd/logind.conf
# 添加 RemoveIPC=no 到 /usr/lib/systemd/system/systemd-logind.service
$ echo "RemoveIPC=no" >> /usr/lib/systemd/system/systemd-logind.service
$ systemctl daemon-reload
$ systemctl stop systemd-logind
$ systemctl start systemd-logind内核参数调整
编辑 /etc/sysctl.conf ，添加如下信息
bash
fs.aio-max-nr=1048576
fs.file-max= 76724600
kernel.sem = 4096 2147483647 2147483646 512000 # 若提示无效可以尝试减小512000值
kernel.shmall = 3643361024 # pages, 80% MEM or higher
kernel.shmmax = 86641802241024 # bytes, 80% MEM or higher
kernel.shmmni = 819200 # 若提示无效可以尝试减小
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304
net.core.somaxconn = 4096
net.ipv4.tcp_fin_timeout = 5
vm.overcommit_memory = 0
vm.swappiness = 10
net.ipv4.ip_local_port_range = 40000 65535
fs.nr_open = 20480000
kernel.core_pattern = /data/core_pattern/core-%e-%p-%t执行以下命令，使配置生效
注意若部分参数报错
bash
sysctl -p时间配置
检查并确保时间配置正常
bash
# 查看服务器使用的时间类型
$ timedatectl
$ ll /etc/localtime
# 修改时区
$ ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
$ date
$ hwclock --show
$ hwclock -w
# 将系统时间写入硬件CMOS
$ clock -w创建用户与目录
bash
$ useradd vastbase
$ echo "vastbase:vastbase" | chpasswd创建数据库数据目录、软件目录
bash
$ mkdir -p /data/vastbase/{vastdata,backup,archive_wals,core_pattern}
$ chmod 700 /data/vastbase/{vastdata,backup,archive_wals,core_pattern}
$ chown -R vastbase:vastbase /data/vastbase/{vastdata,backup,archive_wals,core_pattern}修改资源限制
bash
$ vi /etc/security/limits.conf
vastbase soft nproc unlimited
vastbase hard nproc unlimited
vastbase soft nofile 102400
vastbase hard nofile 102400
vastbase soft stack unlimited
vastbase hard stack unlimited
vastbase soft core 8388608 # KB 100% OS_MEM 按照实际主机内存调整
vastbase hard core 8388608 # KB 100% OS_MEM 按照实际主机内存调整
vastbase soft memlock unlimited
vastbase hard memlock unlimited依赖安装
如果离线环境，可以在有网的服务器上下载后，传到离线服务器安装
bash
$ yum install -y zlib-devel libaio libuuid readline-devel krb5-libs libicu libxslt tcl perl openldap pam openssl-devel libxml2 bzip2数据库安装
解压安装
bash
# 解压
$ tar zxvf Vastbase-G100-installer-2.2_Build10(10603)-centos_7-x86_64-no_mot-20221205.tar.gz
# 进入解压后的目录
$ cd vastbase-installer
# 执行安装
$ ./vastbase_installer根据文字提示安装，根据需要选择自定义/经典安装，根据提示进行安装即可
数据库参数配置
修改postgresql.conf文件，追加业务相关参数
注意：配置追加即可，已存在的配置会覆盖
bash
$ source ~/.bashrc
$ vi $PGDATA/postgresql.conf
#Base Configuration for OS_MEM=8GB
license_path='/data/vastbase/local/vastbase/.license'
listen_addresses='*'
port=5432
max_connections = 1000
maintenance_work_mem=64MB # 64MB~20GB
shared_buffers=1GB # 25% OS_MEM
effective_cache_size=6GB # 75% OS_MEM
max_process_memory=6GB # 75% OS_MEM
work_mem=2MB
cstore_buffers=16MB
wal_buffers=16MB
archive_mode=on
archive_command='cp --remove-destination %p /data/archive_wals/%f'
vacuum_cost_limit=10000
autovacuum=on
autovacuum_mode=mix
autovacuum_max_workers=5
autovacuum_naptime=20s
autovacuum_vacuum_cost_delay=10
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.1
track_sql_count=on
session_timeout=0
enable_cbm_tracking=on
enable_thread_pool = off
password_encryption_type=0
password_force_alter=off
password_effect_time=36500
failed_login_attempts=10
remote_read_mode='non_authentication'
enable_resource_track=on
instr_unique_sql_count=20000
enable_auto_clean_unique_sql=on
enable_wdr_snapshot=on
enable_stmt_track=off
temp_file_limit='50GB'
xloginsert_locks=8
log_lock_waits=on
behavior_compat_options='plsql_security_definer,skip_insert_gs_source'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
timezone= 'PRC'
log_timezone= 'PRC'
wal_level=hot_standby
hot_standby=on
max_wal_senders=4
vastbase_login_info=false
log_statement='ddl'
log_duration = off
log_line_prefix = '%m %r %d %u [%p]'
shared_preload_libraries = 'pg_stat_statements,decoderbufs,jdbc_fdw'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_activity_query_size=4096
enable_opfusion=off添加系统服务
下面
${}的内容使用vasebase用户执行cat ~/.Vastbase查看，进行替换
bash
$ vi /usr/lib/systemd/system/vastbase.service
[Unit]
Description=Vastbase G100 server
After=network.target network-online.target
[Service]
# 注意修改下方标红处，x86环境应修改为amd64
#Environment="LD_LIBRARY_PATH=/home/vastbase/vasthome/lib:/home/vastbase/vasthome/jre/lib/aarch64:/home/vastbase/vasthome/jre/lib/aarch64/server"
Environment="LD_LIBRARY_PATH=${LD_LIBRARY_PATH}"
Environment="GAUSSHOME=${GAUSSHOME}"
Environment="LANG=en_US.UTF-8"
Type=forking
Restart=always
RestartSec=3
#Restart=no
User=vastbase
LimitNOFILE=102400
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
#注意调整目录路径
ExecStart=${GAUSSHOME}/bin/vb_ctl -D ${PGDATA} start
ExecStop=${GAUSSHOME}/bin/vb_ctl -D ${PGDATA} stop
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
[Install]
WantedBy=multi-user.target样例
bash$ cat ~/.Vastbase export PGPORT=5432 export PGUSER=vastbase export PGDATA=/data/vastbase/data/vastbase export LD_LIBRARY_PATH=/data/vastbase/local/vastbase/lib:/data/vastbase/local/vastbase/jre/lib/amd64:/data/vastbase/local/vastbase/jre/lib/amd64/server:$LD_LIBRARY_PATH export GAUSSHOME=/data/vastbase/local/vastbase export PATH=/data/vastbase/local/vastbase/bin:$PATH export OM_GPHOME=/data/vastbase/local/omTmp export LD_LIBRARY_PATH=$OM_GPHOME/lib:$LD_LIBRARY_PATH export PATH=$OM_GPHOME/script/gspylib/pssh/bin:$OM_GPHOME/script:$PATH export PYTHONPATH=$OM_GPHOME/lib export OM_GAUSS_VERSION=2.2.10 export OM_PGHOST=/data/vastbase/local/omTmp/tmp export OM_GAUSSLOG=/data/vastbase/data/vastbase/pg_log export OM_GAUSS_ENV=2 export OM_GS_CLUSTER_NAME=dbClusterservice
bash$ vi /usr/lib/systemd/system/vastbase.service [Unit] Description=Vastbase G100 server After=network.target network-online.target [Service] Environment="LD_LIBRARY_PATH=/data/vastbase/local/vastbase/lib:/data/vastbase/local/vastbase/jre/lib/amd64:/data/vastbase/local/vastbase/jre/lib/amd64/server" Environment="GAUSSHOME=/data/vastbase/local/vastbase" Environment="LANG=en_US.UTF-8" Type=forking Restart=always RestartSec=3 #Restart=no User=vastbase LimitNOFILE=102400 # Where to send early-startup messages from the server (before the logging # options of postgresql.conf take effect) # This is normally controlled by the global default set by systemd # StandardOutput=syslog # Disable OOM kill on the postmaster OOMScoreAdjust=-1000 ExecStart=/data/vastbase/local/vastbase/bin/vb_ctl -D /data/vastbase/data/vastbase start ExecStop=/data/vastbase/local/vastbase/bin/vb_ctl -D /data/vastbase/data/vastbase stop # Give a reasonable amount of time for the server to start up/shut down TimeoutSec=300 [Install] WantedBy=multi-user.target
加载服务、设置开机启动、启动
bash
$ systemctl daemon-reload
$ systemctl enable vastbase
$ systemctl start vastbase
$ systemctl status vastbase常用命令
sql
# 连接数据库【需要在vastbase用户执行】
vsql -r
# 创建用户
create user 用户 identified by '密码';
# 给予用户sysadmin
alter user 用户名 sysadmin;
# 给予用户登录权限
alter user 用户 with login;
# 创建数据库
create database 数据库;
# 给予某用户数据库全部权限
GRANT ALL PRIVILEGES ON DATABASE 数据库 TO 用户;
# 兼容MySQL建库【DBCOMPATIBILITY='B'】
create database 数据库 with owner=数据库所有者 encoding='utf8' DBCOMPATIBILITY='B' lower_case_table_names=1;补丁更新
查询信息
使用vasebase用户查看.Vastbase信息
bash
$ cat ~/.Vastbase
export PGPORT=5432
export PGUSER=vastbase
export PGDATA=/data/vastbase/data/vastbase
export LD_LIBRARY_PATH=/data/vastbase/local/vastbase/lib:/data/vastbase/local/vastbase/jre/lib/amd64:/data/vastbase/local/vastbase/jre/lib/amd64/server:$LD_LIBRARY_PATH
export GAUSSHOME=/data/vastbase/local/vastbase
export PATH=/data/vastbase/local/vastbase/bin:$PATH
export OM_GPHOME=/data/vastbase/local/omTmp
export LD_LIBRARY_PATH=$OM_GPHOME/lib:$LD_LIBRARY_PATH
export PATH=$OM_GPHOME/script/gspylib/pssh/bin:$OM_GPHOME/script:$PATH
export PYTHONPATH=$OM_GPHOME/lib
export OM_GAUSS_VERSION=2.2.10
export OM_PGHOST=/data/vastbase/local/omTmp/tmp
export OM_GAUSSLOG=/data/vastbase/data/vastbase/pg_log
export OM_GAUSS_ENV=2
export OM_GS_CLUSTER_NAME=dbCluster升级操作
使用一个会话执行
1.检查$GAUSSHOME/../omTmp/tmp/也就是OM_PGHOST是否为空，若不为空需要删除该目录下的文件
bash
$ su - vastbase
$ ll $GAUSSHOME/../omTmp/tmp/
$ rm -rf $GAUSSHOME/../omTmp/tmp/*2.数据库参数检查，升级前请检查以下数据库参数是否配置正确，避免影响数据库升级
bash
logging_collector=on #此参数修改需重启数据库生效
enable_global_syscache=on #此参数修改需重启数据库生效
uppercase_attribute_name=off
# 此参数修改方式：登录数据库后执行 alter system set uppercase_attribute_name=off;
# === 检查步骤：
su - vastbase
vsql -c 'show logging_collector;show enable_global_syscache;show uppercase_attribute_name;'3.备份数据库目录（软件目录必须备份，数据目录建议备份）
备份前先停止，
vb_ctl stop
4.解压【root用户】
bash
tar -xf 2-2.2-10-92-702-9104-centos-7-x86-64-1.0.1-11-20230816.tar.gz
cd patch/
tar -xf 2-2.2-10-92-702-9104-centos-7-x86-64-1.0.1-11.tar.gz
cd 2-2.2-10-92-702-9104-centos-7-x86-64-1.0.1-115.进入VPatch下的script目录【root用户】
cd /data/patch/2-2.2-10-92-702-9104-centos-7-x86-64-1.0.1-11/VPatch/script如果没有Python3环境，执行：
source prepare_env.sh
6.指定升级参数【root用户】
下面
${}的内容使用vasebase用户执行cat ~/.Vastbase查看，进行替换
bash
# 相关信息从.Vastbase找：
# ./vpatch --setparam -u vastbase -g vastbase -a ${GAUSSHOME} -d ${PGDATA} -p 5432 -t ${OM_PGHOST}
./vpatch --setparam -u vastbase -g vastbase -a /data/vastbase/local/vastbase -d /data/vastbase/data/vastbase -p 5432 -t /data/vastbase/local/omTmp/tmp7.执行命令进行升级环境检查【root用户】
bash
$ cd /data/patch/2-2.2-10-92-702-9104-centos-7-x86-64-1.0.1-11/VPatch/script
$ ./vpatch -C
Start checking.
Begin clearing temporary environment variables in the file (/home/vastbase/.bashrc).
Checking disk space.
Checking disk space successfully.
The cluster configuration file created is: /data/vastbase/local/omTmp/tmp/vb_cluster_vastbase_vastbase_5432.xml.
Successful check ok!8.执行命令进行用户权限检查，期间输入yes【root用户】
bash
$ cd /data/patch/2-2.2-10-92-702-9104-centos-7-x86-64-1.0.1-11/VPatch/script
$ ./vpatch -P
Before upgrading, please back up the original product and user data package, stop the database service, and maually detach the node from the cluster. The upgrade process may involve modifying the metadata and switching the upgrade mode. The service will be started during the upgrade process. Please specify a dedicated port number for the upgrade to avoid the upgrade process being affected by external commands. And do not put any backup files in the patch directory. Do not delete any files and directories generated by unzipping the patch tar package, and do not destroy the directory structure, including the outermost directory with the same name as the tar package, Do you want to continue the upgrade (please input yes or no):
yes
Begin preparing patch.
Successfully prepared patch.
Begin completing the link of the software.
Successfully completed the link of the software.
Begin preparing patches and environment variables.
Performing cmd: python3 '/data/patch/2-2.2-10-92-702-9104-centos-7-x86-64-1.0.1-11/VPatch/script/gs_preinstall' -U 'vastbase' -G 'vastbase' -X '/data/vastbase/local/omTmp/tmp/vb_cluster_vastbase_vastbase_5432.xml' --non-interactive.
A1. [ OS version status ] : Normal
A2. [ Kernel version status ] : Normal
A3. [ Unicode status ] : Normal
A4. [ Time zone status ] : Normal
A5. [ Swap memory status ] : Warning
A6. [ System control parameters status ] : Normal
A7. [ File system configuration status ] : Warning
A8. [ Disk configuration status ] : Normal
A9. [ Pre-read block size status ] : Normal
A10.[ IO scheduler status ] : Normal
A11.[ Network card configuration status ] : Warning
A12.[ Time consistency status ] : Warning
A13.[ Firewall service status ] : Normal
A14.[ THP service status ] : Normal
Successfully prepared patches and environment variables.
Generate flag file preparation_succeeded.json ( /data/vastbase/local/omTmp/tmp/preparation_succeeded.json ).
Successful prepare ok!9.检查数据库软件目录下，确保所有文件所有者均为vastbase
bash
$ ll $GAUSSHOME
$ chown -R vastbase:vastbase /data/patch/ /data/vastbase/10.切换数据库初始化用户进行升级
bash
$ su - vastbase
$ cd /data/patch/2-2.2-10-92-702-9104-centos-7-x86-64-1.0.1-11/VPatch/script
$ ./vpatch --upgrade
Perform vpatch upgrade.
Upgrade instances under user vastbase and group vastbase.
Regenerate profile. user: vastbase, group: vastbase, clusterXml: /data/vastbase/local/omTmp/tmp/vb_cluster_vastbase_vastbase_5432.xml.
Successfully regenerate profile. user: vastbase, group: vastbase, clusterXml: /data/vastbase/local/omTmp/tmp/vb_cluster_vastbase_vastbase_5432.xml.
Perform upgrade. clusterXml: /data/vastbase/local/omTmp/tmp/vb_cluster_vastbase_vastbase_5432.xml.
Successfully upgrade.
Perform commit. clusterXml: /data/vastbase/local/omTmp/tmp/vb_cluster_vastbase_vastbase_5432.xml.
Successfully commit.
Begin clearing temporary environment variables in the file (/home/vastbase/.bashrc).
Start check.
Successfully perform vpatch upgrade.11.切换root用户，清理root环境变量（也可直接编辑.bashrc文件，注释末尾的PATH、LD_LIBRARY_PATH两行）
bash
./clean_env.sh