openGauss Before You Start
Tags: opengauss
Published: 2023-03-10 02:35:09

This section explains how to use databases, including creating databases and tables, inserting data to tables, and querying data in tables.

Prerequisites
openGauss is running properly.

Procedure
Log in as the OS user omm to the primary node of the database.

If you are not sure which server the primary node of the database is deployed on, see Confirming Connection Information.

Connect to a database.

""
gsql -d postgres -p 8000
If the following information is displayed, the connection has been established:

""
gsql ((openGauss x.x.x build 50dc16a6) compiled at 2020-11-29 05:49:21 commit 1071 last mr 1373)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
postgres is the database generated by default after openGauss installation is complete. You can connect to this database to create a database. 8000 is the port number of the database primary node, and you can change it as needed. You can obtain the port number by following the instructions provided in Confirming Connection Information.

Note:

You need to use a client program or tool to connect to the database and to deliver SQL statements
gsql is a command-line interface (CLI) tool provided for connecting to a database. For more database connection methods, see Connecting to a Database.
Create a database user.

Only administrators that are created during openGauss installation can access the initial database by default. You can also create other database users.

""
CREATE USER joe WITH PASSWORD "xxxxxxxxx";
If the following information is displayed, the user has been created:

""
CREATE ROLE
In this case, you have created a user named joe, and the user password is xxxxxxxxx.

The following command sets the joe user as the system administrator.

""
openGauss=# GRANT ALL PRIVILEGES TO joe;
Use GRANT command to set related permissions. For specific operations, please refer to GRANT.

Note: For details about how to create users, see Managing Users and Their Permissions.

Create a database.

""
CREATE DATABASE db_tpcc OWNER joe;
If the following information is displayed, the database has been created:

""
CREATE DATABASE
After creating the db_tpcc database, you can run the following command to exit the postgres database and log in to the db_tpcc database as the user you created for more operations. You can also continue using the default postgres database.

""
openGauss=#  \q
gsql -d db_tpcc -p 8000 -U joe -W Bigdata@123
gsql ((openGauss x.x.x build 50dc16a6) compiled at 2020-11-29 05:49:21 commit 1071 last mr 1373)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

db_tpcc=> 
Create a schema.

""
db_tpcc=> CREATE SCHEMA joe AUTHORIZATION joe;
If the following information is displayed, the schema has been created:

""
CREATE SCHEMA
Note:

New databases are created in the pg_default tablespace by default. To specify another tablespace, run the following statement:

""
openGauss=#  CREATE DATABASE db_tpcc WITH TABLESPACE = hr_local;
CREATE DATABASE
hr_local indicates the tablespace name. For details about how to create a tablespace, see Creating and Managing Tablespaces.

Create a table.

Create a table named mytable that has only one column. The column name is firstcol and the column type is integer.

""
db_tpcc=>  CREATE TABLE mytable (firstcol int);
""
CREATE TABLE
Run the following command to insert data to the table:

""
db_tpcc=> INSERT INTO mytable values (100);
If the following information is displayed, the data has been inserted:

""
INSERT 0 1
Run the following command to view data in the table:

""
db_tpcc=> SELECT * from mytable;
 firstcol 
----------
      100
(1 row)
Note:

By default, new database objects, such as the mytable table, are created in the $user schema. For more details about schemas, see Creating and Managing Schemas.

For more details about how to create a table, see Creating and Managing Tables.

In addition to the created tables, a database contains many system catalogs. These system catalogs contain openGauss installation information and information about various queries and processes in openGauss. You can collect information about the database by querying system catalogs. For details, see Querying System Catalogs.

openGauss supports row and column storage, providing high query performance for interaction analysis in complex scenarios. For details about how to select a storage model, see Planning a Storage Model.