【最佳实践】MogDB 5.0.4 单机版安装部署说明

原作者:黄宸宁

适用范围

MogDB 5.x 版本的单机部署

安装过程

部署环境信息

1. 软硬件版本

说明：由于麒麟最新内核版本4.19.90-23.28.v2101 下默认OpenSSH_8.2p1 OpenSSL 1.1.1f 与mogdb ibcrypto.so.1.1 存在冲,安装前确认当前openssh openssl 版本信息。

系统配置

1. 创建用户与组

使用 root 用户执行

groupadd -g 4001 sysomm
useradd  -u 4001 -g 4001 sysomm
echo 'K@ifa180ora%hw' | passwd --stdin sysomm
echo 'sysomm' >> /etc/cron.allow

2. 修改/etc/hosts文件

使用root 用户执行

cat >> /etc/hosts <<eof
180.2.72.50 csv-ltB-dspmogdb-72-50
eof

3. 关闭防火墙

使用root用户执行

systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service

systemctl stop iptables.service
systemctl disable iptables.service
systemctl status iptables.service

4. 关闭系统 audit 服务

使用 root 用户执行

systemctl stop auditd
systemctl disable auditd
systemctl status auditd

5. 关闭selinux

使用root用户执行

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
getenforce

6. 内核参数配置

使用 root 用户执行

cat >> /etc/sysctl.conf <<eof
####for mogdb
kernel.sem=250 6400000 1000 25600
net.ipv4.tcp_retries1=5
net.core.somaxconn=65535
net.ipv4.tcp_rmem=8192 250000 16777216
net.ipv4.tcp_max_syn_backlog=65535
net.ipv4.tcp_fin_timeout=60
net.ipv4.tcp_retries2=12
net.ipv4.tcp_syn_retries=5
net.core.netdev_max_backlog=65535
net.ipv4.tcp_keepalive_time=30
net.ipv4.tcp_sack=1
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_wmem=8192 250000 16777216
vm.min_free_kbytes=16777216
vm.vfs_cache_pressure=200
vm.swappiness=10
vm.overcommit_memory=0
kernel.shmall = 51380224        #total memory(KB)/4KB
kernel.shmmax = 105226698752  #total memory(bytes)/2
kernel.shmmni = 4096
####
eof

sysctl -p

7. 修改limit.conf限制

使用 root 用户执行

cat >> /etc/security/limits.conf <<eof
####for mogdb
sysomm  soft    nproc    unlimited
sysomm  hard    nproc   unlimited
sysomm  soft    nofile    1000000
sysomm  hard    nofile   1000000
sysomm  soft     stack   unlimited
sysomm  hard    stack   unlimited
####
eof

8. 关闭透明大页 THP 特性

使用 root 用户执行

if test -f /sys/kernel/mm/transparent_hugepage/enabled;
then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi

if test -f /sys/kernel/mm/transparent_hugepage/defrag;
then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag

chmod +x /etc/rc.d/rc.local
systemctl enable rc-local.service
cat >> /etc/rc.d/rc.local <<EOF
#####for mogdb
if test -f /sys/kernel/mm/transparent_hugepage/enabled;
then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag;
then
  echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF

参数配置完需要重启

reboot

重启之后查看是否关闭成功

cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag

输出结果，参考如下

cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]

cat /sys/kernel/mm/transparent_hugepage/defrag
always defer defer+madvise madvise[never]

9. 安装系统依赖包

使用 root 用户执行

yum install -y libaio-devel gcc gcc-c++ zlib-devel expect numactl flex bison ncurses-devel  patch readline-devel glibc psmisc bzip2
rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n'  libaio-devel  \
gcc \
gcc-c++ \
zlib-devel \
expect \
numactl \
flex \
bison \
ncurses-devel \
patch \
readline-devel \
glibc \
psmisc \
bzip2

10. 修改目录权限

使用 root 用户执行

chown sysomm:sysomm /app /mogdata/  /mogarch/  /mogwal /backup

安装部署

1. 创建相关目录

使用 sysomm 用户执行

mkdir -p /app/ptk
mkdir -p /app/mogdb/5.0
mkdir -p /app/mogdb/5.0/product
mkdir -p /app/mogdb/5.0/log
mkdir -p /app/mogdb/5.0/tool
mkdir -p /app/mogdb/5.0/pg_audit
mkdir -p /app/mogdb/5.0/plugins
mkdir -p /mogdata/cluster_sgedspdb
mkdir -p /mogwal/cluster_sgedspdb
mkdir -p /mogarch/cluster_sgedspdb

