Using gsql to Connect to a Database

gsql provided by openGauss is a 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

Connection information has been confirmed. For details, see Confirming Connection Information.

Procedure

Log in as the OS user omm to the primary node of the database.
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:
```
gsql -d postgres -p 8000
```
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:
```
gsql postgres://omm:Gauss_234@127.0.0.1:8000/postgres -r
gsql -d "host=127.0.0.1 port=8000 dbname=postgres user=omm password=Gauss_234"
```
If information similar to the following is displayed, the connection succeeds:
```
gsql((openGauss x.x.x build f521c606) compiled at 2021-09-16 14:55:22 commit 2935 last mr 6385 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 
```
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. If high security is required, connect to the database in SSL mode.
You are advised to change the password upon the first login. The command is as follows:
```
openGauss=# ALTER ROLE omm IDENTIFIED BY 'XXXXXXXX' REPLACE 'XXXXXXXX';
```
Exit the database.
```
openGauss=# \q
```

Remotely Connecting to a Database

Configure the remote connection. For details, see Configuring Remote Connection.
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.
1. Log in to the host where the client resides as user root.
2. Run the following command to create the /tmp/tools directory:
```
mkdir /tmp/tools
```
3. Obtain openGauss-X.X.X-openEuler-64bit-Libpq.tar.gz from the software installation package and upload it to the /tmp/tools directory.
  NOTE:
  - The software package is located where you put it before installation. Set it based on site requirements.
  - The tool package name may vary in different OSs. Select the tool package suitable for your OS.
4. Run the following commands to decompress the package:
```
cd /tmp/tools
tar -zxvf openGauss-X.X.X-openEuler-64bit-Libpq.tar.gz
```
5. 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.
```
scp -r /opt/huawei/install/app/bin root@10.10.0.30:/tmp/tools
```
  In the preceding command, /opt/huawei/install/app indicates the {gaussdbAppPath} path configured during installation, and 10.10.0.30 indicates the IP address of the client host.
6. Log in to the host where the client is installed and set environment variables.
  Run the following command to open the ~/.bashrc file:
```
vi ~/.bashrc
```
  Enter the following content and run :wq! to save and exit.
```
export PATH=/tmp/tools/bin:$PATH
export LD_LIBRARY_PATH=/tmp/tools/lib:$LD_LIBRARY_PATH
```
7. Run the following command to make the environment variables take effect:
```
source ~/.bashrc
```
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.
```
gsql -d postgres -h 10.10.0.11 -U jack -p 8000
Password for user jack:
```
postgres is the name of the database, 10.10.0.11 is the IP address of the server where the primary node of the database resides, jack is the user for connecting to the database, and 8000 is the port number of the database primary node.
NOTE:
- If a machine connected to openGauss is not in the same network segment as openGauss, the IP address specified by -h should be the value of coo.cooListenIp2 (application access IP address) set in Manager.
- Do not remotely connect to the database as user omm.