Working with Databases-Using gsql to Connect to a Database
Tags: opengauss
Published: 2023-03-13 07:51:20

gsql is an openGauss-provided database connection tool running in the CLI. gsql provides basic and advanced functions of databases to facilitate user operations. This section describes how to use gsql to connect to a database. For details about how to use gsql, see “Client Tools > gsql” in Tool Reference.

**Precautions**
By default, if a client is in idle state after connecting to a database, the client automatically disconnects from the database in the duration specified by session_timeout. To disable the timeout setting, set session_timeout to 0.

**Prerequisites**
You have confirmed connection information.

**Connecting to a Database Locally**
1. Log in as the OS user **omm** to the primary node of the database.

2. Connect to a database.

2.1 After the database is installed, a database named **postgres** is generated by default. When connecting to a database for the first time, you can connect to this database.

Run the following command to connect to the database:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ldu0cga8h9xxi97gtvr.png)

**postgres** is the name of the database to be connected, and **8000** is the port number of the primary database node. Replace the values as required. You can also run either of the following commands to connect to the database:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e22qg30wv4c3ljcsa38p.png)

If information similar to the following is displayed, the connection succeeds:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/92gscq88gq4jbkqr9rsh.png)

User **omm** is the administrator, and **DBNAME=#** is displayed. If you log in to and connect to the database as a common user, **DBNAME=>** is displayed.

**Non-SSL connection** indicates that the database is not connected in SSL mode. For higher security considerations, you are advised to connect to the database in SSL mode.

3. You are advised to change the password upon the first login. The command is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/27x1d50apw82mkop0b8d.png)

4. Exit the database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1smsqjsrlbp4zxkuvj2a.png)

**Connecting to an Encrypted Database**
1. Log in as the OS user **omm** to the primary node of the database.

2. Connect to a database.

After the database is installed, a database named **postgres** is generated by default. When connecting to a database for the first time, you can connect to this database.

Run the following command to connect to the database:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8wcl3gsu5x6al7td56wx.png)

**-C** indicates that the encrypted database is enabled and keys and encrypted tables can be created. For details about other parameters, see Connecting to a Database Locally.

If information similar to the following is displayed, the connection succeeds:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nagzgf819ckd9dtiswi5.png)

3. Exit the database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2h3d65y6qmrlysostrxd.png)

**Remote Connecting to a Database**
1. Configure the remote connection. For details, see Configuring Remote Connection.

2. On the host (whose IP address is 10.10.0.30) where the client resides, upload the client tool package and configure gsql environment variables. The openEuler environment is used as an example.

a. Log in to the host where the client resides as user root.

b. Run the following command to create the /tmp/tools directory:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8um8r79coi0grwhbugno.png)

c. Obtain **openGauss-x.x.x-openEuler-64bit-Libpq.tar.gz** from the software installation package and upload it to the **/tmp/tools** directory.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/slre4z93xw5ip735e1dr.png)

d. Run the following commands to decompress the package:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gd7i2c5irrlc2eb0igfi.png)

e. Log in to the server where the primary database node is located, and copy the **bin** directory in the database installation directory to **/tmp/tools** on the client host.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j37u0zu61c82lr6ic531.png)

In the preceding command, **/opt/huawei/install/app** indicates the {gaussdbAppPath} path configured in the **clusterconfig.xml** file, and **10.10.0.30** indicates the IP address of the client host.

f. Log in to the host where the client is installed and set environment variables.

Run the following command to open the **~/.bashrc** file:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2rm24btqdbyscg5l0igk.png)

Enter the following information and run :**wq!** to save and exit.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b0i5xftzv30x8je14xtp.png)

g. Run the following command to make the environment variables take effect:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q3vt426wx7404y97r8bt.png)

3. Connect to a database.

After the database is installed, a database named **postgres** is generated by default. When connecting to a database for the first time, you can connect to this database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7p9hqtbc939vt1kxyzb9.png)

**postgres** is the name of the database, **10.10.0.11** is the IP address of the server where the primary database node resides, **jack** is the user of the database, **8000** is the port number of the primary database node, and **Test@123** is the password of user **jack**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v3c38rh03qaz7m0twtjh.png)