ls -ld /app/ptk /app/mogdb/5.0 /app/mogdb/5.0/product /app/mogdb/5.0/log /app/mogdb/5.0/tool /app/mogdb/5.0/pg_audit /app/mogdb/5.0/plugins /mogdata/cluster_sgedspdb /mogwal/cluster_sgedspdb /mogarch/cluster_sgedspdb

2. 上传安装包

使用 sysomm 用户执行

上传安装包至**/home/sysomm/mogdb5.0**目录

3. 解压安装包

使用 sysomm 用户执行

tar -xzvf /home/sysomm/mogdb5.0/ptk_linux_x86_64.tar.gz  -C /app/ptk/
tar -xzvf /home/sysomm/mogdb5.0/whale-5.0.4-01-Kylin-x86_64.tar.gz -C /app/mogdb/5.0/plugins
unzip /home/sysomm/mogdb5.0/compat-tools-master_20231124.zip  -d /app/mogdb/5.0/plugins

4. PTK配置文件

使用 sysomm 用户执行

vi /app/ptk/config.yaml
global:
  cluster_name: "cluster_sgedspdb"
  user: "sysomm"
  group: "sysomm"
  db_password: "pTk6ZDUyMzhjMjE8RD1CPUQ+PG5XYTQ1aW42YjBpSFUzS2RucFBKdWtlZzc3ZU00OXdfLTV1d29Hdzc4UjQ="
  db_port: 28174
  base_dir: "/app/mogdb/5.0"
  app_dir: "/app/mogdb/5.0/product"
  log_dir: "/app/mogdb/5.0/log"
  data_dir: "/mogdata/cluster_sgedspdb"
  tool_dir: "/app/mogdb/5.0/tool"
  gs_initdb_opts:
  - "--encoding=UTF-8"
  - "--dbcompatibility=A"
  ssh_option:
     port: 
     user: 
     password: ""
db_servers:
- host: 180.2.72.50
  role: primary
  xlog_dir: /mogwal/cluster_sgedspdb

5. 检查安装环境

使用sysomm 用户执行

/app/ptk/ptk checkos -f /app/ptk/config.yaml

6. 执行安装

使用 sysomm 用户执行

/app/ptk/ptk install -y -f /app/ptk/config.yaml --pkg  /home/sysomm/mogdb5.0/MogDB-5.0.4-Kylin-x86_64-all.tar.gz

7. 检查安装结果

使用 sysomm用户执行

/app/ptk/ptk ls
/app/ptk/ptk cluster status -n cluster_sgedspdb

8. 安装后设置权限

设置750权限，监控用户可以读数据库日志

chmod 750 /app/mogdb/5.0
chmod 750 /app/mogdb/5.0/log/
chmod 750 /app/mogdb/5.0/log/pg_log/
chmod 750 /app/mogdb/5.0/log/pg_log/dn_6001/

数据库加固

1. 设置数据库参数

使用 sysomm 用户执行

source ~/.bashrc

gs_guc set -N all -I all -c "listen_addresses = '*'"
gs_guc set -N all -I all -c "remote_read_mode = non_authentication"
gs_guc set -N all -I all -c "password_encryption_type = 1"
gs_guc set -N all -I all -c "password_reuse_time = 0"
gs_guc set -N all -I all -c "password_lock_time = 0.02"
gs_guc set -N all -I all -c "password_effect_time = 120"
gs_guc set -N all -I all -c "session_timeout = 0"
gs_guc set -N all -I all -c "password_reuse_max = 1"
gs_guc set -N all -I all -c "max_connections = 6000"  #根据内存大小配置

gs_guc set -N all -I all -c "wal_level = logical"
gs_guc set -N all -I all -c "full_page_writes = off"
gs_guc set -N all -I all -c "wal_log_hints = off"
gs_guc set -N all -I all -c "xloginsert_locks = 48"
gs_guc set -N all -I all -c "advance_xlog_file_num = 10"
gs_guc set -N all -I all -c "wal_keep_segments = 768"  #根据MOGWAL目录大小设置
gs_guc set -N all -I all -c "wal_buffers = 512MB"
gs_guc set -N all -I all -c "checkpoint_segments = 768"  #根据MOGWAL目录大小设置

gs_guc set -N all -I all -c "archive_mode = off"          #归档为on,不归档为off
gs_guc set -N all -I all -c "archive_dest = '/mogarch/cluster_sgedspdb'" 

