[entry 84] Python connection to openGauss and problem solving

Author: Ge Ermeng

I openGauss standalone installation

Hardware and software environment:

Hardware environment: virtual machine memory 8GB, 4-core CPU, 900G disk (not required) < o: P > < / O: P >

Software environment: centos7 6<o:p></o:p>

Database version: opengauss2.0 0 Enterprise Edition: opengauss-2.0 0-CentOS-64bit-all. tar. gz


Note: the memory must be more than 8G, otherwise the installation will fail if there is not enough memory.

Installation steps:

1.1.  Turn off firewall

#Stop firewall
systemctl stop firewalld.service
#Disable firewall startup
systemctl disable firewalld.service
#Check that the firewall is turned off.
systemctl status firewalld

Description: < o: P > < / O: P >

If the firewall status is displayed as active (running), it indicates that the firewall is not closed.

If the firewall status displays inactive (dead), there is no need to turn off the firewall.

1.2.  Set time zone and time < o: P > < / O: P >

Set the time zone of each database node to the same time zone. You can copy the time zone file in the / usr/share/zoneinfo / directory to the / etc/localtime file.

cp /usr/share/zoneinfo/Asia/Shanghai  /etc/localtime<o:p></o:p>

1.3.  Close SELinux < o: P > < / O: P >

[root@node1 ~]#

sed -i 's/^SELINUX=./SELINUX=disabled/' /etc/selinux/config
setenforce 0
cat /etc/selinux/config|grep SELINUX

Output: < o: P > < / O: P >

# SELINUX= can take one of these three values:<o:p></o:p>


# SELINUXTYPE= can take one of three values:<o:p></o:p>

SELINUXTYPE=targeted <o:p></o:p>

[root@node1 ~]#

1.4.  Modify / etc/hosts

#Add a row

cat >>/etc/hosts <<EOF node1

1.5.  Configure library path

