gsql

gsql is a database connection tool provided by openGauss and runs in the command-line interface (CLI). Users can use gsql to connect to a server and perform operations and maintenance on the server. In addition to basic functions for performing operations on a database, gsql provides several advanced features for users.

Overview

Basic Features

  • Connect to the database: For details, see “Database Usage > Connecting to a Database > Using gsql to Connect to a Database > Remotely Connecting to a Database” in the Developer Guide.

    NOTE: If the gsql client is used to connect to a database, the connection timeout period will be 5 minutes. If the database has not correctly set up a connection and authenticated the identity of the client within this period, gsql will time out and exit. To resolve this problem, see FAQs.

  • Run SQL statements: Interactively entered SQL statements and specified SQL statements in a file can be run.

  • Run meta-commands: Meta-commands help the administrator view database object information, query cache information, format SQL output, and connect to a new database. For details about meta-commands, see Meta-Command Reference.

Advanced Features

Table 1 lists the advanced features of gsql.

Table 1 Advanced features of gsql

Feature Name

Description

Environment variable

gsql provides a variable feature that is similar to the shell command of Linux. The following \set meta-command of gsql can be used to set a variable:

\set varname value

To delete a variable, run the following command:

\unset varname
NOTE:
  • A variable is a simple name-value pair, where the value can be any characters in any length.
  • Variable names must consist of case-sensitive letters (including non-Latin letters), digits, and underscores(_).
  • If the \set varname meta-command (without the second parameter) is used, the variable is set without a value specified.
  • If the \set meta-command without parameters is used, values of all variables are displayed.

For details about variable examples and descriptions, see variables.

SQL substitution

Common SQL statements can be set to variables using the variable feature of gsql to simplify operations.

For details about examples and descriptions about SQL substitution, see SQL substitution.

Customized prompt

Prompts of gsql can be customized. Prompts can be modified by changing the reserved variables of gsql: PROMPT1, PROMPT2, and PROMPT3.

These variables can be set to customized values or the values predefined by gsql. For details, see Prompt.

Historical client operation records

gsql can record historical client operations. This function is enabled by specifying the -r parameter when a client is connected. The number of historical records can be set using the \set command. For example, \set HISTSIZE 50 indicates that the number of historical records is set to 50. \set HISTSIZE 0 indicates that the operation history is not recorded.

NOTE:
  • The default number of historical records is 32. The maximum number of historical records is 500. If interactively entered SQL commands contain Chinese characters, only the UTF-8 encoding environment is supported.
  • For security reasons, the records containing sensitive words, such as PASSWORD and IDENTIFIED, are regarded sensitive and not recorded in historical information. This indicates that you cannot view these records in command output histories.
  • Environment variables

    To set a variable, run the \set meta-command of gsql. For example, to set variable foo to bar, run the following command:

    postgres=# \set foo bar
    

    To reference the value of a variable, add a colon (:) before the variable. For example, to view the value of variable foo, run the following command:

    postgres=# \echo :foo
    bar
    

    The variable reference method is suitable for regular SQL statements and meta-commands.

    gsql pre-defines some special variables and plans the values of these variables. To ensure compatibility with later versions, do not use these variables for other purposes. For details about special variables, see Table 2.

    NOTE:

    • All the special variables consist of upper-case letters, digits, and underscores(_).
    • To view the default value of a special variable, run the \echo :varname meta-command, for example, \echo :DBNAME.

    Table 2 Settings of special variables

    Environment Variable

    Setting Method

    Description

    DBNAME

    \set DBNAME dbname

    Name of the connected database. This variable is set again when a database is connected.

    ECHO

    \set ECHO all | queries
    • If this variable is set to all, only the query information is displayed. This has the same effect as specifying the -a parameter when gsql is used to connect to a database.
    • If this variable is set to queries, the command line and query information are displayed. This has the same effect as specifying the -e parameter when gsql is used to connect to a database.

    ECHO_HIDDEN

    \set ECHO_HIDDEN  on | off | noexec

    When a meta-command (such as \dg) is used to query database information, the value of this variable determines the query behavior.

    • If this variable is set to on, the query statements that are called by the meta-command are displayed, and then the query result is displayed. This has the same effect as specifying the -E parameter when gsql is used to connect to a database.
    • If this variable is set to off, only the query result is displayed.
    • If this variable is set to noexec, only the query information is displayed, and the query is not run.

    ENCODING

    \set ENCODING   encoding

    Character set encoding of the current client.

    FETCH_COUNT

    \set FETCH_COUNT variable
    • If the value is an integer greater than 0, for example, n, n lines will be selected from the result set to the cache and displayed on the screen when the SELECT statement is run.
    • If this variable is not set or set to a value less than or equal to 0, all results are selected at a time to the cache when the SELECT statement is run.
    NOTE:

    A proper variable value helps reduce the memory usage. The recommended value range is from 100 to 1000.

    HISTFILE

    \set HISTFILE filename

    Specifies the file for storing historical records. The default value is ~/.bash_history.

    HISTSIZE

    \set HISTSIZE size

    Specifies the number of commands to store in the command history. The default value is 500.

    HOST

    \set HOST hostname

    Specifies the name of a connected host.

    IGNOREEOF

    \set IGNOREEOF variable
    • If this variable is set to a number, for example, 10, the first nine EOF characters entered (generally by pressing Ctrl+D) in gsql are ignored and the gsql program exits when Ctrl+D is pressed tenth times.
    • If this variable is set to a non-numeric value, the default value is 10.
    • If this variable is deleted, gsql exits when an EOF is entered.

    LASTOID

    \set LASTOID oid

    Specifies the last OID, which is the value returned by an INSERT or lo_import command. This variable is valid only before the output of the next SQL statement is displayed.

    ON_ERROR_ROLLBACK

    \set  ON_ERROR_ROLLBACK  on | interactive | off
    • If the value is on, an error that may occur in a statement in a transaction block is ignored and the transaction continues.
    • If the value is interactive, the error is ignored only in an interactive session.
    • If the value is off (default value), the error triggers the rollback of the transaction block. In on_error_rollback-on mode, a SAVEPOINT is set before each statement of a transaction block, and an error triggers the rollback of the transaction block.

    ON_ERROR_STOP

    \set ON_ERROR_STOP on | off
    • on: specifies that the execution stops if an error occurs. In interactive mode, gsql returns the output of executed commands immediately.
    • off (default value): specifies that an error, if occurring during the execution, is ignored, and the execution continues.

    PORT

    \set PORT port

    Specifies the port number of a connected database.

    USER

    \set USER username

    Specifies the database user you are currently connected as.

    VERBOSITY

    \set VERBOSITY   terse | default | verbose

    This variable can be set to terse, default, or verbose to control redundant lines of error reports.

    • terse: Only critical and major error texts and text locations are returned (which is generally suitable for single-line error information).
    • default: Critical and major error texts and text locations, error details, and error messages (possibly involving multiple lines) are all returned.
    • verbose: All error information is returned.
  • SQL substitution

    gsql, like a parameter of a meta-command, provides a key feature that enables you to substitute a standard SQL statement for a gsql variable. gsql also provides a new alias or identifier for the variable. To replace the value of a variable using the SQL substitution method, add a colon (:) before the variable. For example:

    postgres=# \set foo 'HR.areaS'
    postgres=# select * from :foo;
     area_id |       area_name        
    ---------+------------------------
           4 | Middle East and Africa
           3 | Asia
           1 | Europe
           2 | Americas
    (4 rows)
    

    The above command queries the HR.areaS table.

    NOTICE: The value of the variable is copied literally, so it can even contain unbalanced quotation marks or backslash commands. Therefore, the input content must be meaningful.

  • Prompt

    The gsql prompt can be set using the three variables in Table 3. These variables consist of characters and special escape characters.

    Table 3 Prompt variables

    Variable

    Description

    Example

    PROMPT1

    Specifies the normal prompt used when gsql requests a new command.

    The default value of PROMPT1 is:

    %/%R%#

    PROMPT1 can be used to change the prompt.

    • Change the prompt to [local]:
      postgres=> \set PROMPT1 %M
      [local:/tmp/gaussdba_mppdb]
    • Change the prompt to name:
      postgres=> \set PROMPT1 name
      name
    • Change the prompt to =:
      postgres=> \set PROMPT1 %R
      =

    PROMPT2

    Specifies the prompt displayed when more input is expected because the command that is not terminated with a semicolon (;) or a quote (") is not closed.

    PROMPT2 can be used to display the prompt.

    postgres=# \set PROMPT2 TEST
    postgres=# select * from HR.areaS TEST;
     area_id |       area_name    
    ---------+--------------------
           1 | Europe
           2 | Americas
           4 | Middle East and Africa
           3 | Asia
    (4 rows))

    PROMPT3

    Specifies the prompt displayed when the COPY statement (such as COPY FROM STDIN) is run and data input is expected.

    PROMPT3 can be used to display the COPY prompt.

    postgres=#  \set PROMPT3 '>>>>'
    postgres=#  copy HR.areaS from STDIN;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >>>>1 aa
    >>>>2 bb
    >>>>\.

    The value of the selected prompt variable is printed literally. However, a value containing a percent sign (%) is replaced by the predefined contents depending on the character following the percent sign (%). For details about the defined substitutions, see Table 4.

    Table 4 Defined substitutions

    Symbol

    Description

    %M

    Specifies the full host name (with domain name). The full name is [local] if the connection is over a Unix domain socket, or [local:/dir/name] if the Unix domain socket is not at the compiled default location.

    %m

    Specifies the host name truncated at the first dot. It is [local] if the connection is over a Unix domain socket.

    %>

    Specifies the number of the port that the host is listening on.

    %n

    Replaced with the database session username.

    %/

    Replaced with the name of the current database.

    %~

    Similar to %/. However, the output is tilde (~) if the database is your default database.

    %#

    Uses # if the session user is the database administrator. Otherwise, uses >.

    %R

    • In PROMPT1 normally =, but ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails).
    • In PROMPT2 %R is replaced with a hyphen (-), an asterisk (*), a single or double quotation mark, or a dollar sign ($), depending on whether gsql expects more input because the query is inside a /*...*/ comment or inside a quoted or dollar-escaped string.

    %x

    Replaced with the transaction status.

    • An empty string when it is not in a transaction block
    • An asterisk (*) when it is in a transaction block
    • An exclamation mark (!) when it is in a failed transaction block
    • A question mark (?) when the transaction status is indefinite (for example, because there is no connection).

    %digits

    Replaced with the character with the specified byte.

    %:name

    Specifies the value of the name variable of gsql.

    %command

    Specifies command output, similar to substitution with the "^" symbol.

    %[ . . . %]

    Prompts may contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. For example:

    potgres=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%#'

    The output is a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals.

    Environment Variables

    Table 5 Environment variables related to gsql

    Name

    Description

    COLUMNS

    If \set columns is set to 0, this parameter controls the width of the wrapped format. This width determines whether to change the wide output mode into the vertical output mode if automatic expansion is enabled.

    PAGER

    If the query results do not fit on the screen, they are redirected through this command. You can use the \pset command to disable the pager. Typically, the more or less command is used for viewing the query result page by page. The default is platform-dependent.

    NOTE:

    Display of the less command is affected by the LC_CTYPE environment variable.

    PSQL_EDITOR

    The \e and \ef commands use the editor specified by the environment variables. The variables are examined in the order listed. The default editor on Unix is vi.

    EDITOR

    VISUAL

    PSQL_EDITOR_LINENUMBER_ARG

    When the \e or \ef command is used with a line number parameter, this variable specifies the command-line parameter used to pass the starting line number to the editor. For editors, such as Emacs or vi, this is a plus sign. Include a space in the value of the variable if space is needed between the option name and the line number. For example:
    PSQL_EDITOR_LINENUMBER_ARG = '+'  
    PSQL_EDITOR_LINENUMBER_ARG='--line '

    A plus sign (+) is used by default on Unix.

    PSQLRC

    Specifies the location of the user's .gsqlrc file.

    SHELL

    Has the same effect as the \! command.

    TMPDIR

    Specifies the directory for storing temporary files. The default value is /tmp.

