瀚高4.5.8的安装部署过程

说明

大周末的写文档.
主要是备忘,之前写的太乱了.
本来想自己写一份, 但是怀疑找到了瀚高工程师的文档 
准备直接学习一下:
https://blog.csdn.net/qiuchenjun/article/details/125985256
感谢原作者

安装

rpm -ivh hgdb-see-4.5.8-db43858.x86_64.rpm

注意提示信息:
Created symlink /etc/systemd/system/multi-user.target.wants/hgdb-see-4.5.8.service → /usr/lib/systemd/system/hgdb-see-4.5.8.service.
Created symlink /etc/systemd/system/graphical.target.wants/hgdb-see-4.5.8.service → /usr/lib/systemd/system/hgdb-see-4.5.8.service.
Created symlink /etc/systemd/system/multi-user.target.wants/hgproxy.service → /usr/lib/systemd/system/hgproxy.service.
Created symlink /etc/systemd/system/graphical.target.wants/hgproxy.service → /usr/lib/systemd/system/hgproxy.service.

专用机数据文件位置处理

mkdir -p /data/highgo/data
mkdir -p /data/highgo/hgdbbak/archive
ln -sf /data/highgo/data /opt/highgo/hgdb-see-4.5.8/data

创建初始化密码
echo "High@xxxx"  > /opt/highgo/hgdb-see-4.5.8/etc/5866
echo "High@xxxx" >> /opt/highgo/hgdb-see-4.5.8/etc/5866
echo "High@xxxx" >> /opt/highgo/hgdb-see-4.5.8/etc/5866

设置环境变量

cat >/etc/profile.d/hg.sh <<EOF
export HG_BASE=/opt/highgo
export HGDB_HOME=/opt/highgo/hgdb-see-4.5.8
export PGPORT=5866
export PGDATABASE=highgo
export PATH=\$HGDB_HOME/bin:\$PATH
export PGDATA=\$HGDB_HOME/data
EOF

source /etc/profile.d/hg.sh

数据库初始化

initdb -A sm3 -e sm4 -c "echo High@xxxx" -D $PGDATA --pwfile=/opt/highgo/hgdb-see-4.5.8/etc/5866 -E 'UTF8'
然后再执行一下:
hg_sslkeygen.sh /opt/highgo/hgdb-see-4.5.8/data

注意会看到提示信息
Success. You can now start the database server using:

    hg_sslkeygen.sh /opt/highgo/hgdb-see-4.5.8/data
    pg_ctl -D /opt/highgo/hgdb-see-4.5.8/data -l 日志文件 start

设置密码文件

cat > /root/.pgpass <<EOF
localhost:5866:*:sysdba:High@xxxx
localhost:5866:*:syssao:High@xxxx
localhost:5866:*:syssso:High@xxxx
EOF
# 修改权限（必需）
chmod 0600 /root/.pgpass

设置外部可以访问

echo "host    all             all             0.0.0.0/0               sm3" >> /opt/highgo/hgdb-see-4.5.8/data/pg_hba.conf

启动服务的命令为:
pg_ctl start

设置参数

psql highgo sysdba <<EOF
alter system set listen_addresses = '*';
alter system set max_connections = 2000;
alter system set work_mem='16MB';
alter system set shared_buffers = '1GB';
alter system set checkpoint_completion_target = 0.8;
alter system set log_destination = 'csvlog';
alter system set logging_collector = on;
alter system set log_directory = 'hgdb_log';
alter system set log_filename = 'highgodb_%d.log';
alter system set log_rotation_age = '1d';
alter system set log_rotation_size = 0;
alter system set log_truncate_on_rotation = on;
alter system set log_statement = 'ddl';
alter system set log_connections=on;
alter system set log_disconnections=on;
alter system set checkpoint_timeout='30min';
alter system set maintenance_work_mem='1GB';
alter system set archive_mode = on;
alter system set archive_timeout = '30min';
alter system set archive_command = 'cp %p /data/highgo/hgdbbak/archive/%f';
alter system set log_line_prefix = '%m [%p] %a %u %d %r %h';
alter system set nls_length_semantics = 'char'; 
EOF

设置密码有效期与否

psql highgo syssso <<EOF
select set_secure_param('hg_idcheck.pwdvaliduntil','0');
alter user current_user password 'High@2013xxxx' valid until 'infinity';
\c - sysdba
alter user current_user password 'High@2013xxxx';
\c - syssao
alter user current_user password 'High@2013xxxx';
EOF
 
# 同时将免密文件中的旧密码也替换了
sed -i 's/High@xxxx/High@2013xxxx/g' /root/.pgpass
pg_ctl restart

关闭部分参数进行性能优化

psql highgo syssso <<EOF
select set_secure_param('hg_macontrol','min');
select set_secure_param('hg_rowsecure','off');
select set_secure_param('hg_showlogininfo','off');
select set_secure_param('hg_clientnoinput','0');
select set_secure_param('hg_idcheck.pwdpolicy','high');
EOF

psql highgo syssao <<EOF
select set_audit_param('hg_audit','off');
EOF
pg_ctl restart

关闭服务设置服务自动启动

pg_ctl stop
systemctl start hgdb-see-4.5.8.service
systemctl enable hgdb-see-4.5.8.service

创建用户以及设置密码

psql highgo sysdba <<EOF
create database hg2211;
create user hg2211 password 'High@2013xxxx' login;
grant all privileges on database hg2211 to hg2211;
EOF

psql hg2211 hg2211  <<EOF
create schema hg2211 ;
EOF 

psql hg2211 sysdba 
update pg_cast set castcontext = 'i' where castsource = 'integer'::regtype and casttarget = 'boolean'::regtype ;

备份恢复的命令

pg_dump -h 127.0.0.1  -U DB2211HIGHGO  -p 5866  -F c -f  /db2211highgo.dmp   DB2211HIGHGO
#异名恢复如下:
#需要先创建用户, 数据库, 赋权.
sed -i 's/DB2211HIGHGO/db2211highgo/g' /db2211highgo.dmp 
pg_restore -U sysdba -d db2211highgo /db2211highgo.dmp