Using gsql to Connect to a Database

gsql is a database connection tool running in the CLI provided by openGauss. 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 the Tool Reference.

Precautions

By default, if a client is 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 postgres database:
```
gsql -d postgres -p 8000
```
postgres is the name of the database to be connected, and 8000 is the port number of the database primary node. Replace the values as required.
If information similar to the following is displayed, the connection succeeds:
```
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.

postgres=# 
```
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.
Change the password after your first login. The initial password was set manually during openGauss database installation. For details, see Installing openGauss > Executing Installation in the Installation Guide. Here you need to change the initial password to a new one, for example, Mypwd123, by running the following command:
```
postgres=# ALTER ROLE omm IDENTIFIED BY 'Mypwd123' REPLACE 'XuanYuan@2012';
```
Exit the database.
```
postgres=# \q
```

Remotely Connecting to a Database

Configure the remote connection. For details, see Configuring a Remote Connection.
On the client whose IP address is 10.10.0.30, upload the client tool package and configure environment variables for the gsql client. 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 the file 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 in the clusterconfig.xml file, 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 -W Test@123
```
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 of the database, 8000 is the port number of the CN, and Test@123 is the password of user jack.
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.