gs_guc set -N all -I all -c "log_duration = on"
gs_guc set -N all -I all -c "log_statement = ddl"
gs_guc set -N all -I all -c "log_line_prefix = '%m %u %d %r %p %S'"
gs_guc set -N all -I all -c "log_checkpoints = on"
gs_guc set -N all -I all -c "log_file_mode=0640"     #新加


gs_guc set -N all -I all -c "vacuum_cost_limit = 1000"
gs_guc set -N all -I all -c "autovacuum_max_workers = 10"
gs_guc set -N all -I all -c "autovacuum_naptime = 20"
gs_guc set -N all -I all -c "autovacuum_vacuum_cost_delay = 10"
gs_guc set -N all -I all -c "autovacuum_io_limits = 104857600"
gs_guc set -N all -I all -c "autovacuum_vacuum_scale_factor = 0.05"
gs_guc set -N all -I all -c "autovacuum_analyze_scale_factor = 0.02"
gs_guc set -N all -I all -c "autovacuum_vacuum_threshold = 200"
gs_guc set -N all -I all -c "autovacuum_analyze_threshold = 200"

gs_guc set -N all -I all -c "instr_unique_sql_count = 200000"
gs_guc set -N all -I all -c "enable_wdr_snapshot = on"
gs_guc set -N all -I all -c "wdr_snapshot_retention_days = 8"
gs_guc set -N all -I all -c "log_min_duration_statement = 200"
gs_guc set -N all -I all -c "track_activity_query_size = 2048"

gs_guc set -N all -I all -c "max_process_memory = 130GB"  #total memory*0.665
gs_guc set -N all -I all -c "shared_buffers = 50GB"         #total memory*0.665*0.4
gs_guc set -N all -I all -c "work_mem = 256MB"
gs_guc set -N all -I all -c "maintenance_work_mem = 2GB"
gs_guc set -N all -I all -c "max_prepared_transactions = 3000"
gs_guc set -N all -I all -c "behavior_compat_options = 'display_leading_zero,char_coerce_compat '"

gs_guc set -N all -I all -c "cstore_buffers = 16MB"
gs_guc set -N all -I all -c "local_syscache_threshold = 32MB"
gs_guc set -N all -I all -c "standby_shared_buffers_fraction = 1"
gs_guc set -N all -I all -c "checkpoint_completion_target = 0.8"
gs_guc set -N all -I all -c "max_files_per_process = 100000"
gs_guc set -N all -I all -c "lc_messages = 'en_US.UTF-8'"
gs_guc set -N all -I all -c "lc_monetary = 'en_US.UTF-8'"
gs_guc set -N all -I all -c "lc_numeric = 'en_US.UTF-8'"
gs_guc set -N all -I all -c "lc_time = 'en_US.UTF-8'"
gs_guc set -N all -I all -c "enable_codegen = off"
gs_guc set -N all -I all -c "enable_mergejoin = off"
gs_guc set -N all -I all -c "enable_date_operator_sub_oracle = on"
gs_guc set -N all -I all -c "max_recursive_times = 15000"
gs_guc set -N all -I all -c "enable_cbm_tracking = on"
gs_guc set -N all -I all -c "undo_zone_count = 16384"
gs_guc set -N all -I all -c "enable_recyclebin = on"
gs_guc set -N all -I all -c "enable_ustore = on"
gs_guc set -N all -I all -c "idle_in_transaction_session_timeout = 1800"
gs_guc set -N all -I all -c "enable_material = off"
gs_guc set -N all -I all -c " update_lockwait_timeout = 172800000"
gs_guc set -N all -I all -c " lockwait_timeout = 172800000"
gs_guc set -N all -I all -c " enable_opfusion=on”

gs_guc set -N all -I all -c "audit_directory    = '/app/mogdb/5.0/pg_audit'"
gs_guc set -N all -I all -c "audit_resource_policy = on"
gs_guc set -N all -I all -c "audit_space_limit = 30GB"
gs_guc set -N all -I all -c "audit_login_logout=2"
gs_guc set -N all -I all -c "audit_database_process=1"
gs_guc set -N all -I all -c "audit_user_locked=1"
gs_guc set -N all -I all -c "audit_set_parameter=1"
gs_guc set -N all -I all -c "audit_system_object=2046"
gs_guc set -N all -I all -c "audit_function_exec=1"

gs_guc set -N all -I all -c "password_notify_time=7"
gs_guc set -N all -I all -c "password_min_special=1"
gs_guc set -N all -I all -c "password_min_length=8"
gs_guc set -N all -I all -c "password_policy=1"
gs_guc set -N all -I all -c "qrw_inlist2join_optmode= 'disable'"
gs_guc set -N all -I all -h "host all all 0.0.0.0/0  md5"