Usage Guidelines

Prerequisites

The user has the permission to access the database.

Background

Use the gsql command to connect to the remote database service. When connecting to the remote database service, enable remote connection on the server. For details, see “Database Usage > Connecting to a Database > Using gsql to Connect to a Database > Remotely Connecting to a Database” in the Developer Guide.

Procedure

  1. Connect to the openGauss server using the gsql tool.

    The gsql tool uses the -d parameter to specify the target database name, the -U parameter to specify the database username, the -h parameter to specify the host name, and the -p parameter to specify the port number.

    NOTE: If the database name is not specified, the default database name generated during initialization will be used. If the database username is not specified, the current OS username will be used by default. If a variable does not belong to any parameter (such as -d and -U), and -d is not specified, the variable will be used as the database name. If -d is specified but -U is not specified, the variable will be used as the database username.

    Example 1: Connect to port 15400 of the postgres database on the local PC as user omm:

    gsql -d postgres -p 15400
    

    Example 2: Connect to the port 15400 of the remote postgres database as user jack.

    gsql -h 10.180.123.163 -d postgres -U jack -p 15400
    

    Example 3: postgres and omm do not belong to any parameter, and they are used as the database name and the username, respectively.

    gsql postgres omm -p 15400
    

    Equals

    gsql -d postgres -U omm -p 15400
    

    For details about the gsql parameters, see Command Reference.

  2. Run a SQL statement.

    The following takes creating database human_staff as an example:

    CREATE DATABASE human_staff;
    CREATE DATABASE
    

    Ordinarily, input lines end when a command-terminating semicolon is reached. If the command is sent and executed without any error, the command output is displayed on the screen.

  3. Execute gsql meta-commands.

    The following takes the listing of all openGauss databases and description information as an example.

    ostgres=#  \l
                                    List of databases
          Name      |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
    ----------------+----------+-----------+---------+-------+-----------------------
     human_resource | omm | SQL_ASCII | C       | C     | 
     postgres       | omm | SQL_ASCII | C       | C     | 
     template0      | omm | SQL_ASCII | C       | C     | =c/omm         +
                    |          |           |         |       | omm=CTc/omm
     template1      | omm | SQL_ASCII | C       | C     | =c/omm          +
                    |          |           |         |       | omm=CTc/omm
     human_staff    | omm | SQL_ASCII | C       | C     | 
    (5 rows)
    

    For details about gsql meta-commands, see Meta-Command Reference .

Example

Run the following command to create a tablespace EXAMPLE:

postgres=# CREATE TABLESPACE EXAMPLE RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE

After the tablespace is created, run the following command to create a schema HR:

postgres=# CREATE schema HR;
CREATE SCHEMA

The example shows how to spread a command over several lines of input. Note the prompt change:

