Navicat连接openGauss 5.0.0 企业版数据库

02/18 18:00
阅读数 16

引言

在当今互联网时代,数据成为企业和组织的重要资产。为了更好地管理和分析数据,数据库管理工具起到至关重要的作用。openGauss是一款开源关系型数据库管理系统,提供了高性能、高可用性和可扩展性,它还具有安全性和数据保护的特性,包括数据加密、访问控制和审计等功能,已广泛应用于各个行业。

Navicat是一款广泛使用的数据库管理工具,具有直观友好的界面和丰富的功能,可以方便地连接、管理和操作各种类型的数据库。Navicat支持主流的数据库系统,如MySQL、PostgreSQL等。它提供了一个集成的环境,用于管理和操作多个数据库,可以执行数据导入导出、数据同步、查询和报表等功能。Navicat具有直观易用的用户界面和强大的功能,适用于数据库开发人员、管理员和分析师等各种角色。

环境准备

  • openGauss_5.0.0 企业版

  • Navicat 16.0.11

  • openEuler-22.03-LTS-SP2

  • MobaXterm 22.1

  • 服务器名称:node0,IP地址:192.168.192.129

openGauss服务设置

查看数据库启动状态

  • 切换至omm用户

  
  
  
[root@node0 ~]# su omm


Welcome to 5.10.0-153.12.0.92.oe2203sp2.x86_64

System information as of time: Sat Jul 22 04:10:52 PM CST 2023

System load: 0.01
Processes: 170
Memory used: 5.5%
Swap used: 0%
Usage On: 7%
IP address: 192.168.192.129
Users online: 1
To run a command as administrator(user "root"),use "sudo <command>".
[omm@node0 root]$

  • 查看数据库启动状态

  
  
  
