Connect to the database using gsql

catalogue

matters needing attention

prerequisite

Local connection database

Dense database connection operation

Remote connection database

gsql is a database connection tool provided by openGauss to run under the command line. In addition to the basic functions of operating the database, this tool also provides several advanced features for users to use. This section only describes how to use gsql to connect to the database. For more information about how to use gsql, please refer to the chapter "Client Tools > gsql" in the tool reference.

matters needing attention

By default, when the client is idle after connecting to the database, it will be determined according to the parameters session_timeout The default value for automatically disconnects. If you want to turn off the timeout setting, set the parameters session_timeout 0 is enough.

prerequisite

already Confirm connection information.

Local connection database

  1. Log in to the database master node as the operating system user omm.

  2. Connect to the database.

After the database installation is completed, a database named postgres is generated by default. You can connect to the database the first time you connect to it.

Execute the following command to connect to the database.

  gsql -d postgres -p 8000

Where postgres is the name of the database to be connected and 8000 is the port number of the database master node. Please replace according to the actual situation. Alternatively, you can use any 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"

After successful connection, the system displays the following information:

  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=# 

The omm user is an administrator user, so the system displays "DBNAME = #". If you log in and connect to the database as an ordinary user, the system displays "DBNAME = >".

"Non SSL connection" means that the database is not connected by SSL. If you need high security, please Use SSL connection.

  1. It is recommended to change the password when logging in for the first time. The command is as follows.

    openGauss=# ALTER ROLE omm IDENTIFIED BY 'Mypwd123' REPLACE 'XuanYuan@2012';
  2. Exit the database.

    openGauss=# \q

Dense database connection operation

  1. Log in to the database master node as the operating system user omm.

  2. Connect to the database.

    After the database installation is completed, a database named postgres is generated by default. You can connect to the database the first time you connect to it.

    Execute the following command to connect to the database.

    gsql -d postgres -p 8000 -C

    -C means that the secret database is opened, and the key and encryption table can be created. For other parameters, see Local connection database.

    After successful connection, the system displays the following information:

    gsql ((GaussDB Kernel VxxxRxxxCxxopenGauss x.x.x build 50dc16a6) compiled at 2020-11-24 20:03:57 commit 1093 last mr 1793 debug)
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
        
    openGauss=#
    
  3. Exit the database.

    openGauss=# \q

Remote connection database

  1. To complete the remote connection configuration, see Configure server remote connection.
  2. On the client machine (10.10.0.30), upload the client toolkit and configure the execution environment variable of gsql. Take the openEuler environment as an example.

    a. Log in to the client machine as root.

    b. Create the "/ tmp/tools" directory.

    ```
    mkdir /tmp/tools
    ```
    

    c. Get the "openGauss-x.x.x-openEuler-64bit-Libpq.tar.gz" in the software installation package and upload it to the "/ tmp/tools" path.

    >![](public_sys-resources/icon-note.gif) **explain:** 
    >-   The relative position of the software package is the position placed during installation, which shall be filled in according to the actual situation.
    
    >-   The toolkit file names will vary with different operating systems. Please select the corresponding toolkit according to the actual operating system type.
    

    d. Unzip the file.

    ```
    cd /tmp/tools
    tar -zxvf openGauss-x.x.x-openEuler-64bit-Libpq.tar.gz
    ```
    

    e. Log in to the server where the database master node is located and copy the bin directory under the database installation directory to the "/ tmp/tools" path of the client host.

    ```
    scp -r /opt/huawei/install/app/bin root@10.10.0.30:/tmp/tools
    ```
    
    Among them,/opt/huawei/install/app by clusterconfig.xml Configured in file\{gaussdbAppPath\}Path, 10.10.0.30 Host for client ip. 
    

    f. Log in to the host of the client and set the environment variable.

    Open“\~/.bashrc"Documents.
    
    ```
    vi ~/.bashrc
    ```
    
    After entering the following, use“:wq!"Save the command and exit.
    
    ```
    export PATH=/tmp/tools/bin:$PATH
    export LD_LIBRARY_PATH=/tmp/tools/lib:$LD_LIBRARY_PATH
    ```
    

    g. Make the environment variable configuration effective.

    ```
    source ~/.bashrc
    ```
    
  3. Connect to the database.

    After the database installation is completed, a database named postgres is generated by default. You can connect to the database the first time you connect to it.

    gsql -d postgres -h 10.10.0.11 -U jack -p 8000 -W Test@123
    

    postgres is the name of the database to be connected, 10.10.0.11 is the server IP address of the database master node, jack is the user connecting to the database, and 8000 is the port number of the database master node, Test@123 The password of the user jack who connects to the database.

    Description:
    -When the machine connected to openGauss is not in the same network segment as openGauss, -h the specified IP address should be coo set on the Manager interface The value of coolistenip2 (application access IP).
    -Remote connection to the database using omm users is prohibited.

Tags: PostgreSQL openeuler

Posted by stbalaji2u on Fri, 06 May 2022 06:46:06 +0300