postgres=# CREATE TABLE HR.areaS(
postgres(# area_ID   NUMBER,
postgres(# area_NAME VARCHAR2(25)
postgres(# )tablespace EXAMPLE;
CREATE TABLE

Query the table definition:

postgres=# \d HR.areaS
               Table "hr.areas"
  Column   |         Type          | Modifiers
-----------+-----------------------+-----------
 area_id   | numeric               |
 area_name | character varying(25) |
Tablespace: "example"

Insert four lines of data into HR.areaS.

postgres=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (1, 'Europe');
INSERT 0 1
postgres=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (2, 'Americas');
INSERT 0 1
postgres=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (3, 'Asia');
INSERT 0 1
postgres=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (4, 'Middle East and Africa');
INSERT 0 1

Change the prompt.

postgres=# \set PROMPT1 '%n@%m %~%R%#'
omm@[local] postgres=#

Query the table:

omm@[local] postgres=#SELECT * FROM HR.areaS;
 area_id |       area_name        
---------+------------------------
       1 | Europe
       4 | Middle East and Africa
       2 | Americas
       3 | Asia
(4 rows)

Use the \pset command to display the table in different ways:

omm@[local] postgres=#\pset border 2
Border style is 2.
omm@[local] postgres=#SELECT * FROM HR.areaS;
+---------+------------------------+
| area_id |       area_name        |
+---------+------------------------+
|       1 | Europe                 |
|       2 | Americas               |
|       3 | Asia                   |
|       4 | Middle East and Africa |
+---------+------------------------+
(4 rows)
omm@[local] postgres=#\pset border 0
Border style is 0.
omm@[local] postgres=#SELECT * FROM HR.areaS;
area_id       area_name        
------- ----------------------
      1 Europe
      2 Americas
      3 Asia
      4 Middle East and Africa
(4 rows)
omm@[local] postgres=#\pset border 2
Border style is 2.
omm@[local] postgres=#SELECT * FROM HR.areaS;
+---------+------------------------+
| area_id |       area_name        |
+---------+------------------------+
|       1 | Europe                 |
|       2 | Americas               |
|       3 | Asia                   |
|       4 | Middle East and Africa |
+---------+------------------------+
(4 rows)
omm@[local] postgres=#\pset border 0
Border style is 0.
omm@[local] postgres=#SELECT * FROM HR.areaS;
area_id       area_name        
------- ----------------------
      1 Europe
      2 Americas
      3 Asia
      4 Middle East and Africa
(4 rows)
omm@[local] postgres=#\pset border 2
Border style is 2.
omm@[local] postgres=#SELECT * FROM HR.areaS;
+---------+------------------------+
| area_id |       area_name        |
+---------+------------------------+
|       1 | Europe                 |
|       2 | Americas               |
|       3 | Asia                   |
|       4 | Middle East and Africa |
+---------+------------------------+
(4 rows)
omm@[local] postgres=#\pset border 0
Border style is 0.
omm@[local] postgres=#SELECT * FROM HR.areaS;
area_id       area_name        
------- ----------------------
      1 Europe
      2 Americas
      3 Asia
      4 Middle East and Africa
(4 rows)

Use the meta-command:

omm@[local] postgres=#\a \t \x
Output format is unaligned.
Showing only tuples.
Expanded display is on.
omm@[local] postgres=#SELECT * FROM HR.areaS;
area_id|2
area_name|Americas

area_id|1
area_name|Europe

area_id|4
area_name|Middle East and Africa

area_id|3
area_name|Asia
omm@[local] postgres=# 

Obtaining Help Information

Procedure

  • After connecting to the database, run the following command to obtain the help information:

    gsql --help
    

    The following help information is displayed:

    ......
    Usage:
      gsql [OPTION]... [DBNAME [USERNAME]]
    
    General options:
      -c, --command=COMMAND    run only single command (SQL or internal) and exit
      -d, --dbname=DBNAME      database name to connect to (default: "postgres")
      -f, --file=FILENAME      execute commands from file, then exit
    ......
    
  • After connecting to the database, run the following command to obtain the help information:

    help
    

    The following help information is displayed:

    You are using gsql, the command-line interface to gaussdb.
    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help with gsql commands
           \g or terminate with semicolon to execute query
           \q to quit 
    

Examples

  1. Run the following command to connect to the database:

    gsql -d postgres -p 15400
    

    postgres is the name of the database to be connected, and 15400 is the port number of the primary database node.

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

    gsql ((openGauss x.x build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
    
    postgres=#
    
  2. View the gsql help information. For details, see Table gsql online help.

    Table 6 gsql online help

    Description

    Example

    Query the copyright.

    \copyright

    View SQL statements supported by openGauss.

    View SQL statements supported by openGauss.

    To query all SQL statements supported by openGauss, run the following command:

    postgres=# \h
    Available help:
      ABORT                            
      ALTER APP WORKLOAD GROUP                  
    ... ...

    For example, view parameters of the CREATE DATABASE command:

    postgres=# \help CREATE DATABASE
    Command:     CREATE DATABASE
    Description: create a new database
    Syntax:
    CREATE DATABASE database_name
         [ [ WITH ] {[ OWNER [=] user_name ]|
               [ TEMPLATE [=] template ]|
               [ ENCODING [=] encoding ]|
               [ LC_COLLATE [=] lc_collate ]|
               [ LC_CTYPE [=] lc_ctype ]|
               [ DBCOMPATIBILITY [=] compatibility_type ]|
               [ TABLESPACE [=] tablespace_name ]|
               [ CONNECTION LIMIT [=] connlimit ]}[...] ];

    View the help information about gsql commands.

    For example, view commands supported by gsql.

    postgres=# \?
    General
      \copyright             show FusionInsight LibrA usage and distribution terms
      \g [FILE] or ;         execute query (and send results to file or |pipe)
      \h(\help) [NAME]              help on syntax of SQL commands, * for all commands
      \parallel [on [num]|off] toggle status of execute (currently off)
      \q                     quit gsql
    ... ...

Command Reference

For details about gsql parameters, see Table Common parameters, Table Input and output parameters, Table Output format parameters, and Table Connection parameters.

Table 7 Common parameters

Parameter

Description

Value Range

-c, --command=COMMAND

Specifies that gsql is to run a string command and then exit.

-

-d, --dbname=DBNAME

Specifies the name of the database to connect to.

A character string

-f, --file=FILENAME

Specifies that files are used as the command source instead of interactively-entered commands. After the files are processed, gsql exits. If FILENAME is - (hyphen), then standard input is read.

NOTE:

Environment scenario: one primary and one standby (8 CPUs and 32 GB memory)

Test result: It takes about 8 minutes and 21 seconds to read a 200 MB data file.

It takes about 18 minutes and 41 seconds to read a 500 MB data file.

Suggestion: The file read time increases with the file data volume. If the file size is too large, the file needs to be read again when an exception occurs. In addition, the I/O of the system OS will be overloaded. It is recommended that the file size be about 500 MB.

An absolute path or relative path that meets the OS path naming convention

-l, --list

Lists all available databases and then exits.

-

-v, --set, --variable=NAME=VALUE

Sets variable NAME to VALUE.

For details about variable examples and descriptions, see variables.

-

-X, --no-gsqlrc

Does not read the startup file (neither the system-wide gsqlrc file nor the user's ~/.gsqlrc file).

NOTE:

The startup file is ~/.gsqlrc by default or it can be specified by the environment variable PSQLRC.

-

-1 ("one"), --single-transaction

When gsql uses the -f parameter to execute a script, START TRANSACTION and COMMIT are added to the start and end of the script, respectively, so that the script is executed as one transaction. This ensures that the script is executed successfully. If the script cannot be executed, the script is invalid.

NOTE:

If the script has used START TRANSACTION, COMMIT, or ROLLBACK, this parameter is invalid.

-

-?, --help

Displays help information about gsql command parameters, and exits.

-

-V, --version

Prints the gsql version and exits.

-

Table 8 Input and output parameters

Parameter

Description

Value Range

-a, --echo-all

Prints all input lines to standard output as they are read.

CAUTION:

When this parameter is used in some SQL statements, the sensitive information, such as user password, may be disclosed. Use this parameter with caution.

-

-e, --echo-queries

Displays all SQL commands sent to the server to the standard output as well.

CAUTION:

When this parameter is used in some SQL statements, the sensitive information, such as user password, may be disclosed. Use this parameter with caution.

-

-E, --echo-hidden

Echoes the actual queries generated by \d and other backslash commands.

-

-k, --with-key=KEY

Uses gsql to decrypt imported encrypted files.

NOTICE:

For key characters, such as the single quotation mark (') or double quotation mark (") in shell commands, Linux shell checks whether the input single quotation mark (') or double quotation mark (") matches. If they do not match, Linux shell regards that the user input is unfinished and waits for more input instead of entering the gsql program.

-

-L, --log-file=FILENAME

Writes normal output source and all query output into the FILENAME file.

CAUTION:
  • When this parameter is used in some SQL statements, the sensitive information, such as user password, may be disclosed. Use this parameter with caution.
  • This parameter retains only the query result in the corresponding file, so that the result can be easily found and parsed by other invokers (for example, automatic O&M scripts). Logs about gsql operations are not retained.

An absolute path or relative path that meets the OS path naming convention

-m, --maintenance

Allows openGauss to be connected during two-phase transaction recovery.

NOTE:

The parameter is for engineers only. When this parameter is used, gsql can be connected to the standby server to check data consistency between the primary server and standby server.

-

-n, --no-libedit

Closes command line editing.

-

-o, --output=FILENAME

Puts all query output into the FILENAME file.

An absolute path or relative path that meets the OS path naming convention

-q, --quiet

Indicates the quiet mode and no additional information will be printed.

By default, gsql displays various information.

-s, --single-step

Runs in single-step mode. It indicates that the user is prompted before each command is sent to the server. This option can be also used for canceling execution. Use this option to debug scripts.

CAUTION:

When this parameter is used in some SQL statements, the sensitive information, such as user password, may be disclosed. Use this parameter with caution.

-

-S, --single-line

Runs in single-line mode where a line break terminates an SQL command, as a semicolon does.

-

Table 9 Output format parameters

Parameter

Description

Value Range

-A, --no-align

Switches to unaligned output mode.

The default output mode is aligned.

-F, --field-separator=STRING

Specifies the field separator. The default is the vertical bar (|).

-

-H, --html

Turns on the HTML tabular output.

-

-P, --pset=VAR[=ARG]

Specifies the print option in the \pset format in the command line.

NOTE:

The equal sign (=), instead of the space, is used here to separate the name and value. For example, enter -P format=latex to set the output format to LaTeX.

-

-R, --record-separator=STRING

Sets the record separator.

-

-r

Enables the function of recording historical client operations.

This function is disabled by default.

-t, --tuples-only

Prints only tuples.

-

-T, --table-attr=TEXT

Specifies options to be placed within the HTML table tag.

Use this parameter with the -H,--html parameter to specify the output to the HTML format.

-

-x, --expanded

Turns on the expanded table formatting mode.

-

-z, --field-separator-zero

Sets the field separator in the unaligned output mode to be blank.

Use this parameter with the -A, --no-align parameter to switch to unaligned output mode.

-

-0, --record-separator-zero

Sets the record separator in the unaligned output mode to be blank.

Use this parameter with the -A, --no-align parameter to switch to unaligned output mode.

-

Table 10 Connection parameters

Parameter

Description

Value Range

-h, --host=HOSTNAME

Specifies the host name of the machine on which the server is running or the directory for the Unix-domain socket.

If the host name is omitted, gsql connects to the server of the local host over the Unix domain socket or over TCP/IP to connect to local host without the Unix domain socket.

-p, --port=PORT

Specifies the port number of the database server.

You can modify the default port number using the -p, --port=PORT parameter.

The default value is 15400.

-U, --username=USERNAME

Specifies the user that connects to the database.

NOTE:
  • If this parameter is specified, you also need to enter your password for identity authentication when connecting to the database. You can enter the password interactively or use the -W parameter to specify a password.
  • To connect to a database, add an escape character before any dollar sign ($) in the user name.

A character string. The default user is the current user that operates the system.

-W, --password=PASSWORD

Specifies the password when the -U parameter is used to connect to a remote database.

NOTE:

When the server where the primary database node is located connects to the local primary database node, the trust connection is used by default and this parameter is ignored.

To connect to a database, add an escape character before any backslash (\) or back quote (`) in the password.

If this parameter is not specified but database connection requires your password, you will be prompted to enter your password in interactive mode. The maximum length of the password is 999 bytes, which is restricted by the maximum value of the GUC parameter password_max_length.

The password must meet the complexity requirement.

Meta-Command Reference

This section describes meta-commands provided by gsql after the openGauss database CLI tool is used to connect to a database. A gsql meta-command can be anything that you enter in gsql and begins with an unquoted backslash.

Precautions

  • The format of the gsql meta-command is a backslash (\) followed by a command verb, and then a parameter. The parameters are separated from the command verb and from each other by any number of whitespace characters.

  • To include whitespace in a parameter, you can quote it with single quotation marks (''). To include single quotation marks in a parameter, add a backslash in front of it. Anything contained in single quotation marks is furthermore subject to C-like substitutions for \n (new line), \t (tab), \b (backspace), \r (carriage return), \f (form feed), \digits (octal), and \xdigits (hexadecimal).

  • Within a parameter, text enclosed in double quotation marks ("") is taken as a command line input to the shell. The command output (with any trailing newline removed) is taken as a parameter.

  • If an unquoted parameter begins with a colon (:), the parameter is taken as a gsql variable and the value of the variable is used as the parameter value instead.

  • Some commands take an SQL identifier (such as a table name) as a parameter. These parameters follow the SQL syntax rules: Unquoted letters are forced to lowercase, while double quotation marks ("") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotation marks, paired double quotation marks reduce to a single double quotation mark in the result name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and “Aweird"“name” becomes A weird"name.

  • Parameter parsing stops when another unquoted backslash appears. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of parameters and continues parsing SQL statements if any. In this way, SQL statements and gsql commands can be freely mixed in a line. However, the parameters of a meta-command cannot continue beyond the end of a line in any situations.

Meta-Commands

For details about meta-commands, see Table Common meta-commands, Table Query buffer meta-commands, Table Input and output meta-commands, Table Information display meta-commands, Table Formatting meta-commands,Table Connection meta-commands , Table OS meta-commands, Table Variable meta-commands, and Table Large object meta-commands.

NOTICE: FILE mentioned in the following commands indicates a file path. This path can be an absolute path such as /home/gauss/file.txt or a relative path, such as file.txt. By default, a file.txt is created in the path where the user runs gsql commands.

Table 11 Common meta-commands

Parameter

Description

Value Range

\copyright

Displays the openGauss version and copyright information.

-

\g [FILE] or ;

Performs a query operation and sends the result to a file or pipe.

-

\h(\help) [NAME]

Provides syntax help on the specified SQL statement.

If NAME is not specified, then gsql will list all the commands for which syntax help is available. If NAME is an asterisk (*), syntax help on all SQL statements is displayed.

\parallel [on [num]|off]

Controls the parallel execution function.

  • on: The switch is enabled and the maximum number of concurrently executed tasks is num.
  • off: This switch is disabled.
NOTE:
  • Parallel execution is not allowed in a running transaction and a transaction is not allowed to be stared during parallel execution.
  • Parallel execution of \d meta-commands is not allowed.
  • If SELECT statements are run concurrently, customers can accept the problem that the return results are displayed randomly but they cannot accept it if a core dump or process response failure occurs.
  • SET statements are not allowed in concurrent tasks because they may cause unexpected results.
  • Temporary tables cannot be created. If temporary tables are required, create them before parallel execution is enabled, and use them only in the parallel execution. Temporary tables cannot be created in parallel execution.
  • When \parallel is executed, num independent gsql processes can be connected to the database server.
  • The duration of all the jobs specified using \parallel cannot exceed session_timeout. Otherwise, the database may be disconnected during parallel execution.

The default value of num is 1024.

NOTICE:
  • The maximum number of connections allowed by the server is determined based on max_connection and the number of current connections.
  • Set the value of num based on the allowed number of connections.

\q

Exits the gsql program. In a script file, this command is run only when a script terminates.

-

Table 12 Query buffer meta-commands

Parameter

Description

\e [FILE] [LINE]

Uses an external editor to edit the query buffer or file.

\ef [FUNCNAME [LINE]]

Uses an external editor to edit the function definition. If LINE is specified, the cursor will point to the specified line of the function body.

\p

Prints the current query buffer to the standard output.

\r

Resets (clears) the query buffer.

\w FILE

Outputs the current query buffer to a file.

Table 13 Input and output meta-commands

Parameter

Description

\copy { table [ ( column_list ) ] | ( query ) } { from | to } { filename | stdin | stdout | pstdin | pstdout } [ with ] [ binary ] [ oids ] [ delimiter [ as ] 'character' ] [ null [ as ] 'string' ] [ csv [ header ] [ quote [ as ] 'character' ] [ escape [ as ] 'character' ] [ force quote column_list | * ] [ force not null column_list ] ]

After logging in to the database on any psql client, you can import and export data. This is an operation of running the SQL COPY command, but not the server that reads or writes data to a specified file. Instead, data is transferred between the server and the local file system. This means that the accessibility and permissions of the file are the permissions of the local user rather than the server. The initial database user permission is not required.

NOTE:

\copy applies only to small-scale data import in good format. It does not preprocess invalid characters or provide error tolerance. COPY is preferred for data import.

\echo [STRING]

Writes a character string to the standard output.

\i FILE

Reads content from FILE and uses them as the input for a query.

\i+ FILE KEY

Runs commands in an encrypted file.

\ir FILE

Is similar to \i, but resolves relative path names differently.

\ir+ FILE KEY

Is similar to \i+, but resolves relative path names differently.

\o [FILE]

Saves all query results to a file.

\qecho [STRING]

Prints a character string to the query result output.

NOTE: In Table 14, option S indicates displaying the system object and PATTERN indicates displaying the additional description information and the name of the object to be displayed.

Table 14 Information display meta-commands

Parameter

Description

Value Range

Example

\d[S+]

Lists all tables, views, and sequences of all schemas in search_path. When objects with the same name exist in different schemas in search_path, only the object in the schema that ranks first in search_path is displayed.

-

List all tables, views, and sequences of all schemas in search_path.

postgres=# \d

\d[S+] NAME

Lists the structure of specified tables, views, and indexes.

-

List the structure of table a.

postgres=#  \dtable+ a

\d+ [PATTERN]

Lists all tables, views, and indexes.

If PATTERN is specified, only tables, views, and indexes whose names match PATTERN are displayed.

List all tables, views, and indexes whose names start with f.

postgres=# \d+ f*

\da[S] [PATTERN]

Lists all available aggregate functions, together with their return value types and the data types.

If PATTERN is specified, only aggregate functions whose names match PATTERN are displayed.

List all available aggregate functions whose names start with f, together with their return value types and the data types.

postgres=# \da f*

\db[+] [PATTERN]

Lists all available tablespaces.

If PATTERN is specified, only tablespaces whose names match PATTERN are displayed.

List all available tablespaces whose names start with p.

postgres=# \db p*

\dc[S+] [PATTERN]

Lists all available conversions between character sets.

If PATTERN is specified, only conversions whose names match PATTERN are displayed.

List all available conversions between character sets.

postgres=# \dc *

\dC[+] [PATTERN]

Lists all type conversions.

If PATTERN is specified, only conversions whose names match PATTERN are displayed.

List all type conversion whose patten names start with c.

postgres=# \dC c*

\dd[S] [PATTERN]

Lists descriptions about objects matching PATTERN.

If PATTERN is not specified, all visible objects are displayed. The objects include aggregations, functions, operators, types, relations (table, view, index, sequence, and large object), and rules.

List all visible objects.

postgres=# \dd

\ddp [PATTERN]

Lists all default permissions.

If PATTERN is specified, only permissions whose names match PATTERN are displayed.

List all default permissions.

postgres=# \ddp

\dD[S+] [PATTERN]

Lists all available domains.

If PATTERN is specified, only domains whose names match PATTERN are displayed.

List all available domains.

postgres=# \dD

\ded[+] [PATTERN]

Lists all Data Source objects.

If PATTERN is specified, only objects whose names match PATTERN are displayed.

List all Data Source objects.

postgres=# \ded

\det[+] [PATTERN]

Lists all external tables.

If PATTERN is specified, only tables whose names match PATTERN are displayed.

List all external tables.

postgres=# \det

\des[+] [PATTERN]

Lists all external servers.

If PATTERN is specified, only servers whose names match PATTERN are displayed.

List all external servers.

postgres=# \des

\deu[+] [PATTERN]

Lists user mappings.

If PATTERN is specified, only mappings whose names match PATTERN are displayed.

List user mappings.

postgres=# \deu

\dew[+] [PATTERN]

Lists foreign-data wrappers.

If PATTERN is specified, only wrappers whose names match PATTERN are displayed.

List foreign-data wrappers.

postgres=# \dew

\df[antw][S+] [PATTERN]

Lists all available functions, together with their parameters and return types. a indicates an aggregate function, n indicates a common function, t indicates a trigger, and w indicates a window function.

If PATTERN is specified, only functions whose names match PATTERN are displayed.

List all available functions, together with their parameters and return types.

postgres=# \df

\dF[+] [PATTERN]

Lists all text search configurations.

If PATTERN is specified, only configurations whose names match PATTERN are displayed.

List all text search configurations.

postgres=# \dF+

\dFd[+] [PATTERN]

Lists all text search dictionaries.

If PATTERN is specified, only dictionaries whose names match PATTERN are displayed.

List all text search dictionaries.

postgres=# \dFd

\dFp[+] [PATTERN]

Lists all text search parsers.

If PATTERN is specified, only parsers whose names match PATTERN are displayed.

List all text search parsers.

postgres=# \dFp

\dFt[+] [PATTERN]

Lists all text search templates.

If PATTERN is specified, only templates whose names match PATTERN are displayed.

List all text search templates.

postgres=# \dFt

\dg[+] [PATTERN]

Lists all database roles.

NOTE:

Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \du. The two commands are all reserved for forward compatibility.

If PATTERN is specified, only roles whose names match PATTERN are displayed.

List all database roles whose names start with j and end with e.

postgres=# \dg j?e

\dl

An alias for \lo_list, which shows a list of large objects.

-

List all large objects.

postgres=# \dl

\dL[S+] [PATTERN]

Lists available procedural languages.

If PATTERN is specified, only languages whose names match PATTERN are displayed.

List available procedural languages.

postgres=# \dL

\dn[S+] [PATTERN]

Lists all schemas (namespace).

If PATTERN is specified, only schemas whose names match PATTERN are displayed. By default, only schemas you created are displayed.

List information about all schemas whose names start with d.

postgres=#  \dn+ d*

\do[S] [PATTERN]

Lists all available operators with their operand and return types.

If PATTERN is specified, only operators whose names match PATTERN are displayed. By default, only operators you created are displayed.

List all available operators with their operand and return types.

postgres=# \do

\dO[S+] [PATTERN]

Lists collations.

If PATTERN is specified, only collations whose names match PATTERN are displayed. By default, only collations you created are displayed.

List collations.

postgres=# \dO

\dp [PATTERN]

Lists tables, views, and related permissions.

The following result about \dp is displayed:

rolename=xxxx/yyyy  --Assigning permissions to a role
=xxxx/yyyy  --Assigning permissions to public

xxxx indicates assigned permissions, and yyyy indicates roles with the assigned permissions. For details about permission descriptions, see Table Description of permissions.

If PATTERN is specified, only tables and views whose names match PATTERN are displayed.

List tables, views, and related permissions.

postgres=# \dp

\drds [PATTERN1 [PATTERN2]]

Lists all modified configuration parameters. These settings can be for roles, for databases, or for both. PATTERN1 and PATTERN2 indicate a role pattern and a database pattern, respectively.

If PATTERN is specified, only collations whose names match PATTERN are displayed. If the default value is used or * is specified, all settings are listed.

List all modified configuration parameters of the postgres database.

postgres=# \drds * postgres

\dT[S+] [PATTERN]

Lists all data types.

If PATTERN is specified, only types whose names match PATTERN are displayed.

List all data types.

postgres=# \dT

\du[+] [PATTERN]

Lists all database roles.

NOTE:

Since the concepts of "users" and "groups" have been unified into "roles", this command is now equivalent to \dg. The two commands are all reserved for forward compatibility.

If PATTERN is specified, only roles whose names match PATTERN are displayed.

List all database roles.

postgres=# \du

\dE[S+] [PATTERN]

\di[S+] [PATTERN]

\ds[S+] [PATTERN]

\dt[S+] [PATTERN]

\dv[S+] [PATTERN]

In this group of commands, the letters E, i, s, t, and v stand for a foreign table, index, sequence, table, or view, respectively. You can specify any or a combination of these letters sequenced in any order to obtain an object list. For example, \dit lists all indexes and tables. If a command is suffixed with a plus sign (+), physical dimensions and related descriptions of each object will be displayed.

NOTE:

This version does not support sequences.

If PATTERN is specified, only objects whose names match PATTERN are displayed. By default, only objects you created are displayed. You can specify PATTERN or S to view other system objects.

List all indexes and views.

postgres=# \div

\dx[+] [PATTERN]

Lists the extensions of the installed database.

If PATTERN is specified, only extensions whose names match PATTERN are displayed.

List the extensions of the installed database.

postgres=# \dx

\l[+]

Lists the names, owners, character set encodings, and permissions of all the databases in the server.

-

List the names, owners, character set encodings, and permissions of all the databases in the server.

postgres=#  \l

\sf[+] FUNCNAME

Displays function definitions.

NOTE:

If the function name contains parentheses, enclose the function name with quotation marks and add the parameter type list following the double quotation marks. Also enclose the list with parentheses.

-

Assume a function function_a and a function func()name. This parameter will be as follows:

postgres=# \sf function_a
postgres=# \sf 
"func()name"(argtype1, argtype2)

\z [PATTERN]

Lists all tables, views, and sequences in the database and their access permissions.

If a pattern is given, it is a regular expression, and only matched tables, views, and sequences are displayed.

List all tables, views, and sequences in the database and their access permissions.

postgres=# \z

Table 15 Description of permissions

Parameter

Description

r

SELECT: allows you to read data from specified tables and views.

w

UPDATE: allows you to update columns for specified tables.

a

INSERT: allows you to insert data to specified tables.

d

DELETE: allows you to delete data from specified tables.

D

TRUNCATE: allows you to delete all data from specified tables.

x

REFERENCES: allows you to create foreign key constraints. This parameter does not take effect because foreign keys are not supported.

t

TRIGGER: allows you to create a trigger on specified tables.

X

EXECUTE: allows you to use specified functions and the operators that are realized by the functions.

U

USAGE:

  • For procedural languages, allows you to specify a procedural language when creating a function.
  • For schemas, allows you to access objects included in specified schemas.
  • For sequences, allows you to use the nextval function.

C

CREATE:

  • For databases, allows you to create schemas within a database.
  • For schemas, allows you to create objects in a schema.
  • For tablespaces, allows you to create tables in a tablespace and set the tablespace to default one when creating databases and schemas.

c

CONNECT: allows you to access specified databases.

T

TEMPORARY: allows you to create temporary tables.

arwdDxt

ALL PRIVILEGES: grants all available permissions to specified users or roles at a time.

*

Authorization options for preceding permissions.

Table 16 Formatting meta-commands

Parameter

Description

\a

Controls the switchover between unaligned mode and aligned mode.

\C [STRING]

Sets the title of any table being printed as the result of a query or cancels such a setting.

\f [STRING]

Sets a field separator for unaligned query output.

\H

  • If the text format schema is used, switches to the HTML format.
  • If the HTML format schema is used, switches to the text format.

\pset NAME [VALUE]

Sets options affecting the output of query result tables. For details about the value of NAME, see Table Adjustable printing options.

\t [on|off]

Switches the information and row count footer of the output column name.

\T [STRING]

Specifies attributes to be placed within the table tag in HTML output format. If the parameter is not configured, the attributes are not set.

\x [on|off|auto]

Switches expanded table formatting modes.

Table 17 Adjustable printing options

Option

Description

Value Range

border

The value must be a number. In general, a larger number indicates wider borders and more table lines.

  • The value is an integer greater than 0 in HTML format.
  • The value range in other formats is as follows:
    • 0: no border
    • 1: internal dividing line
    • 2: table frame

expanded (or x)

Switches between regular and expanded formats.

  • When the expanded format is enabled, query results are displayed in two columns, with the column name on the left and the data on the right. This format is useful if the data does not fit the screen in the normal "horizontal" format.
  • The expanded format is used when the query output is wider than the screen. Otherwise, the regular format is used. The regular format is effective only in the aligned and wrapped formats.

fieldsep

Specifies the field separator to be used in unaligned output format. In this way, you can create tab- or comma-separated output required by other programs. To set a tab as field separator, type \pset fieldsep '\t'. The default field separator is a vertical bar ('|').

-

fieldsep_zero

Sets the field separator to be used in unaligned output format to zero bytes.

-

footer

Enables or disables the display of table footers.

-

format

Selects the output format. Unique abbreviations are allowed. (This means a single letter is sufficient.)

Value range:

  • unaligned: Write all columns of a row on one line, separated by the currently active column separator.
  • aligned: This format is standard and human-readable.
  • wrapped: This format is similar to aligned, but includes the packaging cross-line width data value to suit the width of the target field output.
  • html: This format outputs table to the markup language for a document. The output is not a complete document.
  • latex: This format outputs table to the markup language for a document. The output is not a complete document.
  • troff-ms: This format outputs table to the markup language for a document. The output is not a complete document.

null

Sets a character string to be printed in place of a null value.

By default, nothing is printed, which can easily be mistaken for an empty character string.

numericlocale

Enables or disables the display of a locale-specific character to separate groups of digits to the left of the decimal marker.

  • on: The specified separator is displayed.
  • off: The specified separator is not displayed.

If this parameter is ignored, the default separator is displayed.

pager

Controls the use of a pager for query and gsql help outputs. If the PAGER environment variable is set, the output is piped to the specified program. Otherwise, a platform-dependent default is used.

  • on: The pager is used for terminal output that does not fit the screen.
  • off: The pager is not used.
  • always: The pager is used for all terminal output regardless of whether it fits the screen.

recordsep

Specifies the record separator to be used in unaligned output format.

-

recordsep_zero

Specifies the record separator to be used in unaligned output format to zero bytes.

-

tableattr (or T)

Specifies attributes to be placed inside the HTML table tag in HTML output format (such as cellpadding or bgcolor). Note that you do not need to specify border here because it has been used by \pset border. If no value is given, the table attributes do not need to be set.

-

title

Specifies the table title for any subsequently printed tables. This can be used to give your output descriptive tags. If no value is given, the title does not need to be set.

-

tuples_only (or t)

Enables or disables the tuples-only mode. Full display may show extra information, such as column headers, titles, and footers. In tuples-only mode, only the table data is displayed.

-

Table 18 Connection meta-commands

Parameter

Description

Value Range

\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]

Connects to a new database. The current database is postgres. If a database name contains more than 63 bytes, only the first 63 bytes are valid and are used for connection. However, the database name displayed in the command line of gsql is still the name before the truncation.

NOTE:

If the database login user is changed during reconnection, you need to enter the password for the new user. The maximum length of the password is 999 bytes, which is restricted by the maximum value of the GUC parameter password_max_length.

-

\encoding [ENCODING]

Sets the client character set encoding.

This command shows the current encoding if it has no parameter.

\conninfo

Outputs information about the current database connection.

-

Table 19 OS meta-commands

Parameter

Description

Value Range

\cd [DIR]

Changes the current working directory.

An absolute path or relative path that meets the OS path naming convention

\setenv NAME [VALUE]

Sets the NAME environment variable to VALUE. If VALUE is not provided, do not set the environment variable.

-

\timing [on|off]

Toggles a display of how long each SQL statement takes, in milliseconds.

  • on: enables the display function.
  • off: disables the display function.

\! [COMMAND]

Escapes to a separate Unix shell or runs a Unix command.

-

Table 20 Variable meta-commands

Parameter

Description

\prompt [TEXT] NAME

Prompts the user to use texts to specify a variable name.

\set [NAME [VALUE]]

Sets the NAME internal variable to VALUE. If more than one value is provided, NAME is set to the concatenation of all of them. If only one parameter is provided, the variable is set with an empty value.

Some common variables are processed in another way in gsql, and they are the combination of uppercase letters, numbers, and underscores. Table 21 describes a list of variables that are processed in a way different from other variables.

\unset NAME

Unsets or deletes the variable name of gsql.

Table 21 Common \set commands

Command

Description

Value Range

\set VERBOSITY value

This variable can be set to default, verbose, or terse to control redundant lines of error reports.

Value range: default, verbose, terse

\set ON_ERROR_STOP value

If this variable is set, the script execution stops immediately. If this script is invoked from another script, that script will be stopped immediately as well. If the primary script is invoked using the -f option rather than from one gsql session, gsql will return error code 3, indicating the difference between the current error and critical errors. (The error code for critical errors is 1.)

Value range: on/off, true/ false, yes/no, 1/0

\set RETRY [retry_times]

Determines whether to enable the retry function if statement execution encounters errors. The parameter retry_times specifies the maximum number of retry times and the default value is 5. Its value ranges from 5 to 10. If the retry function has been enabled, when you run the \set RETRY command again, the retry function will be disabled.

The configuration file retry_errcodes.conf shows a list of errors. If these errors occur, retry is required. This configuration file is placed in the same directory as that for executable gsql programs. This configuration file is configured by the system rather than by users and cannot be modified by the users.

The retry function can be used in the following error scenarios:

  • YY001: TCP communication errors. Print information: Connection reset by peer.
  • YY002: TCP communication errors. Print information: Connection reset by peer.
  • YY003: Lock timeout. Print information: Lock wait timeout.../wait transaction xxx sync time exceed xxx.
  • YY004: TCP communication errors. Print information: Connection timed out.
  • YY005: Failed to deliver the SET query command. Print information: ERROR SET query.
  • YY006: Failed to apply for memory. Print information: memory is temporarily unavailable.
  • YY007: Communication library error. Print information: Memory allocate error.
  • YY008: Communication library error. Print information: No data in buffer.
  • YY009: Communication library error. Print information: Close because release memory.
  • YY010: Communication library error. Print information: TCP disconnect.
  • YY011: Communication library error. Print information: SCTP disconnect.
  • YY012: Communication library error. Print information: Stream closed by remote.
  • YY013: Communication library error. Print information: Wait poll unknown error.
  • YY014, YY015, 53200, 08006, 08000, 57P01, XX003, XX009

If an error occurs, gsql queries connection status of every database node. If the connection status is abnormal, gsql sleeps for 1 minute and tries again. In this case, the retries in most of the primary/standby switchover scenarios are involved.

NOTE:
  1. Statements in transaction blocks cannot be retried upon a failure.
  2. Retry is not supported if errors are found using ODBC or JDBC.
  3. For SQL statements with unlogged tables, the retry is not supported if a node is faulty.
  4. For gsql client faults, the retry is not supported.

Value range of retry_times: 5-10

Table 22 Large object meta-commands

Parameter

Description

\lo_list

Displays a list of all openGauss large objects stored in the database, along with comments provided for them.

PATTERN

The various \d commands accept a PATTERN parameter to specify the object name to be displayed. In the simplest case, a pattern is just the exact name of the object. The characters within a pattern are normally folded to lower case, similar to those in SQL names. For example, \dt FOO will display the table named foo. As in SQL names, placing double quotation marks (”) around a pattern prevents them being folded to lower case. If you need to include a double quotation mark (") in a pattern, write it as a pair of double quotation marks ("") within a double-quote sequence, which is in accordance with the rules for SQL quoted identifiers. For example, \dt “FOO"“BAR” will be displayed as a table named FOO"BAR instead of foo"bar. You cannot put double quotation marks around just part of a pattern, which is different from the normal rules for SQL names. For example, \dt FOO"FOO"BAR will be displayed as a table named fooFOObar if just part of a pattern is quoted.

Whenever the PATTERN parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path, which is equivalent to using an asterisk (*) as the pattern. An object is regarded to be visible if it can be referenced by name without explicit schema qualification. To see all objects in the database regardless of their visibility, use a dot within double asterisks (*.*) as the pattern.

Within a pattern, the asterisk (*) matches any sequence of characters (including no characters) and a question mark (?) matches any single character. This notation is comparable to Unix shell file name patterns. For example, \dt int* displays tables whose names begin with int. But within double quotation marks, the asterisk (*) and the question mark (?) lose these special meanings and are just matched literally.

A pattern that contains a dot (.) is interpreted as a schema name pattern followed by an object name pattern. For example, \dt foo*.*bar* displays all tables (whose names include bar) in schemas starting with foo. If no dot appears, then the pattern matches only visible objects in the current schema search path. Again, a dot within double quotation marks loses its special meaning and is matched literally.

Advanced users can use regular-expression notations, such as character classes. For example [0-9] can be used to match any digit. All regular expression special characters, except the following, work as specified in POSIX regular expressions:

  • A dot (.) is used as a separator.
  • An asterisk (*) is translated into an asterisk prefixed with a dot (.*), which is a regular-expression marking.
  • A question mark (?) is translated into a dot (.).
  • A dollar sign ($) is matched literally.

You can, as required, write ?, (R+|), (R|), and R to the following PATTERN characters: ., R*, and R?. The dollar sign ($) does not need to work as a regular-expression character since the pattern must match the whole name, which is different from the usual interpretation of regular expressions. In other words, the dollar sign ($) is automatically appended to your pattern. If you do not expect a pattern to be anchored, write an asterisk (*) at its beginning or end. All regular-expression special characters within double quotation marks lose their special meanings and are matched literally. Regular-expression special characters in operator name patterns (such as the \do parameter) are also matched literally.

FAQs

Low Connection Performance

  • log_hostname is enabled, but DNS is incorrect.

    Connect to the database, and run show log_hostname to check whether log_hostname is enabled in the database.

    If it is enabled, the database kernel will use DNS to check the name of the host where the client is deployed. If the primary database node is configured with an incorrect or unreachable DNS server, the database connection will take a long time to set up. For details about this parameter, see the description of log_hostname in section “GUC Parameter Description > Error Reports and Logs > Log Content” in the Developer Guide.

  • The database kernel slowly runs the initialization statement.

    Problems are difficult to locate in this scenario. Try using the strace Linux trace command.

    strace gsql -U MyUserName -W MyPassWord -d postgres -h 127.0.0.1 -p 23508 -r -c '\q'
    

    The database connection process will be printed on the screen. If the following statement takes a long time to run:

    sendto(3, "Q\0\0\0\25SELECT VERSION()\0", 22, MSG_NOSIGNAL, NULL, 0) = 22
    poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
    

    It indicates that the SELECT VERSION() statement was run slowly.

    After the database is connected, you can run the explain performance select version() statement to find the reason why the initialization statement was run slowly. For more information, see “Performance Tuning > SQL Tuning Guide > SQL Execution Plan” in the Developer Guide.

    An uncommon scenario is that the disk of the machine where the primary database node resides is full or faulty, affecting queries and leading to user authentication failures. As a result, the connection process is suspended. To solve this problem, simply clear the data disk space of the primary database node.

  • TCP connection is set up slowly.

    Adapt the steps of troubleshooting slow initialization statement execution. Use strace. If the following statement is run slowly:

    connect(3, {sa_family=AF_FILE, path="/home/test/tmp/gaussdb_llt1/.s.PGSQL.61052"}, 110) = 0
    

    Or,

    connect(3, {sa_family=AF_INET, sin_port=htons(61052), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 EINPROGRESS (Operation now in progress)
    

    It indicates that the physical connection between the client and the database is set up slowly. In this case, check whether the network is unstable or has high throughput.

Problems in Setting Up Connections

  • gsql: could not connect to server: No route to host

    This problem occurs generally because an unreachable IP address or port number was specified. Check whether the values of -h and -p parameters are correct.

  • gsql: FATAL: Invalid username/password,login denied.

    This problem occurs generally because an incorrect username or password was entered. Contact the database administrator to check whether the username and password are correct.

  • gsql: FATAL: Forbid remote connection with trust method!

    For security purposes, remote login in trust mode is forbidden. In this case, you need to modify the connection authentication information in the pg_hba.conf file. For details, see “Database Security Management > Client Access Authentication > Configuration File Reference” in the Developer Guide.

    NOTE: Do not modify the configurations of the openGauss host in the pg_hba. conf file. Otherwise, the database may become faulty. It is recommended that service applications be deployed outside the openGauss.

  • If -h 127.0.0.1 is specified, the database connection is successful. If -h 127.0.0.1 is removed, the connection fails.

    Run the show unix_socket_directory SQL statement to check whether the Unix socket directory used by the primary database node is the same as that specified by $PGHOST in the shell directory.

    If they are different, set $PGHOST to the directory specified by unix_socket_directory.

    For more information about unix_socket_directory, see “GUC Parameter Description > Connection and Authentication > Connection Settings” in the Developer Guide.

  • The “libpq.so” loaded mismatch the version of gsql, please check it.

    This problem occurs because the version of libpq.so used in the environment does not match that of gsql. Run the ldd gsql command to check the version of the loaded libpq.so, and then load correct libpq.so by modifying the environment variable LD_LIBRARY_PATH.

  • gsql: symbol lookup error: xxx/gsql: undefined symbol: libpqVersionString

    This problem occurs because the version of libpq.so used in the environment does not match that of gsql (or the PostgreSQL libpq.so exists in the environment). Run the ldd gsql command to check the version of the loaded libpq.so, and then load correct libpq.so by modifying the environment variable LD_LIBRARY_PATH.

  • gsql: connect to server failed: Connection timed out

    Is the server running on host “xx.xxx.xxx.xxx” and accepting TCP/IP connections on port xxxx?

    This problem is caused by network connection faults. Check the network connection between the client and the database server. If you cannot ping from the client to the database server, the network connection is abnormal. Contact network management personnel for troubleshooting.

    ping -c 4 10.10.10.1
    PING 10.10.10.1 (10.10.10.1) 56(84) bytes of data.
    From 10.10.10.1: icmp_seq=2 Destination Host Unreachable
    From 10.10.10.1 icmp_seq=2 Destination Host Unreachable
    From 10.10.10.1 icmp_seq=3 Destination Host Unreachable
    From 10.10.10.1 icmp_seq=4 Destination Host Unreachable
    --- 10.10.10.1 ping statistics ---
    4 packets transmitted, 0 received, +4 errors, 100% packet loss, time 2999ms
    
  • gsql: FATAL: permission denied for database “postgres”

    DETAIL: User does not have CONNECT privilege.

    This problem occurs because the user does not have the permission to access the database. To solve this problem, perform the following steps:

    1. Connect to the database as the system administrator dbadmin.

      gsql -d postgres -U dbadmin -p 15400
      
    2. Grant the user with the permission to access the database.

      GRANT CONNECT ON DATABASE postgres TO user1;

      NOTE: Actually, some common misoperations may also cause a database connection failure, for example, entering an incorrect database name, username, or password. Misoperations are accompanied with an error information on the client tool.

      gsql -d postgres -p 15400
      gsql: FATAL:  database "postgres" does not exist
      gsql -d postgres -U user1 -W gauss@789 -p 15400
      gsql: FATAL:  Invalid username/password,login denied.
      
  • gsql: FATAL: sorry, too many clients already, active/non-active: 197/3.

    This problem occurs because the number of system connections exceeds the upper limit. Contact the database administrator to release unnecessary sessions.

    For details about how to view the number of user session connections, see Table 23.

    You can view the session status in the PG_STAT_ACTIVITY view. To release unnecessary sessions, use the pg_terminate_backend function.

    select datid,pid,state from pg_stat_activity;
    
     datid |       pid       | state  
    -------+-----------------+--------
     13205 | 139834762094352 | active
     13205 | 139834759993104 | idle
    (2 rows)
    

    The value of pid is the thread ID of the session. Terminate the session using its thread ID.

    SELECT PG_TERMINATE_BACKEND(139834759993104);
    

    If a command output similar to the following is displayed, the session is successfully terminated.

    PG_TERMINATE_BACKEND
    ----------------------
     t
    (1 row)
    

    Table 23 Viewing the number of session connections

    Description

    Command

    View the maximum number of sessions connected to a specific user.

    Run the following command to view the upper limit of the number of USER1's session connections. -1 indicates that no upper limit is set for the number of user1's session connections.

    SELECT ROLNAME,ROLCONNLIMIT FROM PG_ROLES WHERE ROLNAME='user1';
     rolname | rolconnlimit
    ---------+--------------
     user1    |           -1
    (1 row)

    View the maximum number of sessions connected to a specific database.

    Run the following command to view the upper limit of the number of postgres's session connections. -1 indicates that no upper limit is set for the number of postgres's session connections.

    SELECT DATNAME,DATCONNLIMIT FROM PG_DATABASE WHERE DATNAME='postgres';
    

    datname | datconnlimit ———-+————-- postgres | -1 (1 row)

    View the number of session connections that have been used by a specific database.

    Run the following command to view the number of session connections that have been used by postgres. 1 indicates the number of session connections that have been used by postgres.

    SELECT COUNT(*) FROM PG_STAT_ACTIVITY WHERE DATNAME='postgres';
     count 
    -------
         1
    (1 row)
  • gsql: wait xxx.xxx.xxx.xxx:xxxx timeout expired

    When gsql initiates a connection request to the database, a 5-minute timeout period is used. If the database cannot correctly authenticate the client request and client identity within this period, gsql will exit the connection process for the current session, and will report the above error.

    Generally, this problem is caused by the incorrect host and port (that is, the xxx part in the error information) specified by the -h and -p parameters. As a result, the communication fails. Occasionally, this problem is caused by network faults. To resolve this problem, check whether the host name and port number of the database are correct.

  • gsql: could not receive data from server: Connection reset by peer.

    Check whether primary database node logs contain information similar to “FATAL: cipher file “/data/dbnode/server.key.cipher” has group or world access”. This error is usually caused by tampering with the permissions for data directories or some key files by mistake. For details about how to correct the permissions, see related permissions for files on other normal instances.

  • gsql: FATAL: GSS authentication method is not allowed because XXXX user password is not disabled.

    In pg_hba.conf of the target primary database node, the authentication mode is set to gss for authenticating the IP address of the current client. However, this authentication algorithm cannot authenticate clients. Change the authentication algorithm to sha256 and try again. For details, see “Database Security Management > Client Access Authentication > Configuration File Reference” in the Developer Guide.

    NOTE:

    • Do not modify the configurations of the openGauss host in the pg_hba. conf file. Otherwise, the database may become faulty.
    • It is recommended that service applications be deployed outside the openGauss.

Other Faults

  • There is a core dump or abnormal exit due to the bus error.

    Generally, this problem is caused by changes in loading the shared dynamic library (.so file in Linux) during process running. Alternatively, if the process binary file changes, the execution code for the OS to load machines or the entry for loading a dependent library will change accordingly. In this case, the OS kills the process for protection purposes, generating a core dump file.

    To resolve this problem, try again. In addition, do not run service programs in openGauss during O&M operations, such as an upgrade, preventing such a problem caused by file replacement during the upgrade.

    NOTE: A possible stack of the core dump file contains dl_main and its function calling. The file is used by the OS to initialize a process and load the shared dynamic library. If the process has been initialized but the shared dynamic library has not been loaded, the process cannot be considered completely started.

Feedback
编组 3备份
    openGauss 2024-05-25 00:42:51
    cancel