[omm@node0 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 node0 192.168.192.129 15400 6001 /opt/huawei/install/data/dn P Primary Manually stopped

  • 启动openGauss

  
  
  
[omm@node0 root]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] node0
2023-07-22 16:21:31.942 64bb918b.1 [unknown] 139874136903616 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2023-07-22 16:21:31.942 64bb918b.1 [unknown] 139874136903616 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2023-07-22 16:21:31.945 64bb918b.1 [unknown] 139874136903616 [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 (3301 Mbytes) is larger.
=========================================
Successfully started.
  
  
  
# 停止openGauss
gs_om -t stop

修改pg_hba.conf文件

企业版所在目录:/opt/huawei/install/data/dn

  
  
  
[omm@node0 dn]$ pwd
/opt/huawei/install/data/dn
[omm@node0 dn]$
[omm@node0 dn]$ ll
total 5044
drwx------ 6 omm dbgrp 4096 Jul 20 23:49 base
-rw------- 1 omm dbgrp 4399 Jul 20 23:48 cacert.pem
-rw------- 1 omm dbgrp 72 Jul 22 16:21 gaussdb.state
drwx------ 3 omm dbgrp 4096 Jul 22 16:21 global
-rw------- 1 omm dbgrp 354 Jul 20 23:49 gs_gazelle.conf
-rw------- 1 omm dbgrp 4915200 Jul 20 23:49 gswlm_userinfo.cfg
-rw------- 1 omm dbgrp 21016 Jul 20 23:49 mot.conf
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_clog
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_csnlog
-rw------- 1 omm dbgrp 0 Jul 22 16:21 pg_ctl.lock
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_errorinfo
-rw------- 1 omm dbgrp 4611 Jul 21 23:05 pg_hba.conf
-rw------- 1 omm dbgrp 4587 Jul 20 23:49 pg_hba.conf.bak
-rw------- 1 omm dbgrp 1024 Jul 20 23:49 pg_hba.conf.lock
-rw------- 1 omm dbgrp 1636 Jul 20 23:49 pg_ident.conf
drwx------ 4 omm dbgrp 4096 Jul 20 23:49 pg_llog
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_location
drwx------ 2 omm dbgrp 4096 Jul 22 16:24 pg_logical
drwx------ 4 omm dbgrp 4096 Jul 20 23:49 pg_multixact
drwx------ 2 omm dbgrp 4096 Jul 22 16:21 pg_notify
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_replslot
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_serial
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_snapshots
drwx------ 2 omm dbgrp 4096 Jul 22 16:25 pg_stat_tmp
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_tblspc
drwx------ 2 omm dbgrp 4096 Jul 20 23:49 pg_twophase
-rw------- 1 omm dbgrp 4 Jul 20 23:49 PG_VERSION
drwx------ 3 omm dbgrp 4096 Jul 20 23:49 pg_xlog
-rw------- 1 omm dbgrp 36058 Jul 21 23:07 postgresql.conf
-rw------- 1 omm dbgrp 36083 Jul 20 23:49 postgresql.conf.guc.bak
-rw------- 1 omm dbgrp 1024 Jul 20 23:49 postgresql.conf.lock
-rw------- 1 omm dbgrp 71 Jul 22 16:21 postmaster.opts
-rw------- 1 omm dbgrp 88 Jul 22 16:21 postmaster.pid
-rw------- 1 omm dbgrp 0 Jul 20 23:49 postmaster.pid.lock
-rw------- 1 omm dbgrp 4402 Jul 20 23:48 server.crt
-rw------- 1 omm dbgrp 1766 Jul 20 23:48 server.key
-rw------- 1 omm dbgrp 56 Jul 20 23:48 server.key.cipher
-rw------- 1 omm dbgrp 24 Jul 20 23:48 server.key.rand
drwx------ 5 omm dbgrp 4096 Jul 20 23:49 undo
[omm@node0 dn]$

  
  
  
[omm@node0 dn]$ vi pg_hba.conf
  • 增加需要访问计算机的IP地址

  • 对所有IP地址进行开放:0.0.0.0/0

  • 修改trust替换成md5加密方式

  
  
  
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file. A short
# synopsis follows.

......

#
TYPE DATABASE USER ADDRESS METHOD

#
"local" is for Unix domain socket connections only
local all all trust
host all omm 192.168.192.129/32 trust
# 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

......
  • 输入 :wq! 退出

修改postgresql.conf文件

  • 修改监听地址和加密方式

  
  
  
[omm@node0 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)
local_bind_address = '192.168.192.129'
port = 15400 # (change requires restart)
max_connections = 5000 # (change requires restart)

......

#
failed_login_attempts = 10 #Enter the wrong password reached failed_login_attempts times, the current account will be locked
password_encryption_type = 0 #Password storage type, 0 is md5 for PG, 1 is sha256 + md5, 2 is sha256 only
#password_min_length = 8 #The minimal password length(6-999)
#password_max_length = 32 #The maximal password length(6-999)
#password_min_uppercase = 0 #The minimal upper character number in password(0-999)
#password_min_lowercase = 0 #The minimal lower character number in password(0-999)
#password_min_digital = 0 #The minimal digital character number in password(0-999)
#password_min_special = 0 #The minimal special character number in password(0-999)
#password_effect_time = 90d #The password effect time(0-999)
#password_notify_time = 7d #The password notify time(0-999)

......

重启服务

  
  
  
[omm@node0 dn]$ gs_om -t stop
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
[omm@node0 dn]$
[omm@node0 dn]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] node0
2023-07-22 16:55:05.897 64bb9969.1 [unknown] 140297425711040 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2023-07-22 16:55:05.897 64bb9969.1 [unknown] 140297425711040 [unknown] 0 dn_6001 01000 0 [BACKEND] WARNING: could not create any HA TCP/IP sockets
2023-07-22 16:55:05.901 64bb9969.1 [unknown] 140297425711040 [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 (3301 Mbytes) is larger.
=========================================
Successfully started.

创建远程连接角色

  
  
  
[omm@node0 dn]$ gsql -d postgres -p 15400
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:37:13 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

防火墙开放端口

  • 防火墙为未启动状态,略过此步骤;

  
  
  
# 查看firewalld状态
systemctl status firewalld

#
把端口加入防火墙
firewall-cmd --zone=public --add-port=15400/tcp --permanent

#
重新加载防火墙
systemctl reload firewalld

Navicat连接

  • 打开Navicat,新建PostgreSQL连接方式;

  • 在“新建连接(PostgreSQL)”窗口中,输入连接名、主机IP地址、端口号、用户名、密码等参数

  • 端口号默认为5432,根据实际情况动态调整;

  • 点击“测试连接”按钮,提示连接成功,完成Navicat连接openGauss数据库;

问题

fatal:forbid remote connection with initial user

  • 在连接openGauss数据库时,提示:Forbid remote connection with initial user,意思是禁止用初始用户进行远程连接,即不要使用数据库默认的omm用户访问。

总结

本文就使用Navicat连接openGauss数据库的实践方法做了说明,在实际应用中,应根据实际情况进行调整和优化。
使用Navicat连接openGauss数据库,可以方便地进行数据管理和维护操作。可以轻松地执行数据导入导出、数据备份和恢复等任务。此外,Navicat还提供了强大的数据编辑功能,方便对数据库中的数据进行修改和更新。

附录

记录在openEuler-22.03中安装openGauss_5.0.0企业版

在VMware 17.0.2虚拟机中安装openEuler-22.03-LTS-SP2https://www.modb.pro/db/1682365146923606016



本文分享自微信公众号 - openGauss(openGauss)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部