[OpenGauss] gsql client tool (1) experiment introduction &&gsql client tool

gsql client tool

Preface

gsql command reference official document

Introduction

This guide is suitable for the use of database development and debugging tools. Through this guide, you can use the gsql database development and debugging tools to connect to the openGauss database.

Content description

The main content of this experiment guide is to use the gsql database development and debugging tool to connect to the openGauss database.

Preconditions

  • Since this experiment is mainly to connect to the database with gsql development and debugging tools on the openEuler operating system, you need to master the basic operations and system commands of the Linux system. For details, please refer to Appendix 1.
  • After connecting to the database, you can use the gsql meta-command to manage and use the database. You need to master the basic operations of the openGauss database. For the basic operations of the database, see Appendix II.
    Experimental environment description
  • Networking description
    This experiment environment is the openGauss database management system, which is installed on the HUAWEI CLOUD openEuler elastic server ECS.
Insert picture description here

Client tool

1.1 Introduction to the experiment

1.1.1 About this experiment

This experiment mainly describes the use of the client tool of the openGauss database and the method of connecting to the database.

1.1.2 The purpose of the experiment

  • Master the method of connecting to the database locally with the gsql client tool;
  • Master the method of remotely connecting to the database with the gsql client tool;
  • Master the use of gsql client tools;
  • Grasp the installation and usage method of Data Studio, a graphical interface client tool.

1.2 gsql client tool

gsql is a database connection tool provided by openGauss to run under the command line. You can use this tool to connect to the server and operate and maintain it. In addition to the basic functions of operating a database, gsql also provides a number of advanced features that are easy for users to use.

1.2.1 gsql connect to the database

gsql is a client tool that comes with openGauss. Use gsql to connect to the database, you can interactively input, edit, and execute SQL statements.

1.2.1.1 Confirm connection information

The client tool connects to the database through the database master node. Therefore, before connecting, you need to obtain the IP address of the server where the database master node is located and the port number information of the database master node.

  • Step 1 Switch to user omm and log in to the master node of the database as operating system user omm.
[[email protected] script]# su - omm 
  • Step 2 Use gs_om -t status --detailcommands to query the status of each instance of openGauss.
[[email protected] ~]$ gs_om -t status --detail

The situation is shown as follows: The

Insert picture description here


IP address of the server where the database master node instance is deployed as above is 192.168.56.101. The data path of the master node of the database is “/gaussdb/data/db1997.

  • Step 3 Confirm the port number of the master node of the database.
    Check the port number information in the postgresql.conf file under the data path of the database master node found in step 2. The example is as follows: modify the path
    according to your own master node
[[email protected] ~]$ cat /gaussdb/data/db1997/postgresql.conf | grep port
Insert picture description here


26000It is the port number of the master node of the database.
Please record the server IP address , data path, and port number of the database master node instance during actual operation , and replace it according to the actual situation in subsequent operations .

1.2.1.2 Connect to the database locally

  • Step 1 Switch to user omm and log in to the master node of the database as operating system user omm.
su - omm
  • Step 2 Start the database service
gs_om -t start
  • Step 3 connect to the database.
    Execute the following command to connect to the database.
[[email protected] ~]$ gsql -d postgres -p 26000 -r 

Among them, postgres is the name of the database that needs to be connected, and 26000 is the port number of the master node of the database. Please replace it according to the actual situation.
After the connection is successful, the system displays a message similar to the following: The

Insert picture description here


omm user is an administrator user, so the system displays it DBNAME=#. If you log in and connect to the database as a normal user, the system displays DBNAME=>
Non-SSL connectionthat you are not using SSL to connect to the database. If you need high security, please use SSL for a secure TCP/IP connection .

  • Step 4 Exit the database.
postgres=# \q       

1.2.2 Get help with gsql

1.2.2.2 When connecting to the database, you can use the following commands to get help information

Switch to the omm user.

su - omm

Use the following command to get help information

gsql --help
Insert picture description here

1.2.2.3 After connecting to the database, you can use the following commands to get help information

  • Step 1 Use the following command to connect to the database.
gsql -d postgres -p 26000 -r 
  • Step 2 Enter the help command.
postgres=# help
Insert picture description here
  • Step 3 View the copyright information.
postgres=# \copyright
Insert picture description here
  • Step 4 View all SQL statements supported by openGauss.
postgres=# \h
Insert picture description here
  • Step 5 To view the parameters of the CREATE DATABASE command, use the following command.
postgres=# \help CREATE DATABASE
Insert picture description here
  • Step 6 View the commands supported by gsql.
postgres=# \? 
Insert picture description here
  • Step 7 Exit the database
postgres=# \q

1.2.3 gsql command usage

1.2.3.1 Prerequisites