cat>> /etc/profile<<EOF
export LD_LIBRARY_PATH=/opt/software/openGauss/script/gspylib/clib:KaTeX parse error: Expected 'EOF', got '&' at position 51: ...><!--[if-->1.6.&̲nbsp;<!--[endif... python3 -V

Python 3.6.8

Other software packages can be installed if necessary (or installed directly):

yum install -y libaio-devel flex bison ncurses-devel glibc-devel patch redhat-lsb-core readline-devel
yum install openssl -y
yum install -y java-1.8.0-openjdk* psmisc bzip2 python3 python3-devel lksctp*
reboot #Restart the server


1.10.  Create the storage directory of the installation package

mkdir -p /opt/software/openGauss
chmod 755 -R /opt/software

1.11.  Download openGauss database software < o: P > < / O: P >

The download address is: https://opengauss.org/zh/download.html


After downloading, upload to centos.  

1.12.  Unzip the openGauss DBMS media

cd /opt/software/openGauss
tar -zxvf openGauss-2.0.0-CentOS-64bit-all.tar.gz
tar -zxvf openGauss-2.0.0-CentOS-64bit-om.tar.gz

1.13.  Create XML file < o: P > < / O: P >

< o: P > the following is the official template of xml file. Generally, you only need to change the IP of your centos machine < / O: P >


cat > clusterconfig.xml<<EOF
<?xml version="1.0" encoding="UTF-8"?>
<!-- openGauss Overall information -->
<!-- Database name -->
<PARAM name="clusterName" value="dbCluster" />
<!-- Database node name(hostname) -->
<PARAM name="nodeNames" value="node1" />
<!-- Database installation directory–>
<PARAM name="gaussdbAppPath" value="/opt/huawei/install/app" />
<!-- Log directory–>
<PARAM name="gaussdbLogPath" value="/var/log/omm" />
<!-- Temporary file directory–>
<PARAM name="tmpMppdbPath" value="/opt/huawei/tmp" />
<!-- Database tools catalog–>
<PARAM name="gaussdbToolPath" value="/opt/huawei/install/om" />
<!-- database core File directory–>
<PARAM name="corePath" value="/opt/huawei/corefile" />
<!-- node IP,One to one correspondence with database node name list -->
<PARAM name="backIp1s" value=""/>
<!-- Node deployment information on each server -->
<!-- Deployment information on node 1 -->
<DEVICE sn="node1">
<!-- Host name of node 1 -->
<PARAM name="name" value="node1"/>
<!-- Where node 1 is located AZ and AZ priority -->
<PARAM name="azName" value="AZ1"/>
<PARAM name="azPriority" value="1"/>
<!-- Of node 1 IP,If the server has only one network card available, the backIP1 and sshIP1 Configure to the same IP -->
<PARAM name="backIp1" value=""/>
<PARAM name="sshIp1" value=""/>
<PARAM name="dataNum" value="1"/>
<PARAM name="dataPortBase" value="26000"/>
<PARAM name="dataNode1" value="/opt/huawei/install/data/dn"/>
<PARAM name="dataNode1_syncNum" value="0"/>

1.14.  Check environment variables

echo KaTeX parse error: Undefined control sequence: \Users at position 59: ...src="file:///C:\̲U̲s̲e̲r̲s̲\ADMINI~1\AppDa... cd /opt/software/openGauss/script
[omm@node1 script]$ cp .../clusterconfig.xml .
[omm@node1 script]$ gs_install -X /opt/software/openGauss/script/clusterconfig.xml

Parsing the configuration file.
Check preinstall on every node.
Successfully checked preinstall on every node.
Creating the backup directory.
Successfully created the backup directory.
begin deploy...
Installing the cluster.
begin prepare Install Cluster...
Checking the installation environment on all nodes.
begin install Cluster...
Installing applications on all nodes.
Successfully installed APP.
begin init Instance...
encrypt cipher and rand files for database.
Please enter password for database:huawei@1234
Please repeat for database:huawei@1234
begin to create CA cert files
The sslcert will be generated in /opt/huawei/install/app/share/sslcert/om
Cluster installation is completed.
Deleting instances from all nodes.
Successfully deleted instances from all nodes.
Checking node configuration on all nodes.
Initializing instances on all nodes.
Updating instance configuration on all nodes.
Check consistence of memCheck and coresCheck on database nodes.
Configuring pg_hba on all nodes.
Configuration is completed.
Successfully started cluster.
Successfully installed application.
end deploy...

View database status:

[omm@node1 script]$ gs_om -t status

The following appears:

<o:p> </o:p>-----------------------------------------------------------------------

cluster_name    : dbCluster
cluster_state : Normal
redistributing : No
[omm@node1 script] < / s p a n > < / c o d e > < / p r e > < p > < b > Start move number according to library ( security pretend finish Silence recognize already through Start move ) : < / b > < / p > < p r e t y p e = " S h e l l S e s s i o n " > < c o d e > [ o m m @ n o d e 1 d b 1 ] </span></code></pre><p><b>Start the database (it is started by default after installation):</b></p><pre type="Shell Session"><code>[omm@node1 db1] </span></code></pre><p><b>Start the database (it is started by default after installation):</b></p><pretype="ShellSession"><code>[omm@node1db1] gs_om -t start

Starting cluster.<o:p></o:p>


[SUCCESS] node1:<o:p></o:p>

[2021-04-01 16:50:13.969][29784][][gs_ctl]: gs_ctl started,datadir is /opt/huawei/install/data/dn <o:p></o:p>

[2021-04-01 16:50:13.974][29784][][gs_ctl]:  another server might be running; Please use the restart command<o:p></o:p>


Successfully started.<o:p></o:p>

Use the omm user to do the following.

Log in to the database,

gsql -d postgres -p 26000 -r
#The following prompts appear when you execute the previous command. gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

Other basic commands

\q  #Exit session
gs_om -t stop #close database
gs_om -t restart #Restart database

II Python connection openGauss

The following environments are also available in CentOS 7 6.

Record of problems encountered: solution process:

The first remote connection to openGauss uses the omm initial user, and an error is reported: the omm user is not allowed to connect remotely

To do this, we create a new gem user.

New user gem:

    CREATE USER gem with sysadmin PASSWORD 'huawei@1234';

Configuration file / opt/huawei/install/data/dn/pg_hba.conf append: host all 0.0 0.0/0 sha256, as shown in the figure below:

The first all represents any database, and the second all represents any user (except the initial user) 0.0 0.0/0 represents any host} encryption. Only sha256 is supported.

Restart the database after modifying the configuration:

        gs_om -t restart

2.1 installing python dependent packages

python can connect openGauss with the jaydebeapi third-party library. The construction process is as follows:

 pip3 install prometheus_client
 pip3 install Jpype1
 pip3 install jaydebeapi


If pip is not installed under linux, installation method: < o: P > < / O: P >

    wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo<o:p></o:p>

    yum -y install python-pip<o:p></o:p>

Download openGauss JDBC driver (postgresql.jar) on the official website: https://opengauss.org/zh/download.html

Unzip to a directory, such as / root

2.2 writing test connection script

Write the test script demo under / root py :

import jaydebeapi
url = 'jdbc:postgresql://'
user = 'gem'
password = 'huawei@1234'
dirver = 'org.postgresql.Driver'
jarFile = '/root/postgresql.jar'
sqlStr = """SELECT 666;"""
conn = jaydebeapi.connect(dirver, url, [user, password], jarFile)
curs = conn.cursor()
result = curs.fetchall()

Execute Python 3 demo in the current directory of the command line demo script Py, the results are as follows:


At this time, the connection is successful

III summary
This article provides a way for Python to connect openGauss. Personally, I think the important point in the process is that the memory setting of the virtual machine must be 8G. The installation of openGauss stand-alone version failed because the memory was not given 8G for the first time. In addition, the initial user cannot be used to connect to openGauss remotely, and the configuration file is / opt/huawei/install/data/dn/pg_hba.conf to append: host all 0.0 0.0/0 sha256 .

Keywords: Python CentOS debian

Added by hairulazami on Mon, 27 Dec 2021 09:39:21 +0200