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
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
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