The following operations are performed on the host where the master node of the openGauss database is located (local connection to the database), and switch to the omm user.

su - omm

1.2.3.2 Execute a string command

The gsql command directly executes a string command that displays copyright information

gsql -d postgres -p 26000 -c "\copyright"

1.2.3.3 Use a file as a command source instead of interactive input

  • Step 1 Create a folder to store related documents.
mkdir /home/omm/openGauss
  • Step 2 Create a file, for example, the file name is "mysql.sql", and write the executable sql statement "select * from pg_user;".
vi /home/omm/openGauss/mysql.sql

Open the file, enter i, enter the INSERT mode, enterselect * from pg_user;

select * from pg_user;
  • Step 3 Execute the following command to use the file as the command source.
gsql -d postgres -p 26000 -f  /home/omm/openGauss/mysql.sql     
Insert picture description here
  • Step 4 If FILENAME is-(hyphen), read from standard input.
gsql -d postgres -p 26000 -f -
postgres=# select * from pg_user;
  • Step 5 Exit the database connection.
postgres=# \q   

1.2.3.4 List all available databases (The l of \l means list)

gsql -d postgres -p 26000 -l   
Insert picture description here

1.2.3.5 Set gsql variable NAME to VALUE keyword:-v

  • Step 1 sets the value of foo to bar.
gsql -d postgres -p 26000 -v foo=bar       
  • Step 2 can display the value of foo in the database.
postgres=# \echo :foo
bar
Insert picture description here
  • Step 3 Exit the database connection.
postgres=> \q

1.2.3.6 Print gsql version information.

gsql -V
Insert picture description here

1.2.3.7 Use file as output source keyword:-L

  • Step 1 Create a file, for example, the file name is "output.txt".
touch  /home/omm/openGauss/output.txt
Insert picture description here
  • Step 2 Execute the following command to record all query output to a file except for the normal output source.
gsql -d postgres -p 26000 -L /home/omm/openGauss/output.txt
Insert picture description here

Enter the gsql environment and enter the following statement:

postgres=# create table mytable (firstcol int);

CREATE TABLE

Insert picture description here
postgres=# insert into mytable values(100);

INSERT 0 1

Insert picture description here
postgres=# select * from mytable ;
Insert picture description here


Exit the database

postgres=# \q
  • Step 3 View the content in the "output.txt" file as follows:
cat /home/omm/openGauss/output.txt

Shown as follows:

Insert picture description here

1.2.3.8 Redirect all query output to the file FILENAME keyword:-o

  • Step 1 Create a file, for example, the file name is "outputOnly.txt".
touch /home/omm/openGauss/outputOnly.txt
Insert picture description here
  • Step 2 Execute the following commands.
gsql -d postgres -p 26000 -o /home/omm/openGauss/outputOnly.txt 
Insert picture description here
  • Step 3 Enter the gsql environment and enter the following statement:
postgres=# drop table mytable;
postgres=# create table mytable (firstcol int);
postgres=# insert into mytable values(100);
postgres=# select * from mytable;
postgres=# \q 
Insert picture description here

All operations are not echoed.

  • Step 4 Check the contents of the "outputOnly.txt" document as follows:
cat /home/omm/openGauss/outputOnly.txt
Insert picture description here

1.2.3.9 Quiet mode keywords:-q

Quiet mode: No additional information will be printed during execution

gsql -d postgres -p 26000 -q

Enter the gsql environment and enter the following statement:

postgres=# create table t_test (firstcol int);
postgres=# insert into t_test values(200);
postgres=# select * from t_test;
 firstcol 
----------
      200
(1 row)

postgres=# \q
Insert picture description here

No information is displayed when connecting to the database, creating a database, and inserting data .

1.2.3.10 Single-line operation mode keywords:-S

Single-line operation mode: At this time, each command will end with a newline character, like a semicolon

gsql -d postgres -p 26000 -S

Enter the gsql environment and enter the following statement:

postgres^# select * from t_test;
 firstcol 
----------
      200
(1 row)

postgres^# select * from t_test
 firstcol 
----------
      200
(1 row)

postgres=# \q
Insert picture description here

There are ;signs and no ;signs at the end of the statement , and the effect is the same .

1.2.3.11 Edit mode keywords:-r

  • Step 1 Connect to the database with the following command to open the mode that can be edited in the client operation.
gsql -d postgres -p 26000 -r
  • Step 2 Enter the gsql environment and enter the following statement:
select * from t_test;
Insert picture description here

Do not press Enter after step 3 is finished, the cursor flashes at the end.

Insert picture description here

Step 4 Press the "Left" key to move the cursor to " ", and change" "to "firstcol".

Insert picture description here


Insert picture description here