2. 重启数据库

使用 sysomm 用户在数据库节点执行如下操作

/app/ptk/ptk cluster -n cluster_sgedspdb restart

创建数据库

1. 创建数据库

使用 sysomm 用户在主节点执行如下操作

gsql -r
create database sgedspdb  Encoding 'GBK' LC_Collate 'zh_CN.GBK' LC_Ctype 'zh_CN.GBK';
\l
\q

2. 创建依赖函数

使用 sysomm 用户在主节点执行如下操作

gsql -r
\c sgedspdb
-- create generic compare functions --::bpchar
CREATE OR REPLACE FUNCTION bpchar_text_cmp(larg bpchar, rarg text)
RETURNS integer
    AS $$
        SELECT bpcharcmp(larg, rarg::bpchar);
    $$ LANGUAGE sql immutable strict;

CREATE OR REPLACE FUNCTION bpchar_text_eq(larg bpchar, rarg text)
    RETURNS boolean
    AS $$
        SELECT (larg = rarg::bpchar);
    $$ LANGUAGE sql immutable strict;

CREATE OR REPLACE FUNCTION bpchar_text_lt(larg bpchar, rarg text)
    RETURNS boolean
    AS $$
        SELECT (larg < rarg::bpchar);
    $$ LANGUAGE sql immutable strict;

CREATE OR REPLACE FUNCTION bpchar_text_gt(larg bpchar, rarg text)
    RETURNS boolean
    AS $$
        SELECT (larg > rarg::bpchar);
    $$ LANGUAGE sql immutable strict;

CREATE OR REPLACE FUNCTION bpchar_text_le(larg bpchar, rarg text)
    RETURNS boolean
    AS $$
        SELECT (larg <= rarg::bpchar);
    $$ LANGUAGE sql immutable strict;

CREATE OR REPLACE FUNCTION bpchar_text_ge(larg bpchar, rarg text)
    RETURNS boolean
    AS $$
        SELECT (larg >= rarg::bpchar);
    $$ LANGUAGE sql immutable strict;

CREATE OPERATOR = (
   leftarg = bpchar, rightarg = text, procedure = bpchar_text_eq,
--    commutator = = , negator = = , -- not needed in this example
   restrict = scalarltsel, join = scalarltjoinsel
);

CREATE OPERATOR < (
   leftarg = bpchar, rightarg = text, procedure = bpchar_text_lt,
--    commutator = = , negator = = , -- not needed in this example
   restrict = scalarltsel, join = scalarltjoinsel
);

CREATE OPERATOR > (
   leftarg = bpchar, rightarg = text, procedure = bpchar_text_gt,
--    commutator = = , negator = = , -- not needed in this example
   restrict = scalarltsel, join = scalarltjoinsel
);

CREATE OPERATOR <= (
   leftarg = bpchar, rightarg = text, procedure = bpchar_text_le,
--    commutator = = , negator = = , -- not needed in this example
   restrict = scalarltsel, join = scalarltjoinsel
);

CREATE OPERATOR >= (
   leftarg = bpchar, rightarg = text, procedure = bpchar_text_ge,
--    commutator = = , negator = = , -- not needed in this example
   restrict = scalarltsel, join = scalarltjoinsel
);


-- SOLUTION  不用重建索引的方法--
-- directly add to existing op family (risk of affecting others --
ALTER OPERATOR FAMILY bpchar_ops USING btree ADD
    OPERATOR        1       < (bpchar, text),
    OPERATOR        2       <= (bpchar, text),
    OPERATOR        3       = (bpchar, text),
    OPERATOR        4       >= (bpchar, text),
    OPERATOR        5       > (bpchar, text),
FUNCTION        1       bpchar_text_cmp(bpchar, text);



CREATE OR REPLACE FUNCTION pg_catalog.numtoday(int8)
 RETURNS interval
 LANGUAGE sql
 IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
AS $$select numtoday($1::int4);$$;
CREATE CAST (int8 AS INTERVAL) WITH FUNCTION pg_catalog.numtoday(int8) AS IMPLICIT;

3. 安装whale 插件

使用sysomm用户执行

cd /app/mogdb/5.0/plugins/whale
make install
gsql -d sgedspdb -c 'create extension whale;'
gsql -d sgedspdb -c '\dx'

4. 安装compat-tools插件

使用sysomm用户执行

cd /app/mogdb/5.0/plugins/compat-tools-master/
gsql -d sgedspdb -c '\i runMe.sql'
gsql -d sgedspdb -c '\dn' | grep compat_tools