openGauss2.0 installation tutorial

Origin: I was destined to participate in the Huawei intelligent database course. When I finished the first step of database course design, I accidentally found that openGauss does not support foreign key constraints. However, I wanted to implement foreign key constraints through various methods (stored procedures and triggers). On the way to search for data, I found that openGauss2.0 began to support foreign key constraints, so I have this article.

The environment required for this tutorial is openEuler aarch64. Other versions can be used for reference.

Note: before installing openeuler 2.0, you need to install the libnsl package, otherwise an error will be reported.

Configure yum source

First, we configure yum for the newly leased server

mkdir /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo  /etc/yum.repos.d/bak/
wget -O /etc/yum.repos.d/openEuler_aarch64.repo https://repo.huaweicloud.com/repository/conf/openeuler_aarch64.repo
yum clean all

If an error occurs, you can Huawei image Find a suitable image

Install dependent packages

yum install -y bzip2 libaio-devel flex bison ncurses-devel glibc-devel patch readline-devel libnsl

Turn off security

## Turn off firewall 
systemctl status firewalld
systemctl disable firewalld.service 
systemctl stop firewalld.service

 ## Close SELinux
sed -i '/SELINUX=/d' /etc/selinux/config 
echo "SELINUX=disabled" >> /etc/selinux/config 
cat /etc/selinux/config|grep -v ^#|grep -v '^$'

Create common users and directories, and authorize

groupadd -g 1001 dbgrp
useradd -u 2001 -g dbgrp omm
mkdir -p /opt/software/openGauss
chown -R omm:dbgrp /opt

Download, unzip and install stand-alone openGauss

su - omm
cd /opt/software/openGauss/
wget https://opengauss.obs.cn-south-1.myhuaweicloud.com/2.0.0/arm/openGauss-2.0.0-openEuler-64bit.tar.bz2
tar -jxf openGauss-2.0.0-openEuler-64bit.tar.bz2

## One click script installation
cd /opt/software/openGauss/simpleInstall/
sh install.sh -w Bigdata@123  -p 26000

Description: --w: initialize the database password (specified by gs_initdb). Security must be set. -- p: The specified openGauss port number, if not specified, defaults to 5432. -- h| – help print instructions for use- After installation, the name of the database is sgnode- After installation, the database directory installation path is /opt/software/openGauss/data/single_node, where /opt/software/openGauss is the decompression package path, and data/single_node is the newly created database node directory.

Note that since our openEuler is a Kunpeng architecture, aarch64, we download openeuler_aarch64 version

During the installation process, you will be prompted whether to create the demo database. Here is optional. I entered yes

At this point, Congratulations, the installation is basically successful

You can start or stop the database server using:
    gs_ctl start|stop|restart -D $GAUSSHOME/data/single_node -Z single_node

The above prompt is the command we enter when we open, close and restart the data in the future.

Configure database remote connection

After entering the database, we first need to create a database and a user for our remote connection.

Enter the following command to enter the database

gsql -d postgres -p 26000 -r

Creating databases and users

create database test;
create user chris password 'bigdata@123';
# In order to avoid unnecessary trouble, it is recommended to give users all permissions
GRANT ALL PRIVILEGES TO chris;

Configure remote connections

Turn off firewall

Configuring a remote connection requires that the firewall be turned off, but if you follow this tutorial, you can omit this step

This operation must be performed under root

systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

Switch omm users

su - omm

Configure PG_ Hba Conf

vim /opt/software/openGauss/data/single_node/pg_hba.conf

In PG_ Hba Add the following to conf

host    all             all             0.0.0.0/0               md5

Configure postgresql Conf

vim /opt/software/openGauss/data/single_node/postgresql.conf

We find the corresponding line in the file and uncomment or modify it

listen_addresses = '*'
local_bind_address = '0.0.0.0'
password_encryption_type = 0

Restart database

# Restart database
gs_ctl restart -D $GAUSSHOME/data/single_node -Z single_node

Modify user password

To log in to the database, the omm user must be switched. Under the omm user

gsql -p 26000 -d test -U chris -W bigdata@123 -r

On the command line of the database, enter the following command to modify the password

test=> ALTER USER chris identified by 'admin@123' replace 'bigdata@123';

Prompt that MD5 has been used for encryption. Success.

Navicat remote connection

Select PostgreSQL connection in the upper left corner

Press test connection to test whether the connection is successful

Attachment: openGauss foreign key constraint syntax

drop table if exists employee;
create table employee(
	e_id char(10) not null primary key,	
	e_name varchar(50) not null
)

drop table if exists company;
create table company(
	c__id char(10) not null primary key,
	e_id char(10) not null references employee(e_id),
	c_name varchar(50) not null
)

Python remote connection

python remote connection uses psycopg2

If psycopg2 is not installed on the computer, you can use pip install psycopg2 to install it from the command line.

Method of use

import psycopg2
conn = psycopg2.connect(dbname="test",
                            user="chris",
                            password="admin@123",
                            host="xxx.xxx.xxx.xxx",
                            port="26000")
conn.set_client_encoding('utf8')
cur = conn.cursor()

# Create table
cur.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
conn.commit()

# insert data
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
conn.commit()

# Data query
cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3])
    
conn.close()

Posted by wildncrazyath3rt on Mon, 30 May 2022 04:24:10 +0530