openGauss数据驾驭：跨平台开发实战-每日运维

需求

开发一套跨平台的连接openGauss数据库的可视化的桌面版应用系统；

在应用系统与openGauss之间建立数据交互逻辑，实现数据的增、删、改、查；

环境准备

服务器

openEuler-22.03-LTS-SP3

名称：localhost.localdomain

IP地址：192.168.192.129

openGauss 6.0.0-RC1

客户端

Windows

Windows 10 专业版，64位操作系统

IP地址：192.168.1.3

Kylin

银河麒麟桌面操作系统V10 (SP1)

IP地址：192.168.192.142

UOS

UOS Desktop 20 Pro

IP地址：192.168.192.128

语言：pascal

测试语句

`drop table if exists staffs;`
CREATE TABLE staffs ( staff_id INT not null, first_name VARCHAR(20), last_name VARCHAR(25), email VARCHAR(25), phone_number VARCHAR(20), hire_date DATE, employment_id VARCHAR(10), salary NUMERIC(8,2), commission_pct NUMERIC(2,2), manager_id NUMERIC(6), section_id NUMERIC(4) ); insert into staffs values (1, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', 'SH_CLERK', 2200.00, null, 124, 50); insert into staffs values (2, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', 'SH_CLERK', 4000.00, null, 124, 50); insert into staffs values (3, 'Jennie', 'Grant', 'SMITH', '650.507.9855', 'HZ_CLERK', 5000.00, null, 124, 51); select * from staffs order by staff_id; update staffs set first_name='Jack' where staff_id=2;
`delete from staffs where staff_id=2;`

openEuler环境

Windwos环境

Kylin环境

UOS环境

openGauss 服务设置

查看数据库启动状态

切换至omm用户

[root@localhost ~]# su omm


Welcome to 5.10.0-182.0.0.95.oe2203sp3.x86_64
System information as of time:  Sun Jun  2 11:52:31 AM CST 2024

System load: 0.00 Processes: 216 Memory used: 6.2% Swap used: 0% Usage On: 8% IP address: 192.168.192.129 Users online: 2 To run a command as administrator(user "root"),use "sudo ". [omm@localhost root]$

openGauss数据驾驭：跨平台开发实战-7

查看数据库启动状态

[omm@localhost root]$ gs_om -t status --detail [ Cluster State ]


cluster_state   : Unavailable

redistributing  : No

current_az      : AZ_ALL
[  Datanode State   ]

node node_ip port instance state ------------------------------------------------------------------------------------------------------------ 1 localhost.localdomain 192.168.192.129 15400 6001 /opt/huawei/install/data/dn P Primary Manually stopped [omm@localhost root]$

openGauss数据驾驭：跨平台开发实战-8

启动openGauss

[omm@localhost root]$ gs_om -t start Starting cluster. ========================================= [SUCCESS] localhost.localdomain 2024-06-02 11:54:14.370 665bece5.1 [unknown] 139716790724992 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets 2024-06-02 11:54:14.370 665bece5.1 [unknown] 139716790724992 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets 2024-06-02 11:54:14.395 665bece5.1 [unknown] 139716790724992 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3310 Mbytes) is larger. ========================================= Successfully started. [omm@localhost root]$

openGauss数据驾驭：跨平台开发实战-9

修改pg_hba.conf文件

目录：/opt/huawei/install/data/dn

[omm@localhost dn]$ pwd /opt/huawei/install/data/dn [omm@localhost dn]$ [omm@localhost dn]$ ll total 5052 drwx------ 6 omm dbgrp 4096 May 31 23:40 base -rw------- 1 omm dbgrp 4399 May 31 23:40 cacert.pem -rw------- 1 omm dbgrp 72 Jun 2 11:54 gaussdb.state drwx------ 3 omm dbgrp 4096 Jun 2 11:54 global -rw------- 1 omm dbgrp 354 May 31 23:40 gs_gazelle.conf -rw------- 1 omm dbgrp 4915200 May 31 23:40 gswlm_userinfo.cfg -rw------- 1 omm dbgrp 21016 May 31 23:40 mot.conf drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_clog drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_csnlog -rw------- 1 omm dbgrp 0 Jun 2 11:54 pg_ctl.lock drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_errorinfo -rw------- 1 omm dbgrp 4620 Jun 1 16:25 pg_hba.conf -rw------- 1 omm dbgrp 4587 May 31 23:40 pg_hba.conf.bak -rw------- 1 omm dbgrp 1024 May 31 23:40 pg_hba.conf.lock -rw------- 1 omm dbgrp 1636 May 31 23:40 pg_ident.conf drwx------ 4 omm dbgrp 4096 May 31 23:40 pg_llog drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_location drwx------ 2 omm dbgrp 4096 Jun 2 11:55 pg_logical drwx------ 4 omm dbgrp 4096 May 31 23:40 pg_multixact drwx------ 2 omm dbgrp 4096 Jun 2 11:54 pg_notify drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_replslot drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_serial drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_snapshots drwx------ 2 omm dbgrp 4096 Jun 2 11:55 pg_stat_tmp drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_tblspc drwx------ 2 omm dbgrp 4096 May 31 23:40 pg_twophase -rw------- 1 omm dbgrp 4 May 31 23:40 PG_VERSION drwx------ 3 omm dbgrp 4096 May 31 23:40 pg_xlog -rw------- 1 omm dbgrp 37355 Jun 1 16:29 postgresql.conf -rw------- 1 omm dbgrp 37380 May 31 23:40 postgresql.conf.guc.bak -rw------- 1 omm dbgrp 1024 May 31 23:40 postgresql.conf.lock -rw------- 1 omm dbgrp 71 Jun 2 11:54 postmaster.opts -rw------- 1 omm dbgrp 89 Jun 2 11:54 postmaster.pid -rw------- 1 omm dbgrp 0 May 31 23:40 postmaster.pid.lock -rw------- 1 omm dbgrp 4402 May 31 23:40 server.crt -rw------- 1 omm dbgrp 1766 May 31 23:40 server.key -rw------- 1 omm dbgrp 56 May 31 23:40 server.key.cipher -rw------- 1 omm dbgrp 24 May 31 23:40 server.key.rand drwx------ 5 omm dbgrp 4096 May 31 23:40 undo [omm@localhost dn]$