Edit mode "Up, Down, Left, Right", "Delete" and "Backspace" can all be used, and press the "Up" and "Down" keys to switch the entered commands.

  • Step 5 Exit the database connection
postgres=# \q

1.2.3.12 Use username and password to connect to database remotely

Remotely use jack users to connect to the database with ip address 192.168.0.58 and port number 26000

  • Step 1 Log in to the client host (192.168.0.58) and use the following command to remotely log in to the database.
gsql -d postgres -h 192.168.0.58 -U jack -p 26000 -W [email protected];

The -d parameter specifies the target database name, the -U parameter specifies the database user name, the -h parameter specifies the host name, the -p parameter specifies the port number information, and the -W parameter specifies the database user password.
Enter the gsql environment, the display is as follows:

gsql ((openGauss 1.0 build ec0e781b) compiled at 2020-04-27 17:25:57 commit 2144 last mr 131 )
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

postgres=> 

1.2.4 gsql meta-command usage

1.2.4.1 Prerequisites

The following operations are performed on the host where the master node of the openGauss database is located (local connection to the database), and gsql is used to connect to the openGauss database.

  • Step 1 Switch to user omm and log in to the master node of the database as operating system user omm.
su - omm
  • Step 2gsql connects to the database.
gsql -d postgres -p 26000 -r 

1.2.4.2 Print the current query buffer to standard output

  • Step 1 Create the "outputSQL.txt" file.
touch ./home/omm/openGauss/outputSQL.txt

The following figure has gone through a process of switching directories

Insert picture description here
  • Step 2 connect to the database.
gsql -d postgres -p 26000 -r
  • Step 3 enter the following statement.
postgres=# select * from pg_roles;
Insert picture description here
postgres=# \w /home/omm/openGauss/outputSQL.txt
postgres=# \q
Insert picture description here
  • Step 4 Open the file "outputSQL.txt" and view its contents.
cat /home/omm/openGauss/outputSQL.txt

Shown as follows:

Insert picture description here

1.2.4.3 Import data

  • Step 1 connect to the database.
gsql -d postgres -p 26000 -r
  • Step 2 creates the target table a.
postgres=# CREATE TABLE a(a int);
Insert picture description here
  • Step 3 Import data, copy data from stdin to target table a.
postgres=# \copy a from stdin;
Insert picture description here

When the >> symbol prompt appears, input the data and \.end when inputting .

Insert picture description here
>> 1 
>> 2 
>> \.
  • Step 4 Query the data of the imported target table a.
postgres=# SELECT * FROM a; 
Insert picture description here


Exit the database:

postgres=# \q
  • Step 5 Copy the data from the local file to the target table a, and create the file /home/omm/openGauss/2.csv.
vi /home/omm/openGauss/2.csv
  • Step 6 Input i, switch to INSERT mode, insert the data as follows:
    3
    4
    5
    If there are multiple data, the separator is .
    During the import process, if the data source file has more columns than the external table definition, the extra columns at the end of the row are ignored.
  • Step 7 Press Esc and enter:wq and Enter, save and exit.
  • Step 8 Connect to the database.
gsql -d postgres -p 26000 -r
  • Step 9: Copy the data to the target table with the following command.
postgres=# \copy a FROM '/home/omm//openGauss/2.csv' WITH (delimiter',',IGNORE_EXTRA_DATA 'on');
  • Step 10 Query the data of the imported target table a.
postgres=# SELECT * FROM a; 
Insert picture description here

1.2.4.4 Query table space

postgres=# \db

Shown as follows:

Insert picture description here

1.2.4.5 Attribute keywords of query table:\d+

  • Step 1 creates the table customer_t1.
postgres=# DROP TABLE IF EXISTS customer_t1;
postgres=# CREATE TABLE customer_t1 
Insert picture description here
  • Step 2 Query the attributes of the table.
postgres=# \d+;

Shown as follows:

Insert picture description here
  • Step 3 Query the attributes of the table customer_t1.
postgres=# \d+ customer_t1;
Insert picture description here

1.2.4.6 Query index information keywords:\di+

  • Step 1 creates an index on the table customer_t1.
create index customer_t1_index1 on customer_t1(c_customer_id);
  • Step 2 Query index information.
postgres=# \di+;
Insert picture description here
  • Step 3 Query the information of the customer_t1_index1 index.
postgres=# \di+ customer_t1_index1
Insert picture description here

1.2.4.7 Switch database keywords:\c db

  • Step 1 Create a database.
DROP DATABASE IF EXISTS db_tpcc02;
CREATE DATABASE db_tpcc02;
  • Step 2 Switch the database.
postgres=# \c db_tpcc02;

Shown as follows:

Insert picture description here
  • Step 3: Exit the database:
postgres=# \q