Help Center/ GaussDB/ Tool Guide(Distributed_8.x)/ gsql/ Command Reference
Updated on 2024-06-07 GMT+08:00
View PDF
Share

Command Reference

For details about gsql parameters, see Table 1, Table 2, Table 3, and Table 4.

Table 1 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.

In addition, gsql allows you to use extended DBNAMEs, that is, connection strings in the format of 'postgres[ql]://[user[:password]@][netloc][:port][, ...][/dbname][?param1=value1&...]' or '[key=value] [...]' as DBNAMEs. gsql parses connection information from the connection strings and preferentially uses the information.

CAUTION:

When gsql uses the extended DBNAME to create a connection, the replication parameter cannot be specified.

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.

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 gsql 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 information and exits.

-
Table 2 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 queries 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 no match is found, Linux shell does not enter the gsql program until input is complete.
  • Stored procedures and functions cannot be decrypted and imported.

-

-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 a cluster to be connected when a two-phase transaction is being restored.

NOTE:

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

-

-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 a command, as a semicolon does.

-

-C, -C1, --enable-client-encryption=1

Enables the encrypted database function when the -C parameter is used to connect to a local or remote database. The encrypted equality query is supported.

-

-C3, --enable-client-encryption=3

Enables a memory decryption emergency channel when the -C parameter is used to connect to a local or a remote database. The encrypted equality query and the memory decryption emergency channel are supported.

_
Table 3 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 editing mode on the client.

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.

-

-2, --pipeline

Uses a pipe to transmit the password. This parameter cannot be used on devices and must be used together with the -c or -f parameter.

-
Table 4 Connection parameters

Parameter

Description

Value Range

-h, --host=HOSTNAME

Specifies the host name of the running server, the path of the Unix domain socket, or the domain name. You can specify multiple host addresses by using character strings separated by commas (,), or specify an IPv6 host address.

If multiple host addresses are specified, the faulty node is skipped by default and the first available primary node is automatically selected for connection. You can set the PGTARGETSESSIONATTRS environment variable to connect to different types of nodes. The mapping between variables and node types is as follows:

A value of the PGTARGETSESSIONATTRS environment variable indicates a type of node to be connected.

read-write: readable and writable node.

read-only: read-only node.

primary or not set: primary node.

standby: standby node.

prefer-standby: preferred standby node. If there is no standby node, any is used.

any: The role is not checked.

NOTE:

Primary CNs and DNs are primary nodes. Generally, DNs are read-only nodes. You can set PGTARGETSESSIONATTRS to read-write to connect to CNs.

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 configure one or more port numbers. When one port number is configured, all host addresses use the same port for connection. When multiple port numbers are configured, the sequence is the same as the host address sequence, and the number of port numbers must be the same as the number of host addresses. If they are different, an error is reported.

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

The default port number can be specified by using compilation parameters. If the port number is not specified, the default port number 5432 is used.

-U, --username=USERNAME

Specifies the user who 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 username.

String. The default user is the current user who operates the system.

-W, --password=PASSWORD

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

NOTE:
  • When you attempt to connect to the CN after you have logged in to the server where the CN resides, the trust connection is used by default, and this parameter does not need to be set.
  • 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.

String
Parent topic: gsql