openGauss数据驾驭：跨平台开发实战-11

增加需要访问计算机的IP地址
对所有IP地址进行开放：0.0.0.0/0
修改trust替换成md5加密方式

[omm@localhost dn]$ vi pg_hba.conf


# PostgreSQL Client Authentication Configuration File
....................

# IPv4 local connections: host all all 127.0.0.1/32 trust host all all 192.168.192.129/32 md5 host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication omm trust #host replication omm 127.0.0.1/32 trust #host replication omm ::1/128 trust

openGauss数据驾驭：跨平台开发实战-12

修改postgresql.conf文件

修改监听地址和加密方式

[omm@node0 dn]$ vi postgresql.conf

[omm@localhost dn]$ vi postgresql.conf # ----------------------------------------------------------------------------- # # postgresql_single.conf.sample # Configuration file for centralized environment # # Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group


....................
listen_addresses = '*'          # what IP address(es) to listen on;

                                        # comma-separated list of addresses;

                                        # defaults to 'localhost'; use '*' for all

                                        # (change requires restart)

....................
password_encryption_type = 0            #Password storage type, 0 is md5 for PG, 1 is sha256 + md5, 2 is sha256 only

....................

openGauss数据驾驭：跨平台开发实战-14

重启服务

[omm@localhost dn]$ gs_om -t stop Stopping cluster. ========================================= Successfully stopped cluster. ========================================= End stop cluster. [omm@localhost dn]$ gs_om -t start Starting cluster. ========================================= [SUCCESS] localhost.localdomain 2024-06-02 12:07:45.835 665bf011.1 [unknown] 140209249028480 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets 2024-06-02 12:07:45.835 665bf011.1 [unknown] 140209249028480 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets 2024-06-02 12:07:45.837 665bf011.1 [unknown] 140209249028480 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (3310 Mbytes) is larger. ========================================= Successfully started. [omm@localhost dn]$

创建数据库、远程连接角色

`[omm@localhost dn]$ gsql -d postgres -p 15400 gsql ((openGauss 6.0.0-RC1 build ed7f8e37) compiled at 2024-03-31 12:44:26 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help.`
`openGauss=# create user testuser password 'openGauss!666'; NOTICE: The encrypted password contains MD5 ciphertext, which is not secure. CREATE ROLE openGauss=# openGauss=# create database testdb owner testuser; CREATE DATABASE openGauss=# openGauss=# GRANT ALL PRIVILEGES ON DATABASE testdb to testuser; GRANT openGauss=# openGauss=# GRANT ALL PRIVILEGES TO testuser; ALTER ROLE openGauss=#`

应用程序开发

文章中的开发代码仅做测试使用，实际开发中，需要根据实际情况进行严谨的逻辑分析。

界面布局

在窗体中标签、文本框、按钮等控件，以输入数据库的相关连接信息、进行数据库连接的测试；

openGauss数据驾驭：跨平台开发实战-20

创建公共函数

//实现数据表的创建，数据的新增、删除、更新等操作


function FunExecSql(vSqlStr: string): Boolean;

var

  tmpQuery:TZQuery;//uses ZDataset

begin

  Result:=True;
  try

    tmpQuery := TZQuery.create(nil);

    try

      with tmpQuery do

      begin

        Connection := FConnection;

        Active:=False;

        SQL.Clear;
        SQL.Text:=vSqlStr;

ExecSQL; end; finally tmpQuery.Free; end; except Result:=False; tmpQuery.Free; end; end;

数据库连接

Connection := TZConnection.Create(Self); Connection.Protocol := 'postgresql'; Connection.HostName := edtHostName.Text; //服务器IP Connection.Database := edtDatabase.Text; //数据库名称 Connection.User := edtUserName.Text; //用户名 Connection.Password := edtPassword.Text; //密码 Connection.Port:=15400; //端口号

创建数据表遍历过程

//数据表记录的遍历 procedure FunGetOpenQuery(vSqlStr: string); var tmpQuery:TZQuery;//uses ZDataset begin try tmpQuery := TZQuery.create(nil); try with tmpQuery do begin Connection := FConnection; Active:=False; SQL.Clear; sql.Add(vSqlStr); Active:=True;


        First;

        while not Eof do

        begin

          memlog.Lines.Add('staff_id='+FieldByName('staff_id').AsString);

          memlog.Lines.Add('first_name='+FieldByName('first_name').AsString);

          memlog.Lines.Add('last_name='+FieldByName('last_name').AsString);

          memlog.Lines.Add('email='+FieldByName('email').AsString);

          memlog.Lines.Add('phone_number='+FieldByName('phone_number').AsString);

          memlog.Lines.Add('employment_id='+FieldByName('employment_id').AsString);

          memlog.Lines.Add('salary='+FieldByName('salary').AsString);

          memlog.Lines.Add('commission_pct='+FieldByName('commission_pct').AsString);

          memlog.Lines.Add('manager_id='+FieldByName('manager_id').AsString);

          memlog.Lines.Add('section_id='+FieldByName('section_id').AsString);
          memlog.Lines.Add('*********************************');
          Next;

        end;

      end;

    finally

      tmpQuery.Free;

    end;

  except