1 background

Recently, the company is making a foreign object detection equipment for military airport runway, but due to the tension between countries, the military release requires our software to adapt a domestic database;
After checking, we know that the kernel used by Henkel database is postgresql, and then we have made some modifications and Optimization on this basis.
No more nonsense, arrangement!

2. Database installation

Download installation package
This article explains the installation of hgdb4.5.6-see-centos7-x86-64-20210303.rpm

2.1 environmental preparation

Turn on the firewall

#Turn on the firewall
systemctl start firewalld.service 
systemctl stop firewalld.service 
systemctl disable firewalld.service

Development port
The default port is 5866, which needs to be modified for customization

#Add 5866 port temporarily 
firewall-cmd --add-port=5866/tcp 
#Permanently add 5866 ports 
firewall-cmd --permanent --add-port=5866/tcp 
#Overload effective
firewall-cmd --reload 

#Check whether port 5866 is open
firewall-cmd --list-ports | grep 5866 

Query time zone

#Query time zone
timedatectl
#Change the time zone to the eighth East District of Shanghai
timedatectl set-timezone Asia/Shanghai

2.2 database installation

#rpm installation
rpm -ivh hgdb4.5.6-see-centos7-x86-64-20210303.rpm 
--nodeps #Ignore dependent packages for installation 
--force #Forced installation

Note: the dependent packages required by the database have been encapsulated in the installation package, so when the dependent package error is encountered, you can ignore it or force the installation first, and then set the environment variable after the installation is successful

2.3 setting environment

Environment variables first consider using the file in the database installation directory: the file highgodb.env in the etc directory

vi /root/.bashrc
#Add source /opt/HighGo4.5.6-see/etc/highgodb.env
#Make. bashrc content effective in the current window:
source /root/.bashrc

2.4 initializing the database

Use the root user to initialize the database. During the initialization process, the password will be entered six times, and the three database administrators will each initialize twice. The initialization commands are as follows:

initdb -D $PGDATA -e sm4 -c "echo 12345678" > /opt/HighGo4.5.6-see/bin/initdb.log

Note: the password needs at least 8 characters, which is composed of uppercase and lowercase plus special characters.

#Copy the server.crt and server.key files in the installation directory etc to the $PGDATA directory
cp /opt/HighGo4.5.2-see/etc/server.* $PGDATA
#Modify server. * file permissions
chmod 0600 $PGDATA/server.*
#Start database
pg_ctl start

2.5 database configuration

The database is configured with the following parameters, which are modified by the sysdba user and take effect after restarting the database.

psql -d highgo -U sysdba 
alter system set listen_addresses = '*'; 
alter system set max_connections = 800; #Please set the number of database connections according to the actual situation 
alter system set shared_buffers = '4GB'; #Memory used by the database, set to physical memory * 25%

The valid time of user password of secure version is 7 days by default. If you need to modify the valid time of password,
You need to use the syssso user to log in to the database and execute the following statement. After modification, the validity period parameter will take effect automatically

psql -U syssso -d highgo 
highgo=> select set_secure_param('hg_idcheck.pwdvaliduntil','365'); #The modified password is valid for 365 days. Please modify it according to the actual situation

The audit function is enabled by default. You can use the syssao user to log in to the database and execute the following statements. After modification, restart and take effect.

psql -U syssao -d highgo 
highgo=> select set_audit_param('hg_audit','off');
#sign out
highgo=> \q
#Restart the database to make the parameters take effect
pg_ctl restart -mf

Modify file

vi $PGDATA/pg_hba.conf 
#Append under ipv4
host	 all	 all	 0.0.0.0/0 	sm3
#Restart the database to make the parameters take effect
pg_ctl restart -mf

2.6 connection test

Use the official connection tools to connect and test
Enter the host user name and password to connect


So far, the database installation connection is successful.
This tool is not suitable for you just now. The created table will default to the public mode in the library mode;

Relationship between database and schema: schema is the logical division of database. A database contains one or more named schemas, which in turn contain tables. The schema can also contain other objects, including data types, functions, operators, and so on. The same object name can be used in different modes without conflict; Unlike databases, schemas are not strictly separated: a user can access objects in any schema in the database to which he is connected as long as he has permission.

3 code adaptation

3.1 database synchronization

Since MySQL is used in the company's project itself, many MySQL data types and functions are not available in highgo (because I have never used postgresql), so I don't give too much explanation and explanation;
The next step is table structure and basic data synchronization,
Synchronization is an individual activity. Here are some pits encountered in synchronization:

• Because the DDL field in Navicat is enclosed in "'" single quotation marks, highgo database does not support it; The field COMMENT is not supported either; All are more painful;
• When converting Navicat SQL to postgresql SQL, I encountered a semicolon line break of ";" and highgo does not support it;
• In addition, when executing multiple SQL statements, you need to select all of them for execution;

3.2 modification of configuration file

driver-class-name: com.highgo.jdbc.Driver
db-type: postgresql
url: jdbc:highgo://ip:5866/dbname
username: sysdba
password: High@123

3.3 adding dependencies

You need to pay attention to the version of jdk when selecting dependency

 <dependency>
   	  <groupId>com.highgo</groupId>
      <artifactId>HgdbJdbc</artifactId>
      <version>6.0.6.jre8</version>
 </dependency>

Start project test